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

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
  • 1
    Seriously: Change the OS and see what happens. Any NX will do. I assume your FS is the bottleneck – Markus W Mahlberg Jan 6 '18 at 20:49

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:

  • I will definitely take a look at the info you linked. All of the queries are created by Jamf software. I can't change them at all. I manage the web apps and the database server only. All that I would be able to do is optimize how MySQL is dealing with the info from the Jamf web apps. I can provide more info on settings if that would help. – Mike Donovan Jan 7 '18 at 14:56
  • I have noticed that if my InnoDB Disk Writes on the workbench dashboard get below 1M/s things start to slow down significantly. If they get below 500KB/s then all of my Tomcat nodes are unresponsive. I have adjusted the innodb log file size based on some information in the second link. Writes are currently above 2M but have fluctuated quite a bit. I appreciate your help and I will continue to monitor to see if things change. – Mike Donovan Jan 8 '18 at 14:13

Are you updating the tables that you are reading? If so, the Query Cache may be useless, or even slowing things down. Every change to a table causes the invalidation of all entries in the QC. The bigger the QC, the longer that takes.

What are the queries; perhaps the problem is as simple as a missing composite index.

In many situations, combining operations will be faster than doing lots of individual operations -- which seems to be your case?

  • I tried using query cache but it seemed to be slowing things down more so it is currently disabled. – Mike Donovan Jan 7 '18 at 14:48

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