"table1" structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigintdefault 0
delta3bigintdefault 0
delta
On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
<[EMAIL PROTECTED]> wrote:
> relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
> ---++---+---+---+
> 16461 | table1 | 0 | 8352496 | 5389 |83
Tom,
Thank you for your prompt reply.
On Tue, Apr 29, 2008 at 10:19 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Len Shapiro <[EMAIL PROTECTED]> writes:
> > 1. Why does Postgres come up with a negative n_distinct?
>
> It's a fractional representation. Per the docs:
>
> > stadistinct float4
relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
---++---+---+---+
16461 | table1 | 0 | 8352496 | 5389 |8351242
On Wed, Apr 30, 2008 at 11:07 AM, Pavan Deolasee <[EMAIL PROTECTED]>
wrot
On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
<[EMAIL PROTECTED]> wrote:
> HOT doesn't seems to be working in our case.
>
Can you please post output of the following query ?
SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
from pg_stat_user_tables WHERE relname = 'table1';
HOT doesn't seems to be working in our case.
This is "table1" structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigint
Len Shapiro <[EMAIL PROTECTED]> writes:
> 1. Why does Postgres come up with a negative n_distinct?
It's a fractional representation. Per the docs:
> stadistinct float4 The number of distinct nonnull data values in
> the column. A value greater than zero is the actual number of distin
I hope I am posting to the right list.
I am running Postgresql 8.1.9 and don't understand the behavior of
histograms for data items not in the MVC list. I teach databases and
want to use Postgres as an example. I will appreciate any help that
anyone can provide.
Here is the data I am using. I a
On Tue, Apr 29, 2008 at 2:55 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> This is the best write-up I've seen yet on quantifying what SSDs are good
> and bad at in a database context:
>
> http://www.bigdbahead.com/?p=37
>
> --
> * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, M
On Tue, 29 Apr 2008, Guido Neitzer wrote:
They totally missed "mainly write" applications which most of my applications
are.
Not totally--the very first graph shows that even on random data, 100%
write apps are 1/2 the speed of a regular hard drive.
After poking around the site a bit more t
On 29.04.2008, at 12:55, Greg Smith wrote:
This is the best write-up I've seen yet on quantifying what SSDs are
good and bad at in a database context:
http://www.bigdbahead.com/?p=37
They totally missed "mainly write" applications which most of my
applications are. Reads in a OLTP setup a
This is the best write-up I've seen yet on quantifying what SSDs are good
and bad at in a database context:
http://www.bigdbahead.com/?p=37
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To
Alvaro Herrera wrote:
Gauri Kanekar escribió:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is always working, if it can. You don't need to configure it.
Unless you have upgraded since you started this thread you are still
running 8.1.3.
HO
On Tue, 29 Apr 2008, John Rouillard wrote:
So swap the memory usage from the OS cache to the postgresql process.
Using 1/4 as a guideline it sounds like 600,000 (approx 4GB) is a
better setting. So I'll try 30 to start (1/8 of memory) and see
what it does to the other processes on the box.
[EMAIL PROTECTED] ("Gauri Kanekar") writes:
> Basically we have some background process which updates "table1" and
> we don't want the application to make any changes to "table1" while
> vacuum. Vacuum requires exclusive lock on "table1" and if any of
> the background or application is ON vacuum d
On Mon, Apr 28, 2008 at 02:16:02PM -0400, Greg Smith wrote:
> On Mon, 28 Apr 2008, John Rouillard wrote:
>
> > 2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds
> > apart)
> > so I changed:
> > checkpoint_segments = 30
> > checkpoint_warning = 150
>
> That's good, but you
On Tue, Apr 29, 2008 at 05:19:59AM +0930, Shane Ambler wrote:
> John Rouillard wrote:
>
> >We can't do this as we are backfilling a couple of months of data
> >into tables with existing data.
>
> Is this a one off data loading of historic data or an ongoing thing?
Yes it's a one off bulk data l
On Apr 29, 2008, at 10:16 AM, Tom Lane wrote:
Greg Smith <[EMAIL PROTECTED]> writes:
The model here assumes that you'll need that space again for the
next time
you UPDATE or INSERT a row. So instead VACUUM just keeps those
available
for database reuse rather than returning it to the opera
"A B" <[EMAIL PROTECTED]> writes:
> I'm not sure what the size acctually is... But I can't imagine that it
> is 1,6 GB!!! I'd say I have 11MB of data in it...
Sounds like you've got a rather severe case of table and/or index bloat.
This is typically caused by not vacuuming often enough.
The easie
Greg Smith <[EMAIL PROTECTED]> writes:
> The model here assumes that you'll need that space again for the next time
> you UPDATE or INSERT a row. So instead VACUUM just keeps those available
> for database reuse rather than returning it to the operating system.
> Now, if you don't VACUUM freque
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
>> Any special guideline to follow to make HOT working??
>>
>
> You can do couple of things to benefit from HOT.
>
> 1. HOT addresses a special, but common case where UPDATE operation
> does not change any of the index keys. So check if your UPDATE chan
On Tue, Apr 29, 2008 at 6:42 PM, Gauri Kanekar
<[EMAIL PROTECTED]> wrote:
> Thats how our updates works.
> We usually tend to touch the same row many times a day.
>
Then start with a non-100 fillfactor. I would suggest something like
80 and then adjust based on the testing. Since you are anyways h
Thats how our updates works.
We usually tend to touch the same row many times a day.
~ Gauri
On Tue, Apr 29, 2008 at 6:39 PM, Pavan Deolasee <[EMAIL PROTECTED]>
wrote:
> On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
> <[EMAIL PROTECTED]> wrote:
> >
> >
> > Found that the size increased graduall
On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
<[EMAIL PROTECTED]> wrote:
>
>
> Found that the size increased gradually. Is HOT working over here ??
> Guide me if im doing something wrong.
>
You have chosen a bad case for HOT. Since you are repeatedly updating
the same set of rows, the dead space
Gauri Kanekar escribió:
> Found that the size increased gradually. Is HOT working over here ??
> Guide me if im doing something wrong.
Probably not. Try vacuuming between the updates.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consult
Gauri Kanekar escribió:
> Do we need to do any special config changes or any other setting for HOT to
> work??
No. HOT is always working, if it can. You don't need to configure it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command
Thanx for the help.
Need some more help.
"table1" has two indices
unique indx1 = "pkfld"
unique indx2 = "fkfld1,fkfld2"
did following steps in the listed order -
1. vacuumed the whole DB
2. "table1"
RecCnt ==> 11970789
Size ==> 2702.41 MB
3.update "table1" set fld7 = 1000 where fld1
Tom Lane wrote:
Pablo Alcaraz <[EMAIL PROTECTED]> writes:
We have a database running smoothly for months. 2 days ago I get this
error message. I tried a restore, a full restore (deleting the old
database and recovering from backup all the information) but we are
getting this error every tim
I recall reading posts in the past where one could query the stat tables and
see how well autovacuum was performing. Not finding the posts.
I found this query:
SELECT relname, relkind, reltuples, relpages FROM pg_class where relkind =
'r';
From the output how can I tell the number of dead tu
On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
<[EMAIL PROTECTED]> wrote:
>
> Do we need to do any special config changes or any other setting for HOT to
> work??
No. HOT is enabled by default, on all tables. There is no way and need
to disable it.
>
> Any special guideline to follow to make HOT
Gauri Kanekar wrote:
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
We do vacuum full, as vacuum verbose analyse dont regain space for us.
vacuum full stops all access so that the data files can be re-writen
without the unused space.
normal vacuum w
>From most of the reply found that upgrade to higher version of postgres may
be to 8.3.1 may be one of the solution to tackle this problem
Checked about HOT feature in 8.3.1.
Do we need to do any special config changes or any other setting for HOT to
work??
Any special guideline to follow to ma
Here is some more information.
Size of database:
du -sh /var/lib/pgsql/data/base/*
4,1M/var/lib/pgsql/data/base/1
4,1M/var/lib/pgsql/data/base/10792
4,1M/var/lib/pgsql/data/base/10793
9,1M/var/lib/pgsql/data/base/16388
11M /var/lib/pgsql/data/base/19233
1,6G/var/lib/pgsql/
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
We do vacuum full, as vacuum verbose analyse dont regain space for us.
Ah, now we're getting to the root of your problem here. You expect that
VACUUM should reclaim space.
Whenever you UPDATE a row, it writes a new one out, then switches to use
th
34 matches
Mail list logo
