3

I've recently been put in charge of a system that has been fairly untended for a period of time.

The majority of activity is via queued worker jobs based off of data feeds from other systems/data sources.

Via the slow query log I identified a number of slow queries using the PostgreSQL logs and added indexes to address these. This started around 7am or so.

  • Would the addition of these indexes have contributed to the large increase in IO/Wait?
  • What steps can I take to reduce this?

CPU Graph before and after adding indexes

Improve this question
2
  • Depending on how many and how big indexes you created (an on how busy tables), it might very well be the cause. For an index creation, one has to read the table, and of course write the index. If you do it with CONCURRENTLY, it has to read the table twice. This effect has to disappear after the indexes are created, though - it might be that autovacuum started working hard to update statistics and such.
    – András Váczi
    Commented May 25, 2016 at 8:11
  • The increase in IO Wait in the graph has continued long after the indexes finished creating. If these are tables do have a non-negligible amount write will this contribute show up as IO Wait if the autovacuum daemon is waiting for the disk?
    – anger
    Commented May 26, 2016 at 1:28

1 Answer 1

Reset to default
4
+50

Yes, adding indexes could cause IO wait to increase.

Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data. But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter through the blocks to find the one row it wants).

With the index, it jumps exactly to the row it needs, but it needs to wait for it get read from disk, because read-ahead doesn't help you out.

What steps can I take to reduce this?

Why does it need to be reduced? Is there an actual problem?

Improve this answer
1
  • I'd just like to expand on jjanes's answer: "Why does it need to be reduced? Is there an actual problem?" I agree OP -- you're seeing the expected result after adding an effective index: that it's getting in fact used. Instead of focusing on system usage, focus on your overall results: Are the queries faster after adding the index(es)? If so, excellent!
    – Saul
    Commented Jul 9, 2022 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.