Tuning MySQL

Update - New Innodb tuning section added below.  See also Common MySQL Queries.

If tmp_table_size is not equal to max_heap_table_size then change them to be the same.

If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause
temporary tables to be memory-based instead of disk-based.

select @@global.tmp_table_size, @@global.max_heap_table_size

If opened tables is big, your table cache value is probably too small (defalt 64).

# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512

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

Turn on the query_cache_size, default is zero which is off.

Some dynamic commands for tuning:

mysql> SET GLOBAL query_cache_size = 8192000;
mysql> SET GLOBAL query_cache_limit = 102400;
mysql> SHOW VARIABLES LIKE 'query_cache_size';
mysql> SHOW STATUS LIKE 'Qcache%';

Change Settings for Glogal (all but current session)

 

mysql> SET global max_heap_table_size=524288000;
mysql> SET global tmp_table_size=524288000;
Change Settings for current session
mysql> SET max_heap_table_size=524288000;
mysql> SET tmp_table_size=524288000;

Modify the contents of the /etc/my.cnf file under the [mysqld] section.

Here are my.cnf values I use for 100 active concurrent users.

tmp_table_size=524288000
max_heap_table_size=524288000
table_cache=768
set-variable=max_connections=300
wait_timeout=7800
query_cache_size = 12582912
query_cache_limit=80000
thread_cache_size = 4
join_buffer_size = 128K

You can also change some of these from the command prompt:

mysql> Set global thread_cache_size = 4;
mysql> Set global query_cache_size = 64*1024*1024;
mysql> Set global query_cache_limit=768*1024;
mysql> Set global query_cache_min_res_unit = 2048;

mysql> Set global query_cache_type=1;
mysql> Set long_query_time = 5;
mysql> Set log-slow-queries=/var/log/mysql/log-slow-queries.log;
mysql> Set global wait_timeout = 7800;
mysql> Set global key_buffer_size = 10*1024*1024;
mysql> Set global table_cache = 800;

More Tips for Query Cache

To show current values

1. To see VARIABLES:

mysql > show variables;

or from command line :

# mysqladmin variables

2. To see PROCESS / STATUS

mysql> show status;

or from command line:

# mysqladmin –i10 processlist extended-status

To clear all stats

      mysql> flush status;

      Will clear your status and start logging with fresh data

Optimize all tables in a list of databases

mysqlcheck -u root -optimize --databases drupal mediwiki gallery2 wordpress -p

How to get and use MySQLTuner

wget mysqltuner.pl
chmod 0700 mysqltuner.pl
./mysqltuner.pl

Innodb Tuning

Now that Drupal 7 (D7) joins mediawiki and gallery2 in using the Innodb database engine if you use any of these you must consider tuning it too and not just myISAM, or these products will run slower than they should.
Edit /etc/my.cnf and for a small D7 site start by adding the following lines under the [mysqld] section.
# innodb specific
# innodb_buffer_pool_size 50% of memory on 5GB+ server. Set it to be larger than your data size or 500M on 4G box.
innodb_buffer_pool_size=20M
# innodb_log_file_size This depends on your recovery speed needs but 256M seems to be good performance
# innodb_log_buffer_size=4M 4M is good for most large servers
innodb_log_buffer_size = 2M
# innodb_flush_log_at_trx_commit=2 (1=flush log to disk every transaction, 2=flush to OS Cache, 0=don't flush)
innodb_flush_log_at_trx_commit = 2

Solving INFORMATION_SCHEMA slowness

Many of us find INFORMATION_SCHEMA painfully slow to work it when it comes to retrieving table meta data.  Besides being just slow accessing information_schema can often impact server performance
dramatically. The cause of majority of this slowness is not opening and closing tables, which can be solved with decent table cache size, and which is very fast for Innodb but by the fact MySQL by default looks to refresh Innodb statistics each time table is queried from information schema.

The solution is simple, just set global innodb_stats_on_metadata=0 which will prevent statistic update when you query information_schema. 

 

More on Tuning MySQL

  • For a general overview read High Performance MySQL especially the InnoDB performance basics article.
  • Start with an appropriate MySQL option file. For servers with at least 2GB RAM dedicated to MySQL we recommend my-huge.cnf. For servers, with a lot of writes we recommend my-innodb.cnf instead of the default MyISAM engine type.
  • To reduce the overhead of creating connnections we recommend using persistent DB connections.
  • If a query is called at least twice with no modifications to the queried tables a significant performance improvement can be gained by avoiding the processing of the query and the execution of the query by reading the query from the MySQL query cache. To learn how to set up the query cache read a practical set-up.
  • Be sure to have enough cached threads or you will launch too many new threads as described in this story about a Yahoo site.
  • The biggest performance boosts can come from identifying and tuning the slowest queries using the MySQL slow query logs.
  • You can use the DB Maintenance module or use the mysqlcheck commands below in a cronjob.
    echo "OPTIMIZE TABLE accesslog,cache,comments,node,users,watchdog;FLUSH TABLES;" |mysql -u user -ppasswd

    If you have complete control of the datbase server you can use:
    mysqlcheck -o -A -p
  • MySQL supports many different engine types including MyISAM, InnoDB, and Archive(MySQL 5). Performance sites should use InnoDB for most tables particularly tables such as node that get a lot of writes and reads. MyISAM exclusive table locks for updates before selects versus InnoDB's row level locking can mean MyISAM blocks reads if there are many writes. Convert from MyISAM to InnoDB.
  • In MySQL 5 a new table type called the archive table type was introduced to deal with common requirements for web applications like access logs where only INSERTS and SELECTS were done. If tables such as the Access Log table are determined to not have DELETE, UPDATE, and REPLACE then this MySQL engine type can often offer significant performance improvements as they have done for sites like Slashdot, Yahoo, and Live Journal.

 

Innodb_file_per_table

The innodb_file_per_table configuration option, and is the default in MySQL 5.6.6 and higher.  InnoDB's file-per-table mode is a more flexible alternative, where you store each InnoDB table and its indexes in a separate file.

To see the status run:

SHOW VARIABLES LIKE '%innodb%file%';

SELECT ENGINE, COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','test') GROUP BY 1;

With innodb_file_per_table, there should be a better control over the individual tables; like backup each table separately. However, the claim for better performance is questionable.

External Links

Subject

this new value mysql> Set global thread_cache_size = 4; will be also available after mysql restart? Thank you!

In reply to by Anonymous (not verified)

Changes from the command prompt will be lost when mysql is restarted; however, if you add those commands to your /ect/my.cnf they will be read each time mysql is started.