The last few days have been database/server optimization and scaling day! Before optimizing any of the default mySQL options we decided to test performance against a number of different database engines to see what would happen. As a side note mySQL wasn’t optimized from the beginning, we decided last minute our other box wasn’t going to handle the load well.

Lets try every reasonable mySQL engine…
Since our tables are read only, we tried packing the keys. This had no noticeable effect. With the packed keys we then compressed our core table. We managed to drop the table size from 1.4GB to 1.1GB, which is a pretty significant decrease. This also had no worthwhile effect. Next move was converting the tables over to innoDB. (note: you cant convert compressed tables to innoDB, so un-pack them first :P). My original theory was queries were slow because of table locking, so innoDB would offer us row level locking. That was a wrong assumption and innoDB caused a performance decrease by about 4 fold. The next and last option we tried was MEMORY (old Heap format). A 1.4GB table didn’t fit in the 1.6GB of allocated memory we gave to the engine. It got about 40% of the rows in and tapped out. We ran some queries on the 2.x million rows that actually made it in and the results were decently quick, but this wasn’t an optimal solution on our 2 gigs of RAM.

We finally gave in and decided to really fine tune and optimize mySQL and Apache. It took about 4 hours to get everything exactly how we wanted it. Right off the bat queries responded infinitely faster. Tomorrow will be judgment day, but I don’t expect to see anymore database issues until traffic increases again. Right now our traffic has stabilized and we have a nice core user base for our beta program. We will now begin phase two of operation save you lots of money, and do a full launch soon…

The lesson here is optimizing mySQL should be your number one configuration priority in database applications. Apache only needed a few tweaks, but the mySQL defaults are designed for small sites on low end servers. If the configuration ends up being successful I will post the file.