<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body text="#000000" bgcolor="#ffffff">
Package: mysql-server-5.1
<br>
Version: 5.1.58-1
<br>
Severity: wishlist
<br>
<br>
Hi,<br>
I'm opening a ticket about a general problem, but this is food for
thought too. Please apologize and triage as you wish.<br>
<br>
I am a web developer working on a web application which has a large
schema. There are currently over 200 tables in the schema, all of
them MyISAM.<br>
Every few weeks, I'll install a new version of the application and
create a new MySQL database for it.<br>
<br>
My development desktop was installed in April 2010, and has been
getting slower and slower to boot, to the point where I'm thinking
about reinstalling since months. There is a lot of disk read during
boot, and I never knew precisely why. Today I realized by accident
that mysqld was taking about 10% CPU for a very long time during
boot, and went to investigate why.<br>
<br>
Restarting mysql seems to happen fast (less than 5 seconds), but it
was in fact causing significant CPU usage for a long time. I found
that this CPU usage happened every time mysql was started, and then
that this came from /etc/mysql/debian-start. I then discovered it
came from check_for_crashed_tables(). The function contains:<br>
<br>
<blockquote type="cite"> LC_ALL=C $MYSQL --skip-column-names
--batch -e '<br>
select concat('\''select count(*) into @discard from `'\'',<br>
TABLE_SCHEMA, '\''`.`'\'', TABLE_NAME,
'\''`'\'') <br>
from information_schema.TABLES where ENGINE='\''MyISAM'\' |
\<br>
xargs -i $MYSQL --skip-column-names --silent --batch \<br>
--force -e "{}" >$tempfile <br>
</blockquote>
<br>
The function has the following description:<br>
<br>
<blockquote type="cite">## Check all unclosed tables.<br>
# - Requires the server to be up.<br>
# - Is supposed to run silently in background. <br>
</blockquote>
<br>
It turns out that each table check takes a significant time, and
when you have many MyISAM tables, this can take a very long time.
There are 7119 tables on my machine. Checking a small schema, like
phpmyadmin's, takes no time:<br>
<blockquote type="cite"># time LC_ALL=C /usr/bin/mysql
--defaults-file=/etc/mysql/debian.cnf --skip-column-names --batch
-e 'select concat('\''select count(*) into @discard from
`'\'',TABLE_SCHEMA, '\''`.`'\'', TABLE_NAME, '\''`'\'') from
information_schema.TABLES where TABLE_SCHEMA='\''phpmyadmin'\''
AND ENGINE='\''MyISAM'\' | xargs -i /usr/bin/mysql
--defaults-file=/etc/mysql/debian.cnf --skip-column-names --silent
--batch
<br>
<br>
real
0m0.122s &nbs
p;
<br>
user
0m0.052s &nbs
p;
<br>
sys
0m0.040s &nbs
p;
<br>
root@vinci:/var/log#</blockquote>
<br>
<br>
Checking a large schema, like my application's, takes a noticeable
time:<br>
<br>
<blockquote type="cite"># time LC_ALL=C /usr/bin/mysql
--defaults-file=/etc/mysql/debian.cnf --skip-column-names --batch
-e 'select concat('\''select count(*) into @discard from
`'\'',TABLE_SCHEMA, '\''`.`'\'', TABLE_NAME, '\''`'\'') from
information_schema.TABLES where TABLE_SCHEMA='\''tiki_8.0.beta'\''
AND ENGINE='\''MyISAM'\' | xargs -i /usr/bin/mysql
--defaults-file=/etc/mysql/debian.cnf --skip-column-names --silent
--batch<br>
<br>
real 0m2.784s<br>
user 0m1.256s<br>
sys 0m0.708s<br>
root@vinci:/var/log#</blockquote>
<br>
Checking several large schemas, as on my machine, takes a pretty
long time:<br>
<br>
<blockquote type="cite"># time LC_ALL=C /usr/bin/mysql
--defaults-file=/etc/mysql/debian.cnf --skip-column-names --batch
-e 'select concat('\''select count(*) into @discard from
`'\'',TABLE_SCHEMA, '\''`.`'\'', TABLE_NAME, '\''`'\'') from
information_schema.TABLES where ENGINE='\''MyISAM'\' | xargs
-i /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf
--skip-column-names --silent --batch<br>
<br>
real 1m2.646s<br>
user 0m27.362s<br>
sys 0m16.145s<br>
root@vinci:/var/log#</blockquote>
<br>
Note that the databases are not big, they don't take 200 MB in
total. It's just checking any table seems to take a significant
time. The screenshot attached shows how the databases look.<br>
<br>
So, my first question is whether this check can be optimized. The
last command suggests checking a table takes on average more than
1/200 of a second of CPU time.<br>
<br>
If not, I'm wondering if something could be done to alleviate the
problem. I'm annoyed by 2 things:<br>
<ul>
<li>This check is not needed for me. Of course, I'm on a
development machine...</li>
<li>It took me a lot of time to disable the check.<br>
</li>
</ul>
On the first point, if I boot Debian 5 times today, MySQL will spend
some 5 minutes checking tables, even if no queries were run on
MySQL. This seems like a waste. Couldn't this check work in a way
similar to fsck and only check after either a given time or number
of startups (mounts) limit is reached?<br>
<br>
If that is not possible, then I hope we could at least make it
easier for people to workaround. Once you understand what's wrong,
fixing takes no time, you just do a one character change to
/etc/mysql/debian-start (commenting line 29
"check_for_crashed_tables;"). There is even a comment that hints to
this problem in the file:<br>
<br>
<blockquote type="cite"># If you want to disable the check for
crashed tables comment<br>
# "check_for_crashed_tables" out.<br>
</blockquote>
<br>
The problem is that one needs to find the problem first. There are 2
issues here: First, it took me a long time (I guess a year) to
realize that MySQL was causing such a CPU use. Then, it took me a
considerable time (roughly an hour) to figure out why.<br>
<br>
On the second point, I started with /etc/init.d/mysql, then went to
/etc/mysql/debian-start and then went to
/usr/share/mysql/debian-start.inc.sh. I missed the comment hinting
about this issue in /etc/mysql/debian-start. That comment is
embedded in a paragraph, and not particularly obvious. So I went to
read check_for_crashed_tables(), which is not too clear (a call to
xargs and a check done implicitly by a read that relies on a
configuration value of myisam-recover...).<br>
<br>
It would be nice if there could be something right in
/etc/init.d/mysql to tell about this issue. Perhaps a flag in an
/etc/default/mysql.<br>
<br>
It would be even better if the issue was obvious before even looking
at code. But the scripts don't make it obvious. Starting mysql seems
to happen very quickly:<br>
<br>
<blockquote type="cite"># time invoke-rc.d mysql start<br>
Starting MySQL database server: mysqld.<br>
Checking for tables which need an upgrade, are corrupt or were <br>
not closed cleanly..<br>
<br>
real 0m1.177s<br>
user 0m0.048s<br>
sys 0m0.064s<br>
root@vinci:/var/log#</blockquote>
<br>
If you then look at logs, there is no hint that the check too that
long:<br>
<br>
<blockquote type="cite">Oct 27 16:24:56 vinci mysqld_safe: Starting
mysqld daemon with databases from /var/lib/mysql<br>
Oct 27 16:24:56 vinci mysqld: 111027 16:24:56 [Note] Plugin
'FEDERATED' is disabled.<br>
Oct 27 16:24:56 vinci mysqld: 111027 16:24:56 InnoDB:
Initializing buffer pool, size = 8.0M<br>
Oct 27 16:24:56 vinci mysqld: 111027 16:24:56 InnoDB: Completed
initialization of buffer pool<br>
Oct 27 16:24:56 vinci mysqld: 111027 16:24:56 InnoDB: Started;
log sequence number 0 1275545<br>
Oct 27 16:24:56 vinci mysqld: 111027 16:24:56 [Note] Event
Scheduler: Loaded 0 events<br>
Oct 27 16:24:56 vinci mysqld: 111027 16:24:56 [Note]
/usr/sbin/mysqld: ready for connections.<br>
Oct 27 16:24:56 vinci mysqld: Version: '5.1.58-1' socket:
'/var/run/mysqld/mysqld.sock' port: 3306 (Debian)<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10974]: Upgrading
MySQL tables if necessary.<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10977]:
/usr/bin/mysql_upgrade: the '--basedir' option is always ignored<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10977]: Looking for
'mysql' as: /usr/bin/mysql<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10977]: Looking for
'mysqlcheck' as: /usr/bin/mysqlcheck<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10977]: This
installation of MySQL is already upgraded to 5.1.58, use --force
if you still need to run mysql_upgrade<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10984]: Checking for
insecure root accounts.<br>
Oct 27 16:24:57 vinci /etc/mysql/debian-start[10988]: Triggering
myisam-recover for all MyISAM tables</blockquote>
<br>
This all makes it look as if mysql was done starting after 2
seconds. check_for_crashed_tables() logs "Triggering myisam-recover
for all MyISAM tables" before starting the check, but it doesn't log
when it's done. As it is the last startup step, there is no hint
that the whole startup actually took a lot longer.<br>
<br>
Back to the first problem (not realizing that MySQL is slowing down
the boot), I regularly check how the boot goes, but I never noticed
anything, since the init script returns so fast when it starts. This
happens because the big tasks are run in the background. This is a
deliberate choice, as evidenced by these comments:<br>
<br>
<blockquote type="cite"># - Is supposed to run silently in
background. <br>
</blockquote>
<blockquote type="cite"> # But do it in the background to not stall
the boot process.</blockquote>
<blockquote type="cite"># The following commands should be run when
the server is up but in background<br>
# where they do not block the server start and in one shell
instance so that<br>
# they run sequentially.</blockquote>
<br>
This was probably a great decision when it was made, as the check
seems to be CPU-intensive but very low on I/O, while the rest of the
boot is rather to the opposite, so this allowed a shorter total boot
time. However, now that we have a parallel boot sequence,
backgrounding processes shouldn't be needed to optimize CPU use. I
think it's time to reconsider whether backgrounding is warranted.<br>
<br>
In the end, MySQL probably contributed little to the heavy I/O when
I boot, but I'm happy I found this problem anyway.<br>
<br>
--- System information. ---
<br>
Architecture: i386
<br>
Kernel: Linux 3.0.0-2-amd64
<br>
<br>
Debian Release: wheezy/sid
<br>
990 testing security.debian.org <br>
990 testing <a class="moz-txt-link-abbreviated" href="ftp://ftp.ca.debian.org">ftp.ca.debian.org</a> <br>
500 unstable <a class="moz-txt-link-abbreviated" href="ftp://ftp.ca.debian.org">ftp.ca.debian.org</a> <br>
1 experimental <a class="moz-txt-link-abbreviated" href="ftp://ftp.ca.debian.org">ftp.ca.debian.org</a> <br>
<br>
--- Package information. ---
<br>
Depends (Version) | Installed
<br>
=======================================-+-==============
<br>
mysql-client-5.1 (>= 5.1.58-1) | 5.1.58-1
<br>
libdbi-perl | 1.616-1+b1
<br>
perl (>= 5.6) | 5.12.4-6
<br>
libc6 (>= 2.7) | 2.13-21
<br>
libgcc1 (>= 1:4.1.1) | 1:4.6.1-15
<br>
libmysqlclient16 (>= 5.1.50-1) | 5.1.58-1
<br>
libstdc++6 (>= 4.1.1) | 4.6.1-15
<br>
zlib1g (>= 1:1.1.4) | 1:1.2.3.4.dfsg-3
<br>
debconf (>= 0.5) | 1.5.40
<br>
OR debconf-2.0 | <br>
psmisc | 22.13-1
<br>
passwd | 1:4.1.4.2+svn3283-3
<br>
lsb-base (>= 3.0-10) | 3.2-28
<br>
mysql-server-core-5.1 (= 5.1.58-1) | 5.1.58-1
<br>
<br>
<br>
Recommends (Version) | Installed
<br>
====================================-+-===========
<br>
mailx | <br>
libhtml-template-perl | 2.10-1
<br>
<br>
<br>
Suggests (Version) | Installed
<br>
=======================-+-===========
<br>
tinyca | <br>
<br>
<br>
<br>
<br>
</body>
</html>