Will frequent small select queries slow down frequent write queries in InnoDB table in MySQL?

Question

I'm running a MySQL database with tens of tables in remote server. To monitor the data, I write a small program to query to database via VPN connection every 10 seconds. Each query is a union of 20+ select sub-queries from 20 different tables with the same structure. A server program receives data from external sources and writes thousands of records each second to a table I don't query.

What's the impact of my queries to the database? What if I query every second? Do they slow down the whole database notably or slow down the writing of the table I don't query?

Since the remote database is working in a production system, it is not allowed to stop working. Do my queries create any risk that might shut down the whole database?

Answer 1

The only way to know is to benchmark it. If your query is fast, the chance of any notable impact on other queries is low, assuming your database server has sufficient disk bandwidth, multiple CPU cores, sufficient memory, and an appropriate innodb_buffer_pool_size setting for both the working dataset and the hardware.

If you don't already have a replica ("slave") server, then you should consider implementing that -- a slave server imposes a relatively minimal load on the master, the select queries could be run against the replica, and, of course, you would then have an alternate server you could use in the event of a catastrophic failure of the master server.

Use EXPLAIN SELECT ... to understand how the server is actually processing your query, and then consider whether it needs to be optimized or rewritten, particularly if it is doing things like full table scans or temporary tables.

When benchmarking your query, remember to use SELECT SQL_NO_CACHE ..., otherwise the performance may be deceptively fast if none of the underlying tables has changed between subsequent invocations.

Answer 2

this query will use your resources including network, CPU, Disk,etc. 20+ query with union is not a good idea. specially when it runs every 10 second!!! It will put lots of load on your hardware and it might reduce your Throughput if your hardware and/or configuration does not cope with the load.