11 Responses to “Install MySQL Performance Tuning Primer Script”

1. Identifying slow MySQL queries | cPanelConfig - cPanel server configuration guide, on December 13th, 2007 at 4:35 am Said:

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

2. jake, on January 11th, 2008 at 6:51 am Said:

   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. yolau, on January 11th, 2008 at 6:57 am Said:

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

   Let me know if it woks now.

4. jake, on January 16th, 2008 at 4:54 am Said:

   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. reb, on February 8th, 2008 at 2:41 pm Said:

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

6. Chad, on February 27th, 2008 at 10:32 pm Said:

   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. Ittsmee2, on February 28th, 2008 at 1:33 am Said:

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

8. yolau, on February 29th, 2008 at 3:37 am Said:

   Try running:

   chmod 755 tuning-primer.sh

   before you run the script

9. Me, on June 19th, 2008 at 12:23 pm Said:

   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. Jim, on July 16th, 2008 at 12:53 pm Said:

    “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. Jim, on July 16th, 2008 at 12:57 pm Said:

    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

Leave a Reply