Все конфигурации тут https://github.com/RodrigoViolante/my.cnf
Полезно знать:
https://habrahabr.ru/post/66684/
Пример конфига для 16GB RAM
################################################################################ #DATE: 2011-02-02 #SITE: http://datastrangler.com #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x #RAM: 16GB RAM dedicated server #Connections: 1000 connections ################################################################################ [mysqld_safe] nice = -15 [client] socket = /var/lib/mysql/mysql.sock default-character-set = utf8 [mysqld] ## Charset and Collation character-set-server = utf8 collation-server = utf8_general_ci ## Files back_log = 300 open-files-limit = 8192 open-files = 1024 port = 3306 socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid skip-external-locking skip-name-resolve ## Logging datadir = /var/lib/mysql relay_log = mysql-relay-bin relay_log_index = mysql-relay-index #log = mysql-gen.log log_error = mysql-error.err log_warnings log_bin = mysql-bin log_slow_queries = mysql-slow.log #log_queries_not_using_indexes long_query_time = 10 #default: 10 max_binlog_size = 256M #max size for binlog before rolling expire_logs_days = 4 #binlog files older than this will be purged ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 256K #default: 32bit: 192K, 64bit: 256K sort_buffer_size = 1M #default: 2M, larger may cause perf issues read_buffer_size = 1M #default: 128K, change in increments of 4K read_rnd_buffer_size = 1M #default: 256K join_buffer_size = 1M #default: 128K binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries ## total per-thread buffer memory usage: 8832000K = 8.625GB ## Query Cache query_cache_size = 32M #global buffer query_cache_limit = 512K #max query result size to put in cache ## Connections max_connections = 2000 #multiplier for memory usage via per-thread buffers max_connect_errors = 100 #default: 10 concurrent_insert = 2 #default: 1, 2: enable insert for all instances connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10 max_allowed_packet = 32M #max size of incoming data to allow ## Default Table Settings sql_mode = NO_AUTO_CREATE_USER ## Table and TMP settings max_heap_table_size = 1G #recommend same size as tmp_table_size bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size tmp_table_size = 1G #recommend 1G min #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir ## Table cache settings #table_cache = 512 #5.0.x <default: 64> #table_open_cache = 512 #5.1.x, 5.5.x <default: 64> ## Thread settings thread_concurrency = 16 #recommend 2x CPU cores thread_cache_size = 100 #recommend 5% of max_connections ## Replication #read_only #skip-slave-start #slave-skip-errors = <default: none, recommend:1062> #slave-net-timeout = <default: 3600> #slave-load-tmpdir = <location of slave tmpdir> #slave_transaction_retries = <default: 10> #server-id = <unique value> #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1> #auto-increment-increment = <default: none> #auto-increment-offset = <default: none> #master-connect-retry = <default: 60> #log-slave-updates = <default: 0 disable> #report-host = <master_server_ip> #report-user = <replication_user> #report-password = <replication_user_pass> #report-port = <default: 3306> #replicate-do-db = #replicate-ignore-db = #replicate-do-table = #relicate-ignore-table = #replicate-rewrite-db = #replicate-wild-do-table = #replicate-wild-ignore-table = ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0 #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting ## 5.1.x and 5.5.x replication related setting. #binlog_format = MIXED ## MyISAM Engine key_buffer = 1M #global buffer myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes myisam_repair_threads = 4 #thread quantity when running repairs myisam_recover = BACKUP #repair mode, recommend BACKUP ## InnoDB Plugin Dependent Settings #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x #innodb_io_capacity = 200 ## InnoDB IO settings - 5.1.x only #innodb_file_io_threads = 16 ## InnoDB IO settings - 5.5.x and greater #innodb_write_io_threads = 16 #innodb_read_io_threads = 16 ## InnoDB Plugin Independent Settings innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4 innodb_buffer_pool_size = 10G #global buffer innodb_additional_mem_pool_size = 4M #global buffer innodb_status_file #extra reporting innodb_file_per_table #enable always innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID innodb_table_locks = 0 #preserve table locks innodb_log_buffer_size = 128M #global buffer innodb_lock_wait_timeout = 60 innodb_thread_concurrency = 16 #recommend 2x core quantity innodb_commit_concurrency = 16 #recommend 4x num disks #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush skip-innodb-doublewrite ## Binlog sync settings ## XA transactions = 1, otherwise set to 0 for best performance sync_binlog = 0 ## TX Isolation transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size [mysqldump] quick quote-names max_allowed_packet = 128M
В моем случае пришлось увеличить эти параметры до
table_cache = 4096 #5.0.x <default: 64> table_open_cache = 4096 #5.1.x, 5.5.x <default: 64>
Их можно узнать сделав запрос по ssh
mysql show global status like 'open%';
Формула:
Table cache hit rate = table_open_cache*100/Opened_tables.
Table cache hit rate = 2000*100/482099= 0.41%
Эффективность кеша составляет 0.41%, а в идеале это значение должно быть выше 50%.
Подробнее тут http://techinfobest.com/optimize-mysql-table_open_cache/
И еще почитать https://www.hostcms.ru/documentation/server/mysql/