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.