cPanel Config

Welcome to cPanelConfig the fastest growing cPanel configuration and troubleshooting guide on the internet. Please take the time and register. We would love to have your contribution to this completely free cPanel resource. We are updating this daily so be sure to visit us on a regular basis.

Install MySQL Performance Tuning Primer Script

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.40 out of 5)
Loading ... Loading ...

Posted by yolau


Tuning the performance of MySQL can be a really hard job to do.
There are many thinks to consider and no two servers are identical so there is no universal solution.
Tuning Primer is a script that will help you tune your mysql installation by providing very healthy recommendations based on past mysql records.
For the script to be efficient you must run the mysql server for at least 48 hours.

Installation is extremely simple:

  1. Download the script
  2. wget http://day32.com/MySQL/tuning-primer.sh

  3. Change the permissions for the file

    chmod 755 tuning-primer.sh

  4. Run it
  5. ./tuning-primer.sh

  6. Apply the sugesttions

Enjoy!

Share this:
  • Digg
  • del.icio.us
  • Slashdot
  • StumbleUpon
  • Netvouz
  • DZone
  • ThisNext
  • MisterWong
  • Wists
  • De.lirio.us
  • Furl
  • MyShare
  • Smarking
  • Technorati
  • YahooMyWeb

If you enjoyed this post, make sure you subscribe to my RSS feed!

31 Responses to “Install MySQL Performance Tuning Primer Script”

  1. [...] Install MySQL Performance Tuning Primer Script [...]

  2. I was doing an optimzation search and got to know abt this particluar post.Nice.

    but when i try to run the shell script, i get a permission denied message.

    what is the reason? I am running it as root.

  3. Before step 2 try running:
    chmod 755 tuning-primer.sh

    Let me know if it woks now.

  4. it should be

    #sh tuning-primer.sh

    with CentOS 5..thanks, it worked.but anyone would like to comment on my result?(the red ones)

    MAX CONNECTIONS
    Current max_connections = 500
    Current threads_connected = 6
    Historic max_used_connections = 32
    The number of used connections is 6% of the configured maximum.
    You are using less than 10% of your configured max_connections.
    Lowering max_connections could help to avoid an over-allocation of memory
    See “MEMORY USAGE” section to make sure you are not over-allocating

    MEMORY USAGE
    Max Memory Ever Allocated : 327 M
    Configured Max Per-thread Buffers : 2 G
    Configured Max Global Buffers : 170 M
    Configured Max Memory Limit : 2 G
    Physical Memory : 1.97 G

    Max memory limit exceeds 90% of physical memory

    KEY BUFFER
    Current MyISAM index space = 5 M
    Current key_buffer_size = 96 M
    Key cache miss rate is 1 : 1663
    Key buffer fill ratio = 1.00 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 64 M
    Current query_cache_used = 39 M
    Current query_cache_limit = 4 M
    Current Query cache Memory fill ratio = 61.21 %
    Current query_cache_min_res_unit = 4 K
    Query Cache is 16 % fragmented
    Run “FLUSH QUERY CACHE” periodically to defragment the query cache memory
    If you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.
    MySQL won’t cache query results that are larger than query_cache_limit in size

    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.

    Any comments?

  5. i’ve tried running tuning-primer.sh on http://www.photogiga.com and it works like a horse. it’s good!

  6. This didn’t seem to work too well.

    Original my.cnf

    http://pastebin.com/m60e1a5f9

    After running this tuner script

    http://pastebin.com/m2bd637f0

    It reduced allowance greatly.

    Specs:

    Dual xeon 3.4GHz
    4GB memory
    mysql 5
    cpanel 11
    apache 2 / php 5

    Please suggest proper settings.

  7. why do I get permission denied when I try to run the script?

  8. Try running:

    chmod 755 tuning-primer.sh

    before you run the script

  9. I’m getting this all the time (Debian Etch) and I did the chown already:

    ./tuning-primer.sh: line 332: bc: command not found

    Example:

    TEMP TABLES
    tuning-primer.sh: line 332: bc: command not found
    Current max_heap_table_size = M
    tuning-primer.sh: line 332: bc: command not found
    Current tmp_table_size = M
    Of 718 temp tables, 18% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Created disk tmp tables ratio seems fine

    TABLE SCANS
    tuning-primer.sh: line 332: bc: command not found
    Current read_buffer_size = M
    Current table scan ratio = 4 : 1
    read_buffer_size seems to be fine

    TABLE LOCKING
    Current Lock Wait ratio = 0 : 5781
    Your table locking seems to be fine

  10. “bc” is an arbitrary precision calculator language. It’s ordinarily installed in /usr/bin. It should be easily installed with “yum”. It looks like it’s available as a separate package.

    Sorry for the late reply. I just noticed your post.

    Jim

  11. It looks like ‘yum’ is not distributed with Debian. Instead, “apt-get” is: http://www.debian.org/doc/manuals/apt-howto/ch-apt-get.en.html

    It’s very likely that the update’s available via apt-get.

    Jim

  12. I am having a problem running this script. every time I run it, it says:

    INITIAL LOGIN ATTEMPT FAILED -

    Testing Stored for passwords: None Found

    - RETRY LOGIN ATTEMPT FAILED -

    Could not auto detect login info!

    Do you have your login handy ? [y/N] : y
    User: root
    Password:
    Would you like me to create a ~/.my.cnf file for you? [y/N] : n
    - FINAL LOGIN ATTEMPT FAILED -

    Unable to log into socket: /var/lib/mysql/mysql.sock

    any help?

  13. This was a terrific script – worked perfectly as per your instructions (CPanel based, CentOS machine) – THANK YOU!!

  14. Dody: check if your mysql server is up. Then run again that script.

  15. DOdy: try running the script with “root”

  16. How would u go about running this script in a windows environment vs. Linux?

  17. sensizolmuyor:/# ./tuning-primer.sh

    - INITIAL LOGIN ATTEMPT FAILED -

    Testing Stored for passwords: None Found

    - RETRY LOGIN ATTEMPT FAILED -

    Could not auto detect login info!

    Do you have your login handy ? [y/N] : y
    User: root
    Password:
    Would you like me to create a ~/.my.cnf file for you? [y/N] : n
    - FINAL LOGIN ATTEMPT FAILED -

    Unable to log into socket: /tmp/mysql.sock
    sensizolmuyor:/#

    i have this problem too.

  18. Unable to log into socket: /tmp/mysql.sock
    Bad error message.

  19. thanks goodd..

  20. Do you have your login handy ? [y/N] ?????

  21. had recently herpes virus found in my blood. what I haveto do??? I’m in panic…

  22. Are you kidding me!!??? Swine FLU??? In sommer in heat? Is it undisturbed possible? I am from Cali and I uneasy! Slow hale!

  23. Help please. One week ago I have lost the credit card on which was about 20000 dollars. In some days I have gone to bank, have written the notice on loss of a credit card and to me have restored my credit card, but on a credit card any more there was no my money. I have addressed in bank, they have told, that you have drawn out money though I did not make it. Help please, that to me to make, any service chase credit card can eat. Thanks, I wait for a response!Help please. One week ago I have lost the credit card on which was about 20000 dollars. In some days I have gone to bank, have written the notice on loss of a credit card and to me have restored my credit card, but on a credit card any more there was no my money. I have addressed in bank, they have told, that you have drawn out money though I did not make it. Help please, that to me to make, any service
    chase credit card fraud can eat. Thanks, I wait for a response!

  24. [...] MySQL :: MySQL 5.0 Reference Manual :: 7.5.3 Tuning Server Parameters MySQL Optimization, part 2 Install MySQL Performance Tuning Primer Script | cPanelConfig – cPanel server configuration guide Reply With Quote   + Reply to Thread « Previous Thread | – [...]

  25. elektrik tasarruf cihazı hakkında ayrıntılı bilgi

  26. Hello All,

    For “Unable to log into socket: /tmp/mysql.sock” error, just add the default bin mysql directory on your $PATH.
    It seems that mysqladmin command file not found by the tuning-primer script whith this the script work fine.

  27. It’s Probably Not What You Think! If you looked and felt your best… you wouldn’t be reading this. You’re reading because you carry around an extra 15… 30 or even 60 pounds of ugly fat.
    And you’re tired of it.Maybe you packed it on over the last 20 years… maybe it’s all new weight. It doesn’t matter. Either way, you hate what you see when you look at yourself in the mirror…

    This is the starting of the video on the site. the weight loss presentation on this page will show you how I lost 42 pounds and 10 inches off my waist in just 45 minutes a week, while my wife lost nearly 60 pounds and 8 dress sizes while enjoying yummy foods all day! You’ll be pleased to know this isn’t any type of gimmick… these are REAL techniques with healthy nutrition that stops your junk food cravings, unique brief full body workouts that ignite your body’s fat burning abilities, and the lifestyle and mindset tricks you need to stay lean, strong and healthy for life. You’re going to want to watch the entire presentation to see exactly how we made our incredible transformations, how thousands are simply copying our plan, and how you can do the same.

    Dont pay now…Try it first, start seeing your body change and then decide…

  28. thanks.

    http:/www.wmarea.net

  29. http://www.wmarea.net

    webmaster forum

  30. Am I able to cancel the order inside thirty days if I opt to not keep it?

  31. I agree with you, good luck. thanks

Leave a Reply