gmane.comp.db.postgresql.general

Re: Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

Gavin Flower — 2012-05-23T21:45:38

How does this compare to your other machines running the same, or 
similar, databases?
However, you do say that the other machines are indentical - but are the 
other
machines different in any aspect, that might prove siginificant?


The complexity, structure, and features of the queries. Do you have lots 
of sub queries,
and ORDER BY's? Also the number of tables accessed in a query. This is 
heading into the
territory where others will be better placed to advise you as to what 
might be relevant!


The number, type, size, and usage of indexes might be relevant.


I hope the above comments help, probably others will have more specific 
requests.


Cheers,
Gavin

Re: Does Postgres compress data?

Mike Christensen — 2012-05-24T01:23:13

On Wed, May 23, 2012 at 6:16 PM, Greg Williamson
<gwilliamson39< at >yahoo.com> wrote:

Yup, looks like this is the default behavior for TOAST-able columns.
I'm running on 9.0.  Anyway, good information - I was mostly just
curious..

Mike

Re: Does Postgres compress data?

Greg Williamson — 2012-05-24T01:16:55

Mike --


<...>



It may vary from version of postgres to version, but perhaps you are seeing the effects of TOAST kicking in ? Do a search in the documentation for your specific version (8.3, 9.1 etc.)

HTH,

Greg Williamson

Re: Does Postgres compress data?

Adrian Klaver — 2012-05-24T01:11:10

Yes. See here for complete answer:

http://www.postgresql.org/docs/9.1/static/storage-toast.html

Does Postgres compress data?

Mike Christensen — 2012-05-24T01:07:01

If I run this query:

select sum(length(html)) from Indexer.Pages;

I get:

15,680,005,116

However, if I type:

C:\Program Files\PostgreSQL>dir /s

I get:

Total Files Listed:
       5528 File(s)  7,414,385,333 bytes
        575 Dir(s)  43,146,137,600 bytes free

So all the Postgres data on disk is a little over 7 gigs, however the
total sum of bytes in the HTML column of the Pages table is over 15
gigs.

Is PG compressing this data?  I'm curious as I was considering
converting this column to a byte array and gzip'ing the data to save
space, however if PG is already doing this for me, then I'm not going
to bother.  Thanks!

Mike

Re: FATAL: lock file "postmaster.pid" already exists

Mark Dilger — 2012-05-24T00:42:28

FindFirstFile can take a wildcard filename
pattern.  It appears that we are effectively
calling FindFirstFile without a pattern, getting
all 56000 file names with complete stat
information, doing a poor-man's regex on
those names, and matching just the temporary
files.

If RemovePgTempFiles were modified to
pass a filter, this code might perform better
on Windows.  I'll look into this.




________________________________
 From: Tom Lane <tgl< at >sss.pgh.pa.us>
To: Mark Dilger <markdilger< at >yahoo.com> 
Cc: deepak <deepak.pn< at >gmail.com>; Alban Hertroys <haramrae< at >gmail.com>; "pgsql-general< at >postgresql.org" <pgsql-general< at >postgresql.org> 
Sent: Wednesday, May 23, 2012 4:25 PM
Subject: Re: [GENERAL] FATAL: lock file "postmaster.pid" already exists 
 
Mark Dilger <markdilger< at >yahoo.com> writes:


That would explain it all right.  I think you're basically screwed here,
because so far as I can see Windows doesn't provide any means to
enumerate a directory's contents without fetching that info; at least
http://msdn.microsoft.com/en-us/library/windows/desktop/aa364232(v=vs.85).aspx
doesn't seem to offer any substitutes for FindFirstFile/FindNextFile.

It's barely possible that using FindFirstFileEx with fInfoLevelId =
FindExInfoBasic would save enough to be useful, except that that option
doesn't exist on Windows 2003 anyway.

Consider using another operating system ...

            regards, tom lane

Re: FATAL: lock file "postmaster.pid" already exists

Tom Lane — 2012-05-23T23:25:58


That would explain it all right.  I think you're basically screwed here,
because so far as I can see Windows doesn't provide any means to
enumerate a directory's contents without fetching that info; at least
http://msdn.microsoft.com/en-us/library/windows/desktop/aa364232(v=vs.85).aspx
doesn't seem to offer any substitutes for FindFirstFile/FindNextFile.

It's barely possible that using FindFirstFileEx with fInfoLevelId =
FindExInfoBasic would save enough to be useful, except that that option
doesn't exist on Windows 2003 anyway.

Consider using another operating system ...

regards, tom lane

Re: pg_log is 2 hours ahead ???

Steve Crawford — 2012-05-23T23:17:33

show log_timezone;

If not what you want, update it in postgresql.conf

Cheers,
Steve

pg_log is 2 hours ahead ???

Andreas — 2012-05-23T23:11:48

Hi,

I'm running a PG 9.1.3 on OpenSuse 12.1.
I found that pg_log is 2 hours ahead though   date   on the console 
shows the right date and time.
I have the ntp daemon watching the system's time every 60 minutes so 
this shouldn't be an issue.

The time in PG's logfiles filenames as well as the timestamps within the 
logs are 2 hours ahead.

How can I fix this?

Re: FATAL: lock file "postmaster.pid" already exists

Mark Dilger — 2012-05-23T22:47:18

I am running this code on Windows 2003.  It
appears that postgres has in src/port/dirent.c
a port of readdir() that internally uses the
WIN32_FIND_DATA structure, and the function
FindNextFile() to iterate through the directory.
Looking at the documentation, it seems that
this function does collect file creation time,
last access time, last write time, file size, etc., 

much like performing a stat.

In my case, the code is iterating through roughly
56,000 files.  Apparently, this is doing the 

equivalent of a stat on each of them.


See http://msdn.microsoft.com/en-us/library/windows/desktop/aa365740%28v=vs.85%29.aspx





________________________________
 From: Tom Lane <tgl< at >sss.pgh.pa.us>
To: Mark Dilger <markdilger< at >yahoo.com> 
Cc: deepak <deepak.pn< at >gmail.com>; Alban Hertroys <haramrae< at >gmail.com>; "pgsql-general< at >postgresql.org" <pgsql-general< at >postgresql.org> 
Sent: Wednesday, May 23, 2012 1:54 PM
Subject: Re: [GENERAL] FATAL: lock file "postmaster.pid" already exists 
 
Mark Dilger <markdilger< at >yahoo.com> writes:

OK.  I had forgotten that in recent versions, RemovePgTempFiles doesn't
only iterate through the pgsql_tmp directories; it scans the regular
database directories too, looking for possibly orphaned temp relations.
So if you had lots and lots of files in your regular database
directories, possibly scanning those could be slow.  Still, it's only
looking at the file names, not attempting to stat() them or anything,
so it would be a pretty shoddy filesystem that would take a really long
time for that.

            regards, tom lane

Re: Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

Lonni J Friedman — 2012-05-23T22:33:36

Yes, that is correct.

Re: Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

Tom Lane — 2012-05-23T22:33:00


I think Lonnie said that the other machines are just running standby
clusters, which would mean they aren't running autovacuum as such,
merely applying any WAL it produces.  So that could be plenty enough
to explain a difference in kernel-visible behavior.

regards, tom lane

Re: Extreme PostgreSQL?

Devrim GÜNDÜZ — 2012-05-23T21:49:15

These are not "extreme" for PostgreSQL. PostgreSQL 9.2 will be able to
scale up to 64 cores. I know a 9.1 installation that runs on a 48-core
machine. It also depends on the application behavior, though.

More RAM is nice, and if you can fit your data to the RAM, you will feel
more comfortable.

For details about SSD, please look at pgsql-performance archives. You
will see lots of posts there (or just search for posts from Greg Smith,
it will be a shortcut to the solution)

Regards,

Extreme PostgreSQL?

Lists — 2012-05-23T21:37:38

We're getting ready to do system upgrades to our postgresql hosting 
cluster. Currently, we're using
8-core servers with 32 GB of RAM in each to host approximately 75 end 
user databases per server. I'm wondering
if there are particular performance bottlenecks we should be aware of as 
we scale the hardware up? Does
PG handle 64 GB of RAM well? 128 GB? 16 cores? 48 cores? SAS/SATA III 
with SSDs? (etc.)

A bit of Googling found this thread indicating potential performance 
issues at 28ish cores a couple years back
http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html

Thanks,

Ben

Re: FATAL: lock file "postmaster.pid" already exists

Tom Lane — 2012-05-23T20:54:43

OK.  I had forgotten that in recent versions, RemovePgTempFiles doesn't
only iterate through the pgsql_tmp directories; it scans the regular
database directories too, looking for possibly orphaned temp relations.
So if you had lots and lots of files in your regular database
directories, possibly scanning those could be slow.  Still, it's only
looking at the file names, not attempting to stat() them or anything,
so it would be a pretty shoddy filesystem that would take a really long
time for that.

regards, tom lane

Re: One schema per different databases

Igor — 2012-05-23T20:22:40

Up...

2012/5/18 Igor <igorya.inscriptio< at >gmail.com>

Re: Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

Lonni J Friedman — 2012-05-23T20:18:18

On Wed, May 23, 2012 at 12:36 PM, Gavin Flower
<GavinFlower< at >archidevsys.co.nz> wrote:

16 core Xeon X5550 2.67GHz
128GB RAM
$PGDATA sits on a RAID5 array comprised of 3 SATA disks.  Its Linux's
md software RAID.


nothing else.  its dedicated exclusively to postgresql.


I'm not sure how I'd obtain this data.  however, the patterns didn't
change since the upgrade.  If someone can point me in the right
direction, I can at least obtain this data as its generated currently.


I need some clarification on specifically what you're asking for here.


there are several rather large tables (90 million+ rows), but most
others are under 1M rows.  However, most tables are accessed & written
to with equal frequency.

Re: FATAL: lock file "postmaster.pid" already exists

Mark Dilger — 2012-05-23T20:04:14

We only use one database, not counting the
built-in template databases.  The server is
running 9.1.3.  We were running 9.1.1 until
fairly recently.

We are still getting set up to test this on 

non-virtual hardware, but hope to have results
from that in a few hours or less.



________________________________
 From: Tom Lane <tgl< at >sss.pgh.pa.us>
To: Mark Dilger <markdilger< at >yahoo.com> 
Cc: deepak <deepak.pn< at >gmail.com>; Alban Hertroys <haramrae< at >gmail.com>; "pgsql-general< at >postgresql.org" <pgsql-general< at >postgresql.org> 
Sent: Wednesday, May 23, 2012 12:23 PM
Subject: Re: [GENERAL] FATAL: lock file "postmaster.pid" already exists 
 
Mark Dilger <markdilger< at >yahoo.com> writes:

[ scratches head... ]  If you aren't using any tablespaces, there should
be only *one* pgsql_tmp directory, which makes this even more confusing.

(Unless you're using a pre-8.3 release, in which case there would be one
per database, so maybe if you've got hundreds/thousands of databases in
the cluster that would explain it.  But I sure hope you're not still
using pre-8.3, especially not on Windows.)

            regards, tom lane

Re: Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

Gavin Flower — 2012-05-23T19:36:44

I think they will need details of things like: RAM, number/type 
processors, number & type
of disks, disk controllers & any other hardware specs that might be 
relevant etc.- at very
least: total RAM & number of spindles

Also anything else running on the box.

Plus transaction load pattern - over time and read/write ratios.

type/nature of queries

size of heavily accessed tables and their indexes

Apologies, if you have already supplied the above!

Take the above with a grain of salt, I have read about performance 
issues in databases,
but never had to chase issues myself. Apart from once where I reduced 
the query time
of one particular query down from 30+ minutes to about 10 seconds PROGRESS
4GL/Oracle db (not relevant here)!


Cheers,
Gavin

(who has the luxury of designing a very sophisticated db with very low 
transaction rates,
so doesn't need to worry about performance issues)

Re: FATAL: lock file "postmaster.pid" already exists

Tom Lane — 2012-05-23T19:23:42

[ scratches head... ]  If you aren't using any tablespaces, there should
be only *one* pgsql_tmp directory, which makes this even more confusing.

(Unless you're using a pre-8.3 release, in which case there would be one
per database, so maybe if you've got hundreds/thousands of databases in
the cluster that would explain it.  But I sure hope you're not still
using pre-8.3, especially not on Windows.)

regards, tom lane

Re: FATAL: lock file "postmaster.pid" already exists

Mark Dilger — 2012-05-23T18:38:58

We do not use tablespaces at all.  We do use table
partitioning very heavily, with many check
constraints.  That is the only thing unusual about
the schema.

To my eyes, the birds appear to be flying pretty
darned fast, though we have not figured out how
to remove the message bands quickly without
cutting off their feet.

The server is a virtual machine, and at this point
I will ask the sys admins to get a non-virtual
server running to reconfirm the problem.

Thanks



________________________________
 From: Tom Lane <tgl< at >sss.pgh.pa.us>
To: Mark Dilger <markdilger< at >yahoo.com> 
Cc: deepak <deepak.pn< at >gmail.com>; Alban Hertroys <haramrae< at >gmail.com>; "pgsql-general< at >postgresql.org" <pgsql-general< at >postgresql.org> 
Sent: Wednesday, May 23, 2012 11:17 AM
Subject: Re: [GENERAL] FATAL: lock file "postmaster.pid" already exists 
 
Mark Dilger <markdilger< at >yahoo.com> writes:


OK, so we're back to the original question: how could this possibly be
taking that long?  Have you got thousands of tablespaces (and if so why)?
Does your system have a habit of crashing at times when there are
thousands of temp files?  Maybe you're using IP over avian carriers to
access your SAN?  It just doesn't make any sense given the information
you've provided.

            regards, tom lane