MySQL Forums
Forum List  »  Performance

Help make my big new server fast
Posted by: Jason Rhoades
Date: October 04, 2010 11:31AM

Sorry for my first post being a request for help, but here goes.

Recently completed migration of several DBs from MySQL 5.0 to 5.1 on Linux. Old server was busy but did 8-10k qps all day long with single dual-core CPU, 16GB RAM, and disks on a somewhat-slow SAN. Its subsystems were very busy but not max'd out. Some of the highest throughput tables had delay_key_write enabled.

New server is much beefier - 144GB RAM, 16x internal 15k RPM drives, and the two highest throughput DBs are on a Fusionio card. Initially had delay_key_write off but turned it back on, and it didn't seem to help anything. It is only doing 3-5k qps. Expecting it to be able to do about twice what the old one did.

Below are some dumps from various configs and monitors for your perusal. Many many many thanks for your assistance!!!!!

vmstat 2:
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 10504 637888 825652 123274656 0 0 58 55 2 9 5 2 91 1
1 0 10504 636108 825660 123277664 0 0 134 4532 8517 7209 4 1 95 1
2 0 10504 648308 825648 123263544 0 0 270 24 20859 8754 5 3 92 0
2 0 10504 647140 825656 123264640 0 0 374 3834 14037 9452 7 2 89 1
3 3 10504 647500 825656 123265600 0 0 424 0 27872 13438 5 3 90 2
2 1 10504 646160 825660 123266832 0 0 166 0 8839 7226 4 2 93 1
1 0 10504 660888 825668 123253024 0 0 498 3954 13792 13418 6 1 92 2
1 0 10504 652896 825668 123254784 0 0 30 1254 6956 5238 6 1 93 0
1 0 10504 652840 825668 123254816 0 0 2 1112 25506 6569 4 3 93 0
2 0 10504 658276 825672 123254904 0 0 216 80 6373 6022 5 1 94 1
2 0 10504 657512 825672 123255704 0 0 202 70 23105 9214 6 3 90 1


my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /mysql/data/
tmpdir = /tmp
skip-external-locking
delay-key-write = ON
key_buffer = 10240M
max_allowed_packet = 128M
thread_stack = 128K
thread_cache_size = 80
myisam-recover = ""
max_connections = 5000
open_files_limit = 32768
table_cache = 2500
thread_concurrency = 10
bulk_insert_buffer_size=128M
max_connect_errors=1000
wait_timeout=1800
query_cache_limit = 10M
query_cache_size = 160M
skip-innodb
skip-federated
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 1024M
sort_buffer = 1024M
read_buffer = 16M
write_buffer = 16M

Here's the header from mytop:
MySQL on localhost (5.1.31-ubuntu2) up 1+21:26:08 [10:19:30]
Queries: 551.7M qps: 3537 Slow: 5.3k Se/In/Up/De(%): 85/10/03/01
Sorts: 6 qps now: 1419 Slow qps: 0.0 Threads: 383 ( 129/ 31) 98/14/03/01
Cache Hits: 72.3M Hits/s: 463.8 Hits now: 73.8 Ratio: 15.5% Ratio now: 5.3%
Key Efficiency: 99.7% Bps in/out: 627.9k/ 2.0M Now in/out: 394.8k/632.8k

And some of the nonzero things from status:
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| Aborted_clients | 57431 |
| Aborted_connects | 2 |
| Bytes_received | 105186548816 |
| Bytes_sent | 341216613195 |
| Com_admin_commands | 124205 |
| Com_alter_table | 67 |
| Com_begin | 14 |
| Com_call_procedure | 16056199 |
| Com_change_db | 3466 |
| Com_check | 8 |
| Com_commit | 13 |
| Com_create_table | 36 |
| Com_create_trigger | 6 |
| Com_dealloc_sql | 13021501 |
| Com_delete | 7029726 |
| Com_drop_table | 32 |
| Com_execute_sql | 16827573 |
| Com_flush | 4 |
| Com_insert | 55155043 |
| Com_insert_select | 2642696 |
| Com_lock_tables | 262097 |
| Com_prepare_sql | 13021554 |
| Com_repair | 2 |
| Com_replace | 673459 |
| Com_select | 413539467 |
| Com_set_option | 34309714 |
| Com_show_collations | 18 |
| Com_show_create_table | 142 |
| Com_show_create_trigger | 12 |
| Com_show_databases | 9 |
| Com_show_fields | 129899 |
| Com_show_function_status | 1 |
| Com_show_procedure_status | 1 |
| Com_show_processlist | 28006 |
| Com_show_slave_status | 27997 |
| Com_show_status | 29067 |
| Com_show_table_status | 60 |
| Com_show_tables | 2667125 |
| Com_show_triggers | 60 |
| Com_show_variables | 39 |
| Com_stmt_close | 13021501 |
| Com_stmt_execute | 16827573 |
| Com_stmt_prepare | 13021554 |
| Com_truncate | 578 |
| Com_unlock_tables | 262091 |
| Com_update | 18651546 |
| Com_update_multi | 8 |
| Compression | OFF |
| Connections | 610910 |
| Created_tmp_disk_tables | 131200 |
| Created_tmp_files | 1410 |
| Created_tmp_tables | 3120472 |
| Flush_commands | 2 |
| Handler_delete | 19772991 |
| Handler_read_first | 94789 |
| Handler_read_key | 627629518 |
| Handler_read_next | 4838522277 |
| Handler_read_prev | 38094480 |
| Handler_read_rnd | 38476532 |
| Handler_read_rnd_next | 34832193230 |
| Handler_update | 57132375 |
| Handler_write | 329192286 |
| Key_blocks_not_flushed | 18446744073709390806 |
| Key_blocks_used | 8485068 |
| Key_read_requests | 3372652073 |
| Key_reads | 11698294 |
| Key_write_requests | 144144014 |
| Key_writes | 74750532 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 414 |
| Open_files | 3064 |
| Open_table_definitions | 584 |
| Open_tables | 2500 |
| Opened_files | 174364346 |
| Opened_table_definitions | 6187 |
| Opened_tables | 10279 |
| Prepared_stmt_count | 53 |
| Qcache_free_blocks | 104952 |
| Qcache_free_memory | 449796944 |
| Qcache_hits | 75868572 |
| Qcache_inserts | 104791726 |
| Qcache_lowmem_prunes | 808924 |
| Qcache_not_cached | 25397521 |
| Qcache_queries_in_cache | 377822 |
| Qcache_total_blocks | 868800 |
| Queries | 800625175 |
| Questions | 578594328 |
| Rpl_status | NULL |
| Select_full_join | 54137 |
| Select_range | 99296 |
| Select_scan | 3149919 |
| Slave_running | OFF |
| Slow_queries | 5386 |
| Sort_merge_passes | 771 |
| Sort_range | 3679543 |
| Sort_rows | 12831013 |
| Sort_scan | 218642 |
| Table_locks_immediate | 257978887 |
| Table_locks_waited | 23136189 |
| Threads_cached | 21 |
| Threads_connected | 393 |
| Threads_created | 1067 |
| Threads_running | 119 |
| Uptime | 163635 |
| Uptime_since_flush_status | 81242 |


And the global variables:
| Variable_name | Value |
+---------------------------------+---------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 134217728 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /mysql/data/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0 |
| event_scheduler | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
| group_concat_max_len | 1024 |
| have_community_features | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_partitioning | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
| hostname | ssdstrdb4 |
| identity | 0 |
| init_connect | |
| init_file | |
| init_slave | |
| insert_id | 0 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 10737418240 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /mysql/data/ssdstrdb4.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 134217728 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 1000 |
| max_connections | 5000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | OFF |
| open_files_limit | 32768 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | /usr/lib/mysql/plugin |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| pseudo_thread_id | 0 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 10485760 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1024000000 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| rand_seed1 | |
| rand_seed2 | |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 2097144 |
| sql_auto_is_null | ON |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 18446744073709551615 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | PDT |
| table_definition_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 2500 |
| table_type | MyISAM |
| thread_cache_size | 80 |
| thread_handling | one-thread-per-connection |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1286213279 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.31-1ubuntu2 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 1800 |
| warning_count | 0 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Help make my big new server fast
2885
October 04, 2010 11:31AM
957
October 05, 2010 07:27PM
1419
October 07, 2010 10:01PM
1028
October 08, 2010 02:27PM
924
October 08, 2010 08:03PM
1443
October 08, 2010 09:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.