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
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
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
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
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
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
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
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
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
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
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
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
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:
> 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
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
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
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
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
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
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_
If Autovacuum was working, and your tables still got very bloated, it may be
because your free space map is not configured large enough.
What is your value for max_fsm_pages?
The effect of having max_fsm_pages or max_fsm_relations too small is bloating
of tables and indexes.
Increasing it too l
21 matches
Mail list logo
