Announcement

Announcement Module
Collapse
No announcement yet.

New, Need Help

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

  • New, Need Help

    Hi,

    I am running a pretty small MySQL Database (92 tables, biggest table is under 35000 rows, average is 2000 rows, only 40 of them have over 250 rows)

    But performance seems sub-obtimal. I'm running the database on a dedicated server with Core 2 Duo and 2GB Ram. The only other duties of the server are a standard web server. I noticed that mysql is using about 35% of my cpu usage (which I guess is only 17% on dual core). I can definelty spare it, but it still seems high and I would like to optimize it my DB and queries as much as possible.

    Below is what I get for runtime variables.



    Value Variable1 Flush_commands299 Slow_queries643 Handler_commit670 k Handler_delete0 Handler_discover24 M Handler_read_first1,744 M Handler_read_key862 M Handler_read_next12 M Handler_read_prev131 M Handler_read_rnd1,020 M Handler_read_rnd_next567 Handler_rollback2,033 M Handler_update375 M Handler_write0 Qcache_free_blocks0 Qcache_free_memory0 Qcache_hits0 Qcache_inserts0 Qcache_lowmem_prunes0 Qcache_not_cached0 Qcache_queries_in_cache0 Qcache_total_blocks308 Slow_launch_threads0 Threads_cached4 Threads_connected1,376 k Threads_created1 Threads_running0.00% Threads_cache_hitrate_%0 Binlog_cache_disk_use0 Binlog_cache_use Begin Temporary data355 k Created_tmp_disk_tables4,984 Created_tmp_files17 M Created_tmp_tables0 Delayed_insert_threads0 Delayed_writes0 Not_flushed_delayed_rows0 Key_blocks_not_flushed5,713 Key_blocks_unused1,952 Key_blocks_used235.87G Key_read_requests29 M Key_reads11 M Key_write_requests289 k Key_writes30.26% Key_buffer_fraction_%2,664 k Select_full_join0 Select_full_range_join12 k Select_range0 Select_range_check24 M Select_scanNULL Rpl_status0 Slave_open_temp_tables0 Slave_retried_transactionsOFF Slave_running2,490 Sort_merge_passes15 k Sort_range188 M Sort_rows17 M Sort_scan64 Open_tables4,399 k Opened_tables165 M Table_locks_immediate312 k Table_locks_waited116 Open_files0 Open_streams

    From what I read, I'm most concerned about:

    slow_queries
    Handler_read_rnd
    Handler_read_rnd_next
    slow_launch_threads
    Created_tmp_disk_tables
    Select_full_join
    Sort_merge_passes
    Opened_tables
    Table_locks_waited

    I'm on MySql 4.1.21 and can't move to 5+ for at least a few more months. I realize a lot of it is probably that my queries aren't optimized so if someone can tell me what I need to do to log the slow queries and the ones that cause hanlder_read_rnd and rnd_next and select_full_join to be so high so that I can find and fix them I would apprecaite it.

    Also, if someone can tell me how to change my settings (and what I should change them to) to maximize performance and memory usage (the whole database should easily fit in ram) and anything else I can do to speed things up.

    Thanks.

  • #2
    tfittsy wrote on Fri, 16 March 2007 20:50


    0 Qcache_free_blocks0 Qcache_free_memory0 Qcache_hits0 Qcache_inserts0 Qcache_lowmem_prunes0 Qcache_not_cached0 Qcache_queries_in_cache0 Qcache_total_blocks0 Threads_cached0.00% Threads_cache_hitrate_%64 Open_tables4,399 k Opened_tables


    Enable the Query cache
    Enable the Thread cache
    Enable the Table cache

    Those would speed things up substantially.

    Comment


    • #3
      Please post output of SHOW VARIABLES, so we could comment the server settings.

      Comment

      Working...
      X