GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Performance Tuning Question

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance Tuning Question

    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
Working...
X