jeger003 Posted January 7, 2013 Share Posted January 7, 2013 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 Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/ Share on other sites More sharing options...
Christian F. Posted January 7, 2013 Share Posted January 7, 2013 Please post the results of SHOW CREATE TABLE as well, as it seems you have a missing index that's hurting your performance. Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1403987 Share on other sites More sharing options...
jeger003 Posted January 7, 2013 Author Share Posted January 7, 2013 here is the results table http://pastebin.com/PX6qDHCd Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1403988 Share on other sites More sharing options...
jeger003 Posted January 7, 2013 Author Share Posted January 7, 2013 any ideas on where my issue is? Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1404003 Share on other sites More sharing options...
jazzman1 Posted January 7, 2013 Share Posted January 7, 2013 Please, don't pressure the people of the forum! Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1404016 Share on other sites More sharing options...
jazzman1 Posted January 7, 2013 Share Posted January 7, 2013 @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 Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1404055 Share on other sites More sharing options...
jeger003 Posted January 9, 2013 Author Share Posted January 9, 2013 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' Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1404483 Share on other sites More sharing options...
jazzman1 Posted January 9, 2013 Share Posted January 9, 2013 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%' Link to comment https://forums.phpfreaks.com/topic/272805-fine-tuning-mysql-server-configuration-for-better-performance/#findComment-1404494 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.