Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 2.2.6
-
Fix Version/s: 2.2.10
-
Component/s: cache
-
Labels:None
-
Environment:mysql server: 5.7.17-11-log
-
Sprint: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
|
}
|
}
|
}
|