Thread: Are bitmap index scans slow to start?

Hi Jeff, thanks for the reply.

<< 

What is going on during the interregnum?  Whatever it is, it seems to be driving the log_2013_01_session_idx index out of the cache, but not the log_2013_01 table.  (Or perhaps the table visit is getting the benefit of effective_io_concurrency?)
…

Rebuilding the index might help, as it would put all the leaf pages holding values for session_id=27 adjacent to each other, so they would read from disk faster.  But with a name like "session_id", I don't know how long such clustering would last though.

>> 

Technically, nothing should be happening. We used to keep one massive audit log, and was impossible to manage due to its size. We then changed to a strategy where every month a new audit log would be spawned, and since log_2013_01 represents January, the log should be closed and nothing should have changed (it is technically possible that a long-running process would spill over into February, but not by this much). So, assuming that it’s stable, it should be a very good candidate for reindexing,  no?

Our effective_io_concurrency is 1, and last I heard the PG host was a LINUX 4 drive RAID10, so I don’t know if there is any benefit to  raising this number – and if there was any benfit, it would be to the Bitmap Scan, and the problem is the data building before the fact.

>> the bitmap itself doesn't get cached.  But the data needed to construct the bitmap does get cached.  It gets cached by the generic caching methods of PG and the OS, not through something specific to bitmaps.
<<

This has always been a problem for me. I spend hours trying different strategies and think I’ve solved the problem, when in fact it seems like a cache has spun up, and then something else expires it and the problem is back. Is there a way around this problem, can I force the expiration of a cache?

Carlo

A cool idea, but if I understand it correctly very specific and fussy. New DB’s are spawned on this model, and all the developers would have to be aware of this non-standard behaviour, and DBA”s would have to create these indexes every month, for every DB (as the log tables are created every month). There are 89 session_id values in the January log (log_2013_01) so this would quickly get out of control. But – like I said – an interesting idea for more specific challenges.

>> Also, you might could try clustering newly created tables on session_id and setting the fillfactor down so rows with the same session id will stick together on disk.

<< 

My understanding of PG’s cluster is that this is a one-time command that creates a re-ordered table and doesn’t maintain the clustered order until the command is issued again. During the CLUSTER, the table is read and write locked. So, in order for me to use this I would need to set up a timed event to CLUSTER occasionally.

>> I can't really help, but I can make it more clear why postgres is choosing a _bitmap_ index scan rather than a regular index scan

<< 

The EXPLAIN ANALYZE is showing it is taking a long time to prepare the bitmap (i.e.->  Bitmap Index Scan on log_2013_01_session_idx  (cost=0.00..63186.52

rows=2947664 width=0) (actual time=32611.918..32611.918 rows=2772042 loops=1)" Index Cond: (session_id = 27)" the bitmap scan is actually very fast. Jeff sasys that the bitmap is not cached, so I will assume the PG general caches being created are of general use.

I think what I need to do is figure out is:

1)      Why does it take 36 seconds to set up the general index caches?

2)      What can I do about it (what stats do I need to look at)?

3)      How can I force these caches to expire so I can tell if the strategy worked?

From: Nikolas Everett [mailto:nik9000@gmail.com]
Sent: February 22, 2013 2:05 PM
To: Carlo Stonebanks
Cc: Marc Mamin; Jeff Janes; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Are bitmap index scans slow to start?

Hi Jeff, thanks for the insight.

<< And then the next question would be, once they are in the cache, why don't they stay there?  For that you would have to know what other types of activities are going on that might be driving the data out of the cache.

>> 

To give you an idea of the activity level, each physical machine hosts multiple DB’s with the same structure – one DB per client.

We run automated ETL processes which digests client feeds (E) normalizes them (T) and then stores them in our DB (L).

Looking at the stats from our audit log, the average feed load is 4 hours, divided up into 14 client sessions. Each session averages about 50 write (update, insert, no deletes) operations per second, representing 700 write operations per second. The ratio of reads per write is pretty high as the system goes through the transformation process.

Since I don’t know how this compares to other PG installations, the question of using periodic REINDEX and CLUSTER brings up these questions:

1)      Because we are hosting multiple DB’s, what is the impact on OS and disk caches?

2)      Is there an automated CLUSTER and REINDEX strategy that will not interfere with normal operations?

3)      By PG standards, is this a busy DB - and does explain why the general caches expire?

Thanks,

Carlo

<<Is each of these write operations just covering a single row?  Does this description apply to just one of the many (how many?) databases, so that there are really 14*N concurrent sessions?

>> 

All writes are single row. All DB’s have exactly the same structure, only the content is different. Currently the server is hosting five active DB’s – although there 14 DB’s actually on the host, the balance are backups and or testing environments.

When a feed comes in, it can be anything from dozens to millions of rows, and may take minutes or days to run. I had asked that PG bouncer be installed in front of the host to act as a traffic cop. Try as I may to convince the engineering team that fewer sessions running faster is optimal, they say that the 14 concurrent sessions is based on real-world experience of what imports the fastest.

<< You really need to know whether those reads and writes are concentrated in a small region (relative to the amount of your RAM), or widely scattered.  If you are reading and writing intensively (which you do seem to be doing) but only within a compact region, then it should not drive other data out of the cache.  But, since you do seem to have IO problems from cache misses, and you do have a high level of activity, the easy conclusion is that you have too little RAM to hold the working size of your data.
>>

It won’t be a problem of physical RAM, I believe there is at least 32GB of RAM. What constitutes “a compact region”? The ETL process takes the feed and distributes it to 85 core tables.  I have been through many PG configuration cycles with the generous help of people in this forum. I think the big problem when getting help has been this issue of those offering assistance understanding that the whopping majority of the time, the system is performing single row reads and writes. The assumption tends to be that the end point of an ETL should just be a series of COPY statements, and it should all happen very quickly in classic SQL bulk queries.

<< pgbouncer is more for making connections line up single-file when the line is moving at a very fast clip, say 0.01 second per turn.  If I were trying to make tasks that can each last for hours or days line up and take turns, I don't think pgbouncer would be the way to go.
>>

The recommendation at the time was assuming that write contention was slowing things down and consuming resources, since I can’t stop people from creating big multi-threaded imports. Each import consists of about 50 writes

>> Is the original query you posted part of the transform process, rather than being the production query you run after the ETL is over?

Neither, it is part of our auditing and maintenance processes. It is not called with any great frequency. The audit report generates rows defining how the a particular  item (an “item” being a particular table/row) was created: it returns the names of the import tables, the row ids, the write operations and any transformation messages that may have been generated – all in the order they occurred.

You can imagine how useful this in creating a document describing what happened and why.

The same data generated by the report is used to “resurrect” an item. If – for example - our business logic has changed, but the change only affects a small sub-set of our core data, then we perform a “rollback” (a logical cascading delete) on the affected items. Then we create a “rebuild” which is a script that is generated to re-import ONLY the import table rows defined in the audit report.

So, this query is not called often, but the fact is that if it takes over 30 seconds to load an item (because the audit report takes so long to prepare the bitmap index scan when passed new query parameters) then it severely restricts how much data we can resurrect at any one time.

<< 

I had thought you were saying that any one ETL procedure into one database used 14 concurrent threads.  But really, each ETL procedure is single-threaded, and there can be up to 5 (or theoretically up to 14) of them running at a time into different databases?
>>

Sorry, just caught this.

Your first interpretation was correct. Each DB runs an ETL that can have up to 14 concurrent threads. I don’t think the number should be that high, but the engineering team insists the load time is better than fewer threads running faster.

<<Could you use CLUSTER on the table after it had been closed off?  If appropriate, that should make the queries run much faster, as elated entries will be in the same or nearby blocks on disk.

>> 

Technically, yes. That would really help, but the issue is scheduling. Although the logs are closed off for writes, they aren’t closed off for reads, ref PG documentation: “When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.”

Not ideal, but a lot better than doing nothing at all!

Sorry this took so long to get back to you. Here is where we were:

I said: <<

So, this query is not called often, but the fact is that if it takes over 30 seconds to load an item (because the audit report takes so long to prepare the bitmap index scan when passed new query parameters) then it severely restricts how much data we can resurrect at any one time.

>> 

Your reply: <<
Is that a restriction you have observed, or are you extrapolating based on a single query?  If you run a bunch of similar queries in close succession, it is likely that the first few queries will warm up the cache, and following queries will then run much faster.  Also, if you restructure the series of queries into a large one that reconstructs many rows simultaneously, it might choose a more efficient path than if it is fed the queries one at a time.
>>

Actual observation. The first run with a new parameter actually takes 90 seconds. Another run with the same parameter takes 15-30 seconds. Running the query immediately afterwards with different parameters starts with a new 90 seconds query. Unfortunately, since going to LINUX, our sys ops hiss and snarl at anyone who comes anywhere near machine or DB server configs, so I am no longer well informed on how well optimized the machines are.

Ultimately, the machines need to be optimized by an expert. As I mentioned before, our ETL is entirely single-load reads-and-writes (I didn’t go into the “why” of this because the nature of the data and the product dictates this). And this is an example of one of the few complex joins that return hundreds/thousands of rows. The problem is that a full index scan has to be done before we can start building the results. So, if clustering will help such that the index scan KNOWS that there’s no point is scanning the rest of the index because we’ve gone beyond the maximum value in the list of possible values, then that would help, as each table being scanned has 50 – 100 million rows (there is one table for every month of production).

As always, thanks.

From: Jeff Janes [mailto:jeff.janes@gmail.com]
Sent: March 5, 2013 4:21 PM
To: Carlo Stonebanks
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Are bitmap index scans slow to start?

Is that a restriction you have observed, or are you extrapolating based on a single query?  If you run a bunch of similar queries in close succession, it is likely that the first few queries will warm up the cache, and following queries will then run much faster.  Also, if you restructure the series of queries into a large one that reconstructs many rows simultaneously, it might choose a more efficient path than if it is fed the queries one at a time.

Cheers,

Jeff