We are currently using Ignition 7.9.18 with plans to upgrade to 8.3 soon. At some of our sites we are getting "slow queries" for the audit log INSERT INTO SQL queries. We are also receiving complaints from the operators of general Ignition slowness at the same time of these "slow queries". Here is a screenshot from the "Longest Recent Queries" table of the Ignition gateway webpage database section.
The database is currently handling about 40-60 queries per second. When we disable audit logging, the general Ignition slowness disappears completely, but we would like to find a path forward that allows to continue using the audit logging feature while addressing the slow query issue.
Just wondering if anyone has any advice on how to tackle this problem.
Thanks!
I would look at your database and whether it has the resources it needs to perform the indexing on insert. If the database is on the same server (or same VM) as Ignition, fix that first.
Side note, in order to make sure the upgrade process goes smoothly, please upgrade to 8.1 first. Upgrading from 7.9 to 8.3 isn't supported. Upgrading from 7.9 > 8.1 > 8.3 will be ok.
Thanks for the advice. We are running SQL express on a separate server. All of our SQL servers across several different plants have the same resources, but for some reason, the SQL server at this plant is consuming a lot more RAM than the others.
SQL express is significantly throttled in performance compared to the paid version. If you're not going to pay for sql server, use something truly free and enterprise level, like postgresql
