Having a case with a lot of traffic within 30 minutes, and selects are exploding (5000+ q/s). It’s an old application. If disk I/O can handle it would make sense to make a replica on the same hardware (cloud server with dedicated cpu) to use an additional core for selects only?
Hello @Lars_Erik_Dangvard_J,
No, that does not make sense. You would not be changing the load. For example, you could have 2 MySQL processes running, each using 4 cores, OR have 1 MySQL process using 8 cores. There’s no difference there, same amount of total cores used by MySQL, same amount of load. Not to mention the additional overhead replication introduces. Disks are not the problem if you have plenty of RAM to hold the data in memory. I would focus on the queries, as it is more likely you have poorly written queries, or queries that run full table scans due to missing indexes.
Do you have any query monitoring? Install PMM Server on another host, then install the pmm client onto your MySQL host. Set the slow query source on the client to perfschema. Wait 30-60m. Check PMM UI → QAN and view all your queries. Sort by full table scan, or ‘no index used’. Fix these queries, or add missing indexes.
1 Like
Great. Somehow I got into my head that MySQL only could use 1 core.
MySQL is a single-process, multi-threaded application. MySQL does not “use cores”. It creates threads via the linux kernel, and the kernel is responsible for assigning processing time on the CPUs per thread. What you might have read, and been confused on, is that a query executing in MySQL can only use 1 thread. Parallel query execution (where 1 executing query uses multiple threads to fetch data) does not yet exist in MySQL. Percona has many customers that run 128+ core systems, where all the cores are fully utilized by a single MySQL process + thousands of threads. 
1 Like