Ronald Bradford
MySQL Expert

MySQL Expert Ronald Bradford shares valuable input in MySQL Performance Tuning, MySQL Scalability and general MySQL Help from his two decades of working with MySQL, Oracle, Ingres and development technologies.

Posts Tagged ‘configuration’

Be sure to know your my.cnf [sections]

Tuesday, January 26th, 2010

The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.

[mysqld]
...
log-bin=mysql-bin
server-id=1
query_cache_size = 100M
query_cache_type = 1

...

[mysqld_safe]
...
key_buffer_size=600M
skip-innodb
...

In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.

mysql> show global variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+

Be sure to add the right options to the [mysqld] section.

What I didn’t know until yesterday was that some programs read from multiple groups. From the 5.1.2. Server Command Options MySQL reference manual page. In helping the describe the problem for the benefit of readers I actually learned something new myself.


mysqld reads options from the [mysqld] and [server] groups. mysqld_safe reads options from the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups. mysql.server reads options from the [mysqld] and [mysql.server] groups.

I have for example always put log-error in both the [mysqld_safe] and [mysql]d sections because both of these write different errors. Seems that is unnecessary.

Tags: , ,
Posted in Databases, MySQL, Professional, Uncategorized | 3 Comments »

Have you checked your MySQL error log today?

Thursday, August 20th, 2009

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.

Tags: , ,
Posted in Databases, MySQL, Open Source, Professional | 1 Comment »