Tomas,
OK, what was the number of unused pointer items in the VACUUM output?
I posted it in this thread:
VACUUM FULL ANALYZE VERBOSE;
...
INFO: free space map contains 14353 pages in 314 relations
DETAIL: A total of 2 page slots are in use (including overhead).
89664 page slots are requ
>> Given the fact that the performance issues are caused by bloated tables
>> and / or slow I/O subsystem, moving to a similar system won't help I
>> guess.
>
> I have ran VACUUM FULL ANALYZE VERBOSE
> and set MAX_FSM_PAGES = 15
>
> So there is no any bloat except pg_shdepend indexes which shou
Tomas,
Let's suppose you set a reasonable value (say 8096) instead of 2GB. That
gives about 160MB.
Anyway this depends - if you have a lot of slow queries caused by on-disk
sorts / hash tables, use a higher value. Otherwise leave it as it is.
Probably product orders table is frequently joined
Scott,
thank you.
> work_mem = 512
This is very easy to try. You can change work_mem for just a single
session, and this can in some cases help performance quite a bit, and in
others not at all.
I would not recommend having it lower than at least 4MB on a server like
that unless you have a lo
My test computer has PostgreSql 8.3, 4 GB RAM, SSD disks, Intel X2Extreme
CPU
So it is much faster than this prod server.
No idea how to emulate this environment.
I can create new db in prod server as old copy but this can be used in late
night only.
Well, a faster but comparable system may not
Scott,
thank you.
> work_mem = 512
This is very easy to try. You can change work_mem for just a single
session, and this can in some cases help performance quite a bit, and in
others not at all.
I would not recommend having it lower than at least 4MB on a server like
that unless you have a lo
I guess you have backups - take them, restore the database on a different
machine (preferably with the same / similar hw config) and tune the
queries on it.
After restoring all the tables / indexes will be 'clean' (not bloated), so
you'll see if performing VACUUM FULL / CLUSTER is the right solut
> Appoaches which probably does not change perfomance:
> 6. Upgrade to 8.4 or to 8.3.5
Both of these will improve performance a little, even with the same query plan
and same data. I would expect about a 10% improvement for 8.3.x on most memory
bound select queries. 8.4 won't be out for a few
Risky to try in prod server. Requires creating randomly distributed
product_id testcase to measure
difference.
What should I do next?
I guess you have backups - take them, restore the database on a
different machine (preferably with the same / similar hw config) and
tune the queries on it.
You could try writing a plpgsql function which would generate the data
set.
Or you could use your existing data set.
Creating 3.5 mln rows using stored proc is probably slow.
Probably it would be better and faster to use some random() and
generate_series() trick.
In this case others can try it a
Thank you very much for great sample.
I tried to create testcase from this to match production db:
1.2 million orders
3.5 million order details
13400 products with char(20) as primary keys containing ean-13 codes
mostly
3 last year data
every order has usually 1..3 detail lines
same product
You could perhaps run a little check on the performance of the RAID, is
it better than linux software RAID ?
Does it leverage NCQ appropriately when running queries in parallel ?
I was told that this RAID is software RAID.
I have no experience what to check.
This HP server was installed 3 years
On Fri, 21 Nov 2008 21:07:02 +0100, Tom Lane <[EMAIL PROTECTED]> wrote:
PFC <[EMAIL PROTECTED]> writes:
Index on orders_products( product_id ) and orders_products( order_id ):
=> Same plan
Note that in this case, a smarter planner would use the new index to
perform a BitmapAn
@postgresql.org
Subject: Re: [PERFORM] Hash join on int takes 8..114 seconds
> If it's not a million rows, then the table is bloated. Try (as postgres
> or some other db superuser) "vacuum full pg_shdepend" and a "reindex
> pg_shdepend".
reindex table pg_shdepend
If it's not a million rows, then the table is bloated. Try (as postgres
or some other db superuser) "vacuum full pg_shdepend" and a "reindex
pg_shdepend".
reindex table pg_shdepend causes error
ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone mode
vacuum full verbose pg_
2. Run the following commands periodically in this order:
VACUUM FULL;
vacuum full pg_shdepend;
CLUSTER rid on (toode);
CLUSTER dok on (kuupaev);
REINDEX DATABASE mydb;
REINDEX SYSTEM mydb;
ANALYZE;
Are all those command required or can something leaved out ?
Running CLUSTER after VACUUM FULL
Thank you.
My 8.1.4 postgresql.conf does not contain such option. So
vacuum_cost_delay is off probably.
Since doc does not recommend any value, I planned to use 2000
Will value of 30 allow other clients to work when VACUUM FULL is running ?
No, as someone already noted the VACUUM FULL is bloc
Andrus wrote:
>> So I gather you're not doing any vacuuming, eh?
>
> Log files for every day are full of garbage messages below.
> So I hope that vacuum is running well, isn't it ?
This does not really mean that autovacuum has done anything in the
databases. If the times are consistently separat
Alvaro,
1. vacuum_cost_delay does not affect vacuum full
2. vacuum full is always blocking, regardless of settings
So only way is to disable other database acces if vacuum full is required.
So I gather you're not doing any vacuuming, eh?
Log files for every day are full of garbage messages
PFC <[EMAIL PROTECTED]> writes:
> Index on orders_products( product_id ) and orders_products( order_id ):
> => Same plan
> Note that in this case, a smarter planner would use the new index to
> perform a BitmapAnd before hitting the heap to get the rows.
Considering that the query h
Andrus wrote:
> Will value of 30 allow other clients to work when VACUUM FULL is running ?
1. vacuum_cost_delay does not affect vacuum full
2. vacuum full is always blocking, regardless of settings
So I gather you're not doing any vacuuming, eh?
--
Alvaro Herrera
Alvaro,
Are you really using vacuum_cost_delay=2000? If so, therein lies your
problem. That's a silly value to use for that variable. Useful values
are in the 20-40 range probably, or maybe 10-100 being extremely
generous.
Thank you.
My 8.1.4 postgresql.conf does not contain such option. So
Andrus wrote:
> I discovered vacuum_cost_delay=2000 option. Will this remove blocking
> issue and allow vacuum full to work ?
No.
Are you really using vacuum_cost_delay=2000? If so, therein lies your
problem. That's a silly value to use for that variable. Useful values
are in the 20-40 range
On Friday 21 November 2008, "Andrus" <[EMAIL PROTECTED]> wrote:
> Those commands cause server probably to stop responding to other client
> like vacuum full pg_shdepend
> did.
>
> Should vacuum_cost_delay = 2000 allow other users to work when running
> those commands ?
Any vacuum full or cluster w
How to vacuum full pg_shdepend automatically so that other users can
work at same time ?
Your table is horribly bloated.
You must use VACUUM FULL + REINDEX (as superuser) on it, however
unfortunately, it is blocking.
Therefore, you should wait for sunday night to do this, when noone will
notic
log file seems that mostly only those queries are slow:
SELECT ...
FROM dok JOIN rid USING (dokumnr)
JOIN ProductId USING (ProductId)
WHERE rid.ProductId LIKE :p1 || '%' AND dok.SaleDate>=:p2
:p1 and :p2 are parameters different for different queries.
dok contains several years of d
Thomas,
Thank you.
Just the most important points:
1) "dok" table contains 1235086 row versions in 171641 pages (with 8kB
pages this means 1.4GB MB of data), but there are 1834279 unused item
pointers (i.e. about 60% of the space is wasted)
2) "rid" table contains 3275189 roiws in 165282 (wit
Server has 2 GB RAM.
It has SATA RAID 0,1 integrated controller (1.5Gbps) and SAMSUNG HD160JJ
mirrored disks.
You could perhaps run a little check on the performance of the RAID, is
it better than linux software RAID ?
Does it leverage NCQ appropriately when running queries in para
How to vacuum full pg_shdepend automatically so that other users can
work at same time ?
Your table is horribly bloated.
You must use VACUUM FULL + REINDEX (as superuser) on it, however
unfortunately, it is blocking.
Therefore, you should wait for sunday night to do this, when noo
Richard,
Thank you.
Try "SELECT count(*) FROM pg_shdepend".
This query returns 3625 and takes 35 seconds to run.
If it's not a million rows, then the table is bloated. Try (as postgres
or some other db superuser) "vacuum full pg_shdepend" and a "reindex
pg_shdepend".
vacuum full verbose
Andrus wrote:
>> - what's the size of the dataset relative to the RAM ?
>
> Db size is 7417 MB
> relevant table sizes in desc by size order:
>
> 140595 dok 2345 MB
> 2 1214 pg_shdepend 2259 MB
> 6 123
Just the most important points:
1) "dok" table contains 1235086 row versions in 171641 pages (with 8kB
pages this means 1.4GB MB of data), but there are 1834279 unused item
pointers (i.e. about 60% of the space is wasted)
2) "rid" table contains 3275189 roiws in 165282 (with 8kB pages this means
PFC,
thank you.
OK so vmstat says you are IO-bound, this seems logical if the same plan
has widely varying timings...
Let's look at the usual suspects :
- how many dead rows in your tables ? are your tables data, or bloat ?
(check vacuum verbose, etc)
set search_path to firma2,public;
vacuu
Richard,
In addition to "top" below, you'll probably find "vmstat 5" useful.
Thank you.
During this query run (65 sec), vmstat 5 shows big values in bi,cs and wa
columns:
procs ---memory-- ---swap-- -io --system-- cpu
r b swpd free buff cache si
OK so vmstat says you are IO-bound, this seems logical if the same plan
has widely varying timings...
Let's look at the usual suspects :
- how many dead rows in your tables ? are your tables data, or bloat ?
(check vacuum verbose, etc)
- what's the size of the dataset re
Andrus wrote:
> Richard,
>
>> At a quick glance, the plans look the same to me. The overall costs are
>> certainly identical. That means whatever is affecting the query times it
>> isn't the query plan.
>>
>> So - what other activity is happening on this machine? Either other
>> queries are taking
Richard,
At a quick glance, the plans look the same to me. The overall costs are
certainly identical. That means whatever is affecting the query times it
isn't the query plan.
So - what other activity is happening on this machine? Either other
queries are taking up noticeable resources, or some
Andrus wrote:
> Query below seems to use indexes everywhere in most optimal way.
> dokumnr column is of type int
>
> Speed of this query varies rapidly:
>
> In live db fastest response I have got is 8 seconds.
> Re-running same query after 10 seconds may take 60 seconds.
> Re-running it again af
Just a question, what are you doing with the 20.000 result rows ?
Those rows represent monthly sales data of one item.
They are used as following:
1. Detailed sales report for month. This report can browsed in screen for
montly sales and ordering analysis.
2. Total reports. In those reports,
Query below seems to use indexes everywhere in most optimal way.
dokumnr column is of type int
Speed of this query varies rapidly:
In live db fastest response I have got is 8 seconds.
Re-running same query after 10 seconds may take 60 seconds.
Re-running it again after 10 seconds may take 114
Query below seems to use indexes everywhere in most optimal way.
dokumnr column is of type int
Speed of this query varies rapidly:
In live db fastest response I have got is 8 seconds.
Re-running same query after 10 seconds may take 60 seconds.
Re-running it again after 10 seconds may take 114 s
41 matches
Mail list logo
