Mysql使用gettimeofday查杀CPU

我有一个问题，在Debian Squeeze 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时都会发生这种情况，我甚至尝试重新启动服务器。

谢谢！

其他信息请求：

SHOW PROCESSLIST的输出

 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)

顶部输出：

 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

我不能拿出任何其他东西，所以最后我不得不重新安装MySQL服务器，照顾这个问题，从备份恢复数据库现在一切顺利运行。

我有一个类似的问题，其中Debian Squeeze（32位）上的MySQL 5.1在有时（不是全部）时间达到了100％的CPU，但没有足够的时间来诊断它，因为在几天之前关键的最后期限。

细节

我发现有几种不同的方式来解决MySQL中的高CPU问题。

最简单的重现方法是运行一个特定的Djangopipe理视图（标准pipe理用户界面页面），它连接几个表并返回几千行 – 这将一个线程可靠地放到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 – MySQL删除所有连接后创build2,000到5,000个数据库

https://groups.google.com/forum/?fromgroups=#!topic/django-users/sU-zj7s8uU4 – 由于有20个内部联接，所以在某些Djangopipe理查询上无终止查询优化器！解决方法是将optimizer_search_depth设置为3（默认为62）

我的解决scheme – 切换到PostgreSQL

Django非常容易通过configuration切换到PostgreSQL，加上安装和configurationPostgreSQL的时间 – 我意识到这可能不是您的select，但是如果您的语言/框架可以轻松切换，请认真考虑。我在Debian 6.0 Squeeze中使用了默认的postgres软件包，或者你可以在Postgres项目中使用Debian的9.1或9.2软件包，这可能会更好，而且更新。

切换只花了几个小时，尽pipe之前没有使用PostgreSQL，并且摆脱了这个问题而没有创build新的。而且PostgreSQL还有很多其他很好的function，所以我现在非常高兴我换了。

在这之前，我对MySQL和PostgreSQL没有太多的看法，但是现在我只能使用后者。

我遇到过同样的问题。这是由错字造成的，而不是：

 innodb_buffer_pool_size = 256M

我写了

 innodb_buffer_pool_size = 256M