MySQL Forums
Forum List  »  InnoDB

TEXT field performance issue on idle server
Posted by: Jason Brunette
Date: February 10, 2021 01:10PM

I'm looking into an issue of unexplained performance drops when running multiple queries against a table with a TEXT column concurrently, and the differences seen between one server and another.

Assuming the following query on a server with 32 cores, 256GB RAM, 128GB InnoDB buffer pool and a 5-column table with 6 million rows, one of the columns being TEXT with max length of 600 characters and no index:

SELECT COUNT(TEXT_column) FROM table;

Once the server is started and this query is run once to load it into the buffer pool, is it assumed that performance would be consistently fast when running this query numerous times concurrently, correct? However, on this server, running this query multiple times concurrently (20, for arguments sake) shows a severe delay in results returned for each query. Run once, the query takes about 2 seconds. Run 20 times concurrently, all 20 queries take about 250 seconds to complete. Again, this is after the buffer pool is already "warmed up" for this query.

If I run another query on a much smaller (10k records) table that also has a TEXT field at the same time the 20 concurrent queries above are running, it takes 2 seconds to return the results.

On a different, less powerful server, this exact same test with same table and data results in the 20 concurrent queries completing in 10 seconds or less, and the test query with the smaller table returns instantly.

On the problem server, Buffer Pool Read Requests top out at about 1M pages/s (per Workbench Performance > Dashboard) during the 20 concurrent queries, where on the "slower" server it tops out at about 9M+ pages/s.

If I convert the TEXT_column field to a VARCHAR(600) on the problem server, the queries return in 10 seconds or less and Buffer Pool Read Request readings are much closer to what the "slower" server achieves.

What would cause such a difference in buffer pool read performance, which is what I assume is related to the poor query performance?

Options: ReplyQuote


Subject
Views
Written By
Posted
TEXT field performance issue on idle server
58
February 10, 2021 01:10PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.