Underperforming mySQL during updates

[NancyJ]

Currently we have a database with a main table containing 3 million records - we want to increase that to 10 million but thats not a possibility at the moment.
Nearly all 3 million records are deleted and replaced every day - all through the day - currently we're handling this by having 2 sets of tables - 1 for inserting, 1 for searching.

A block of records (10k - 1 million) (distinguished by a client identifier field) happen on the 'alt' set of tables, then records are inserted from CSV files using LOAD_DATA_INFILE (csv file created by loading xml or csv files in proprietary client formats, validating and rewriting data in our format)
To facilitate faster search times summary tables are updated from the latest update - ie. insert into summarytable select fields from alttable join on supportingtables where clientID = $clientID
Then we LOAD INDEX INTO CACHE for all the relevant tables (key_buffer is set to 512MB)
Then we switch a flag in an info table to tell the searches to start pulling from these updated tables and then we repeat the process on the table that was previously the search table.

During this time even simple queries can end up in the slow query log and I cant figure out why.

This query benchmarks at approx 0.25s
SELECT fldResort AS dest_name, fldResort as ap_destname, fldDestinationAPC, min( fldPrice ) AS price, fldCountry as country, fldBoardBasis, fldFlyTime, sum( fldOfferCount ) as offercount
FROM tblSummaryFull WHERE fldStatus = 0 AND fldDepartureDate >= '2006-12-27' AND fldDepartureDate <= '2007-01-02' AND fldDuration >= 7 AND fldDuration <= 7 AND tblSummaryFull.fldSearchTypes LIKE '%all%'
GROUP BY dest_name, fldBoardBasis ORDER BY price
Its using where, temporary and filesort with a key length of 3 - examined 23k rows -
The log reads:
Query_time: 11 Lock_time: 0 Rows_sent: 267 Rows_examined: 23889

But even the most basic queries are being affected

SELECT * FROM tblResortInfo WHERE fldClientID=17 AND fldAccomRef='3883'

Benchmarked at 0.02s (there are 0 results for this query)
From the log: # Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 1

The site is at very low traffic atm, (around 3k visitors per day)

I'm doing everything I can to improve performance and query speeds before next summer (where we're aiming for around 30k per day) but I cant seem to do anything about this and if queries wont run at their optimal speed then all this work has been for nothing.

Its probably worth noting that our CPU usage is barely at 50% - ditto with RAM

[txbakers]

Maybe time to up the CPU? Dual processor perhaps? Even a quad?

[NancyJ]

See above comments about not even utilising our current CPU capacity.

We have 2x 3.2Ghz Xeons and 4GB RAM

Just as another example of a poorly performing query -

# Query_time: 16 Lock_time: 0 Rows_sent: 1 Rows_examined: 2
SELECT fldTitle, fldLive, fldResort, fldDesc, fldKeyWords, fldTVViews,
fldTVToday, fldPosted, fldFeature,AVG(fldRating) AS avgRating
FROM tblVidReviews LEFT JOIN tblVidRating
ON tblVidReviews.fldVidID=tblVidRating.fldVidID
WHERE tblVidReviews.fldVidID=83 GROUP BY tblVidReviews.fldVidID

and from Explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tblVidReviews const PRIMARY PRIMARY 4 const 1
1 SIMPLE tblVidRating ref fldVidID fldVidID 4 const 2

[FalseDawn]

You say your queries are "poorly performing", but without knowing exactly how much data is in your table at the time of the query, it's hard to know if that is the case or not.

Are your queries having to swap to disk at all, because that will kill performance. I know you said RAM was only half used, but disk swapping can still occur.

I suspect that maybe you need to tweak the parameters in your mysql configuration file quite severely, especially with 4G of Ram.

[FalseDawn]

Also, I'm not really sure what your plan is with those 2 tables, but you might need to rebuild indexes on your tables at some point with all those inserts going on (and make sure you have appropriate indexes on the tables as well - they are especially useful for "range" type queries, like in your first example).

[NancyJ]

The vids table has only a few hundred rows. Another exmaple is a simple insert into an empty table - 3 columns - 23s. The only other *noticeable* queries running was 1 LOAD_DATA_INFILE into the main data table.
I say noticeable because there are hundreds of smaller queries that I assume were running but too fast to show in the processlist

I'm noticeing the same poor performance occasionally on other queries eg. select field from table where index = const = 6-15s on queries like that

[NancyJ]

look like we do have quite a bit of swap disk activity going on - what can I do about that?
I've done quite a bit of my.cnf tweaking

key_buffer= 16M
max_allowed_packet= 16M
thread_stack= 192K
max_connections = 600
table_cache=12000
max_connect_errors=999999
#
# * Query Cache Configuration
#
query_cache_limit= 64M
query_cache_size = 512M
query_cache_type = 1
key_buffer_size = 512M
read_buffer_size = 4M
sort_buffer_size = 32M
tmp_table_size = 512M
max_heap_table_size = 512M
low_priority_updates = 1
long_query_time = 5
bulk_insert_buffer_size = 64M
join_buffer_size = 4M

[FalseDawn]

You didn't say what OS you are on - windows/linux? I know in some configurations, to be able to access > 2GB of RAM, you need to change certain parameters. Maybe only half your RAM is accessible, and also check that both processors are being utilized.

Also, your query cache size looks a bit big - do you really need a 512MB cache? Check its utilization - I bet you can drop this down to 64MB or less. Your other settings look OK.

[NancyJ]

We're on linux
I wanted the cache to be big enough but we never seem to get more than 5k queries in there - we get a lot that dont go in but I never can figure out why.

Qcache_free_blocks 2 k
Qcache_free_memory 528 M
Qcache_hits 7 M
Qcache_inserts 144 k
Qcache_lowmem_prunes 0
Qcache_not_cached 34 k
Qcache_queries_in_cache 5 k
Qcache_total_blocks 13 k

Thats what our status looks like at the moment.

What I dont understand is if theres been 144k cache inserts and 0 low memory prunes - how can there be only 5k in cache?

[Frank_Rizzo]

When inserting a csv file it will be advantageous to either have that file on a seperate disk in the system or even use a ramdisk. Copy the file to ramdisk and run the insert on that file.

I don't know your disk setup (presumably some kind of raid) but it is always advantageous to have a totally seperate disk for temp, tmp dirs and log files.

[Frank_Rizzo]

BTW,

You may want to run MySysop and monitor the results for a while. It could help you tune up the parameters for optimum effect. At a guess you have some of them way over the top.

mysysop can be found at forge.mysql.com under snippets.

[NancyJ]

at 512MB QCache we still get low memory prunes - I've reduced it to 128Mb and thats definately too low - I dont like how the pruning is handled though - it seems like as soon as it gets full, nearly the whole cache is pruned.
Also, the max size of all our index files is less than 512Mb - but at a key_buffer_size of 1GB it is still full within seconds of restarting. How can this be?

We have only 1 disk with a single partition (thats how it was sold to us - already set up)

[Frank_Rizzo]

Disk looks to be your bottleneck. Too much going on - I presume webserving, logging, database read / write, indexes, temporary files, and then of course all the OS stuff including disk swapping....

See if you can get a secondary disk put in. Create dirs on it for logs and tmp files.

Here's a typical setup for the second disk:

/secondary/var/log #store standard log files
/secondary/apache_log #store access log, error logs
/secondary/tmp #standard tmp for os and apps
/secondary/mysql_tmp #exclusive tmp for mysql
/secondary/backups #backup dir for sequential backups of primary disk

on your primary disk you would have

/var/lib/mysql

or where ever for your mysql data etc.

On the point about memory. It's one of those things where you need to try different options as it's not easy to explain withouth testing. I think what is happening is that mysql is taking all the ram and is not leaving enough for the os to cache files.

[edited by: Frank_Rizzo at 3:55 pm (utc) on Nov. 6, 2006]

[Frank_Rizzo]

Just thinking of a few more things I have done to tune to the max.

Again, I'm presuming your sever does the database stuff and webserving all in one. If you are using php then try this:

1. Force sessions to be stored in /dev/shm and not a disk directory
2. Use a php cache for your php files and again force them to use /dev/shm and not a directory
3. Strip out all uneccessary apache modules
4. Compile php with the minimum configure possible