MySQL Optimization...

[Nhojohl]

I'm running around 19 total sites on one of my servers, only one of which is extremely resource intensive...

The one site gets a large number of hits and is CONSTANTLY making long queries and updating data, the server load usually goes up above 2 with over 30 users online at a time.

How would I go about optimizing my.cnf to help keep it down?

Here's the contents of my current my.cnf

[mysqld]
skip-locking
skip-bdb
skip-innodb
query_cache_limit=8M
query_cache_size=8M
query_cache_type=1
max_connections=500
max_user_connections=500
interactive_timeout=20
wait_timeout=6
long_query_time = 5
connect_timeout=6
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=2048
tmp_table_size=256M
record_buffer=1M
sort_buffer_size=2M
key_buffer_size=8M
read_buffer_size=2M
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
server-id=1
log-slow-queries = /var/log/mysql/mysql-slow.log
concurrent_insert=2

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

After an hour of running MySQL and running a cool script to get your info I got this...

SLOW QUERIES
Current long_query_time = 5 sec.
You have 0 out of 11313288 that take longer than 5 sec. to complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 1
Historic max_used_connections = 6
The number of used connections is 1% of the configured maximum.
Your max_connections variable seems to be fine.

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 5
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MEMORY USAGE
Max Memory Ever Allocated : 51 M
Configured Max Memory Limit : 2117 M
Total System Memory : 3065 M

KEY BUFFER
Current MyISAM index space = 10 M
Current key_buffer_size = 8 M
Key cache miss rate is 1 / 8264
Key buffer fill ratio = 47.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 = 8 M
Current query_cache_used = 2 M
Current Query cache fill ratio = 27.91 %

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256.00 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1020.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

TABLE CACHE
Current table_cache value = 2048 tables
You have a total of 872 tables
You have 884 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current tmp_table_size = 256 M
57% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 58 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1991
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrentcy of inserts on Dynamic row-lenght tables consider setting 'concurrent_insert=2'

Thanks!

EDIT: Should have added, server is

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
Processor #1 speed: 2814.860 MHz
Processor #1 cache size: 512 KB

with 1GB of ram, spamAssassin is disabled along with Box trapper, ASSP is installed. CSF firewall and LFD. The security check also shows that it's a 68/70. The only things not done are phpSuEXEC and no disabled functions in PHP.

Also, eaccelerator:

eAccelerator support
enabled	Version 	0.9.5 
Caching Enabled 	true 
Optimizer Enabled 	true 
Memory Size 	 	16,777,180 Bytes 
Memory Available 	9,887,112 Bytes 
Memory Allocated 	6,890,068 Bytes 
Cached Scripts  	109 
Removed Scripts 	0
Cached Keys 	 	0

 

[dempsey]

A bit offtopic, but what was the script you used to get that info about your mysql connections, it looks very useful

 

[Nhojohl]

I put it in one of my sites directories for you... I also did a lot of browsing and tweaking and got my server running fast as it ever has...For anyone who cares its info is after the script...

wget http://www.day32.com/MySQL/tuning-primer.sh
chmod 0644 tuning-primer.sh
./tuning-primer.sh

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
Processor #1 speed: 2814.860 MHz
Processor #1 cache size: 512 KB

1024 MB RAM

my.cnf contents...

[mysqld]
skip-locking
skip-bdb
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
max_user_connections=500
interactive_timeout=20
wait_timeout=6
long_query_time = 5
connect_timeout=6
thread_cache_size=80
key_buffer=128M
join_buffer=1M
max_allowed_packet=16M
table_cache=2048
tmp_table_size=512M
record_buffer=1M
sort_buffer_size=1M
key_buffer_size=8M
read_buffer_size=1M
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
read_rnd_buffer_size=768K
server-id=1
log-slow-queries = /var/log/mysql/mysql-slow.log
concurrent_insert=2
low_priority_updates=1

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

 

[freedman]

the original script and other handy related scripts can be found here:

http://www.day32.com/MySQL/

 

[Nhojohl]

the original script and other handy related scripts can be found here:

http://www.day32.com/MySQL/

Thanks - Appreciate it:D

 

[procam]

It just makes me shudder when u guys use scripts from places that cant keep their own configurations straight!!
http://www.day32.com/pricing.php

 

[freedman]

It just makes me shudder when u guys use scripts from places that cant keep their own configurations straight!!
http://www.day32.com/pricing.php

regardless of where one gets a script, one should review the code and what it does before running it.

While someone may have a poor server configuration, it doesn't mean they didn't come up with a gem of a script.

I wonder when they'll realize their sales are slipping? :P

Keith

 

[Nhojohl]

I like the script, it's honestly helped. I'm now running my Pentium 4 2.8Ghz with a 1024k cache and 1024 MB of ram with the following my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
safe-show-database
bind-address=127.0.0.1
log-slow-queries=/var/log/mysql_slow_query.log
skip-locking
skip-bdb
skip-innodb

query_cache_limit=1M
query_cache_size=8M
query_cache_type=1

max_connections=500
max_user_connections=500
interactive_timeout=20
wait_timeout=6
long_query_time=5
connect_timeout=6

thread_cache_size=80
key_buffer=128M
join_buffer=1M
max_allowed_packet=16M
table_cache=2048
tmp_table_size=512M
record_buffer=1M
sort_buffer_size=1M
key_buffer_size=6M
read_buffer_size=1M
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
read_rnd_buffer_size=2MB

server-id=1
concurrent_insert=2
low_priority_updates=1

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
no-auto-rehash

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqldump]
quick
max_allowed_packet=16M

 

[katmai]

im gonna give it a try as well.

 

[TodoInTX]

Response from script writer

Hello Folks... I'm glad to see my script getting some play out and about. There have been some bugfixes in the past couple of days and I recently have added support for FreeBSD and MacOS.

I'm very open to feedback/patches to improve this script if anyone has some ideas.

The problems mentioned earlier about about my pricing page are largely because I've given up on whole the virtual hosting thing. I just let that part of the site die since joining MySQL. That page been taken down now.

For other community projects that you may consider helpful see:
http://forge.mysql.com/

Thanks!!!

Matt

 

[oulzac]

I have a couple questions about this, I am getting this when running your script:

MEMORY USAGE
Max Memory Ever Allocated : 67 M
Configured Max Memory Limit : 4 G
Total System Memory : 7 G

I have it set for 1G why is it only saying 67M

KEY BUFFER
Current MyISAM index space = 758 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 9
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 = 32 M
Current query_cache_used = 1 M
Current Query cache fill ratio = 5.75 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere

How is that too high? should I lower them, or just leave them?

TEMP TABLES
Current tmp_table_size = 1.00 G
53% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size

How in the heck is that 53% used?

 

[Nhojohl]

MEMORY USAGE
Max Memory Ever Allocated : 67 M
Configured Max Memory Limit : 4 G
Total System Memory : 7 G

I have it set for 1G why is it only saying 67M

Max Memory Ever Allocated is the maximum memory it has ever actually used, it's not the cap.

KEY BUFFER
Current MyISAM index space = 758 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 9
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 = 32 M
Current query_cache_used = 1 M
Current Query cache fill ratio = 5.75 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere

How is that too high? should I lower them, or just leave them?

Well, key buffer, you're currently only using 1% of the memory you allocated to it...
The query cache, this just means that of 32M you've allocated you've only used 1M, basically you're allocating 32 times the amount of memory you actually need.

Since these values are so small you probably wouldn't need to modify them, however, I'd try setting the query cache size to something like 4M or 8M.

TEMP TABLES
Current tmp_table_size = 1.00 G
53% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size

How in the heck is that 53% used?[/QUOTE]
I'm not 100% sure on this one, I just leave mine at 2048M and it gives me a green light. (My box only has 1GB so I'm not sure how that one works out)


Hope this helps... I've been running the last config I posted for a week or more now and it's like a frigging rocket.


EDIT: Forgot to mention, be sure you are running this after mySQL has been running for a day or two... otherwise the values are more than likely going to be way off of what you actually use.

 

[oulzac]

EDIT: Forgot to mention, be sure you are running this after mySQL has been running for a day or two... otherwise the values are more than likely going to be way off of what you actually use.

I think this is my problem, I rebooted right after making my.cnf chagnes.

 

[freedman]

you're not reading the output of the script!!!
first section.. MAX EVER ALLOCATED is different from MAX MEMORY LIMIT!

as I read this, your total mysql memory usage at any moment in time maxed out at 67M
you're allowing mysql to allocate 4G of ram.. this would seem to indicate you're overallocating memory. mysql only needs 67M and you're tying up 4G for it.

secton 2.. key buffer:
you have 758M of indexes, you're allocating 16M to buffer the index keys
you're using 1% of the key buffer size.. therefor it might be you're allocating too much since you're only using 1%
same with query cache.

the recommendation to lower them, I'm guessing, is that you're overallocating which is uneccessary. save the ram for something else. however, given how much memory you have, you're not killing yourself here.. but all these numbers factor into the MAX MEMORY LIMIT number... which you're definitely not making use of.

I'm not sure what causes temp tables to be used or disgarded, but it appears however your db's are being utilized, it's causing mysql to generate a LOT of temp tables.. so much so it exceeds the 1G you've allocated for them..

if you're after an overall performance improvement on your server in general, I'd look to use less memory for mysql and use that memory as tmpfs for /tmp

since most webservers generate a lot of /tmp files (especially if you're using mod_gzip), this alone will make a HUGE improvement in webserver throughput.
your db seems way overallocated.

my .02

I have a couple questions about this, I am getting this when running your script:

MEMORY USAGE
Max Memory Ever Allocated : 67 M
Configured Max Memory Limit : 4 G
Total System Memory : 7 G

I have it set for 1G why is it only saying 67M

KEY BUFFER
Current MyISAM index space = 758 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 9
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 = 32 M
Current query_cache_used = 1 M
Current Query cache fill ratio = 5.75 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere

How is that too high? should I lower them, or just leave them?

TEMP TABLES
Current tmp_table_size = 1.00 G
53% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size

How in the heck is that 53% used?

 

[TodoInTX]

I have a couple questions about this, I am getting this when running your script:

TEMP TABLES
Current tmp_table_size = 1.00 G
53% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size

How in the heck is that 53% used?

This is a bug in the script, I have recognized there is a problem with the TEMP TABLE checks. Before version 1.3.0 the tuning script did not take into consideration that in memory tmp tables are also limited by max_heap_table_size which is 16M by default. Raising tmp_table_size without also raising max_heap_table_size is ineffective. Also any tmp table that contains BLOB/TEXT data types will always be created on disk.

 

[erick_paper]

This is a bug in the script, I have recognized there is a problem with the TEMP TABLE checks. Before version 1.3.0 the tuning script did not take into consideration that in memory tmp tables are also limited by max_heap_table_size which is 16M by default. Raising tmp_table_size without also raising max_heap_table_size is ineffective. Also any tmp table that contains BLOB/TEXT data types will always be created on disk.

I actually found your script quite handy as it doesn't actually change anything, just advises. My output said "You may consider using InnoDB tables selectively". Which is exactly what I have been thinking for a table that is updated very frequently.

My questions:

1. How to turn InnoDB on? The following code does not work for me (I just picked it off the web) --

#skip-innodb
innodb_buffer_pool_size=1G
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_data_file_path=idbdata1:200M:autoextend
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /iblogs
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=24

2. Will your script also analyze the performance of my InnoDB settings?

 

[Nhojohl]

I'm honestly unfamiliar with InnoDB but have been thinking the same things as you... I can however point out that the code you pasted:

#skip-innodb
innodb_buffer_pool_size=1G
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_data_file_path=idbdata1:200M:autoextend
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /iblogs
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=24

Possibly isn't working because you need to start and /or compile MySQL with the '--with-innodb' command.

Also, I'm not sure if your hints about "my" script were pointed at me but the script is actually not mine its Matthew Montgomery's, you can find the newest version here:
http://www.day32.com/MySQL/tuning-primer.sh

 

[foussa]

I put it in one of my sites directories for you... I also did a lot of browsing and tweaking and got my server running fast as it ever has...For anyone who cares its info is after the script...

wget http://www.day32.com/MySQL/tuning-primer.sh
chmod 0644 tuning-primer.sh
./tuning-primer.sh

Hello,

I do the your make her understand but its give an error.

./tuning-primer.sh
-bash: ./tuning-primer.sh: Permission denied

What do I do not must what. Help me please

 

[nabuhonodozor]

Thanks Nhojohl for great script.
best regards,
Piotr

 

[abdoh2010]

great script and for me it's initial

for the people who had problem with the script i advice them to try to change chmod from 0644 to 0777 and it will work