Thread: CPU and RAM
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.
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
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
"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