I've just upgraded one of our servers. The old one was mysql4 and 1gig of ram. The new one is mysql 5 and 2gigs of ram, and about 4 times the processing power. It is running Plesk for hosting basically.
One of my hosting customers has a specialist site that does a lot of mysql stuff. It seems to be creating a lot of temp tables when I use show full processlist.
The problem is this: that site is running increadibly slowly. really slowly. It was far faster on the old system - at least 10 times faster. And it seems to be down to mysql from what I can see, as that is using 100% of one processor a lot of the time, while everything else barely registers in comparison. All other sites are fine. Blasting away in fact. It is just that one site that is so much slower when doing its mysql stuff.
my.cnf on the old system had basically no performance related configuration lines in it. It was pretty naked.
On the new system I'm using the stock my.cnf from centos 4.4 which is as follows (I added the max_packet_size because the default is lower and I could not import a particular databse from the old system)
Code: Select all
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-bdb
set-variable = max_allowed_packet=2M
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
1) I do not understand why I have two section each with set-variables in them -- mysqls_safe and mysqld - can someone enlighten me? When I had put stuff in my.cnf before I always put it under [mysqld].
2) You'll note there is no query caching. Is it safe to enable it? I have a memory of someone trying and things going horribly wrong.
3) Any suggestions for some typical values? What does scott's my.cnf in his RPM have?
I've tried my best to understand what mysqlreport is telling me but I'm failing to figure out exactly what I should be doing:
Code: Select all
MySQL 5.0.27 uptime 0 2:1:4 Tue Jul 3 22:42:31 2007
__ Key _________________________________________________________________
Buffer used 1.33M of 8.00M %Used: 16.56
Current 2.25M %Usage: 28.09
Write hit 52.34%
Read hit 98.37%
__ Questions ___________________________________________________________
Total 148.04k 20.4/s
DMS 81.21k 11.2/s %Total: 54.85
Com_ 47.37k 6.5/s 32.00
COM_QUIT 17.34k 2.4/s 11.71
+Unknown 2.12k 0.3/s 1.43
Slow 40 0.0/s 0.03 %DMS: 0.05
DMS 81.21k 11.2/s 54.85
SELECT 73.28k 10.1/s 49.50 90.24
INSERT 4.81k 0.7/s 3.25 5.92
UPDATE 1.68k 0.2/s 1.13 2.07
DELETE 1.44k 0.2/s 0.97 1.78
REPLACE 2 0.0/s 0.00 0.00
Com_ 47.37k 6.5/s 32.00
set_option 20.91k 2.9/s 14.12
change_db 9.81k 1.4/s 6.63
show_variab 9.28k 1.3/s 6.27
__ SELECT and Sort _____________________________________________________
Scan 26.93k 3.7/s %SELECT: 36.75
Range 2.99k 0.4/s 4.07
Full join 403 0.1/s 0.55
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1.91k 0.3/s
Sort range 2.60k 0.4/s
Sort mrg pass 0 0/s
__ Table Locks _________________________________________________________
Waited 110 0.0/s %Total: 0.13
Immediate 85.67k 11.8/s
__ Tables ______________________________________________________________
Open 64 of 64 %Cache: 100.00
Opened 7.22k 1.0/s
__ Connections _________________________________________________________
Max used 31 of 100 %Max: 31.00
Total 17.59k 2.4/s
__ Created Temp ________________________________________________________
Disk table 4.62k 0.6/s
Table 17.70k 2.4/s
File 5 0.0/s
__ Threads _____________________________________________________________
Running 1 of 25
Cached 0 of 0 %Hit: 0.01
Created 17.59k 2.4/s
Slow 0 0/s
Any suggestions would be appreciated.
Faris.
