Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
2.2.6
-
None
-
mysql server: 5.7.17-11-log
-
MXS-SPRINT-60
Description
I'm testing MaxScale for caching selects from mysql server. I've created simple application that periodically reads data from a huge test table with 2M rows and 512MB size using query
SELECT /*TestCache*/ db_load_test.* FROM db_load_test |
The application directly connected to mysql server reads the entire table in 4 seconds.
The application connected to MaxScale server reads the same data in 5-7 seconds.
I configured caching as follows:
[TestCache]
|
type=filter
|
module=cache
|
soft_ttl=240
|
hard_ttl=240
|
selects=assume_cacheable
|
rules=/etc/maxscale.cnf.d/TestCache.json
|
debug=31
|
[TestService]
|
filters=TestCache
|
TestCache.json:
{
|
"store": [
|
{
|
"attribute": "query",
|
"op": "like",
|
"value": "/\\*TestCache\\*/"
|
}
|
]
|
}
|
The application reads data from the cache pretty quickly: 1-2 seconds. But invalidating cache leads to very slow read from mysql backend server: about 2 minutes! It is actually performing read operation as I see this query in mysql process list.
MaxScale> show filters
|
Filter 0x1513910 (TestCache)
|
Module: cache
|
{
|
"rules": {
|
"store": [
|
{
|
"attribute": "query",
|
"op": "like",
|
"value": "/\\*TestCache\\*/"
|
}
|
]
|
},
|
"storage": {
|
"lru": {
|
"size": 502529185,
|
"items": 5,
|
"hits": 154,
|
"misses": 6,
|
"updates": 0,
|
"deletes": 0,
|
"evictions": 0
|
},
|
"real_storage": {
|
"size": 502529185,
|
"items": 5,
|
"hits": 159,
|
"misses": 0,
|
"updates": 0,
|
"deletes": 0
|
}
|
}
|
}
|