I am trying to work out if we need to upgrade our server hardware or need to configure the MySQL settings to make things faster.

A simple SELECT * From table took 1.6 seconds from the command line. However mysql-slow.log shows that it takes 7 seconds when one of our processes from another server executes it. This seems very slow to me. The table contains three INT columns only.

It's an Ubuntu Rackspace cloud server. We have 4GB RAM and only 1.2GB is used up.

So do the numbers look about right or is there something wrong?

Additional information

I have added the EXPLAIN EXTENDED SELECT * From table here:

enter image description here

  • What happens if you rif you run the MySQL client on these other machines? I'm thinking network latency here! – Vérace Sep 14 '17 at 12:38
  • @ Vérace I am logging into MySQL from a remote machine and running the queries. So network latency could be an issue with the numbers that I see. However should the mysql-slow.log show those times because of network latency? Does it not give the times for the queries running locally? – I Newton Sep 14 '17 at 12:45
  • What happens if you have a PRIMARY KEY on the table? Re. the log - does the query appear no matter where you perform it or what? It is my understanding that what appears in the slow query log has nothing to do with the network latency - it all happens on the server! – Vérace Sep 14 '17 at 18:04

Both 1.6s and 7s seem reasonable. Some issues that lead to variations:

  • Caching (Was I/O needed, or was everything sitting in RAM -- a factor of 10 variation is possible)
  • Distance to other server (Ping time)
  • Bandwidth to other server (Mb/sec)
  • Client's ability to absorb that much data that fast
  • Whether client is receiving it in chunks or all at once

But my question to you is -- what can you do with 3.7M rows all at once? That is, this does not seem like a reasonable benchmark or whatever.

Back to the "caching". Try this:

  1. restart mysqld
  2. run your test
  3. run your test again -- likely to run faster.

Main cache: for a 4GB server and InnoDB tables, innodb_buffer_pool_size should be something like 2G.

| improve this answer | |

The query is done only after the results have been delivered, so latency will be included, imho. Could you post in your original questions EXPLAIN EXTENDED SELECT * ....?

| improve this answer | |

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.