Optimizing MySQL

Optimizing MySQL
mysql_fix_privilege_tables
mysqlcheck -o –all-databases

Open /etc/my.cnf
[mysqld]
max_connections=500
safe-show-database
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
key_buffer_size=256M
table_cache=150
thread_cache_size=200
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

Below are notes on some of the important variables, I took down while tuning the config file.

1. QUERY CACHE
query_cache_size:
* MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has
to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result
set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.

query_cache_limit : a total size of memory that server utilizes for caching results of queries

query_cache_type : 0 - Off
1 - Cache all query results except for those that begin with SELECT SQL_NO_CACHE
2 - Cache results only for queries that begin with SELECT SQL_CACHE.

2. key_buffer_size:
* The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster
the SQL command will finish and a result will be returned. Ideally, it will be large enough to contain
all the indexes (the total size of all .MYI files on the server).
Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common.

The Key_reads/Key_read_requests ratio < 0.01
The Key_writes/Key_write_requests ratio ~ 1

SHOW STATUS;

3. LOG
log : Whether logging of all statements to the general query log is enabled. See Section 5.3.2, “The General Query Log”.
log_error : The location of the error log. This variable was added in MySQL 4.0.10.
log_slow_queries : Whether slow queries should be logged. “Slow” is determined by the value of the long_query_time
variable.

4. table_cache:
* The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many
tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at
one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the
same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase
the table_cache if you have enough memory.

check for Open_tables
SHOW STATUS;

5. sort_buffer_size: Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster
ORDER BY or GROUP BY operations.

6. read_rnd_buffer_size:
* The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with
ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is
allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as
the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine
with 1GB memory.

7. thread_cache_size:
* If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the
Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.

8. tmp_table_size:
* “Created_tmp_disk_tables” are the number of implicit temporary tables on disk created while executing statements
and “created_tmp_tables” are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.

Increase the value of tmp_table_size if you do many advanced GROUP BY queries and you have lots of memory.
This variable does not apply to user-created MEMORY tables.

9. innodb_buffer_pool_size
While the key_buffer_size is the variable to target for MyISAM tables, for InnoDB tables, it is innodb_buffer_pool_size.
Again, you want this as high as possible to minimize slow disk usage. On a dedicated MySQL server running InnoDB tables,
you can set this up to 80% of the total available memory.

10. innodb_additional_mem_pool_size
This variable stores the internal data structure. Make sure it is big enough to store data about all your InnoDB tables
(you will see warnings in the error log if the server is using OS memory instead).

11. max_connections

12. wait_timeout=500
This variable determines the timeout in seconds before mysql will dump a connection. If set to low
you will likely receive mySQL server has gone away errors in your log, which in vBulletins case is quite common.

13. max_allowed_packet
The maximum size of one packet or any generated/intermediate string.
Again if set to low (the default is 8M) users will likely experience errors. 16M has always
worked fine for my production environments.

You can grab a mySQL performance script from the guys at hackmysql.com( http://hackmysql.com/mysqlreport ). I use it to
tell me how the database is performing under load. You can run this from any shell when you are loaded with traffic.
Nothing fancy but should give you an idea.

==================================
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html