SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Rawalpindi, Pakistan
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql Performance

    Hi,
    I want to know how we can improve performance of mySql, we can get different variables and status by
    show variables; and show status; But what do each of these variables mean, can any body tell me tutorial for that.

    Basically I am facing connection problems, currently max_connections is set to 14, I think that needs to be increased, but what is the maximam limit, we have enough RAM to support.


    Thanx
    Aatif Malik

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i love tweaking MySQL!

    the manual pages for SHOW VARIABLES and SHOW STATUS do a pretty good job of explaining what each thing means. however, knowing what to look for and what to change is harder.

    you say your max_connections variable is set to 14? that's weird since the default is 100. or did you mean that the Max_used_connections in SHOW STATUS is 14? how many connections would you like to be able to handle? MySQL should be able to handle 300-500 at least, depending on your server. the maximum limit should be 1000 or more depending, again, on your server and what queries are being run.

    some key stats (from SHOW STATUS) to look at are:

    • Key_reads divided by Key_read_requests should be less than 0.01
    • Opened_tables -- the lower the better
    • Slow_queries, Threads_created, Created_tmp_tables, Created_tmp_disk_tables -- higher is worse
    • Max_used_connections is the most connections used at one time
    • Threads_connected is the amount of connections currently
    • Threads_running is how many of those connections are actually busy doing something (processing a query, etc.)


    2 variables (from SHOW VARIABLES) that are most often adjusted (and the most important) are:

    • key_buffer_size -- where MySQL keeps table indexes in RAM. if this is too low, it will have to read indexes from disk rather than having them cached in RAM. if it's too high, you'll just be wasting RAM. the default is 8M. it should typically be set to about 16M.
    • table_cache -- tables are files. opening and closing files is expensive (one of the problems with flat-file DBs). to avoid this constant opening and closing for each query, MySQL keeps table_cache tables open continuously. each open table needs a file descriptor from the OS, so you can't go too huge on this. the default is a pathetic 64. you should be able to almost always set this to 1024, which will greatly reduce the Opened_tables stat.


    depending on your RAM, you can probably increase the size of the record_buffer, join_buffer, and sort_buffer. having some value for thread_cache_size is better than the default of 0 too.


    if you want me to recommend a my.cnf/my.ini file for your system, could you please post: CPU speed/type and number (single or dual), amount of RAM, operating system, output from SHOW VARIABLES, output from SHOW STATUS (after MySQL has been running for a while; at least a few hours, preferably a few days), and the current contents of your my.cnf/my.ini file, if you have one.

    if you're using *nix, how are your load averages and swap usage?

    hope that helps some.
    Last edited by DR_LaRRY_PEpPeR; Aug 10, 2002 at 00:26.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Rawalpindi, Pakistan
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    Thanx a lot,
    yes max_connections was 14, and I dont know why was it set to 14, now I have increased that to 300.
    we have 1 GHz processors, dual processors and 1 GB of ram,
    we are running at 60% free RAM mostly and 100% free swap space and load is 0.7.

    About variables and status, these were before I increased max_connections to 14. Your suggestion please...


    Show Status;
    +--------------------------+------------+
    | Variable_name | Value |
    +--------------------------+------------+
    | Aborted_clients | 145 |
    | Aborted_connects | 4 |
    | Bytes_received | 29154091 |
    | Bytes_sent | 221809143 |
    | Connections | 45382 |
    | Created_tmp_disk_tables | 345 |
    | Created_tmp_tables | 3426 |
    | Created_tmp_files | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Delayed_errors | 0 |
    | Flush_commands | 1 |
    | Handler_delete | 4902 |
    | Handler_read_first | 5695 |
    | Handler_read_key | 593551 |
    | Handler_read_next | 21054205 |
    | Handler_read_prev | 83176 |
    | Handler_read_rnd | 2575519 |
    | Handler_read_rnd_next | 3781652920 |
    | Handler_update | 317458 |
    | Handler_write | 186755 |
    | Key_blocks_used | 15582 |
    | Key_read_requests | 3768739 |
    | Key_reads | 16820 |
    | Key_write_requests | 20886 |
    | Key_writes | 10203 |
    | Max_used_connections | 14 |
    | Not_flushed_key_blocks | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_tables | 64 |
    | Open_files | 124 |
    | Open_streams | 0 |
    | Opened_tables | 7419 |
    | Questions | 340170 |
    | Select_full_join | 1225 |
    | Select_full_range_join | 0 |
    | Select_range | 771 |
    | Select_range_check | 0 |
    | Select_scan | 87283 |
    | Slave_running | OFF |
    | Slave_open_temp_tables | 0 |
    | Slow_launch_threads | 4 |
    | Slow_queries | 501 |
    | Sort_merge_passes | 0 |
    | Sort_range | 544 |
    | Sort_rows | 2642640 |
    | Sort_scan | 50362 |
    | Table_locks_immediate | 237620 |
    | Table_locks_waited | 302 |
    | Threads_cached | 0 |
    | Threads_created | 45381 |
    | Threads_connected | 2 |
    | Threads_running | 2 |
    | Uptime | 33573 |
    +--------------------------+------------+
    54 rows in set (0.02 sec)


    Show Variables;

    | Variable_name | Value |
    +---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | ansi_mode | OFF |
    | back_log | 50 |
    | basedir | /usr/local/mysql/ |
    | binlog_cache_size | 32768 |
    | character_set | latin1 |
    | character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
    | concurrent_insert | ON |
    | connect_timeout | 5 |
    | datadir | /usr/local/mysql/var/ |
    | delay_key_write | ON |
    | delayed_insert_limit | 100 |
    | delayed_insert_timeout | 300 |
    | delayed_queue_size | 1000 |
    | flush | OFF |
    | flush_time | 0 |
    | have_bdb | NO |
    | have_gemini | NO |
    | have_innodb | NO |
    | have_isam | YES |
    | have_raid | NO |
    | have_ssl | NO |
    | init_file | |
    | interactive_timeout | 28800 |
    | join_buffer_size | 131072 |
    | key_buffer_size | 16773120 |
    | language | /usr/local/mysql/share/mysql/english/ |
    | large_files_support | ON |
    | locked_in_memory | OFF |
    | log | ON |
    | log_update | OFF |
    | log_bin | OFF |
    | log_slave_updates | OFF |
    | long_query_time | 10 |
    | low_priority_updates | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 1047552 |
    | max_binlog_cache_size | 4294967295 |
    | max_binlog_size | 1073741824 |
    | max_connections | 14 |
    | max_connect_errors | 10 |
    | max_delayed_threads | 20 |
    | max_heap_table_size | 16777216 |
    | max_join_size | 4294967295 |
    | max_sort_length | 1024 |
    | max_user_connections | 0 |
    | max_tmp_tables | 32 |
    | max_write_lock_count | 4294967295 |
    | myisam_recover_options | OFF |
    | myisam_max_extra_sort_file_size | 256 |
    | myisam_max_sort_file_size | 2047 |
    | myisam_sort_buffer_size | 8388608 |
    | net_buffer_length | 16384 |
    | net_read_timeout | 30
    | net_retry_count | 10 |
    | net_write_timeout | 60 |
    | open_files_limit | 0 |
    | port | 3306 |
    | protocol_version | 10 |
    | record_buffer | 131072 |
    | query_buffer_size | 0 |
    | safe_show_database | OFF |
    | server_id | 0 |
    | slave_net_timeout | 3600 |
    | skip_locking | ON |
    | skip_networking | OFF |
    | skip_show_database | OFF |
    | slow_launch_time | 2 |
    | socket | /tmp/mysql.sock |
    | sort_buffer | 2097144 |
    | table_cache | 64 |
    | table_type | MYISAM |
    | thread_cache_size | 0 |
    | thread_stack | 131072 |
    | transaction_isolation | READ-COMMITTED |
    | timezone | PDT |
    | tmp_table_size | 33554432 |
    | tmpdir | /tmp/ |
    | version | 3.23.40-log |
    | wait_timeout | 28800 |
    +---------------------------------+-------------------------------------------------------------------------------------------
    ----------------------------------------------------------------+
    80 rows in set (0.00 sec)

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks. nice system.

    you're using an older version of MySQL (3.23.40). i'd suggest upgrading to the latest version (3.23.51 right now) for bug fixes and possible performance improvments. and if you're using Linux, make sure you're using the 2.4 kernel since it runs MySQL the best.

    i noticed in your variables that log is ON. this means that all queries are being logged. you shouldn't need this for any reason and it probably increases load, so it will be off with this new config file.

    other than that, i increased max_connections to 450; table_cache from 64 to 1024; join_buffer and record_buffer from 128K to 1M; and thread_cache_size from 0 to 64 (this will make it so that a new thread isn't created for each connection. you may see more mysqld processes running, that's fine.)

    replace your my.cnf file with the following and restart MySQL:

    Code:
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    skip-locking
    set-variable = max_connections=450
    set-variable = max_allowed_packet=16M
    set-variable = key_buffer=16M
    set-variable = myisam_sort_buffer_size=64M
    set-variable = join_buffer=1M
    set-variable = record_buffer=1M
    set-variable = sort_buffer=2M
    set-variable = table_cache=1024
    set-variable = thread_cache_size=64
    set-variable = wait_timeout=600
    
    [safe_mysqld]
    open-files-limit = 8192
    
    [mysqldump]
    quick
    set-variable = max_allowed_packet=16M
    
    [mysql] 
    no-auto-rehash
    
    [mysqlcheck]
    set-variable = key_buffer=64M
    set-variable = sort_buffer=64M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M
    
    [myisamchk]
    set-variable = key_buffer=64M
    set-variable = sort_buffer=64M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    that should do pretty good for you. hope it helps.

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Rawalpindi, Pakistan
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx Larry,
    That is really great help.

    Aatif Malik

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem!


Bookmarks

Posting Permissions

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