CODEWITHSUNDEEP
mysqlperformancefetch
Dimas
Dimas

Reputation: 341

MySQL fetch time issue

I've two different MySQL servers with the same database (a copy), both with Ubuntu x64, 4Gb RAM. Both are virtual machines hosted in the same VMWare server.

The first is our old server with MySQL 5.6.33-0ubuntu0.14.04.1-log, and the new one have the version 5.7.17-0ubuntu0.16.04.1 installed.

I'm comparing the performance of some SQL scripts and I noticed that the new server have bigger fetch times with the exact same SQL. Can you help to determinate possible causes?

Ideas? Thx

Upvotes: 1

Views: 3924

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

I suspect your problem is that your buffer pool is allocated, but not yet full of data. As you run queries, it has to fetch data from disk, which is much slower than RAM. As you run those queries again and again, the data required will already be in the buffer pool, and MySQL will take advantage of that. Data that is already in the buffer pool can be read without touching the disk.

You can check how much is in your buffer pool. Here's an example from my test instance (I put "..." because the output is long, and I'm showing an excerpt).

mysql> SHOW ENGINE INNODB STATUS\G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
...
Buffer pool size   65528
Free buffers       64173
Database pages     1339
...

These numbers are in "pages" of 16KB each. You can see I have 64*1024 pages = 1GB allocated, but nearly all of it is free, i.e. unoccupied by data. Only 2% of my buffer pool pages have data in them. It's likely that if I run queries now, it will have to read from the disk to load data. Unless perhaps I have very little data in my database on disk too, and it only fills 2% of my buffer pool even when it's fully loaded.

Anyway, assuming you have more data than the size of your buffer pool, it will gradually fill the buffer pool as you run queries. Then you'll see the ratio of "Database pages" to "Free buffers" change over time (I don't know why they say both pages and buffers, since they refer to the same thing). Subsequent queries should run faster.

Upvotes: 1

Related Questions