Announcement Announcement Module
Collapse
No announcement yet.
MYSQL OPTIMIZATION TIPS Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSQL OPTIMIZATION TIPS

    MYSQL OPTIMIZATION


    MySQL is a popular choice of database for use in web applications. Fully optimizing MySQL takes both time and effort since every application has different requirements.

    The MySQL database server performance depends on the number of factors. The Optimized Query is one of the factors for the MySQL robust performance.

    The MySQL performance depends on the below factors.

    1) Hardware (RAM, DISK, CPU etc)
    2) Operating System (i.e. Linux OS will give the more performance compare to Windows OS )
    3) Application
    4) Optimization of MySQL Server & Queries

    This article is meant to be an easy and relatively safe way to enhance mysql performance.

    Below are notes on some of the important variables,

    Key Buffer

    The key buffer holds the indexes of tables in memory and a bigger key buffer results in faster row lookups. Adjust according to your own needs. Bigger is better, but prevent swapping at all costs. The larger the buffer, the faster the SQL command will finish and a result will be returned.

    A good rule of thumb seems to be to use 1/4 of system memory.

    key_buffer = 128M

    Query Cache

    The query_cache_size, as the name suggests, is the total size of memory available to query caching.

    The value query_cache_limit is the maximum number of kilobytes one query may be in order to be cached.
    Setting this value too high might prevent a lot of smaller queries to be cached.

    Setting it too low will result in bigger queries to never be cached, and the smaller queries not being able to completely fill the cache size, which would be a waste of resources. Adjust according to your own needs and memory available:

    query_cache_size = 128MB
    query_cache_limit = 4MB


    Table Cache

    An important variable if your application accesses many tables.

    Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache.

    MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times.

    If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing,You should increase the table_cache if you have enough memory.

    table_cache = 512


    Sort Buffers

    Sort_buffer_size (the variable previously known as sort_buffer), used for grouping and sorting and is a per-thread buffer. If the buffer can not hold the data to be sorted, a sort is performed on disk.

    Watch out for making this too large as the buffer is allocated for every thread that needs sorting and with many sorts it can easily consume all your memory.

    The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.

    sort_buffer_size = 32M
    myisam_sort_buffer_size = 32M


    Connection Timeout

    This is a little tweak that determines the closing of sleeping connections. The default is one hour and is often too long for practical purposes. I often set this at one minute instead (60).

    Setting the max_connections too high will result in the MySQL server crashing with an “Out of memory” error. General value is 400 -500 depending on your ram and other process that are occupying the ram.

    wait_timeout = 60


    Temporary Tables

    Temporary tables are used for sorting and grouping.

    The buffer is created on demand so watch out for setting this too high here as well. If the buffer cannot accomodate the data, a temp file is used on disk instead.

    tmp_table_size = 64MB

    eg: SELECT NAME, MAX(TIME) as TIME FROM table GROUP BY time ORDER BY time DESC


    Thread cache

    If you have a busy server that's getting a lot of quick connections, set your thread cache high.

    when a new connection is requested MySQL first looks into the thread cache to see if there is any ideal thread, if it finds one it uses that one for the connection, if it doesn’t find any it has to create the thread.

    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.

    Obviously MySQL response time much faster if its just using threads from the cache and not creating them.

    The number of threads in the cache is dictated by the thread_cache_size variable.

    thread_cache_size = 150

    max_connection

    Max connections is the maximum connections that can be made to the Mysql server and max_user connection is the maximum a user can make to the Mysql server.

    max_connections = 500
    max_user_connections=50


    Binary Logging

    The binary log contains “events” that describe database changes such as table creation operations or changes to table data.

    The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started.

    which keeps track of all updates to the database. It is used for database restoration and replication.

    1) Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

    2) For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master

    Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the master.

    mysqlbinlog --database=db1 /data/binlog/mysql-bin.000047 > recover.sql


    slow_query

    Sometime, a single SQL query may be the cause of all the server’s problems. MySQL has built-in functionality to capture slow query log or identify queries that are not optimal and take a long time to finish.

    To enable slow query log, simply add the following line to MySQL configuration file (my.cnf or my.ini), and then restart the MySQL server:

    # log-slow-queries=/var/log/mysql-slow.log

    Long_query

    You can specify how long a quey needs to run for before it is logged with the "long_query_time" setting. By default this is 10 seconds.

    The default value of long_query_time is 10.

    # long_query_time = 5



    tuning-primer.sh

    Performance Tuning Primer Script is the shell script which gets performance related information from MySQL engine and produce some recommendations for tuning of MySQL server variables. It’s easy to use and offers valuable output for MySQL performance optimization.

    Performance Tuning Primer Script Usage[*] Download the shell script here.[*] Upload the shell script to your server (if not already downloaded directly by the server:[*] wget http://day32.com/MySQL/tuning-primer.sh)[*] Place the script almost wherever you want (one of the best locations is your home directory)[*] Set proper permissions for the shell script to be able to run (chmod 755 should be enough)[*] Run Performance Tuning Primer Script[*] Apply the suggestions of the shell script output


    mysqltuner.pl

    MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.

    Mysql Tuner Script Usage

    1)wget http://mysqltuner.com/mysqltuner.pl
    2)chmod +x mysqltuner.pl
    3)./mysqltuner.pl

    You should carefully read the output, especially the recommendations at the end. It shows exactly which variables you should adjust in the [mysqld] section of your my.cnf

    Whenever you change your my.cnf, make sure that you restart MySQL. You can then run MySQLTuner again to see if it has further recommendations to improve the MySQL performance. This way, you can optimize MySQL step by step.

Working...
X