1

I have several tables with ~15 million rows. When I create an idex on the id column and then I execute a simple query like SELECT * FROM my_table WHERE id = 1 I retrieve the data within one second. But then, after a few minutes, if I execute the query with a different id it takes over 15 seconds.

I'm sure it is not the query cache because I'm trying different ids all the time to make sure I'm not retrieving from the cache. Also, I used EXPLAIN to make sure the index it's being used.

The specs of the server are:

CPU: Intel Dual Xeon 5405 Harpertown 2.0Ghz Quad Core
RAM: 8GB
Hard drive 2: 146GB SAS (15k rpm)

Another thing I noticed is that if I execute REPAIR TABLE my_table the queries become within one second again. I assume something is being cached, either the table or the index. If so, is there any way to tell MySQL to keep it cached. Is it normal, given the specs of the server, to take around 13 seconds on an indexed table? The index is not unique and each query returns around 3000 rows.

NOTE: I'm using MyISAM and I know there won't be any write in these tables, all the queries will be to read data.

SOLVED: thank you for your answers, as many of you pointed out it was the key_buffer_size.I also reordered the tables using the same column as the index so the records are not scattered, now I'm executing the queries consistently under 1 second.

dan87
  • 333
  • 3
  • 16
  • 2
    Have you adjusted the `key_buffer_size` option? You can allocate multiple key buffers and designate them for specific MyISAM indexes. Read https://dev.mysql.com/doc/refman/5.7/en/myisam-key-cache.html – Bill Karwin Feb 03 '19 at 02:32
  • FWIW, all experienced MySQL users recommend to stop using MyISAM because it [MyISAM doesn't support any ACID properties](https://stackoverflow.com/a/17706717/20860). Use InnoDB instead. – Bill Karwin Feb 03 '19 at 02:33
  • @BillKarwin thanks for your answers, I tried both MyISAM and InnoDB with the same results. I went for MyISAM because I know there won't be any write to those tables, but I don't mind changing back to InnoDB. What you pointed out in your first comment seems very promising, is there anything like that in InnoDB?. Also, I forgot to mention the server is using mysql 5.5 and I can't change that.. – dan87 Feb 03 '19 at 02:47
  • 1
    The closest thing for InnoDB is the buffer pool, which caches both data and indexes. Read https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html – Bill Karwin Feb 03 '19 at 03:01
  • 2
    If you want more specific help with optimizing your query, you should show the query, as well as the result of `SHOW CREATE TABLE ` for each table referenced by the query. – Bill Karwin Feb 03 '19 at 03:02
  • @BillKarwin thanks again I'll look into it to try to figure out by myself, regarding the query I'm executing is exactly the one I put in the question, a simple select using the id, I'll look into `SHOW CREATE TABLE `, buffer pool and key cache,thanks! – dan87 Feb 03 '19 at 03:15
  • Oops, my apologies, I got this mixed up with another question I read earlier today. You did supply the query of course. – Bill Karwin Feb 03 '19 at 05:13
  • 1
    Please show the table structure (e.g. output from `SHOW CREATE TABLE ...`) and MySQL configuration (especially `key_buffer_size` for MyISAM and `innodb_buffer_pool_size` for InnoDB). – crishoj Feb 03 '19 at 18:23

2 Answers2

2

Please provide

SHOW CREATE TABLE
SHOW VARIABLES LIKE '%buffer%';

Likely causes:

  • key_buffer_size (when using MyISAM) is not 20% of RAM; or innodb_buffer_pool_size is not 70% of available RAM (when using InnoDB).
  • Another query (or group of queries) is coming in and "blowing out the cache" (key_buffer or buffer_pool). Look for such queries).
  • When using InnoDB, you don't have a PRIMARY KEY. (It is really important to have such.)

For 3000 rows to take 15 seconds to load, I deduce:

  • The cache for the table (not necessarily for the index) was blown out, and
  • The 3000 rows were scattered around the table (hence fetching one row does not help much in finding subsequent rows).

Memory allocation blog: http://mysql.rjweb.org/doc.php/memory

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

Is it normal, given the specs of the server, to take around 13 seconds on an indexed table?

The high variance in response time indicates that something is amiss. With only 8 GB of RAM and 15 million rows, you might not have enough RAM to keep the index in memory.

Is swap enabled on the server? This could explain the extreme jump in response time.

Investigate the memory situation with a tool like top, htop or glances.

crishoj
  • 5,660
  • 4
  • 32
  • 31