Hi there,
I've been reading the advice in this forum with interest. There was recently a post where someone has high CPU usage, which is something I'm experiencing.
I've been tweaking for days, and followed the advice given by setting buffer_pool, key_buffer and query_cache_size, but still can't seem to get MySql to calm down on it's processor usage.
The "show variables" and "show innodb status" output can be seen here
http://rafb.net/paste/results/y1t10X55.html.
Don't worry about reading all this if you don't have time, but if anyone could scan the status output and look for any obvious problems, that would be much much appreciated.
Below are some other observations/facts that may/may not be useful.
Application
=======
My application is a web spidering app, which constantly queries the "articles" table, and also does inserts. These queries are quite well optimised, and use indexes most of the time. The application has 14 threads that access the database concurrently.
Tables
====
My Articles table is quite small, only having 250,000 records (growing 11,000 per day). It's 0.9GB in size, and the index length is 103MB. I'm using InnoDB for all tables at the moment (considering MyISAM for Articles table).
Server
=====
CPUs: 2x Intel Xeon 2.8
Mem: 2GB
1 SCSI Hard Disk
Available Memory
===========
Looking at Perfmon, I can see that the server consistantly has approx 600MB-700MB of available memory free, even when things are at there busiest. I'm tempted to allocate 400MB of this to the buffer_pool, is there anywhere better this memory could go?
Page Faults
=======
There are a lot of page faults occurring (avg >1000/sec) , where something is trying to retreive data from memory, but instead having to get it from disk. Looking in more detail, it seems my application (not MySql) is the culprit for most page faults, desite the fact is uses little CPU, memory and virtual memory.
Queries
=====
It's worth mentioning the queries I'm running.
One of my suspicions is that all the MD5 hashing is CPU intensive, which is making MySql so busy. Can I get any stats on this?
See below:
Stage 1 Duplication Check:
select 1
from articles
where source_id = ?source
and date_indexed >= ?date
and ( url_hash = md5(?url) or headline_hash = md5(?headline) ) limit 1
Stage 2 Duplication Check:
select id
from articles
where (url_hash = md5(?url) and url = ?url)
union
select id
from articles
where (headline_hash = md5(?headline) and headline = ?headline)
union
select id
from articles
where body_leftmost_900_hash = ?bodyhash
union
select id
from articles
where ?headline rlike '([^a-zA-Z0-9]|^)(update|updated|revised|version)[^a-zA-Z0-9]'
and headline like ?headline
limit 1;
Thanks for taking the time to read this, any thoughts welcome.
Tobin
P.S - As you may be able to tell, I'm slightly out of my depth here! We've been tyring to get a MySql DBA involved in our project but so far it's proving difficult to find anyone. If there is someone out there with a bit of free time then please feel free to get in touch with rates and availability. My contact details are at www.tobinharris.com.