mysql - 微调 MySQL 服务器配置以获得更好的性能

Question

我需要帮助调整我的 mysql 服务器以获得更好的性能。我有很多资源，但它仍然表现不佳。我打得最多的一张表只有350万条记录。

我需要帮助关注更改哪些设置以获得更好的性能。

像这样的简单查询

SELECT label,
COUNT(ObjectKey) AS labelcount
FROM db.results
GROUP BY label
ORDER BY labelcount DESC
LIMIT 30

EXPLAINED:

'1', 'SIMPLE', 'results', 'index', NULL, 'label_index', '258', NULL, '9093098', 'Using index; Using temporary; Using filesort'

需要 44 秒。

这是我的设置。

SHOW VARIABLES LIKE '%buffer%';


'bulk_insert_buffer_size', '8388608'
'innodb_buffer_pool_instances', '1'
'innodb_buffer_pool_size', '16106127360'
'innodb_change_buffering', 'all'
'innodb_log_buffer_size', '10485760'
'join_buffer_size', '131072'
'key_buffer_size', '304087040'
'myisam_sort_buffer_size', '70254592'
'net_buffer_length', '16384'
'preload_buffer_size', '32768'
'read_buffer_size', '65536'
'read_rnd_buffer_size', '262144'
'sort_buffer_size', '262144'
'sql_buffer_result', 'OFF'


SHOW VARIABLES LIKE 'innodb%'


innodb_data_home_dir,
innodb_doublewrite, ON
innodb_fast_shutdown, 1
innodb_file_format, Antelope
innodb_file_format_check, ON
innodb_file_format_max, Antelope
innodb_file_per_table, OFF
innodb_flush_log_at_trx_commit, 0
innodb_flush_method,
innodb_force_load_corrupted, OFF
innodb_force_recovery, 0
innodb_io_capacity, 200
innodb_large_prefix, OFF
innodb_lock_wait_timeout, 50
innodb_locks_unsafe_for_binlog, OFF
innodb_log_buffer_size, 10485760
innodb_log_file_size, 536870912
innodb_log_files_in_group, 2
innodb_log_group_home_dir, .\
innodb_max_dirty_pages_pct, 75
innodb_max_purge_lag, 0
innodb_mirrored_log_groups, 1
innodb_old_blocks_pct, 37
innodb_old_blocks_time, 0
innodb_open_files, 300
innodb_purge_batch_size, 20
innodb_purge_threads, 0
innodb_random_read_ahead, OFF
innodb_read_ahead_threshold, 56
innodb_read_io_threads, 4
innodb_replication_delay, 0
innodb_rollback_on_timeout, OFF
innodb_rollback_segments, 128
innodb_spin_wait_delay, 6
innodb_stats_method, nulls_equal
innodb_stats_on_metadata, ON
innodb_stats_sample_pages, 8
innodb_strict_mode, OFF
innodb_support_xa, ON
innodb_sync_spin_loops, 30
innodb_table_locks, ON
innodb_thread_concurrency, 10
innodb_thread_sleep_delay, 10000
innodb_use_native_aio, ON
innodb_use_sys_malloc, ON
innodb_version, 1.1.8
innodb_write_io_threads, 4

这是我的表格解释。

http://pastebin.com/PX6qDHCd

Answer 1

由于这取决于许多因素，您可以使用 MySQL Primer和/或 MySQL Tuner脚本来为您的服务器找到合适的设置。

由于MySQL需要创建一个临时表来执行查询，这也可能与磁盘输入/输出不佳有关。