By Luis Freitas | 1 comment1
This week i had a urgent job about recovering a huge Innodb Mysql database with 2.4 million records and 2GB in size and with 160GB on 107 418 Jpeg files with IPTC and EXIF info associated with. I had to prepare and configure a new server with this hardware:
- 4 GB RAM
- 64 Bit system
- 3 GHz Core 2 processor
After load testing i found the perfect tuned my.cnf for this database with hight volume entries daily:
# MySQL configuration # InnoDB database with 3 MyISAM tables linked port = 3306 socket = /var/run/mysql/mysql.sock # MySQL server [mysqld] port = 3306 socket = /var/run/mysql/mysql.sock datadir = /var/lib/mysql skip-locking key_buffer_size = 16M max_allowed_packet = 2M table_open_cache = 128 sort_buffer_size = 1024K net_buffer_length = 8K read_buffer_size = 512K read_rnd_buffer_size = 1024K myisam_sort_buffer_size = 8M query_cache_size = 16M # Added values after load testing thread_cache_size = 4 tmp_table_size = 128M max_heap_table_size = 128M table_cache = 512 join_buffer_size = 512 # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # binary logging format binlog_format=mixed # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Innodb specifics innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # 50 - 80 % of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2048M # innodb_additional_mem_pool_size generally not needed after tests innodb_additional_mem_pool_size = 8M # log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 # innodb_file_per_table to avoid defragmentation but when optimizing will lock tables innodb_file_per_table = 1 # The safe_mysqld script [safe_mysqld] log-error = /var/log/mysql/mysqld.log socket = /var/run/mysql/mysql.sock !include_dir /etc/mysql [mysqldump] socket = /var/run/mysql/mysql.sock quick max_allowed_packet = 32M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 60M sort_buffer_size = 60M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/log/mysqld_multi.log # Please give new suggestions if you think you can optimize performance even more.