Mysql用gettimeofday杀死CPU

Question

我对mysql在Debian压缩64上杀死CPU有问题。这是VPS上的一台开发机器，所以我停止了所有其他服务，包括apache2。mysql版本为5.1.49。这是mysql启动时的日志：

Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: 130206 19:03:40 [Note] Plugin 'FEDERATED' is disabled.
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: InnoDB: 1 transaction(s) which must be rolled back or cleaned up
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: InnoDB: in total 1 row operations to undo
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: InnoDB: Trx id counter is 0 31809536
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: 130206 19:03:40  InnoDB: Started; log sequence number 2 892018402
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: 130206 19:03:40 [Note] Event Scheduler: Loaded 0 events
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: 130206 19:03:40 [Note] /usr/sbin/mysqld: ready for connections.
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: Version: '5.1.49-3-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: InnoDB: Starting in background the rollback of uncommitted transactions
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: InnoDB: Cleaning up trx with id 0 2218455
Feb  6 19:03:40 Debian-60-squeeze-64-LAMP mysqld: 130206 19:03:40  InnoDB: Rollback of non-prepared transactions completed
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4616]: Upgrading MySQL tables if necessary.
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4619]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4619]: Looking for 'mysql' as: /usr/bin/mysql
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4619]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4619]: This installation of MySQL is already upgraded to 5.1.49, use --force if you still need to run mysql_upgrade
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4626]: Checking for insecure root accounts.
Feb  6 19:03:41 Debian-60-squeeze-64-LAMP /etc/mysql/debian-start[4630]: Triggering myisam-recover for all MyISAM tables

我一启动mysql，CPU就会飙升，即使没有查询正在运行。这是/etc/init.d/mysql状态的输出：

Server version          5.1.49-3-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 29 min 38 sec

Threads: 1  Questions: 955  Slow queries: 0  Opens: 5512  Flush tables: 1  Open tables: 32  Queries per second avg: 0.537.

使用使用100% CPU的mysql pid的strace，我只需1到2分钟就可以得到这样的结果：

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
90.89  126.423901      179579       704           select
4.01    5.572348     2786174         2           rt_sigtimedwait
2.99    4.164260      118979        35         1 futex
2.11    2.929960           1   3471808           gettimeofday
0.00    0.000000           0         3         1 read
0.00    0.000000           0         3           write
0.00    0.000000           0         1           close
0.00    0.000000           0         4           rt_sigprocmask
0.00    0.000000           0         1         1 access
0.00    0.000000           0         6           sched_yield
0.00    0.000000           0         1           alarm
0.00    0.000000           0         1           accept
0.00    0.000000           0         1           shutdown
0.00    0.000000           0         1           getsockname
0.00    0.000000           0         2         1 setsockopt
0.00    0.000000           0         7           fcntl
0.00    0.000000           0         1           tgkill
------ ----------- ----------- --------- --------- ----------------
100.00  139.090469               3472581         4 total

实际的电话看起来如下：

19:37:26.553922 gettimeofday({1360175846, 553939}, NULL) = 0 <0.000004>
19:37:26.622537 gettimeofday({1360175846, 622591}, NULL) = 0 <0.000011>
19:37:26.622659 gettimeofday({1360175846, 622679}, NULL) = 0 <0.000009>
19:37:26.622737 gettimeofday({1360175846, 622754}, NULL) = 0 <0.000009>
19:37:26.622812 gettimeofday({1360175846, 622829}, NULL) = 0 <0.000008>
19:37:26.622887 gettimeofday({1360175846, 622951}, NULL) = 0 <0.000010>
19:37:26.623010 gettimeofday({1360175846, 623028}, NULL) = 0 <0.000008>
19:37:26.623109 gettimeofday({1360175846, 623132}, NULL) = 0 <0.000009>

我认为3471808次调用gettimeofday是问题所在，但是如何解决它呢？每当我启动mysql时，我都会尝试重新启动服务器。

谢谢！

应要求提供更多信息：

显示程序输出

mysql> SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 325 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

顶级-H的输出：

top - 21:21:26 up  5:35,  2 users,  load average: 1.07, 1.02, 1.00
Tasks: 152 total,   2 running, 150 sleeping,   0 stopped,   0 zombie
Cpu(s): 96.2%us,  1.9%sy,  0.0%ni,  0.0%id,  0.0%wa,  1.9%hi,  0.0%si,  0.0%st
Mem:   2061536k total,   973540k used,  1087996k free,    44952k buffers
Swap:  2102552k total,        0k used,  2102552k free,   693716k cached

Answer 1

我想不出任何其他的东西，所以最后我不得不重新安装mysql服务器，它解决了这个问题，在从备份恢复数据库之后，现在一切都运行得很顺利。

Answer 2

我有一个有点类似的问题，在Debian (32位)上的MySQL 5.1有时会被100%的CPU占用(并不是所有的时间)，但是没有足够的时间来诊断它，因为这是在关键的截止日期之前的几天。

详细信息

我发现，在MySQL中有几种不同的解决高CPU问题的方法。

最简单的再现方法是运行一个特定的Django管理视图(标准管理UI页面)，它连接几个表并返回几千行--这将一个线程可靠地放入99%的CPU中。杀了那条线就阻止了这个问题。

mysql> show processlist;
+----+------------+-----------+-----------+---------+------+------------+-------------------------------------------------------------------------------------------------------+
| Id | User       | Host      | db        | Command | Time | State      | Info                                                                                                    |
+----+------------+-----------+-----------+---------+------+------------+-----------------------------------------------------------------------------------------------------+
| 68 | djangouser | localhost | django_db | Query   |   77 | statistics | SELECT `mytable`.`id`, `mytable`.`tenant_id`, `mytable |
| 69 | djangouser | localhost | django_db | Query   |    0 | NULL       | show processlist                                                                                     |
+----+------------+-----------+-----------+---------+------+------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

相关

https://groups.google.com/forum/?fromgroups=#!topic/django-users/Iz6x7c0i9nI非常类似的情况下，挂起发生在Django queryset。

有些类似的情况：

https://dba.stackexchange.com/questions/24643/mysql-5-5-runs-out-of-memory-drops-all-connections-when-creating-many-databases dba.stackexchange -在创建2,000至5,000个数据库后，MySQL删除了所有连接

https://groups.google.com/forum/?fromgroups=#!topic/django-users/sU-zj7s8uU4 -不终止查询优化器对某些Django管理查询，由于20个内部联接！Fix将optimizer_search_depth设置为3(默认为62)

我的解决方案-切换到PostgreSQL

Django使仅通过配置切换到PostgreSQL变得非常容易，加上安装和配置PostgreSQL的时间--我意识到这对您来说可能不是一个选项，但是如果您的语言/框架使切换变得容易，那么请认真考虑它。我使用了Debian6.0中默认的postgres包，它们很好--或者您可以从Postgres项目中为Debian使用9.1或9.2包，这些包可能更好，而且更新。

尽管以前没有使用过PostgreSQL，但是切换只花了几个小时，并且没有创建新的问题就解决了这个问题。而且PostgreSQL还有许多其他好的特性，所以我现在很高兴我切换了。

在此之前，我对MySQL和PostgreSQL没有很强的看法，但现在我只使用后者。

Answer 3

我也有过同样的问题。它是由一个错误引起的，而不是：

innodb_buffer_pool_size = 256M

我写

innodb_buffer_pool_size = 256M