The PostgreSQL logs for our Moodle database show that certain queries are exceeding one second in execution time. What strategies or tools can be used to identify and optimize these slow queries?
Well, whether a query taking 1 second is good or bad depends on the query.
If this is a bug complex report, that is probalby expected. For other queries it would be bad.
You have to look on a case-by-case basis. Find the query in the code, understand what is going on, then use Explain to work out if there are obvious ways to improve it. Then follow Moodle development processes to contribute any improvements.
If this is a bug complex report, that is probalby expected. For other queries it would be bad.
You have to look on a case-by-case basis. Find the query in the code, understand what is going on, then use Explain to work out if there are obvious ways to improve it. Then follow Moodle development processes to contribute any improvements.
This is rather like those generic security reports. They suggest somewhere to look next but don't guarantee that you have found a problem.
You could try using the Ad-hoc database queries plugin check up on your queries. I've used it a couple of times to speed up a query. When you use it to run a query, at the bottom of the report it will tell you how many rows are in the report, the date and time the report was run, and how much time it took to run.
But, if you can, better to use 'real' DB tools where you can directly run queries, and use commands like EXPLAIN to see what is going on.
Hi Tim,
Since I am literally at-the-moment, struggling along with a database query, would you mind telling me what you use as a 'real' DB tool?
Since I am literally at-the-moment, struggling along with a database query, would you mind telling me what you use as a 'real' DB tool?
I use MySQL workbench which provides extra tools to manage my DB and save queries I don't need to have on Moodle itself. Here's a list of Postgres clients, many of which can work with any DB. Adhoc queries is limited because it prevents you doing some queries (DELETE, UPDATE, ALTER, EXPLAIN etc) and offers no intellisense.
And you'll want to use EXPLAIN because you'll discover if your query is using indices properly, or if you need to add indices (but not to core 😉)
Thanks so much for the info Mark! I especially appreciate the Postgres clients list.