MySQL Buckeling after a million rows?
 

Hello everyone,

I am having issues with MySQL buckeling after a million rows. While I have never taken mysql to this extent before, I would hope it wouldn't have issues with this. a million rows should be nothing for a RDMS. This is affecting Deletes, selects, and updates.

Does anyone know why it would behave like this?
I have indexes on all relevant columns

PK - Auto Increment (record_id)
Indexes on 3 other columns (account_id, site_id, status);

I also have a unique compound across 3 columns (site_id, url, status) (I ran into a situation of which required mass inserts using INSERT IGNORE.. and this provided the best way to let the database kick out rather then me making 2 connections to check for existance first then insert if not exists)


I guess out of this... if I have a compound unique should I remove the indexes that already exist as part of the unique?

I would suggest moving to another database oracle or mssql. Mysql is built more for smaller sites, its not really up to par on sites this big. Just what I have gotten from talking with a couple other guys in the field.

Absurd, MySQL is more than capable of being used for large sites! It certainly shouldn't be choking with only a million rows. drewbee, is it specifically that figure that causes the problems or is there a degradation as the number of rows increases?

There's not really much information to go on in your first post.

P.S. Take a looksee at some of the MySQL customers. Some pretty huge clients and websites there.

Wikipedia!? Don't they have over a billion pages? and youtube has more than 100,000,000 videos.. ...wow

Right. That is exactly what I was thinking. The performance degrades as it approaches the millionth mark. Probobly from about 800k+. I thought that 1M rows would be childs play for a database, so it has to be something that I have setup incorrectly.


Does anyone know if it is pointless that I have unique keys and indexes for the same columns? Does creating a unique key on a column also automatically create an index, thus making the indexes I created for the already-unque-key columns pointless?

That is not pointless, it's plain wrong. When creating unique [indexes] on a field, remove the existing index from it first. Unique also creates an index on the given field.

Ok... Thanks! I wasn't sure if the unique index was a searchable index and only was used during updates/inserts, but not by selects.

I have been proven wrong, way wrong.

No worries Cory :) No database can handle that type of data out of the box. There is definate tuning / optimization that needs to be done to handle that. I'm almost starting to think i created some type of locking issue with it, rather then just being from the sheer row count.

I am batch processing in 25, however, it runs a little like this:

Query 25 records, update 25 records with status of 'in-process' (already two queries in the first batch);

loop over each record and update it with its unique page information saving information in an array; also grabs possible new records from each page it is data mining from;

Update the 25 records with there new information and set status to 'complete'
INSERT IGNORE new possible records, silently failing insert on duplicate keys

.......
So for every 25 records that I process, I make 4 queries to the same table. I don't think it can get much more optimized then this as I need the newly pulled records to be marked as inprocess, and while updating the 25 records I use a transaction to process this in hopes for performance... however I currently believe I am making 25 seperate calls to the database during this update (unique data with each unique record), regardless of the transaction... I need to research that further;