CODEWITHSUNDEEP
mysqlperformanceindexing
Tyler DeWitt
Tyler DeWitt

Reputation: 23576

MySQL query runs 5x slower on staging server than local dev maching

I've got a query that is running 5x slower on my staging server as opposed to my local dev machine.

Stackoverflow doesn't want to play nicely with the formatting; the query, describes, and explains are located here

Looking at the describe statements, I can't see any difference between the local and remote schemas.

The record counts for the 2 machines are in the same order of magnitude (500k vs 600k)

Edit In Response to Comments

It was my highly unscientific approach of throwing the queries into MySQL Workbench and looking at the query time. The local query time was on the order of 1.3 seconds and the remote query time was on the order of 5.2 seconds (so its 4x as slow). I'm sure there's a better way to test this query time.

The machines are different. My dev machine is a Mac Book Pro with 8 gigs of RAM. The staging server is a linode VPS with 512 megabytes of RAM. There shouldn't be much load on the staging server (I'm the only one that uses it). I've noticed most queries run in approximately the same time frame on the local machine and staging server, so I was confused as to why this one had such a drastically different time frame.

RAM Issue

Since a temporary table isn't being used (no mention in the EXPLAINS), is the amount of RAM still an issue?

Output from free

             total       used       free     shared    buffers     cached
Mem:        508576     453880      54696          0       4428     254200
-/+ buffers/cache:     195252     313324
Swap:       262140      19500     242640

Profiling Added to Gist

It looks like the remote is taking 2.5 seconds 'sending data' whereas the local is only taking 0.5 seconds. Is this an I/O issue? (Complete profiling info in gist)

Upvotes: 1

Views: 2537

Answers (1)

Dancrumb
Dancrumb

Reputation: 27529

Your staging server has one sixteenth of the RAM that you Mac Book Pro has.

Without knowing how much RAM is available to your two instances of MySQL, it's hard to be definitive, but that's the first place I'd look.

Also, if you run these queries from the MySQL command line, locally, how do the times compare? It could be that the increase in time is in network transfer and not query processing.

Actually... network transfer time is the first place I'd look... then MySQL memory usage.

EDIT following question updates

The 'sending data' phase is the phase where the server is sending data to the client ref. I don't know exactly how large your dataset is, but 2.5s seems pretty high for what's probably 50kB of data or so. Having looked at the profiling data, nearly all the time is spent sending data, so I'd strongly suspect the network here.

EDIT 2

Some research lead me to this page which indicates that the 'Sending data' is misleading and that this is actually the time spend executing your query.

Thus, I really think you need to be looking at CPU and memory usage on your server since it's specced at a level so much lower than your MacBook.

Upvotes: 4

Related Questions