Marvin Wright wrote:
I have 3 tables where I keep cache records, the structures are something
like
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.
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
My suggestions:
1) Add an (expirydate,primary key) composite index on table A and make
sure your foreign keys are in place
2) Turn on cascading deletes for these three tables or (less optimally)
use a single multi-table delete in a stored procedure to delete lots of
rows (in A) per query not singles
3) run the delete query with a limit of 10000 rows or so in a
transaction, use show innodb status to monitor how much rollback space
the innodb engine has left and up the number rows if possible.
4) If your data integrity can take the risk turn off innodb's flush on
commit for this connection during the deletes.
5) Run optimise table once a week, if your deleting lots of records for
a while the database is probably fragmented. If nessasary for
availablity use a DR slave/master and run optimise table on the DR pair
before promoting them to live.
Then look at the memory. If you cant upgrade the OS to use all that
memory (we do on 32 bit servers) at least create a huge ram disk and
tell MySQL to use it as temporary storage.
Hope that helps
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
