Problems Deleting Data I'm stuck on trying to delete some information on my MySQL database.

I've never used a database this big and when I developed this application I didn't imagine it would get so big (maybe small for you guys, but for me it's huge).

My thought was:


I have a table with "mos" Managed Objects (PK moid) (Image 1)
I have a table with "periods" (PK periodid) (Image 1)
I have a table that joins "Period" with the MO (PK moperiodid, FKs periodid, moid) (Image 1)
I have 200 tables (the number of tables is variable according to user inputs) that uses the moperiodid as FK (Image 2)
Then, when I want to clean the "historical data", I just delete cascade from MO or from PERIOD.


That was what I thought, but in my real world, it isn't happening well.

Everything works fine for Selects and Inserts, but for the first time I want to begin cleaning data, the main idea was to keep only the last X days (configured according to users input also).

For every day I have 3094555 new moperiods, even the delete of only one is not working:

0   13  22:46:19    delete from parser_customer_period_mo where id = 1  Error Code: 1205. Lock wait timeout exceeded; try restarting transaction    51.169 sec


And after every try the InnoDB Writes/Reads per Second goes crazy for 1 hour. (Image 3)

I have no idea about how I'll delete the old information, and this is the small system, I'm building the second one of 30 systems, and the second will have 12 times the amount of data.

Image 1:



Image 2:



Image 3:
parser_customer_period_mo is a many-to-many relationship table?  It probably does not need id.  (Getting rid of unnecessary indexes helps at some level.)  (This may have somehow caused the lock wait timeout.)

200 similar tables -- bad design.  (But seems to be 'necessary' in this situation.)

FKs cost something, but probably that won't explain what you are seeing.

IN ( SELECT ... ) is an inefficient construct (especially in older versions of mysql), see if you can turn it into a JOIN.

OR inhibits the use of an index.  (Probably cannot improve on that.)

Change innodb_buffer_pool_size to be about 70% of available RAM.  This should cut back the I/O a lot.