SEO

I am currently using a dual Opteron (248) single core system (RAM
PC3200) and for a change I am finding that the bottleneck is not disk
I/O but CPU/RAM (not sure which). The reason for this is that the most
frequently accessed tables/indexes are all held in RAM and when
querying the database there is almost no disk activity which is great,
most of the time. However, the database is growing and this database
is supporting an OLTP system where the retrieval of the data is an
order of magnitude more important than the insertion and general
upkeep of the data. It supports a search engine[0] and contains a
reverse index, lexicon and the actual data table (currently just under
2Gb for the three tables and associated indexes).

At the moment everything is working OK but I am noticing an almost
linear increase in time to retrieve data from the database as the data
set increases in size. Clustering knocks the access times down by 25%
but it also knocks users off the website and can take up to 30 minutes
which is hardly an ideal scenario. I have also considered partitioning
the tables up using extendible hashing and tries to allocate the terms
in the index to the correct table but after some testing I noticed no
noticeable gain using this method which surprised me a bit.

The actual size of the database is not that big (4Gb) but I am
expecting this to increase to at least 20Gb over the next year or so.
This means that search times are going to jump dramatically which also
means the site becomes completely unusable. This also means that
although disk access is currently low I am eventually going to run out
of RAM and require a decent disk subsystem.

Do people have any recommendations as to what hardware would alleviate
my current CPU/RAM problem but with a mind to the future would still
be able to cope with heavy disk access. My budget is about £2300/$4000
which is not a lot of money when talking databases so suggestions of a
Sun Fire T2000 or similar systems will be treated with the utmost
disdain unless you are about to give me one to keep.

--
Harry
http://www.hjackson.org
http://www.uklug.co.uk


Before anyone asks I have considered using tsearch2.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, 22 Dec 2005, Harry Jackson wrote:

> I am currently using a dual Opteron (248) single core system (RAM
> PC3200) and for a change I am finding that the bottleneck is not disk
> I/O but CPU/RAM (not sure which). The reason for this is that the most
> frequently accessed tables/indexes are all held in RAM and when
> querying the database there is almost no disk activity which is great,
> most of the time.
>
> At the moment everything is working OK but I am noticing an almost
> linear increase in time to retrieve data from the database as the data
> set increases in size. Clustering knocks the access times down by 25%

Let's find out what's going on first. Can you find out the most expensive
query. Also, according to you what you said: (1) execution time is linear
to data set size (2) no disk IO - so why cluster will improve 25%?

Regards,
Qingqing

Harry Jackson wrote:
> I am currently using a dual Opteron (248) single core system (RAM
> PC3200) and for a change I am finding that the bottleneck is not disk
> I/O but CPU/RAM (not sure which).

Well that's the first thing to find out. What is "top" showing for CPU
usage and which processes?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Harry Jackson <harryjackson@gmail.com> writes:

> At the moment everything is working OK but I am noticing an almost
> linear increase in time to retrieve data from the database as the data
> set increases in size. Clustering knocks the access times down by 25%
> but it also knocks users off the website and can take up to 30 minutes
> which is hardly an ideal scenario.

If the whole database is in RAM I wouldn't expect clustering to have any
effect. Either you're doing a lot of merge joins or a few other cases where
clustering might be helping you, or the cluster is helping you keep more of
the database in ram avoiding the occasional disk i/o.

That said, I would agree with the others to not assume the plans for every
query is ok. It's easy when the entire database fits in RAM to be fooled into
thinking plans are ok because they're running quite fast but in fact have
problems.

In particular, if you have a query doing a sequential scan of some moderately
large table (say a few thousand rows) then you may find the query executes
reasonably fast when tested on its own but consumes enough cpu and memory
bandwidth that when it's executed frequently in an OLTP setting it pegs the
cpu at 100%.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Greg Stark" <gsstark@mit.edu> wrote
>
> If the whole database is in RAM I wouldn't expect clustering to have any
> effect. Either you're doing a lot of merge joins or a few other cases
> where
> clustering might be helping you, or the cluster is helping you keep more
> of
> the database in ram avoiding the occasional disk i/o.
>

Hi Greg,

At first I think the same - notice that Tom has submitted a patch to scan a
whole page in one run, so if Harry tests against the cvs tip, he could see
the real benefits. For example, a index scan may touch 5000 tuples, which
involves 5000 pairs of lock/unlock buffer, no matter how the tuples are
distributed. After the patch, if the tuples belong to a few pages, then a
significant number of lock/unlock are avoided.

Regards,
Qingqing

On 24 Dec 2005 10:25:09 -0500, Greg Stark <gsstark@mit.edu> wrote:
>
> Harry Jackson <harryjackson@gmail.com> writes:
>
> > I always look at the explain plans.
> >
> > =# explain select item_id, term_frequency from reverse_index where
> > term_id = 22781;
> > QUERY PLAN
> > -----------------------------------------------------------------------------------------------
> > Bitmap Heap Scan on reverse_index (cost=884.57..84443.35 rows=150448 width=8)
> > Recheck Cond: (term_id = 22781)
> > -> Bitmap Index Scan on reverse_index_term_id_idx
> > (cost=0.00..884.57 rows=150448 width=0)
> > Index Cond: (term_id = 22781)
> > (4 rows)
>
> Can you send EXPLAIN ANALYZE for this query for a problematic term_id? Are you
> really retrieving 150k records like it expects? In an OLTP environment that's
> an awful lot of records to be retrieving and might explain your high CPU usage
> all on its own.

The above is with the problematic term_id

The above comes in at around 1/4 of a second which is fine for now but
will cause me severe problems in a few months when the size of teh
database swells.

> 250ms might be as good as you'll get for 150k records. I'm not sure precaching
> that many records will help you. You're still going to have to read them from
> somewhere.

This is what I am thinking. I have tried various methods to reduce the
time. I even tried to use "order by" then reduce the amount of data to
50K records to see if this would work but it came in at around the
same amount of time. It is faster if I use the following though but
not by much.

=# explain select * from reverse_index where term_id = 22781 order by
term_frequency DESC limit 30000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=67337.87..67412.87 rows=30000 width=12)
-> Sort (cost=67337.87..67565.26 rows=90956 width=12)
Sort Key: term_frequency
-> Index Scan using reverse_index_term_id_idx on
reverse_index (cost=0.00..59846.33 rows=90956 width=12)
Index Cond: (term_id = 22781)
(5 rows)

I was actually suprised by this and it shows that whatever routines
Postgresql is using to sort the data its pretty bloody fast. The total
sort time for 110K records is about 193ms. The its retrieval after
that. What also suprised me is that without the sort

select * from reverse_index where term_id = 22781;

is slower than

select item_id, term_frequency from reverse_index where term_id = 22781;

but with the sort and limit added

select * from reverse_index where term_id = 22781 order by
term_frequency DESC limit 30000;

is faster than

select item_id, term_frequency from reverse_index where term_id =
22781 order by term_frequency DESC limit 30000;

> I guess clustering on term_id might speed this up by putting all the records
> being retrieved together. It might also let the planner use a plain index scan
> instead of a bitmap scan and get the same benefit.

Yep. I clustered on the term_id index again before running the above
explain and this time we have a plain index scan.

> > The next query absolutely flies but it would have been the one I would
> > have expected to be a lot slower.
> > ...
> > This comes in under 10.6ms which is astounding and I am more than
> > happy with the performance to be had from it.
>
> Out of curiosity it would be interesting to see the EXPLAIN ANALYZE from this
> too.


QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on item i (cost=8.01..16.18 rows=4 width=478)
Recheck Cond: ((item_id = 20006293) OR (item_id = 20097065) OR
(item_id = 20101014) OR (item_id = 20101015))
-> BitmapOr (cost=8.01..8.01 rows=4 width=0)
-> Bitmap Index Scan on item_item_id_pk (cost=0.00..2.00
rows=1 width=0)
Index Cond: (item_id = 20006293)
-> Bitmap Index Scan on item_item_id_pk (cost=0.00..2.00
rows=1 width=0)
Index Cond: (item_id = 20097065)

<snip lots of single item_id bitmap index scans>

-> Bitmap Index Scan on item_item_id_pk (cost=0.00..2.00
rows=1 width=0)
Index Cond: (item_id = 20101014)
-> Bitmap Index Scan on item_item_id_pk (cost=0.00..2.00
rows=1 width=0)
Index Cond: (item_id = 20101015)


Another intereting thing I noticed was the size of the tables and
indexes after the cluster operation

BEFORE:
relname | bytes | kbytes | relkind | mb
---------------------------+-----------+--------+---------+-----
reverse_index | 884293632 | 863568 | r | 843
reverse_index_pk | 548126720 | 535280 | i | 522
reverse_index_term_id_idx | 415260672 | 405528 | i | 396

AFTER:
reverse_index | 635944960 | 621040 | r | 606
reverse_index_pk | 322600960 | 315040 | i | 307
reverse_index_term_id_idx | 257622016 | 251584 | i | 245

This database has autovacuum running but it looks like there is a lot
of space in pages on disk that is not being used. Is this a trade off
when using MVCC?

--
Harry
http://www.uklug.co.uk
http://www.hjackson.org

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster