Mysql parallel query problem

the business table has too much data to design 4 sub-tables, and then the requirement is to export the full data of the 4 tables.

Test machine 4 cores. Start 4 threads to query at the same time, and summarize the final results.

if it takes 2s for a thread to query a table, it takes 2s for 4 threads to run (using cpu multi-core to run in parallel), but the reality is that 2 * 4 equals more than 8s.

A problem that has been bothering me for a long time. I hope the boss can help me. Thank you.

attach mysql parameter: 

+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 0                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 10                        |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | 1000                      |
| pseudo_thread_id                        | 81                        |
| thread_cache_size                       | 8                         |
| thread_concurrency                      | 10                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 262144                    |
+-----------------------------------------+---------------------------+

mysql version is 5.5.28

Query parallel mysql
Mar.02,2021

if the bottleneck lies in IO, no matter how many threads are the same, unless the subtable exists on a different physical disk.
you need to count IO.

you can use top or iostat to check the wa, IO-wait parameters of sql execution. What is the proportion?

bottleneck of IO resources
when there is a bottleneck of IO resources, it is mainly manifested in that the server iowait is very high, the usr is relatively small, the system response is slow, and there are often a large number of execution state session in the database.
when we encounter the bottleneck of IO resources, the hardware optimization solution we can use is:
increase memory and increase the amount of cacheable data: the effect of this scheme depends on the total amount of hot data in the system. After all, the cost of memory is relatively high, and the amount of memory that can be managed by a single device is limited
to improve the IO capability of the underlying storage device: as described earlier in this article, The improvement of the underlying storage capacity also depends on many aspects, not only the capacity of the single disk itself, but also the strategy of the number of disks, but also limited by the storage itself and the bandwidth between the storage and the host. Therefore, while optimizing the underlying storage capacity, we need to consider these three factors at the same time, and do a good overall analysis and local balance

.

if it is true that there is a bottleneck in the stand-alone IO, you can consider distributing the tables to several separate databases and eventually merging the data in the application layer.

Previous: Give me a thousand statistics through curl json, how can I divide the front end?

Next: How to set cross-domain with cookie in vue axios

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e476cc-44176.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e476cc-44176.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?