Migrating largish database: MyISAM vs. InnoDB

sneakyimp

I've got a website that's been running about ten years. It's accumulated some 2.4M users and started to have unacceptable performance problems with new registrations some time ago. We are redesigning it.

Obviously, I'm hoping to improve performance for the user table. The original table, despite having proper indexes, was causing performance issues. It is my belief that this is because the data file has grown quite large. The original table has nearly 60 columns in it (!) so my plan is to create a really simple basic user table:
id
email_address
* hashed_password
and break out all the other columns into separate tables (e.g., user_profile) or something like that.

So I created the new simpler user table, chose InnoDB for the engine, and concocted a query to import records from the live db. I was pretty surprised to see that the new table (352.7Mib including indexes according to PHPMyAdmin) was not that much smaller than the old table (818.5Mib including indexes) despite the fact that I had eliminated over 90% of the columns in the table AND had decreased the length of the email_address field from 255 chars to just 70 chars.

When I changed the engine on the new table from InnoDB to MyISAM it resulted in a dramatic reduction in the db size (270.2Mib including indexes).

So I started wondering about the differences between MyISAM and InnoDB and found this rackspace page which looks pretty helpful. I'm still wondering though if maybe MyISAM might be better. I don't foresee using a lot of transactional type stuff on the user table? Or perhaps I should....

The basic performance-killing operation is that I want to check if an email address is in use before allowing someone to register with it (emails must be unique). Two approaches come to mind:
try to insert email address and catch error that pops up with UNIQUE index prevents double entry of a particular address
use transactional approach: LOCK TABLE, check for address, if it doesn't exist, INSERT, then UNLOCK.

In practice (and this is very hard to simulate), I have classrooms with dozens of people all trying to register at the same time and the table-locking approach sounds like it might really have a fundamental performance problem. Can anyone comment here? What is the best way to approach this problem?

dalecosp

sneakyimp wrote:
It is my belief that this is because the data file has grown quite large.

Hard to say without more information about it, but I'm not sure that's true; what evidence do you have to back up this belief?

Terabytes is not big data, petabytes is
MySQL Database File Size Limit

As for your two approaches to email checking, the 2nd (LOCK TABLE) is approximately equal to what will happen if you do use MyISAM. If you really expect to have that much concurrency in registration requests, I'd suggest approach #1 (UNIQUE index) and InnoDB, at least for that particular table.

I assume you have checked your queries with EXPLAIN to check if there's some additional optimization you can make?

And, regarding the table/file size, does "show table status where name like "user%" show you anything helpful? Data_free? Index_length? Avg_row_length?

sneakyimp

Thank you, dalecosp, for encouraging me to go back to basics. I constructed a test query script which generates novel query to check the db (in case the query cache is on) and I can check for 10,000 different randomly-generated email addresses in an average of 1ms. This has encouraged me to go back and look into the actual registration process where I'm finding some other issues. Some pretty ugly stuff in this old code.

Bonesnap

When I was working on my password hash database I was running into performance issues as well. Reading was still quite fast but inserting was taking its time.

During my research to write my queries better, I came across info about this same question: which engine to use. Because it was determined I should use transactions I ended up using InnoDB.

From what I remember, MyISAM is great for reading but not as much for inserting. Also if I remember correctly MyISAM does not support foreign keys, so that could be a deal breaker for you.

sneakyimp

Bonesnap;11049927 wrote:
When I was working on my password hash database I was running into performance issues as well. Reading was still quite fast but inserting was taking its time.

During my research to write my queries better, I came across info about this same question: which engine to use. Because it was determined I should use transactions I ended up using InnoDB.

From what I remember, MyISAM is great for reading but not as much for inserting. Also if I remember correctly MyISAM does not support foreign keys, so that could be a deal breaker for you.

Thanks for this. I think it is likely I'll be using transactions during registration because I must a) check to see if an email/username is in use and b) if not, insert it.

bradgrafelman

sneakyimp;11050059 wrote:
I think it is likely I'll be using transactions during registration because I must a) check to see if an email/username is in use and b) if not, insert it.

That's not justification to use transactions but rather UNIQUE indexes.

Bonesnap

bradgrafelman;11050067 wrote:
That's not justification to use transactions but rather UNIQUE indexes.

ARE YOU BACK?!?!