Hi,
This is a bit of a long mail, so apologies in advance, I've tried to
five as much information as possible that I think might be useful
regarding my problem.
I have 3 tables where I keep cache records, the structures are something
like
TableA TableB TableC
Id Id Id
... ... ...
The Id is the primary key on all tables and link on this field.
TableA is a 1 to many on TableB which is a 1 to many on TableC
To give you an idea of size, TableA has 8,686,769 rows, TableB has
5,6322,236 rows and TableC has 1,089,635,551 rows.
Whether date is to be expired/deleted is based on some rule about a date
in TableA.
To decide this I run a select query to get all Id's from tableA that
have expired.
This query takes a minute or 2 to run.
I then create a loop for all Id's doing
delete from TableC where id=
delete from TableB where id=
delete from TableA where id=
I am grouping 10,000 loops into a transaction.
My expiry runs on a daily basis and deletes approximately this number of
rows from each table.
TableA 500,000
TableB 4,836,560
TableC 71,534,549
I'm starting to run into problems where my expiry process is now taking
longer than 24 hours to complete.
Looking at system stats, the CPU is only at 33%, memory usage is normal
so it looks to me like the i/o is the bottle neck.
I'm wondering how I can improve the performance.
Firstly, the system I have is
Redhat Enterprise ES rel 3 32-bit
2 x Dual Core CPU
16Gb Ram
1 x 160Gb 10k SCSI drive boot drive
5 x 160gb 10k SCSI drives RAID Level 1 all combined as 1 partition
I have 2 of these in a master/slave config, writes are to the master and
reads to the slave.
MySQL version is 4.1.12 and all tables are InnoDB. File Systems are all
ext3.
I have some basic questions
1. Would upgrading to a later MySQL version give any performance
improvements?
2. If I could split the tables up into smaller tables would this
help ? My dilemma here is that I can split the data, the data would be
in different tables but on the same hardware, the same number of deletes
would still have to happen so would it actually make any difference ?
3. Would an alternative file system help, i.e. raw disk ?
I think I seem to be limited because of running on a linux 32-bit OS, I
am unable to take advantage of the 16Gb memory the hardware, if I try
and allocate more than 1.6Gb in my buffer pool, MySQL will not start, I
seem to recall this is due to a glibc/malloc issue. If I were to move
to a 64-bit I believe this would go away.
I'd be grateful for any advice or suggestions with this.
Cheers,
Marvin
