I have a multi-process program that performs, in a 2 hour period, 5-10 million inserts to a 34GB table within a single Master/Slave MySQL setup (plus an equal number of reads in that period).
The table in question has only 5 fields and 3 (single field) indexes. The primary key is auto-incrementing.
I am far from a DBA, but the database appears to be crippled during this two hour period. So, I have a couple of general questions.
1) How much bang will I get out of batching these writes into units of 10?
Currently, I am writing each insert serially because, after writing, I immediately need to know, in my program, the resulting primary key of each insert. The PK is the only unique field presently and approximating the order of insertion with something like a Datetime field or a multi-column value is not acceptable.
If I perform a bulk insert, I won't know these IDs, which is a problem.
So, I've been thinking about turning the auto-increment primary key into a GUID and enforcing uniqueness. I've also been kicking around the idea of creating a new column just for the purposes of the GUID. I don't really see the what that achieves though, that the PK approach doesn't already offer.
As far as I can tell, the big downside to making the PK a randomly generated number is that the index would take a long time to update on each insert (since insertion order would not be sequential).
Is that an acceptable approach for a table that is taking this number of writes?
Thanks,
Ben
