Very slow JSS, machines now fail to enroll, and why are there 2,000,000+ issued certificates?

[bradtchapman]

Hello,

Our JSS (9.72) has about 1,500 enrolled computers and zero configuration profiles. There are more than 20 DPs around the globe that rsync nightly from the master DP. DeployStudio is used for imaging and enrollment in this environment.

The JSS is running on a Linux VM with 8GB of RAM. It is inherited from a previous administration and was not maintained or pruned for a while. The JSS has been very sluggish and has to be rebooted every few days. Page loads can vary between a few seconds to several minutes.

Today, we see machines failing to enroll because the JSS times out while downloading the certificate.

When I checked Global Settings > PKI > Issued Certificates, the heading indicated that there were over 2,000,000 entries. That number just seems mind-bogglingly large, and quite absurd for a system with less than 2,000 devices. By comparison, a colleague's company has almost 10,000 devices enrolled in their JSS (only Macs), and the number of issued certificates is just under 200,000. The system is very responsive at all times.

What can be done about this?

[andrew_nicholas]

Where does the DB sit? Maybe check your MySQL slow queries log to see if there are any smart groups causing major hang ups. As for the certificates, how far back do they go?

[bradtchapman]

@andrew.nicholas, the database is on a linux VM.

There are 125,000,000 rows in the plugins table, consuming 60GB. Flushing is set to one week and we just turned it off yesterday, so I assume that table will shrink in the coming days.

Database Type                        MySQL
    Database Driver .................... com.mysql.jdbc.Driver
    Database Server                      servername.company.com
    Database Port ...................... 3306
    Database Name                        jamfsoftware
    Database Size ...................... 77.61 GB
    JDBC Parameters                      ?characterEncoding=utf8&useUnicode=true&jdbcCompliantTruncation=false
    ----------------------------------------------------------------------------------
    Connection Pool Partitions           1
    Minimum Pool Size .................. 5
    Maximum Pool Size                    90
    Acquire Increment .................. 5
    Maximum Idle Time                    1
    Helper Threads ..................... 3
    Maximum Connection Age               5
    Idle Connection Test Period ........ 0
    4-Byte Characters Supported          true

    datadir .................... /var/lib/mysql/
    expire_logs_days             0
    log_bin .................... OFF
    log_error                    /var/lib/mysql/servername.company.com.err
    max_allowed_packet ......... 268435456
    max_connections              300
    skip_name_resolve .......... OFF
    sql_mode                     STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    version .................... 5.6.15
    version_comment              MySQL Community Server (GPL)
    version_compile_machine .... x86_64
    version_compile_os           Linux
    Database Size .............. 4.86 GB
    MyISAM Tables                311
    InnoDB Tables .............. 0

[andrew_nicholas]

My DB-fu isn' the most powerful so I may be grasping at straws, but even without the plug-in table isn't the size of the DB larger than what would typically be expected? How's the communication time between the JSS and server hosting the DB itself? Could there be any errant policies forcing machines to re-enroll continuously?

[bradtchapman]

@andrew.nicholas The application table is about 12GB.

[bradtchapman]

We don't have direct control over the server. I'm not sure how to measure the communication time between the database and the JSS, but it only bogs down during certain operations. Other times it's lightning quick between page loads.

[andrew_nicholas]

My first concern would be that DB being so large. It could explain why certain executions are zippy while others slow it to a crawl. I'd ping your TAM about typical sizes for your sort of environment but also put a request in for some one who admins that machine to pull the slow query logs; it could be something as simple as an extremely complex smart group running over a bunch of bloated/unused data.

[bradtchapman · Accepted solution]

As a final update to this thread, the database had grown to almost 80GB.

About two years ago, the previous Casper admin had enabled the collection of 'Plug-ins' as a request from management to track usage of a given item. The JSS doesn't make any distinction between stock and third-party plugins, so it captures them all. What nobody realized at the time was how unwieldy the database would become after gathering details about hundreds of plugins, across 6 different apps, for >1,000 computers.

We purged the entire 'plug-ins' table and brought the database back down to a more manageable 8 GB.

[Person]

I am curious, but were they collecting plug-ins outside of /Library/Internet Plug-Ins/ folder as well?

[bradtchapman]

They were collecting plugins for

• Adobe Photoshop CS5
• Adobe Illustrator CS5
• Adobe Photoshop CC
• Adobe Illustrator CC
• Adobe Photoshop CC 2014
• Adobe Illustrator CC 2014

As you might imagine, there are hundreds of plugins. Multiply that by over 1,000 computers, and baby you've got a stew going!