Results 1 to 6 of 6

Thread: Mysql Help

  1. #1
    Join Date
    Aug 2002
    Posts
    1,632

    Mysql Help

    hi

    i have a Dual Xeon 2.8 with two Cpu and 2Gb ram but i have a high Mysql usage can u tell me a good my.cnf configuration for this machine?

    +--------------------------+-----------+
    | Variable_name | Value |
    +--------------------------+-----------+
    | Aborted_clients | 584 |
    | Aborted_connects | 2 |
    | Bytes_received | 17289481 |
    | Bytes_sent | 400306686 |
    | Com_admin_commands | 705 |
    | Com_alter_table | 31 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 12 |
    | Com_change_db | 6820 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_commit | 12 |
    | Com_create_db | 1 |
    | Com_create_function | 0 |
    | Com_create_index | 37 |
    | Com_create_table | 53 |
    | Com_delete | 753 |
    | Com_delete_multi | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 31 |
    | Com_flush | 3 |
    | Com_grant | 19 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_insert | 1712 |
    | Com_insert_select | 38 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 107 |
    | Com_optimize | 3 |
    | Com_purge | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 0 |
    | Com_replace | 276 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 21424 |
    | Com_set_option | 455 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 0 |
    | Com_show_create | 23 |
    | Com_show_databases | 0 |
    | Com_show_fields | 0 |
    | Com_show_grants | 0 |
    | Com_show_keys | 0 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_processlist | 16 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 15 |
    | Com_show_innodb_status | 0 |
    | Com_show_tables | 0 |
    | Com_show_variables | 4 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 138 |
    | Com_update | 9275 |
    | Com_update_multi | 3 |
    | Connections | 3948 |
    | Created_tmp_disk_tables | 249 |
    | Created_tmp_tables | 1361 |
    | Created_tmp_files | 3 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 1183 |
    | Handler_read_first | 2827 |
    | Handler_read_key | 1470710 |
    | Handler_read_next | 4006145 |
    | Handler_read_prev | 4866 |
    | Handler_read_rnd | 440762 |
    | Handler_read_rnd_next | 13359428 |
    | Handler_rollback | 43 |
    | Handler_update | 236636 |
    | Handler_write | 114849 |
    | Key_blocks_used | 24482 |
    | Key_read_requests | 3416077 |
    | Key_reads | 19980 |
    | Key_write_requests | 448350 |
    | Key_writes | 16546 |
    | Max_used_connections | 18 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 629 |
    | Open_files | 1157 |
    | Open_streams | 0 |
    | Opened_tables | 757 |
    | Questions | 83794 |
    | Qcache_queries_in_cache | 4806 |
    | Qcache_inserts | 20967 |
    | Qcache_hits | 38859 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 456 |
    | Qcache_free_memory | 17727192 |
    | Qcache_free_blocks | 525 |
    | Qcache_total_blocks | 10663 |
    | Rpl_status | NULL |
    | Select_full_join | 479 |
    | Select_full_range_join | 0 |
    | Select_range | 1830 |
    | Select_range_check | 0 |
    | Select_scan | 7305 |
    | Slave_open_temp_tables | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 2 |
    | Sort_merge_passes | 0 |
    | Sort_range | 1913 |
    | Sort_rows | 580048 |
    | Sort_scan | 2809 |
    | Table_locks_immediate | 51150 |
    | Table_locks_waited | 20 |
    | Threads_cached | 9 |
    | Threads_created | 19 |
    | Threads_connected | 10 |
    | Threads_running | 1 |
    | Uptime | 1051 |
    +--------------------------+-----------+


    total used free shared buffers cached
    Mem: 2075612 1188844 886768 0 64640 597476
    -/+ buffers/cache: 526728 1548884
    Swap: 4096564 0 4096564

  2. #2
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,549
    Hello,

    There are multiple good my.cnf files on this forum. It just depends on your preference.

    Code:
    [mysqld]
    skip-locking
    max_connections=300
    connect_timeout=15
    key_buffer=16M
    join_buffer=1M
    record_buffer=1M
    sort_buffer=2M
    table_cache=1028
    thread_cache_size=286
    max_allowed_packet=5M
    wait_timeout=15
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    thread_concurrency=2
    ^^
    http://www.webhostingtalk.com/showth...hreadid=340239
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: [email protected]

  3. #3
    Join Date
    Jul 2005
    Posts
    256

    Re: Mysql Help

    Originally posted by dlc2000
    i have a Dual Xeon 2.8 with two Cpu and 2Gb ram but i have a high Mysql usage can u tell me a good my.cnf configuration for this machine?
    From what I can see in your show status; output your mysql config is OK. (But you provided status output only after 15 minutes of running mysql, after couple of hours it may turn out that you need to change some values).
    If you're not satisfied with your mysql performance, you'll have to optimize table indexes and queries. At some point adding more memory, CPUs, key buffers and query caches simply doesn't work.

  4. #4
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    367
    I found some good information for optimizing MySQL at: http://interworx.info/forums/showthread.php?p=2346
    I N T H R I V E
    when you can't afford downtime
    [email protected]
    High Availability Web Hosting

  5. #5
    Join Date
    Aug 2002
    Posts
    1,632
    this is the status after 4 days

    +--------------------------+------------+
    | Variable_name | Value |
    +--------------------------+------------+
    | Aborted_clients | 38584 |
    | Aborted_connects | 148 |
    | Bytes_received | 708933731 |
    | Bytes_sent | 4235872122 |
    | Com_admin_commands | 1576393 |
    | Com_alter_table | 358 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 12 |
    | Com_change_db | 2424395 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_commit | 24 |
    | Com_create_db | 1 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 4610 |
    | Com_delete | 280354 |
    | Com_delete_multi | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 4004 |
    | Com_flush | 863 |
    | Com_grant | 63 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_insert | 444653 |
    | Com_insert_select | 7048 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 19232 |
    | Com_optimize | 613 |
    | Com_purge | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 27 |
    | Com_replace | 93353 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 6586718 |
    | Com_set_option | 128936 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 13 |
    | Com_show_create | 25399 |
    | Com_show_databases | 585 |
    | Com_show_fields | 22315 |
    | Com_show_grants | 1752 |
    | Com_show_keys | 174 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_processlist | 1155 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 1 |
    | Com_show_innodb_status | 0 |
    | Com_show_tables | 103468 |
    | Com_show_variables | 468 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 19666 |
    | Com_update | 3427135 |
    | Com_update_multi | 1349 |
    | Connections | 750757 |
    | Created_tmp_disk_tables | 53447 |
    | Created_tmp_tables | 210134 |
    | Created_tmp_files | 137 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 7 |
    | Handler_delete | 313551 |
    | Handler_read_first | 611726 |
    | Handler_read_key | 226633043 |
    | Handler_read_next | 393452257 |
    | Handler_read_prev | 1791442 |
    | Handler_read_rnd | 76648172 |
    | Handler_read_rnd_next | 3218375294 |
    | Handler_rollback | 222 |
    | Handler_update | 50768714 |
    | Handler_write | 29305651 |
    | Key_blocks_used | 15586 |
    | Key_read_requests | 804313753 |
    | Key_reads | 2211164 |
    | Key_write_requests | 129601657 |
    | Key_writes | 5931186 |
    | Max_used_connections | 35 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 1028 |
    | Open_files | 1913 |
    | Open_streams | 0 |
    | Opened_tables | 42239 |
    | Questions | 22251643 |
    | Qcache_queries_in_cache | 5867 |
    | Qcache_inserts | 6406470 |
    | Qcache_hits | 7920840 |
    | Qcache_lowmem_prunes | 719460 |
    | Qcache_not_cached | 180205 |
    | Qcache_free_memory | 11232480 |
    | Qcache_free_blocks | 3443 |
    | Qcache_total_blocks | 16264 |
    | Rpl_status | NULL |
    | Select_full_join | 61381 |
    | Select_full_range_join | 1177 |
    | Select_range | 355020 |
    | Select_range_check | 3 |
    | Select_scan | 1468801 |
    | Slave_open_temp_tables | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 91 |
    | Sort_merge_passes | 2 |
    | Sort_range | 379933 |
    | Sort_rows | 100527778 |
    | Sort_scan | 554944 |
    | Table_locks_immediate | 13748250 |
    | Table_locks_waited | 2467 |
    | Threads_cached | 32 |
    | Threads_created | 36 |
    | Threads_connected | 4 |
    | Threads_running | 1 |
    | Uptime | 345234 |
    +--------------------------+------------+

  6. #6
    Join Date
    Jul 2005
    Posts
    256
    Looks good, if some of your queries are slow, that's not because of server config. But changing some of the values still may give you a little performance gain:
    key_buffer = 32M
    join_buffer = 8M
    myisam_sort_buffer = 8M
    table_cache = 4096
    query_cache_size = 128M
    tmp_table_size = 32M
    query_prealloc_size = 8M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •