1

I am working and experimenting with MySQL a bit, have database several GBs in size. And I have found interesting thing: when MySQL works, Windows doesn't cache .ibd files database consists of. For example, I run a query, see how Windows reads from drive actively. I copy the corresponding files in FAR Manager to nul at the same time several times and each time they are read from drive.

I can see in Windows 7's Resource Monitor how cache memory is raising while file is read and is dropping back right after it's done. Sometimes I saw how pausing of copying in one FAR helps reading to other FAR :) But this is not obligatory. If MySQL service is turned off, caching works normally - e.g., if a table's .ibd file is 200 MB and there is 1 GB of free RAM or at least cache RAM, it is cached after one or two reads.

Why does this happen and is it possible to fix this?

This is my notebook for everything and development, so I don't want to allocate much memory to MySQL (maybe 200 MB is ok, but 500 is too much). Windows 7 x64, Core i5, 8 GB RAM, hybrid SSHD (1 TB HDD + 8 GB flash), about 8 GB ReadyBoost cache, InnoDB tables (default, I'm not a big MySQL expert). ReadyBoost helps a lot, I suppose. I see system writes a lot (say 500 KB/s) to ReadyBoost.sfcache file and reads a lot (1-5 MB/s).

Improve this question
3
  • I don't understand what information this will add but ok. MySQL is on, before reading 200 MB table file: prntscr.com/ekj75d, after: prntscr.com/ekj7k7 MySQL is off, before: prntscr.com/ekj807, after: prntscr.com/ekj84s I have just noticed that cache doesn't drop right after MySQL start. It drops after accessing the corresponding table, e.g. by selecting from it in SQL-Front. So accessed one table - 200 MB minus, accessed another - minus 150 MB (if they both were cached before)
    – Mikhail M
    Mar 16, 2017 at 4:26
  • SELECT @@INNODB_BUFFER_POOL_SIZE;. This is the optimum place for caching -- not the OS -- and is one of the few tunable parameters that should always be customized. What's your setting?
    – Michael - sqlbot
    Mar 16, 2017 at 11:39
  • @Michael-sqlbot, 134 217 728. Increased recently. I would prefer MySQL to "configure on-the-fly" - so if I run several hard requests Windows would cache it, and if I don't use SQL for some - MySQL service wouldn't occupy much memory
    – Mikhail M
    Mar 16, 2017 at 13:16

1 Answer 1

Reset to default
0

I found solution quite good for me: I upgraded to latest MySQL 5.7 and now change pool size on the fly:

set global innodb_buffer_pool_size = 512 * 1024 * 1024;

Performance improvement is very big when pool size is big enough e.g. during massive inserts.

I got a lot of troubles during upgrade (MySQL 5.7 didn't accept 5.5's databases, mysql_upgrade don't even start, MySQL Front crashes and so on). But it looks like 5.7's performance is also higher in some cases (with equal pool size).

Improve this answer

Not the answer you're looking for? Browse other questions tagged .