As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”
Today’s special found in a 13GB MySQL server error log.
090819 22:49:37InnoDB: Warning: difficult to find free blocks from InnoDB: the buffer pool (1101071 search iterations)! Consider InnoDB: increasing the buffer pool size. InnoDB: It is also possible that in your Unix version InnoDB: fsync is very slow, or completely frozen inside InnoDB: the OS kernel. Then upgrading to a newer version InnoDB: of your operating system may help. Look at the InnoDB: number of fsyncs in diagnostic info below. InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs InnoDB: Starting InnoDB Monitor to print further InnoDB: diagnostics to the standard output. 090819 22:49:37InnoDB: Warning: difficult to find free blocks from InnoDB: the buffer pool (1101051 search iterations)! Consider InnoDB: increasing the buffer pool size. InnoDB: It is also possible that in your Unix version InnoDB: fsync is very slow, or completely frozen inside InnoDB: the OS kernel. Then upgrading to a newer version InnoDB: of your operating system may help. Look at the InnoDB: number of fsyncs in diagnostic info below. InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs InnoDB: Starting InnoDB Monitor to print further InnoDB: diagnostics to the standard output. 090819 22:49:37InnoDB: Warning: difficult to find free blocks from InnoDB: the buffer pool (1101072 search iterations)! Consider InnoDB: increasing the buffer pool size. InnoDB: It is also possible that in your Unix version InnoDB: fsync is very slow, or completely frozen inside InnoDB: the OS kernel. Then upgrading to a newer version InnoDB: of your operating system may help. Look at the InnoDB: number of fsyncs in diagnostic info below. InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs InnoDB: Starting InnoDB Monitor to print further InnoDB: diagnostics to the standard output.
As you can see the same error is occuring, and in this example 3 times in the last second. To find the cause of the error I didn’t have to look far because I had already checked the /etc/my.cnf file.
$ cat /etc/my.cnf [mysqld] set-variable = max_connections=500 innodb_buffer_pool_size = 500 safe-show-database
Where do people come up with these my.cnf files? A decision was made to create one, and not use either no file or at least the default that was provided with the installation that is still on the system.
$ find / -name *my*cnf /home/dontcold/.my.cnf /etc/my.cnf /root/.my.cnf /usr/local/cpanel/whostmgr/my.cnf /usr/share/doc/MySQL-server-5.0.77/my-small.cnf /usr/share/doc/MySQL-server-5.0.77/my-medium.cnf /usr/share/doc/MySQL-server-5.0.77/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-5.0.77/my-large.cnf /usr/share/doc/MySQL-server-5.0.77/my-huge.cnf /usr/share/mysql/my-small.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-huge.cnf
What is interesting is that I’ve seen nearly the same file on a previous installation and I documented in For MySQL DBA fame and glory. Prize included .