1

I'm aware I should expect slower fetch times for large result sets generally, but I don't understand why fetching should be this slow. The workload fetches a large number of rows (1-10M) from a large table (100M+ rows).

mysql> describe testdb.test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   | PRI | NULL    |       |
| col2  | int(11) | NO   | PRI | NULL    |       |
| col3  | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

For the test case I use as simple query as possible:

select * from test_table limit 1000000;

However, the client appears to bottleneck around 1.5-2.5M rows/sec per query (1.5M in a Python client, 2.5M in MySQL Workbench). I know 2.5M rows/sec seems pretty fast, but that only works out to 30 MB/sec (2.5M * 3 cols * 4-byte ints). I'm on macOS 10.15.4, MySQL 5.7.29 installed via Homebrew, and connecting to the server over localhost. Python is using the MySQLClient db driver.

The query plan shows the results selected from the primary index, as expected:

+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | my_table | NULL       | index | NULL          | PRIMARY | 12      | NULL | 10821795 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+

The query profile doesn't indicate any hot spots.

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000055 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000016 |
| init                 | 0.000015 |
| System lock          | 0.000021 |
| optimizing           | 0.000011 |
| statistics           | 0.000012 |
| preparing            | 0.000019 |
| explaining           | 0.000028 |
| end                  | 0.000007 |
| query end            | 0.000007 |
| closing tables       | 0.000009 |
| freeing items        | 0.000015 |
| cleaning up          | 0.000023 |
+----------------------+----------+

And as best I can tell, the entire test db fits in the buffer pool, and no disk IO occurs during the query. The Innodb_buffer_pool_reads value is unchanged after executing the query, and the InnoDB Status metrics from MySQL Workbench's performance dashboard are all zero throughout its runtime.

In MySQL Workbench's Duration / Fetch Time columns, the duration stays consistently under 1ms, regardless of the number of rows selected. However, the fetch time is proportional to rows returned: ~0.5 sec for 1M and and 5.0 sec for 10M rows.

When I observe processes with top I can see MySQL spiking to 100% CPU for a short time followed by MySQLWorkbench spiking to 100% for the remaining duration of the query after the query completes. The same test with the Python client (that doesn't do any additional work) shows the time a little more evenly split, but it's hard to measure.

That seems to only leave the the db client driver or the network connection itself as the bottleneck. I assume it's not the network since I'm testing over localhost (though I have not tested localhost in isolation). Does it make sense that the client bottlenecks processing rows at 30MB/sec? Can anything be done to improve throughput?

Update

Including requested global status, variables, processlist, and innodb status, but note that this is not on a dedicated server. My tests are on a MacBook Pro with 16GB RAM, 4-cores with Hyperthreading (i.e. macOS sees 8 hardware threads). The hard drive is an NVMe (~232k Read IOPS RND4k@QD32), but as I noted above, I observe no disk IO (and that includes swapping/paging by the OS).

I wanted to further isolate duration vs fetch time, so I trimmed the table to exactly 10M rows, and then compared a large select with an aggregate version of the same query using a "cheap" aggregate function.

select col1, col2, col3 
from test_table;

Duration: 0.00082 sec, Fetch Time: 4.729 sec

select count(col1), count(col2), count(col3) 
from test_table;

Duration: 2.692 sec, Fetch Time: 0.000011 sec

I think the duration/fetch time metrics are a little confusing, since I assume the first query duration only includes time to identify row IDs (not buffer them), while the second has to get each row in memory, a step that overlaps with fetch behavior in the first query, even though it's included in duration in the second.

Assuming 25% overhead for count() that's about 2 secs to walk through 10M rows, so 5M rows or 60 MB/sec. Assuming the same access time cost for the first query, that would mean an additional ~45 MB/sec to copy them into a buffer to fetch.

In any case, even assuming additional data overhead per row, it seems like at a minimum simply accessing rows in memory in InnoDB is a significant bottleneck, regardless of the driver or network. mysqld CPU% maxes out one thread for the duration of the aggregate query, so it appears to be a CPU-bound operation. Does that sound right? Is this just the cost of doing business with B+ trees? For (an unfair) comparison, the same operation takes about 200ms in Python using Pandas.

Additional info

>>show create table test_table;
...
CREATE TABLE `test_table ` (
  `col1` int(11) NOT NULL,
  `col2 ` int(11) NOT NULL,
  `col3 ` int(11) NOT NULL,
  PRIMARY KEY (`col1`,`col3`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
...
>ulimit
unlimited
>ulimit -n
256
>ulimit -u
2784
>iostat
              disk0               disk2       cpu    load average
    KB/t  tps  MB/s     KB/t  tps  MB/s  us sy id   1m   5m   15m
   24.24   31  0.73    12.61    0  0.00   4  3 93  2.46 2.21 2.42
>top
Processes: 541 total, 2 running, 3 stuck, 536 sleeping, 2564 threads                                                                                                              11:40:37
Load Avg: 1.71, 1.53, 1.53  CPU usage: 6.94% user, 6.94% sys, 86.11% idle  SharedLibs: 364M resident, 61M data, 16M linkedit.
MemRegions: 246424 total, 6527M resident, 153M private, 2132M shared. PhysMem: 16G used (3318M wired), 86M unused.
VM: 7190G vsize, 1995M framework vsize, 26292674(64) swapins, 27667013(0) swapouts. Networks: packets: 137115709/125G in, 167115774/85G out.
Disks: 13216718/253G read, 8333988/245G written.
11
  • i don't think there any bottlenecks at all, 5 seconds for 10 M is very fast and have no need for improvement
    – nbk
    Jun 7, 2020 at 23:13
  • 1
    @nbk Everything in theory can go faster, so every process will have a bottleneck. For my application, 30MB/sec is not very fast, so I want to know why. Let's say it's the network; at a minimum I would expect localhost to simulate a Gigabit connection, and in that case I should see over 100MB/sec. Again, this suggests something in the db driver is bottlenecking, i.e. that the db can deliver rows much faster than they can be received.
    – wst
    Jun 7, 2020 at 23:32
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions for improving speed. Jun 8, 2020 at 14:12
  • Additional information request from your MacOS, equivalent commands may exist. Post on pastebin.com and share the links. Text results of Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Jun 8, 2020 at 14:17
  • @WilsonHauck I added most of the requested information, please see the updated post.
    – wst
    Jun 8, 2020 at 23:13

4 Answers 4

2

Some more things to try:

SHOW GLOBAL STATUS;   -- and capture somewhere
SELECT ....;
SHOW GLOBAL STATUS;   -- and capture somewhere else

Then subtract the Handler_% values and the InnoDB_% values. In a similar test (of only 3.1M rows), I got these:

Handler_read_next      3.1M
Innodb_rows_read       3.1M
Innodb_buffer_pool_bytes_data  53M
Innodb_data_read               53M  -- These matched the "Using index" it used

A second run had a zero difference instead of 53M. This because the first run had to read everything from disk; the second found it all in the buffer_pool

(I suggest AVG(col) if the col is numeric; this makes it clear that the aggregate had to read every row. First I tried MAX(col); it simply went to the end of the index, so virtually 0 time and effort and data read.)

Semantically, COUNT(col) checks each item for being NOT NULL. However your cols were part of the PRIMARY KEY, which is, by definition, composed of not-NULL columns. So, I am a bit suspicious of the effort taken with that aggregate.

Back to your main question. Why does reading a row take so long?

  • Assuming it is walking through a B+Tree and it is sitting at the 'next' row.
  • Check for transaction locks, history list, etc. (There could be multiple copies of the row, some waiting for COMMIT/ROLLBACK.)
  • Pick apart the record. (You asked for 3 columns.)
  • Move on to the next block (when appropriate)
  • Perform any the expression (COUNT(col1))
  • Convert from internal format to external (SELECT col1)
  • Handoff the row to something else, which will buffer it for transmission
  • Deal with localhost or TCP/IP.

Note: each of those is fast, but there are a lot of details. Also, it is single-threaded. Well, not totally -- fetching the next block from disk (if needed) may be performed by a separate thread.

Bringing a block from disk:

  • Issue the read
  • Lock the buffer_pool (buffer_pool_instances helps a little here)
  • Get an empty block (or wait for flushing a block to disk)
  • Finish the read
  • Update various flags, hashes, etc
  • Unlock the buffer_pool

Again, this mostly single-threaded.

That brings me to other points:

  • Reading a million rows from disk to a program is not normal.
  • Normally one tries to get SQL to do more of the work (eg aggregates).
  • It is possible (but clumsily) to have multiple connections, each reading part of the data and processing it. (Probably should not have more threads than CPU cores.) 8 threads might run only 4 times as fast as a single thread -- due to extra overhead and contention.
5
  • Interesting, the AVG version's duration is much more similar to the fetch time of the "dump all rows" query. And yes,I know this is not normal. It's part of a somewhat complicated OLAP-like report where the client will also provide a LOT of ephemeral data relevant to the query, and my idea was to avoid inserting all that data, querying, and then deleting it. Operationally it seems like too much overhead to move the raw rows into the application, so I'll attempt moving the aggregation in-database.
    – wst
    Jun 10, 2020 at 21:54
  • My takeaway is that I can probably tweak around the margins for fractional improvements, but if I want more significant gains, I probably need to look to other databases and data structures or non-database solutions. Do you think that is accurate?
    – wst
    Jun 10, 2020 at 22:01
  • @wst - (1) MySQL is rarely dinged for slow connection. In fact has been praised in having a fast 'connect'. (2) I have a Rule of Thumb: "If a fix does not look like it will provide at least a 10% improvement (speed/space/whatever), drop it. Move on to other ideas."
    – Rick James
    Jun 10, 2020 at 22:24
  • I mean, more specifically, it seems like there is not a faster way (on the same hardware) to select or aggregate 10M rows in MySQL.
    – wst
    Jun 10, 2020 at 22:51
  • Would the presence of a PRIMARY KEY make your innodb table perform better? Jun 11, 2020 at 18:13
1

In theory everything can be a bottleneck. True.

Practically you have explained where the issue could be:

When I observe processes with top I can see MySQL spiking to 100% CPU for a short time, followed by MySQL Workbench spiking to 100% for the remaining duration of the query.

Data Grid (View)

The data has to be transformed from memory to some form of table in a GUI (MySQL Workbench) and this requires some time. The retrieved data has to be converted from some binary value into a graphical representation thereof in a table (data grid) that needs to be continuously populated.

Populating a data grid is one of the GUI elements that doesn't perform well when populating lots of values. This is the reason that some programming languages offer the possibility to fill the data grid page by page.

Why is DataGridView Slow at Scrolling, Rendering, Filtering and Populating? (10tec.com)

Paging

Another possibility could be paging. Are you observing paging while your data is being inserted into the data grid of MySQL Workbench?

Endless List

Other possible reasons for slow performance range from hardware to software to versions thereof and hot-fixes and patches.

1
  • Unfortunately I suspect it's the third item. Workbench actually takes much longer than duration + fetch time to display the values in the GUI, so I assume they're not counting time to populate the data grid. And I see no paging by the OS during over the duration of the query.
    – wst
    Jun 8, 2020 at 23:15
1

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

query_cache_size=0  # to conserve RAM and CPU cycles since query_cache_type=OFF
net_buffer_length=48K  # to reduce malloc requests
performance_schema=OFF  # from ON to conserve CPU cycles
innodb_lru_scan_depth=100  # from 256 to conserve ~ 60% CPU cycles used for function each SECOND
innodb_log_compressed_pages=OFF  # from ON when using NVME to conserve CPU cycles
read_rnd_buffer_size=64K  # from 256K to reduce handler_read_rnd_next RPS of 899
read_buffer_size=512K  # from 128K to reduce handler_read_next RPS of 6599

You may find these changes reduce time required to complete many queries.

Select_scan count of 3,442 in 20 hours indicates indexes are missing. On our FAQ page we describe how you can determine the queries that could use indexes. View profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning.

6
  • Thanks for the detailed analysis. I need to take some more reliable measurements, but I think this netted roughly 5% improvement. Hopefully more with a larger table.
    – wst
    Jun 10, 2020 at 21:15
  • 1
    You are welcome. Please consider picking up our Utility Scripts to assist with improving performance. Jun 10, 2020 at 21:17
  • For some reason, query_cache_size=0 causes myslqd to crash on start on my system. MySQL 8.0.23 | OSX 11.3.1
    – Ares
    Jun 1, 2021 at 15:38
  • @Ares ALL 8.0.n have Query Cache REMOVED. Remove from your my.cnf or my.ini all lines that start with QUERY_CACHE, please. Jun 2, 2021 at 7:49
  • @Ares It would be helpful for you to Open your own new question when you have a new event to ask about. No need in mixing a new question with a topic that is a year old or more. Jun 6, 2021 at 14:28
0

If the goal is to aggregate a large amount of data, there is a much faster way to do it. Well, we have to throw out the premise that you have to do it all at once.

Summary Table(s).

Each night, summarize the day's data and store one row (or a small number of rows) into a Summary table.

When you "want" to fetch 10 million rows from the raw ("Fact", id DW parlance) table, instead read and further aggregate the rows from the Summary Table. (Sum the counts; sum the subtotals; avg = (sum of subtotals / sum of counts); etc).

More details: http://mysql.rjweb.org/doc.php/summarytables

Improvement: Maybe 10-fold. (No, I am not saying a trivial 10 percent.)

4
  • I don't think any work can be done offline. The user/client will capture some time-sensitive data, process it, and supply a large set of values to be joined/filtered on the database rows before aggregating.
    – wst
    Jun 10, 2020 at 23:20
  • @wst - Any more hints of that the data and processing are? I may have other tricks up my sleeves. IODKU can do the aggregation in 'realtime' (as opposed to overnight).
    – Rick James
    Jun 10, 2020 at 23:45
  • It's not a very relational problem (which is why I assume this is the end of MySQL's rope) but this is the high level: Signal data, like from IoT, wearables, etc. gets processed into a fine grained chunks, then matched against a database of "signatures". Nothing will ever match exactly, so it needs to generate a report of counts, then the application can use heuristics to guess if it's close enough to be considered a match.
    – wst
    Jun 11, 2020 at 0:04
  • 1
    @wst - "Closeness in N-dimensional space is a tough problem (at least for me). (I've tackled 2D, as in geographical closeness, but that does not extend to more dimensions efficiently.)
    – Rick James
    Jun 11, 2020 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.