silvrhand
Thu 22nd Jun '06, 6:20pm
Can someone point me to helping optimize this query or tell me where it is coming from, query is listed below all of the system information:
System Information:
Dual Xeon 2.8GHZ 2G RAM
3x73G RAID5 partition
Redhat ES4
Apache 2.0.52/PHP 4.3.9
[root@240z mysql]# mysqlreport --all
MySQL 4.1.7-log uptime 0 1:0:21 Thu Jun 22 13:58:29 2006
__ Key __________________________________________________ _______________
Buffer usage 68.73M of 256.00M %Used: 26.85
Write ratio 0.60
Read ratio 0.00
__ Questions __________________________________________________ _________
Total 417.78k 115.38/s
QC Hits 148.79k 41.09/s %Total: 35.61
DMS 138.50k 38.25/s 33.15
Com_ 65.44k 18.07/s 15.66
COM_QUIT 65.06k 17.97/s 15.57
-Unknown 3 0.00/s 0.00
Slow 135 0.04/s 0.03 %DMS: 0.10
DMS 138.50k 38.25/s 33.15
SELECT 80.56k 22.25/s 19.28 58.17
UPDATE 47.85k 13.22/s 11.45 34.55
INSERT 7.38k 2.04/s 1.77 5.33
REPLACE 1.64k 0.45/s 0.39 1.19
DELETE 1.06k 0.29/s 0.25 0.77
Com_ 65.44k 18.07/s 15.66
change_db 65.16k 17.99/s 15.60
show_tables 117 0.03/s 0.03
set_option 60 0.02/s 0.01
__ SELECT and Sort __________________________________________________ ___
Scan 6.35k 1.75/s %SELECT: 7.88
Range 17.88k 4.94/s 22.19
Full join 10 0.00/s 0.01
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 3.20k 0.88/s
Sort range 20.30k 5.60/s
Sort mrg pass 4 0.00/s
__ Query Cache __________________________________________________ _______
Memory usage 38.18M of 128.00M %Used: 29.83
Block Fragmnt 5.36%
Hits 148.79k 41.09/s
Inserts 80.17k 22.14/s
Prunes 1 0.00/s
Insrt:Prune 80.17k:1 22.14/s
Hit:Insert 1.86:1
__ Table Locks __________________________________________________ _______
Waited 1.69k 0.47/s %Total: 0.67
Immediate 249.22k 68.83/s
__ Tables __________________________________________________ ____________
Open 1.05k of 2500 %Cache: 42.08
Opened 1.06k 0.29/s
__ Connections __________________________________________________ _______
Max used 251 of 400 %Max: 62.75
Total 65.07k 17.97/s
__ Created Temp __________________________________________________ ______
Disk table 1.50k 0.41/s
Table 2.66k 0.73/s
File 8 0.00/s
======================================
my.cnf file
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
skip-locking
skip-innodb
skip-name-resolve
max_connections = 400
key_buffer = 256M
join_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 1M
table_cache = 2500
thread_cache_size = 384
thread_concurrency=4 # Try number of CPU's*2
connect_timeout = 10
tmp_table_size=128M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 32780
wait_timeout = 15
connect_timeout = 10
# Query_time: 63 Lock_time: 57 Rows_sent: 1 Rows_examined: 393
SELECT COUNT(*) AS threads,
SUM(IF(thread.lastpost >= 1150997545 AND open <> 10, 1, 0)) AS newthread
FROM thread AS thread
WHERE forumid = 51
AND sticky = 0
AND visible = 1
AND thread.lastpost >= 1148417025;
# User@Host: root[root] @ localhost []
# Query_time: 35 Lock_time: 29 Rows_sent: 1 Rows_examined: 0
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate
) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS display
groupid,
language.phrasegroup_forumdisplay AS phrasegroup_forumdisplay,
language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.
userid) INNER JOIN language AS language ON (language.languageid = IF(user.languageid = 0,
1, user.languageid))
WHERE user.userid = 58872;
System Information:
Dual Xeon 2.8GHZ 2G RAM
3x73G RAID5 partition
Redhat ES4
Apache 2.0.52/PHP 4.3.9
[root@240z mysql]# mysqlreport --all
MySQL 4.1.7-log uptime 0 1:0:21 Thu Jun 22 13:58:29 2006
__ Key __________________________________________________ _______________
Buffer usage 68.73M of 256.00M %Used: 26.85
Write ratio 0.60
Read ratio 0.00
__ Questions __________________________________________________ _________
Total 417.78k 115.38/s
QC Hits 148.79k 41.09/s %Total: 35.61
DMS 138.50k 38.25/s 33.15
Com_ 65.44k 18.07/s 15.66
COM_QUIT 65.06k 17.97/s 15.57
-Unknown 3 0.00/s 0.00
Slow 135 0.04/s 0.03 %DMS: 0.10
DMS 138.50k 38.25/s 33.15
SELECT 80.56k 22.25/s 19.28 58.17
UPDATE 47.85k 13.22/s 11.45 34.55
INSERT 7.38k 2.04/s 1.77 5.33
REPLACE 1.64k 0.45/s 0.39 1.19
DELETE 1.06k 0.29/s 0.25 0.77
Com_ 65.44k 18.07/s 15.66
change_db 65.16k 17.99/s 15.60
show_tables 117 0.03/s 0.03
set_option 60 0.02/s 0.01
__ SELECT and Sort __________________________________________________ ___
Scan 6.35k 1.75/s %SELECT: 7.88
Range 17.88k 4.94/s 22.19
Full join 10 0.00/s 0.01
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 3.20k 0.88/s
Sort range 20.30k 5.60/s
Sort mrg pass 4 0.00/s
__ Query Cache __________________________________________________ _______
Memory usage 38.18M of 128.00M %Used: 29.83
Block Fragmnt 5.36%
Hits 148.79k 41.09/s
Inserts 80.17k 22.14/s
Prunes 1 0.00/s
Insrt:Prune 80.17k:1 22.14/s
Hit:Insert 1.86:1
__ Table Locks __________________________________________________ _______
Waited 1.69k 0.47/s %Total: 0.67
Immediate 249.22k 68.83/s
__ Tables __________________________________________________ ____________
Open 1.05k of 2500 %Cache: 42.08
Opened 1.06k 0.29/s
__ Connections __________________________________________________ _______
Max used 251 of 400 %Max: 62.75
Total 65.07k 17.97/s
__ Created Temp __________________________________________________ ______
Disk table 1.50k 0.41/s
Table 2.66k 0.73/s
File 8 0.00/s
======================================
my.cnf file
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
skip-locking
skip-innodb
skip-name-resolve
max_connections = 400
key_buffer = 256M
join_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 1M
table_cache = 2500
thread_cache_size = 384
thread_concurrency=4 # Try number of CPU's*2
connect_timeout = 10
tmp_table_size=128M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 32780
wait_timeout = 15
connect_timeout = 10
# Query_time: 63 Lock_time: 57 Rows_sent: 1 Rows_examined: 393
SELECT COUNT(*) AS threads,
SUM(IF(thread.lastpost >= 1150997545 AND open <> 10, 1, 0)) AS newthread
FROM thread AS thread
WHERE forumid = 51
AND sticky = 0
AND visible = 1
AND thread.lastpost >= 1148417025;
# User@Host: root[root] @ localhost []
# Query_time: 35 Lock_time: 29 Rows_sent: 1 Rows_examined: 0
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate
) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS display
groupid,
language.phrasegroup_forumdisplay AS phrasegroup_forumdisplay,
language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.
userid) INNER JOIN language AS language ON (language.languageid = IF(user.languageid = 0,
1, user.languageid))
WHERE user.userid = 58872;