User Tools

Site Tools


database:mysql:performance

Optimizing performance

Optimize Indexes and Tables

Regularly check all tables to prevent fragmentation:

mysqlcheck --optimize -A
run the mysqlcheck tool within a regular crontask, i.e. as you do you backup jobs.

Example mysqld configuration

Server:

  • RHEL 5.3 64bit
  • 1 Dual Core CPU (Intel(R) Xeon(R) CPU 5160 @ 3.00GHz)
  • 16 G of RAM
  • mysql-5.0.45-7.el5
[mysqld]
default-character-set           = utf8
 
# MySQL paths
datadir                         = /var/lib/mysql
socket                          = /var/lib/mysql/mysql.sock
skip-bdb        # deactivate the Berkely engine
thread_cache                    = 40
 
# Connections
max_connections                 = 250
max_user_connections            = 220
max_allowed_packet              = 4M
wait_timeout                    = 1800
max_connect_errors              = 100
 
### Troubleshooting
log-slow-queries                = slow-queries.log      # log every query exceeding log_query_time below
long_query_time                 = 4     # queries taking longer then 4 sec are logged
#log-queries-not-using-indexes  # logs of every unindexed query
#log-bin                        = /data/binary_logs/node300     # space and I/O HOG
#max_binlog_size                = 50M   # log file size
#expire_logs_days               = 2
 
# Optimizing MyISAM
table_cache                     = 32M
tmp_table_size                  = 2G
max_heap_table_size             = 2G
sort_buffer_size                = 32M
key_buffer_size                 = 256M
query_cache_type                = 1
query_cache_limit               = 8M
query_cache_size                = 512M
join_buffer_size                = 3M
 
# Optimizing InnoDB
innodb_buffer_pool_size         = 1G
innodb_additional_mem_pool_size = 96M
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 0
innodb_thread_concurrency       = 2     # should match number of processors
 
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords                   = 1
 
[mysql.server]
user                            = mysql
basedir                         = /var/lib
 
[mysqld_safe]
log-error                       = /var/log/mysqld.log
pid-file                        = /var/run/mysqld/mysqld.pid
 
[client]
default-character-set           = utf8
/srv/wiki.niwos.com/data/pages/database/mysql/performance.txt · Last modified: 2011/07/05 13:25 (external edit)