I'm developing a web app and have run into a large performance difference between the speed of MySQL lookups on my development and live environments.

DATABASE: 2.5GB, three tables (one table has 27mil records). FULLTEXT indexes and searching. Everything is indexed where needed. Database contains records of all addresses in the UK.

DEV SERVER: Two-year old Samsung i5 Ultrabook. Windows 8 running XAMPP. Single SSD. Most lookups take under 200ms, deliberately stressing the system results in a maximum lookup time of 5 seconds.

LIVE SERVER: "Hybrid" VPS (max 8 nodes per server). Hardware specs are "Dual Intel Xeon processors with a minimum of 8 CPU cores, 24GB RAM, Raid 10 Drives with 15K SAS drives". Most lookups take about 3-5 seconds, with a stress test resulting in 30 second lookups.

I am happy with the database performance on my development server, but I really need the production server to match or better this.

Before I splash out on a dedicated box for the app, in your experience, would the bottleneck likely be disk I/O or CPU time?

Just posting an answer for the record:

I tried the app on a VPS with RAID-ed SSDs. The difference is night and day and lookups are now even faster than my development machine. This is with a default MySQL installation, with no edits made to caching, max memory usage and other parameters.

  • You know if the OS is linux, you can use iostat -x 1 to see if you're I/O bound. – David Schwartz Nov 25 '13 at 11:59

Ignore MySQL for the moment - this is the same for all databases. Physics does not care about open source.

Generally you are IO Limited UNLESS you are not. The last means having enough RAM to cache all IO operations in memory - typical for large OLAP workloads. But anything transactional WILL have IO as a limit much earlier than CPU, unless the CPU is pathetic (i.e. you can always build a server that forces CPU to overload - atoms may be a bad choice for database servers).

ow, production server. SOmeone majorly made "stupid" mistakes buying thise.

Raid 10 Drives with 15K SAS drives".

That is around 450 IOPS per drive.

Single SSD

That is around 40.000 to 60.000 IOPS - can go up to 90.000 depending on disc and load.

See the problem? The SSD is flying around the SAS discs - it is a game changer. SAS is slow. I use a lot of 10k SAS discs in my main database - but with SSD as transparent caching layer.

So, unless you have enough RAM to make the SAS discs irrelevant (cache everything in memory) and then preload (and that is doable on your small database of just 2.5g).... it is IO bound.

In your particular case I would check configuration. MySQL standard config will IIRC not use a lot of memory, regardless whether it is there. With a tiny database (2.5g) you should cache it all in memory. Even on the laptop. Looks like a configuration issue.

  • Thanks a lot TomTom!!! You've pointed me in the right direction. – R C Nov 25 '13 at 10:58
  • TomTom, what SSD caching software do you use and what kind of IO boost do you see in your MySQL server setup with the same? – shaloo shalini Mar 9 '14 at 13:21
  • I do not use SSD caching software at all and I do not use MySQL. I use SQL Server and my SSD caching is done by an Adaptec 71605Q raid controller, transparent to the operating system, or the SSD are directly used by the operating system (and hence the database), or in case of the db used in what adaptec calls a hybrid raid (1xssd,1xdisc in a mirror, all ready to the ssd). IO boosts - I hav a Raid 6 where I store virtual machiens that is built of 5400 RPM drives (5 of them) and i normally, outside of patching days, have single digit latency on it. THanks to a generous SSD cache of some hundred – TomTom Mar 9 '14 at 16:02
  • gb that gets all the often used sectors that are active. You have to try it out yourself, but if you need IO then basically an SSD is the ONLY answer -and a decent one, because many low end ones have pathetic write guarantees. There are some models that are ok (as in: 5 total writes per day for 5 years guaranteed - samsung 843T in this case) while others come as 400gb and allow 20gb writes average per day (ouch). – TomTom Mar 9 '14 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.