fine tuning MySQL server configuration for better performance

[jeger003]

hello,

I need help tuning my mysql server for better performance. I have a lot of resources but it still is performing poorly. I only have 3.5 million records in one table that i hit the most.

 

I need help focusing on which settings to change for better performance.

 

a simple query like

 

 

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'

 

 

takes 44 seconds.

 

here are my settings.

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

Edited January 7, 2013 by jeger003

[Christian F.]

Please post the results of SHOW CREATE TABLE as well, as it seems you have a missing index that's hurting your performance.

[jeger003]

here is the results table

 

http://pastebin.com/PX6qDHCd

[jeger003]

any ideas on where my issue is?

[jazzman1]

Please, don't pressure the people of the forum!

[jazzman1]

@jeger003, try to increase read_rnd_buffer_size.

 

If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, you should increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations.

 

http://dev.mysql.com...parameters.html

http://dev.mysql.com...rnd_buffer_size

 

Also, run this piece of code into the mysql terminal and give us the result back:

 

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

After then:

SHOW WARNINGS\G

[jeger003]

workbench wont let me use \G

 

EXPLAIN

 

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

 

WARNINGS

 

 

'Note', '1003', 'select `db`.`results`.`label` AS `label`,count(`db`.`results`.`ObjectKey`) AS `labelcount` from `db`.`results` group by `db`.`results`.`label` order by count(`db`.`results`.`ObjectKey`) desc limit 30'

[jazzman1]

Did you try to increase --read_rnd_buffer_size?

 

According the manual, the maximum permissible setting is 2GB.

 

You can check the current one:

SHOW VARIABLES LIKE '%read_rnd_buffer_size%'

Edited January 9, 2013 by jazzman1