MySQL innodb scaling I am using Jamf to manage 25K Macs and 13K iOS devices. Every time I try to update iOS apps or configuration profiles for a large number of devices my database gets overwhelmed. I have MySQL 5.6 running on Windows Server 2008R2 with 16 cores and 32GB RAM. I have tried adjusting things like query_cache or thread concurrency without any success. Any help would be greatly appreciated.

innodb_buffer_pool_size = 25G

--------------
ROW OPERATIONS
--------------
72 queries inside InnoDB, 0 queries in queue
71 read views open inside InnoDB
Main thread id 2572, state: flushing log
Number of rows inserted 269699, updated 352837, deleted 170748, read 2807450339
9.11 inserts/s, 8.43 updates/s, 6.83 deletes/s, 64676.96 reads/s
There's really not enough information here to make any specific recommendations. 

64676.96 reads/s seems pretty high for a 16 core server, and it seems disproportionately large compared to the number of inserts/updates/deletes.

I would guess (again, with a big grain of salt because I know nothing about your app, your database, or your queries) that this is so high because you have queries that are not optimized with indexes.

Creating the right indexes for your queries usually helps reduce the average rows examined per query (but of course this depends a lot on the types of queries you run).

You might like to read my presentation How to Design Indexes, Really or a video of me giving this presentation: https://www.youtube.com/watch?v=ELR7-RdU9XU



Re your comments:

In most cases, you can optimize with indexes without changing any queries. You can get the log of queries from MySQL without looking at any code in Jamf. 

Tuning settings can be important, but only up to a point. You should read:


https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/