Magento Expert Forum - Improve your Magento experience

Results 1 to 2 of 2

Using MySQLTuner to Optimize MySQL configuration

  1. #1
    Junior Member golddev's Avatar
    Join Date
    Mar 2013
    Posts
    43
    Thanks
    1
    Thanked 8 Times in 4 Posts

    Default Using MySQLTuner to Optimize MySQL configuration

    Tweaking MySQL is something you need to do regularly. Unlike PHP & Nginx tweaking, this is not a set & forget job!

    We will use mysqltuner for tweaking mysql on a regular basis.

    Tweaking MySQL default config first



    Open /etc/mysql/my.cnf file & scroll down to [mysqld] section.

    You will see many settings & some config variables. Some values are global while some are per-thread values. Its important because if you change something like join_buffer_size from 2M to 4M, it can shoot-up mysql’s max memory utilization by 300M memory (as per default 150 mysql’s max_connections value)

    To start with, adjust following values:

    Code:
    max_connections = 50   #default is 150
    wait_timeout = 30       #default is 28800
    You can leave remaining as it is. Mysqltuner will guide you further.

    Don’t forget to restart mysql. Command: service mysql restart

    Using mysqltuner



    If you are following our setup, you may already have mysqltuner installed. Otherwise run apt-get install mysqltuner on Ubuntu. Non-ubuntu guys can get it from here. It’s just a perl script!

    When you run mysqltuner, it will show you a report with many suggestions. Just follow them. Exact suggestion will vary so its hard to cover all of them here. Rather I will give you some notes some of them are offered by mysqltuner itself.

    Notes:



    • Run mysqltuner after 24 hours. It you don’t, it will remind you by showing “MySQL started within last 24 hours – recommendations may be inaccurate.” Reason: mysqltuner recommendation may prove inaccurate.
    • If it asks you to change value of tmp_table_size or max_heap_table_size variable, make sure you change both and keep them equal. These are global values so feel free to increase them by large chunks (provided you have enough memory on server)
    • If it asks you to tweak join_buffer_size, tweak in small chunks as it will be multiplied by value of max_connections.
    • If it asks you to increase innodb_buffer_pool_size, make it large. Ideally, it should be large enough to accomodate your all innodb databases. If you do not have enough RAM consider buying some. Otherwise try to delete unwanted database. Do not ignore this as it can degrade performance significantly.


    Apart from above, always keep an eye on following lines in Performance Metrics section of mysqltuner report:

    Code:
    [--] Total buffers: 2.6G global + 130.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 15.3G (48% of installed RAM)
    [OK] Highest usage of available connections: 81% (81/100)
    Try to keep maximum possible memory less than 50%. Other lines can tell you, if your site is using too “less” mysql connections. In that case, you can reducemax_connections and increase other buffers more generously.

    Also, whenever you make changes to mysql config and restart mysql server, always run mysqltuner immediately to check if by mistake you haven’t made maximum possible memory usage too high! Ignore any other suggestion it will give for next 24-hours!

    View more threads in the same category:


  2. #2
    Junior Member
    Join Date
    Sep 2018
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    thanks to share useful information with us.

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
  •