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.
Best Answer
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.
That is around 450 IOPS per drive.
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.