This is a very good shell script for fine-tuning MySQL variables.
This script takes information from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..."
to produce sane recomendations for tuning server variables.
It is compatable with all versions of MySQL 3.23 and higher (including 5.1).
# Currently it handles recomendations 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
Script location: http://www.day32.com/MySQL/tuning-primer.sh
Full Version: fine-tuning MySQL
Nice script 
I've tested it but I got a strange result with the read_buffer_size setting.
If I use read_buffer_size = 3M I got this message:
If I use read_buffer_size = 2M I got this message:
:confused: :confused:
I've tested it but I got a strange result with the read_buffer_size setting.
If I use read_buffer_size = 3M I got this message:
CODE
TABLE SCANS
Current read_buffer_size = [COLOR="Red"]2 M[/COLOR]
Current table scan ratio = 20520 : 1
read_buffer_size is over 2 MB there is probably no need for such
a large read_buffer
Current read_buffer_size = [COLOR="Red"]2 M[/COLOR]
Current table scan ratio = 20520 : 1
read_buffer_size is over 2 MB there is probably no need for such
a large read_buffer
If I use read_buffer_size = 2M I got this message:
CODE
TABLE SCANS
Current read_buffer_size = [COLOR="red"]1 M[/COLOR]
Current table scan ratio = 11450 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving
your use of indexes.
Current read_buffer_size = [COLOR="red"]1 M[/COLOR]
Current table scan ratio = 11450 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving
your use of indexes.
:confused: :confused:
Hi,
It is recommended that for exact result after changing my.cnf parameter you will restart the mysql server and wait for 48 hours. This is because after restarting mysql will clear all memory and allocate new memory. Based on last 48 hours logs we could go for exact modification.
Regarding read_buffer_size, each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans,you might want to increase this value.
thx
It is recommended that for exact result after changing my.cnf parameter you will restart the mysql server and wait for 48 hours. This is because after restarting mysql will clear all memory and allocate new memory. Based on last 48 hours logs we could go for exact modification.
Regarding read_buffer_size, each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans,you might want to increase this value.
thx
Hello Manuel,
the strange thing is that if I set read_buffer_size to 3M it reads 2M if I wrote 2M it reads 1M .... don't know why !
the strange thing is that if I set read_buffer_size to 3M it reads 2M if I wrote 2M it reads 1M .... don't know why !
it's always good to see some tips how to fine tune mysql and such analytic script is always handy. Thanks a lot!
is this an error?
MEMORY USAGE
Max Memory Ever Allocated : 1228 M
Configured Max Memory Limit : 5007 M
./tuning-primer.sh: line 322: [: 9.59829e+09: integer expression expected
./tuning-primer.sh: line 328: [: 9.59829e+09: integer expression expected
Total System Memory : 9.59829e+09 bytes
also...
TEMP TABLES
Current tmp_table_size = 32 M
80% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size
I don't have a tmp_table_size listed is it referring to query_cache_size = 32M
MEMORY USAGE
Max Memory Ever Allocated : 1228 M
Configured Max Memory Limit : 5007 M
./tuning-primer.sh: line 322: [: 9.59829e+09: integer expression expected
./tuning-primer.sh: line 328: [: 9.59829e+09: integer expression expected
Total System Memory : 9.59829e+09 bytes
also...
TEMP TABLES
Current tmp_table_size = 32 M
80% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size
I don't have a tmp_table_size listed is it referring to query_cache_size = 32M
Hi,
I am not sure but I have also faced the same issue with RHEL 3 where as it is working smoothly with newer version of OS.
Thanx
I am not sure but I have also faced the same issue with RHEL 3 where as it is working smoothly with newer version of OS.
Thanx
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.