SEARCH

How-To Geek

Using a MySQL Performance Tuning Analyzer Script

When you are working on increasing the speed of your website, a very important piece is making sure you get every last drop of performance out of your database server. Unfortunately, for most of us that aren’t normally database administrators this can be a difficult proposition.

There’s a number of performance tuning scripts that will analyze your server settings and current status and give you information on recommended changes that you should make. You shouldn’t necessarily follow all of the suggestions, but it’s worthwhile to take a look at anyway.

The script I’ve been using gives you recommendations for the following:

  • Slow Query Log
  • Max Connections
  • Worker Threads
  • Key Buffer
  • Query Cache
  • Sort Buffer
  • Joins
  • Temp Tables
  • Table (Open & Definition) Cache
  • Table Locking
  • Table Scans (read_buffer)
  • Innodb Status
  • Once you download the script, you’ll need to make it executable with the following command:

    chmod u+x tuning-primer.sh

    If you run this script as a regular user, it will prompt you for your password, so you’ll have to make sure to set access accordingly. If you run it as root it’ll pick up the mysql password from Plesk if you have that installed.

    I’ve cut out a lot of the output, which had a lot more recommendations, but was just too long to fit on the page.

    ./tuning-primer.sh

            — MYSQL PERFORMANCE TUNING PRIMER –
                 – By: Matthew Montgomery –

    MySQL Version 4.1.20 i686

    Uptime = 5 days 10 hrs 46 min 5 sec
    Avg. qps = 4
    Total Questions = 2020809
    Threads Connected = 1

    Server has been running for over 48hrs.
    It should be safe to follow these recommendations

    ———– snipped ————–

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 8 M
    Current query_cache_used = 7 M
    Current query_cach_limit = 1 M
    Current Query cache fill ratio = 89.38 %
    However, 254246 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won’t cache query results that are larger than query_cache_limit in size

    ———– snipped ————–

    Looks like I need to increase my query cache… I set it to only 8MB but it’s cleaning out the cache far too often.

    ———– snipped ————–

    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 32 M
    Of 35170 temp tables, 74% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.

    ———– snipped ————–

    This type of information is just invaluable when you are trying to tune the performance of your website.

    Download MySQL Performance Tuning Primer Script

    Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

    • Published 10/4/07

    Comments (13)

    1. bushido

      ./tuning-primer.sh: line 1164: bc: command not found
      ./tuning-primer.sh: line 1197: [: -gt: unary operator expected
      ./tuning-primer.sh: line 350: [: max_memoryHR: integer expression expected
      ./tuning-primer.sh: line 356: [: max_memoryHR: integer expression expected
      ———– [ chopped by editor for length] —————
      TABLE SCANS
      ./tuning-primer.sh: line 331: bc: command not found
      Current read_buffer_size = K
      Current table scan ratio = 7 : 1
      read_buffer_size seems to be fine

      plese help.

    2. The Geek

      bushido:

      The problem you are having is that you are missing some of the utilities required by the script. You’ll have to install the missing utilities, such as bc.

    3. Matthew Montgomery

      Thanks for using my script :-)

      If you do see any bugs or recommendations that seem ill advised please let me know. My e-mail address is found inside the script header.

      Thanks
      Matt

    4. Torzak

      Check also MysqlTunner :
      http://rackerhacker.com/mysqltuner/

      Antoher good script for auto tunning your mysql server

    5. Craig M. Rosenblum

      Is there anything like this for windows users?

    6. Steve Bacher

      I made a slight modification to the cecho function so the output can be optionally directed to a file:

      [ -t 1 ] && echo -e “$color”
      echo -e “$message”
      [ -t 1 ] && tput sgr0 # Reset to normal.
      [ -t 1 ] && echo -e “$black”
      return

      (I also changed the yellow and green settings because I can never read yellow on my xterm window.)

      – seb

    7. Robert

      Anyone have a windows equivalent?

    8. David O'Dwyer

      For anyone getting a “unexpected operator” error on startup please try changing the default shell
      from:
      #!/bin/sh
      to:
      #!/bin/bash
      this fixed the problem for me on my Ubuntu 8.04 box

    9. Fred

      I don’t think there’s a windows equivalent..

    10. UbuntuLinuxHelp

      For those with the BC error…

      If you’re on an Ubuntu server:

      SSH to the server as root and run the command:

      aptitude install bc

      If you’re on a CentOS server:

      SSH to the server as root and run the command:

      yum install bc

      Then you can run the script afterwards with the command:

      ./tuning-primer.sh

    11. Anna

      If you’re using a Windows server, then MySQL performance is the least of your problems.

    12. Twat Hater

      @Anna

      Get your head out of your ass and or the sand, you obviously have little to no real knowledge of windows server environments, but thanks for the useless troll, you are a credit to the linux community.

      You have been such a help, I’ll remember recommend you to……well….NO ONE!!

      Nowt but a grade A TWAT!!!

    13. Anna Amore

      Anna is correct, if you can’t run a perl script or compile bc for your OS then you should think about using a server OS for your server. I was building huge Windows servers when you were licking jelly off of your little sticky fingers, so don’t tell me I know nothing about rebooting Windows servers.

    Enter Your Email Here to Get Access for Free:

    Go check your email!