mysql server: 5.7.17-11-log

Ok, let me rephrase what you have written to ensure that I have understood the problem.

• If the application is directly connected to the MySQL server, then reading the table takes 4 seconds.
• If the application is connected to MaxScale with no cache configured, then reading the table takes 5-7 seconds.
• If the application is connected to MaxScale with a cache configured, then reading the table takes 1-2 seconds (with the data already in the cache).

Is the above correct?

Then, do you with But invalidating cache leads to very slow read from mysql backend server: about 2 minutes! mean that if time-to-live has passed, then reading the table takes 2 minutes?

exactly!

Now, that's interesting.

Let's first do some simple experiments.

• Remove the debug=31 line. Does that have any impact.
• Remove the rules line. Does that have any impact.

How much memory do you have in the computer and what else do run there in addition to MaxScale?

Remove the debug=31 line. Does that have any impact.

Cache logging is disappeared, nothing other is changed: fresh load data from db still takes 2 minutes

Remove the rules line. Does that have any impact.

The behavior is the same. It seems w/o rules MaxScale cache all selects.
Also I commented out
#selects=assume_cacheable
and then
#soft_ttl=120
#hard_ttl=240

Nothing is changed. Fresh load from db always takes about 2 minutes.

How much memory do you have in the computer and what else do run there in addition to MaxScale

There are many processes, but they consume a few resources. The server has CPU idle time 90-95% and 25GB free memory.
MaxScale is running inside OpenVZ container with no resource limits.
Linux 2.6.32-042stab126.1 #1 SMP Wed Nov 15 20:14:46 MSK 2017 x86_64 x86_64 x86_64 GNU/Linux

Thanks for trying those out. I'll see if I can repeat the behaviour.

konst In your setup, how long does it take the first time you read the data through MaxScale with the cache configured?

That is, when the cache does not contain the data but will be populated with the data.

Johan

konst In my test setup, which is much smaller with 200000 rows I get the following kind of figures for SELECT * FROM tbl.

- MariaDB 10.2 direct: 0.10 seconds

- MaxScale direct: 0.14 seconds

- MaxScale cache 1st query: 0.73 seconds

- MaxScale cache 2nd query: 0.03 seconds

- MaxScale cache after ttl: 0.73

So it would be quite interesting to know how long it takes the first time you query through the cache.

I believe the problem appears on huge data.

• mysql direct: loaded 2324270 records in 3.757 s
• MaxScale direct: loaded 2324270 records in 3.689 s
• MaxScale cache 1st query: loaded 2324270 records in 1.930 min
• MaxScale cache 2nd query: loaded 2324270 records in 1.704 s

Ok, so you get the large load-time also the first time the data is fetched and not just when the TTL kicks in.

That's good to know as it suggests the issue is not related to the TTL but to the population of the cache. I'll see if there's something that could be done about that.

konst Ok, I found a problem that could explain the behaviour.

If I build you a custom package, would you be interested in trying it out whether the fix helps your situation?

Ok, I'll try

konst Thanks for helping out. What OS do you use?

> MaxScale is running inside OpenVZ container with no resource limits.
> Linux 2.6.32-042stab126.1 #1 SMP Wed Nov 15 20:14:46 MSK 2017 x86_64 x86_64 x86_64 GNU/Linux

konst Sorry, I meant what kind of package do you need? E.g. Ubuntu or CentOS?

Johan, Please prepare package for CentOS 7

konst Ok, here you are: http://max-tst-01.mariadb.com/ci-repository/2.2.6-MXS-1887/

Please try it out and tell me how it behaves.

Johan

Johan, thank you for your update.
I have tested it and now it works mush better: cache update takes about 6 seconds instead of 2 minutes as before.

konst Thanks for verifying that the fix works as intended.

The fix will be included in 2.2.10 that probably will be released next week or the week after that.

Johan