Hey Everyone,
I’m pretty much a noob at MySQL and was hoping someone can point me in the right direction as far as tuning a MySQL box. Here’s the specs on the hw/sw:
HP Workstation
2 Dual Core 1.8 Processors
2GB RAM (usually only about 500MB used)
Running Tomcat web app and MySQL 5.0.15-nt
The problem is my CPU load for MySQL is through the roof almost consistently. Unfortunately I’m guessing the queries may be partly to blame but the vendor has yet to optimize them. Most of the time I see 2-3 queries, mainly table scans, and about 100 sleeping connections.
Any help is greatly appreciated. Also, please let me know if you need any further info. Thanks in advance.
Here’s the output from a show status and the my.ini is attached.
Show Status;
Variable_name | Value
----------------------------------±-------------
Aborted_clients | 1822
Aborted_connects | 2
Binlog_cache_disk_use | 0
Binlog_cache_use | 0
Bytes_received | 423
Bytes_sent | 33003
Com_admin_commands | 0
Com_alter_db | 0
Com_alter_table | 0
Com_analyze | 0
Com_backup_table | 0
Com_begin | 0
Com_change_db | 1
Com_change_master | 0
Com_check | 0
Com_checksum | 0
Com_commit | 0
Com_create_db | 0
Com_create_function | 0
Com_create_index | 0
Com_create_table | 0
Com_dealloc_sql | 0
Com_delete | 0
Com_delete_multi | 0
Com_do | 0
Com_drop_db | 0
Com_drop_function | 0
Com_drop_index | 0
Com_drop_table | 0
Com_drop_user | 0
Com_execute_sql | 0
Com_flush | 0
Com_grant | 0
Com_ha_close | 0
Com_ha_open | 0
Com_ha_read | 0
Com_help | 0
Com_insert | 0
Com_insert_select | 0
Com_kill | 0
Com_load | 0
Com_load_master_data | 0
Com_load_master_table | 0
Com_lock_tables | 0
Com_optimize | 0
Com_preload_keys | 0
Com_prepare_sql | 0
Com_purge | 0
Com_purge_before_date | 0
Com_rename_table | 0
Com_repair | 0
Com_replace | 0
Com_replace_select | 0
Com_reset | 0
Com_restore_table | 0
Com_revoke | 0
Com_revoke_all | 0
Com_rollback | 0
Com_savepoint | 0
Com_select | 3
Com_set_option | 0
Com_show_binlog_events | 0
Com_show_binlogs | 0
Com_show_charsets | 0
Com_show_collations | 0
Com_show_column_types | 0
Com_show_create_db | 0
Com_show_create_table | 0
Com_show_databases | 0
Com_show_errors | 0
Com_show_fields | 0
Com_show_grants | 0
Com_show_innodb_status | 2
Com_show_keys | 0
Com_show_logs | 0
Com_show_master_status | 0
Com_show_ndb_status | 0
Com_show_new_master | 0
Com_show_open_tables | 0
Com_show_privileges | 0
Com_show_processlist | 0
Com_show_slave_hosts | 0
Com_show_slave_status | 0
Com_show_status | 1
Com_show_storage_engines | 0
Com_show_tables | 0
Com_show_triggers | 0
Com_show_variables | 0
Com_show_warnings | 0
Com_slave_start | 0
Com_slave_stop | 0
Com_stmt_close | 0
Com_stmt_execute | 0
Com_stmt_fetch | 0
Com_stmt_prepare | 0
Com_stmt_reset | 0
Com_stmt_send_long_data | 0
Com_truncate | 0
Com_unlock_tables | 0
Com_update | 0
Com_update_multi | 0
Com_xa_commit | 0
Com_xa_end | 0
Com_xa_prepare | 0
Com_xa_recover | 0
Com_xa_rollback | 0
Com_xa_start | 0
Connections | 10822
Created_tmp_disk_tables | 0
Created_tmp_files | 2535
Created_tmp_tables | 2
Delayed_errors | 0
Delayed_insert_threads | 0
Delayed_writes | 0
Flush_commands | 1
Handler_commit | 0
Handler_delete | 0
Handler_discover | 0
Handler_prepare | 0
Handler_read_first | 0
Handler_read_key | 0
Handler_read_next | 0
Handler_read_prev | 0
Handler_read_rnd | 0
Handler_read_rnd_next | 22262
Handler_rollback | 0
Handler_savepoint | 0
Handler_savepoint_rollback | 0
Handler_update | 0
Handler_write | 129
Innodb_buffer_pool_pages_data | 2619
Innodb_buffer_pool_pages_dirty | 0
Innodb_buffer_pool_pages_flushed | 20532
Innodb_buffer_pool_pages_free | 259
Innodb_buffer_pool_pages_latched | 0
Innodb_buffer_pool_pages_misc | 130
Innodb_buffer_pool_pages_total | 3008
Innodb_buffer_pool_read_ahead_rnd | 3
Innodb_buffer_pool_read_ahead_seq | 24
Innodb_buffer_pool_read_requests | 127963082
Innodb_buffer_pool_reads | 1095
Innodb_buffer_pool_wait_free | 0
Innodb_buffer_pool_write_requests | 163020
Innodb_data_fsyncs | 57400
Innodb_data_pending_fsyncs | 0
Innodb_data_pending_reads | 0
Innodb_data_pending_writes | 0
Innodb_data_read | 42881024
Innodb_data_reads | 2494
Innodb_data_writes | 63833
Innodb_data_written | 692898304
Innodb_dblwr_pages_written | 20532
Innodb_dblwr_writes | 13801
Innodb_log_waits | 0
Innodb_log_write_requests | 10056
Innodb_log_writes | 15805
Innodb_os_log_fsyncs | 29500
Innodb_os_log_pending_fsyncs | 0
Innodb_os_log_pending_writes | 0
Innodb_os_log_written | 13093888
Innodb_page_size | 16384
Innodb_pages_created | 135
Innodb_pages_read | 2484
Innodb_pages_written | 20532
Innodb_row_lock_current_waits | 0
Innodb_row_lock_time | 0
Innodb_row_lock_time_avg | 0
Innodb_row_lock_time_max | 0
Innodb_row_lock_waits | 0
Innodb_rows_deleted | 0
Innodb_rows_inserted | 40197
Innodb_rows_read | 49439573
Innodb_rows_updated | 0
Key_blocks_not_flushed | 0
Key_blocks_unused | 19694
Key_blocks_used | 22484
Key_read_requests | 18055999471
Key_reads | 3261754
Key_write_requests | 217955
Key_writes | 217846
Last_query_cost | 27224.619117
Max_used_connections | 267
Not_flushed_delayed_rows | 0
Open_files | 63
Open_streams | 0
Open_tables | 49
Opened_tables | 0
Qcache_free_blocks | 0
Qcache_free_memory | 0
Qcache_hits | 0
Qcache_inserts | 0
Qcache_lowmem_prunes | 0
Qcache_not_cached | 0
Qcache_queries_in_cache | 0
Qcache_total_blocks | 0
Questions | 2013427485
Rpl_status | NULL
Select_full_join | 0
Select_full_range_join | 0
Select_range | 0
Select_range_check | 0
Select_scan | 2
Slave_open_temp_tables | 0
Slave_retried_transactions | 0
Slave_running | OFF
Slow_launch_threads | 0
Slow_queries | 0
Sort_merge_passes | 0
Sort_range | 0
Sort_rows | 0
Sort_scan | 1
Table_locks_immediate | 591974709
Table_locks_waited | 79989
Tc_log_max_pages_used | 0
Tc_log_page_size | 0
Tc_log_page_waits | 0
Threads_cached | 7
Threads_connected | 101
Threads_created | 2057
Threads_running | 4
Uptime | 1411487