Query cache bad performance on huge result set.

[konstantinosss]

Hello!
I have a lot of services which periodically reload their configuration/state from mysql DB. Some tables in the DB have many rows. To decrease load on mysql server, I'm trying to use query caching function of proxysql.
Unfortunately, the query cache shows performance problems with heavy queries and a lot of client requests.
For testing this, I created test mysql table with 2M rows and size 512MB. Reading from the table takes about 5 seconds.
Test application runs 10-20 parallel threads which just reads the data from the table using query
SELECT * FROM db_load_test in infinite loop with a small delay of 1 second between queries. The application connects to proxysql and proxysql connects to a single backend mysql server without load.
There is one mysql query rule specifies caching the select query for 2 minutes.
Proxysql can process requests from the application without query caching, just sometimes below messages appear in the log:
main.cpp:1086:main(): [ERROR] Watchdog: 1 threads missed a heartbeat.

By default proxysql does not cache such heavy results, so I applied changes in configuration:

update global_variables set variable_value = 104857600 where variable_name = 'mysql-threshold_resultset_size';
SET mysql-query_cache_size_MB=1024; 
LOAD MYSQL VARIABLES TO RUNTIME;

After changes are applied, proxysql shows rapid decrease in performance:
A lot of error messages from Watchdog appear in the log, until Watchdog aborts the proxysql process.
Statistics from query cache are fetched slowly:

Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';
+--------------------------+----------------+
| Variable_Name            | Variable_Value |
+--------------------------+----------------+
| Query_Cache_Memory_bytes | 904580181      |
| Query_Cache_count_GET    | 418            |
| Query_Cache_count_GET_OK | 71             |
| Query_Cache_count_SET    | 54             |
| Query_Cache_bytes_IN     | 5427315198     |
| Query_Cache_bytes_OUT    | 7135914427     |
| Query_Cache_Purged       | 45             |
| Query_Cache_Entries      | 9              |
+--------------------------+----------------+
8 rows in set (1 min 20.92 sec)

Increasing mysql-threads and restart_on_missing_heartbeats does not help much.

It seems while proxysql reads big data from the cache, the cache is locked and other threads can not get access to it.

+-------------------+
| @@version         |
+-------------------+
| 1.4.8-32-g669c149 |
+-------------------+

[renecannao]

I confirm the issue, although the cause is not a locking issue on QC.
It can be easily reproduced using this query:

SELECT t1.id, t2.id FROM sbtest t1 JOIN sbtest t2 LIMIT 3000000;

The said query is faster without QC than with QC.

In QC the whole resultset is stored as a single buffer. When returned from the QC, MySQL_Data_Stream::buffer2resultset() converts the buffer in an array of packets.
This leads to:
a) a lot of small memory allocations and deallocations
b) when data needs to be sent, MySQL_Data_Stream::array2buffer() causes a lot of calls to memmove()

[renecannao]

Fixed in 1.4.9 and 2.0.0