Results 1 to 4 of 4
  1. #1
    pascal Guest

    [HowTo] Optimising MYSQL


    I’d like to share with the interworx-cp community my knowledge about tuning / tweaking mysql.

    First of all, sorry if my English is not perfect. Maybe some sentences may be difficult to understand. So do not hesitate to ask me for explanations :-p

    Secondly, the default INTEWORX-CP mysql setup works very fine and is setup for all common usage.
    DO NOT CHANGE your setting without understanding what you do.
    Make a backup of your my.cnf before editing the /etc/my.cnf

    My thread is only to help you to more well understand how tuning mysql.

    Optimising mysql is very well commented on the net, and you’ll find huge information on how to do this. There is never “best parameters”, the best parameters is those fits your needs, box hardware, mysql usage…
    So, I’ll not give the best parameters but rather how to define these ones. Make some tests, and you’ll quickly find your own parameters.

    I’ll give you at the end of this post some web pointers which may help you.

    There a lot of available parameters but only few one are very important to tweak your mysql box.

    The most important variables are (for me, and it is not exhaustive)
    - max_connections
    - wait_timeout
    - thread_cache_size
    - table_cache
    - key_buffer_size
    - query_cache_size
    - tmp_table_size

    First of all, how to find your variable, and the mysql usage ?

    from mysql :
    show variables;

    or from command line :
    mysqladmin variables
    from Mysql :
    show status;

    or from command line
    mysqladmin –i10 processlist extended-status

    >ps –axfu

    >vmstat 1


    To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.

    1 - The two most important variables : Table_cache and Key_buffer_size

    * If Opened_tables is big, then your table_cache variable is probably
    too small.

    table_cache 64
    Open_tables 64
    Opened_tables 544468

    This is the first serious problem. "The table_cache is the number of open
    tables for all threads. MySQL, being multi-threaded, may be running many
    queries on the table at one time, and each of these will open a table."
    Therefore, even though we only have a few tables, we will need many more

    The Opened_tables value is high and shows the number of
    cache misses. Getting the table_cache size correct is one of the two best
    things you can do to improve performance.

    * If Key_reads is big, then your key_buffer_size variable is probably
    too small. The cache hit rate can be calculated with

    key_buffer_size 16M
    Key_read_requests 2973620399
    Key_reads 8490571
    (cache hit rate = 0.0028)

    “The key_buffer_size affects the size of the index buffers and the speed
    of index handling, particularly reading." The MySQL manual (and other sources) say that
    "Key_reads/Key_read_request ratio should normally be < 0.01." This is the
    other most important thing to get correct. Here the value seems to be correct (< 0.01)

    Also check key_write_requests and key_writes.
    The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)

    Here is a very interesting web pointer :

    2 - Others important settings are : Wait_timeout, max_connexion, thread_cache

    A little explanation :
    Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

    The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.
    The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

    So the solution is to use the Thread_cache (from mysql doc) :
    “How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.”

    * If Threads_created is big, you may want to increase the
    thread_cache_size variable. The cache hit rate can be calculated with

    thread_cache_size 0
    Threads_created 150022
    Connections 150023

    This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.

    you may try this formula : table_cache = opened table / max_used_connection

    3 - Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next

    * If Created_tmp_disk_tables is big, you may want to increase the
    tmp_table_size variable to get the temporary tables memory-based instead
    of disk based.

    tmp_table_size 32M
    Created_tmp_disk_tables 3227
    Created_tmp_tables 159832
    Created_tmp_files 4444

    Created_tmp_disk_tables are the "number of implicit temporary tables on
    disk created while executing statements" and Created_tmp_tables are
    memory-based. Obviously it is bad if you have to go to disk instead of
    memory. About 2% of temp tables go to disk, which doesn't seem too bad
    but increasing the tmp_table_size probably couldn't hurt either.

    * If Handler_read_rnd is big, then you probably have a lot of queries
    that require MySQL to scan whole tables or you have joins that don't use
    keys properly.

    Handler_read_rnd 27712353
    Handler_read_rnd_next 283536234

    These values are high, that we could probably stand to improve
    the indexes and queries.

    I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.

    Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)

    Last edited by pascal; 01-21-2005 at 10:11 PM.

  2. #2
    pascal Guest
    I forgot to tell you two or three importants things like :

    Used MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

    Notice the max_connexion and the multiplier.
    connexion increase = memory usage increase too.

    Notice key_buffer
    for a given memory :
    more you add mem to key buffer, less connexion is
    less is key buffer, more connexion is

    If you change one of these settings for a high value, you system may swap.
    If you system swap, try lot decrease these values

    Also, about table_cache :
    Increasing the size of the table cache may really help you.
    But you must be careful not to make the value too large. All operating systems have a limit on the number "open file pointer" (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.
    If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.

    Last edited by pascal; 01-21-2005 at 10:06 PM.

  3. #3
    Christian Guest

    Exclamation Help!

    Hi Pascal,

    If got a huge problem, we have a dual xeon 2,8 ghz with 2gb of mem running only the sql server is sooo slow... i cant find the problem, i already have cepperated the database on 2 servers but still the same problem... Could you look to my statics and help me out what the problem is?!

    Many thanks,

    Christian (Netherlands)

    ################################################## #####

    Variable_name Value
    Aborted_clients 1632
    Aborted_connects 446
    Binlog_cache_disk_use 0
    Binlog_cache_use 0
    Bytes_received 3526355276
    Bytes_sent 880506088
    Com_admin_commands 40
    Com_alter_db 0
    Com_alter_table 6
    Com_analyze 0
    Com_backup_table 0
    Com_begin 0
    Com_change_db 821
    Com_change_master 0
    Com_check 17
    Com_checksum 0
    Com_commit 0
    Com_create_db 0
    Com_create_function 0
    Com_create_index 0
    Com_create_table 2
    Com_dealloc_sql 0
    Com_delete 4
    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 19
    Com_grant 0
    Com_ha_close 0
    Com_ha_open 0
    Com_ha_read 0
    Com_help 0
    Com_insert 25509
    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 1
    Com_preload_keys 0
    Com_prepare_sql 0
    Com_purge 19
    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 11355963
    Com_set_option 11306312
    Com_show_binlog_events 0
    Com_show_binlogs 165
    Com_show_charsets 464
    Com_show_collations 464
    Com_show_column_types 0
    Com_show_create_db 0
    Com_show_create_table 133
    Com_show_databases 64
    Com_show_errors 0
    Com_show_fields 228
    Com_show_grants 205
    Com_show_innodb_status 0
    Com_show_keys 159
    Com_show_logs 0
    Com_show_master_status 0
    Com_show_new_master 0
    Com_show_open_tables 5
    Com_show_privileges 0
    Com_show_processlist 80
    Com_show_slave_hosts 0
    Com_show_slave_status 0
    Com_show_status 11
    Com_show_storage_engines 6
    Com_show_tables 872
    Com_show_variables 994
    Com_show_warnings 0
    Com_slave_start 0
    Com_slave_stop 0
    Com_truncate 1
    Com_unlock_tables 0
    Com_update 11208407
    Com_update_multi 0
    Connections 11306142
    Created_tmp_disk_tables 0
    Created_tmp_files 3
    Created_tmp_tables 877
    Delayed_errors 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Variable_name Value
    Flush_commands 1
    Handler_commit 0
    Handler_delete 2
    Handler_discover 0
    Handler_read_first 263
    Handler_read_key 22408394
    Handler_read_next 32313
    Handler_read_prev 1058708072
    Handler_read_rnd 0
    Handler_read_rnd_next 769312987
    Handler_rollback 0
    Handler_update 11180960
    Handler_write 25550
    Key_blocks_not_flushed 0
    Key_blocks_unused 13667
    Key_blocks_used 830
    Key_read_requests 94470278
    Key_reads 2193
    Key_write_requests 25960
    Key_writes 25910
    Max_used_connections 123
    Not_flushed_delayed_rows 0
    Open_files 88
    Open_streams 0
    Open_tables 64
    Opened_tables 335
    Qcache_free_blocks 9
    Qcache_free_memory 16077208
    Qcache_hits 51511
    Qcache_inserts 11328247
    Qcache_lowmem_prunes 0
    Qcache_not_cached 27719
    Qcache_queries_in_cache 29
    Qcache_total_blocks 83
    Questions 45258200
    Rpl_status NULL
    Select_full_join 0
    Select_full_range_join 0
    Select_range 0
    Select_range_check 0
    Select_scan 128879
    Slave_open_temp_tables 0
    Slave_running OFF
    Slave_retried_transactions 0
    Slow_launch_threads 0
    Slow_queries 2
    Sort_merge_passes 0
    Sort_range 0
    Sort_rows 774
    Sort_scan 32
    Table_locks_immediate 22517635
    Table_locks_waited 73303
    Threads_cached 0
    Threads_connected 1
    Threads_created 11306141
    Threads_running 1
    Uptime 1626337
    Last edited by Christian; 03-14-2006 at 06:43 PM.

  4. #4
    Saeven Guest
    Your runtime values don't point necessarily to a bad mysql server configuration, but instead to poor query design. These values:

    Handler_read_rnd_next 769312987
    Key_read_requests 94470278
    Select_scan 128879
    Created_tmp_tables 877

    Indicate that you have several queries that either:
    - are not using indexes
    - are performing full table scans to retrieve values
    - are not performing lossless joins

    You need to schedule a nice evening with your SQL console, your SQL queries and run EXPLAIN to discover which are creating these temp tables, and where you could use indexes to prevent your mysql server from scanning entire tables to locate single potential keys.

    Use the mysql slow query log, and the log queries without indexes directives and then examine your logs to find the culprit.

    There's a saying in our field, tweaking servers can improve your performance by ten, tweaking the queries can improve it by ten thousand.

    You might want to add skip-locking to your mysqld section, that's a huge lock value for what I assume are myisam tables (which perform table locking). If you're using InnoDB and have set it to a finer grained lock, you can ignore this last statement. If you are not using InnoDB, turn it off.

    Disk access also plays a great part, make sure you have hdparm set to have DMA access on drives that can support it. Further, if something is chewing up your filesystem time, ie. scripts that have bad code that loop file locks, this could be consuming your file descriptors. Tweaking your filesystem is an entirely different ballgame - but should be considered. Lots of good tutorials available on the net.
    Last edited by Saeven; 03-31-2006 at 02:45 PM.

Posting Permissions

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