[GENERAL] Vaccum Stalling
Version 7.4.12 AIX 5.3 Scenario - a large table was not being vacuumed correctly, there now ~ 15 million dead tuples that account for approximately 20%-25% of the table. Vacuum appears to be stalling - ran for approximately 10 hours before I killed it. I hooked up to the process with gdb and this looks a bit suspicious to me. gdb attach 979354 GNU gdb 6.0 Copyright 2003 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "powerpc-ibm-aix5.1.0.0"...attach: No such file or directory. Attaching to process 979354 [Switching to Thread 1] 0xd033ce1c in read () from /usr/lib/libc.a(shr.o) (gdb) bt #0 0xd033ce1c in read () from /usr/lib/libc.a(shr.o) #1 0x10034398 in FileRead (file=805585096, buffer=0xb38d0e40 "", amount=8192) at fd.c:973 #2 0x1009eea8 in mdread (reln=0x301aa618, blocknum=160246, buffer=0xb38d0e40 "") at md.c:434 #3 0x1009d3d0 in smgrread (which=8192, reln=0x301aa618, blocknum=160246, buffer=0x ) at smgr.c:316 #4 0x1002c0a8 in ReadBufferInternal (reln=0x301aa618, blockNum=160246, bufferLockHeld=84 'T') at bufmgr.c:230 #5 0x101e5080 in _bt_getbuf (rel=0x301aa618, blkno=4294967295, access=1) at nbtpage.c:401 #6 0x101e41c4 in btbulkdelete (fcinfo=0x) at nbtree.c:692 #7 0x10048670 in OidFunctionCall3 (functionId=8192, arg1=807052824, arg2=805496980, arg3=806803160) at fmgr.c:1260 #8 0x100dad04 in index_bulk_delete (indexRelation=0x301aa618, [EMAIL PROTECTED]: 0x10194150 , callback_state=0x3016d6d8) at indexam.c:618 #9 0x10193fcc in lazy_vacuum_index (indrel=0x301aa618, vacrelstats=0x3016d6d8) at vacuumlazy.c:712 #10 0x10194b04 in lazy_vacuum_rel (onerel=0x302357b8, vacstmt=0x) at vacuumlazy.c:279 #11 0x10192da4 in vacuum_rel (relid=807622584, vacstmt=0x3016b558, expected_relkind=114 'r') at vacuum.c:855 #12 0x10192fb0 in vacuum (vacstmt=0x3016b558) at vacuum.c:290 #13 0x10141670 in ProcessUtility (parsetree=0x3016b558, dest=0x3016b5a8, completionTag=0x2ff1d0c4 "") at utility.c:831 #14 0x10121b20 in PortalRunUtility (portal=0x301f3768, query=0x3016b5f8, dest=0x3016b5a8, completionTag=0x2ff1d0c4 "") at pquery.c:772 #15 0x1012204c in PortalRun (portal=0x301f3768, count=806794920, dest=0x3016b5a8, altdest=0x3016b5a8, completionTag=0x2ff1d0c4 "") at pquery.c:836 #16 0x10010034 in exec_simple_query (query_string=0x3016b278 "VACUUM ANALYZE v\erbos\e;") at postgres.c:914 ---Type to continue, or q to quit--- #17 0x100119f8 in PostgresMain (argc=4, argv=0x30086e78, username=0x30086db8 "slony") at postgres.c:2973 #18 0x101b79e4 in ServerLoop () at postmaster.c:2564 #19 0x101b95a0 in PostmasterMain (argc=1, argv=0x300853c8) at postmaster.c:897 #20 0x153c in main (argc=1, argv=0x2ff22c40) at main.c:222 #21 0x1204 in __start () Ideas? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Vaccum Stalling
On Tue, 2007-07-10 at 11:19 -0400, Tom Lane wrote: > Oh, I forgot to mention --- you did check that vacuum_mem is set to > a pretty high value, no? Else you might be doing a lot more > btbulkdelete scans than you need to. > > regards, tom lane What would you define as high for 7.4? I bumped it up to ~ 245mbs -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Vaccum Stalling
On Tue, 2007-07-10 at 11:31 -0400, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > On Tue, 2007-07-10 at 11:19 -0400, Tom Lane wrote: > >> Oh, I forgot to mention --- you did check that vacuum_mem is set to > >> a pretty high value, no? Else you might be doing a lot more > >> btbulkdelete scans than you need to. > > > What would you define as high for 7.4? I bumped it up to ~ 245mbs > > That sounds like plenty --- you only need 6 bytes per dead tuple, > so that should be enough to handle all your 15-20M dead tuples in > one scan. > > How big is this index again? Not sure which one it's working on - there are 6 of them each are ~ 2.5GB -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Time for Vacuum vs. Vacuum Full
I have a couple of database clusters that need a vacuum full, and I would like to estimate how long it will take, as it will need to be in a maintenance window. I have the times that it takes to to do a regular vacuum on the clusters, will vacuum full take longer? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Cluster and MVCC
I just want to confirm that the cluster/MVCC issues are due to transaction visibility. Assuming that no concurrent access is happening to a given table when the cluster command is issued (when takes it visibility snapshot), it is safe to cluster that table. Correct? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] I get a error msg when i try to connect to the DB
On Thu, 2007-08-16 at 21:16 +0530, Rajaram J wrote: > Hi > > I have installed PostGRESql 7.4.17 and when i try to connect to the db > i get the msg > > psql: FATAL: no pg_hba.conf entry for host "???", user "sfmdb", > database "LOGDB", SSL off > You are allowing local, non TCP/IP connctions, and connections through the loop back, but you are not allowing remote conncetions. You need an entry of some form that is host all all ??? 255.255.255.255 trust btw, trust is usually bad, you should use some form of authentication. > My pg_hba.conf entry is as below > > > > # TYPE DATABASEUSERIP-ADDRESSIP-MASK > METHOD > > # Disable default configuration > > local all all > trust > # IPv4-style local connections: > hostall all 127.0.0.1 255.255.255.255 > trust > # IPv6-style local connections: > hostall all ::1 > ::::::: trust > > > can you please guide me on what the problem might be. > > Regards > > Rajaram > > -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] lowering impact of checkpoints
On Tue, 2007-09-25 at 11:58 +0200, hubert depesz lubaczewski wrote: > hi, > our system is handling between 600 and 2000 transactions per second. all > of them are very small, very fast. typical query runs in under 1ms. > yes - sometimes we get queries that take longer than then should get. > simple check shows that we have a very visible pattern of > every-5-minutes peak. > in the minute that there is checkpoint - we get usually 15-20 times more > queries "over 500 ms" than in other minutes. > > we are using 8.2.4 (upgrade will be soon), with these settings: > # select name, setting, unit from pg_settings where name ~* > 'bgwriter|wal|checkpoint'; > name | setting | unit > ---+---+ > bgwriter_all_maxpages | 5 | > bgwriter_all_percent | 0.333 | [null] > bgwriter_delay| 200 | ms > bgwriter_lru_maxpages | 5 | > bgwriter_lru_percent | 1 | [null] > checkpoint_segments | 32| > checkpoint_timeout| 300 | s > checkpoint_warning| 30| s > wal_buffers | 128 | 8kB > wal_sync_method | fdatasync | [null] > (10 rows) > > is there anything i can change to make it "smoother"? Sounds like bgwriter is not flushing dirty pages quickly enough, so there is still a lot of work to do at checkpoint time. You probably need to tune it. This can be a tough thing to do properly though. There are no magic values to suggest, as what will work is highly dependent on your hardware and your applications pattern of use. If possible, up the settings for bgwriter_all_percent a *little* and perhaps bgwriter_all_maxpages and see if it helps. You can change these with a reload. If you are doing this on a production system as opposed to a test system, keep a close eye on what is going on, as it is possible that you can make things worse. I would start with something like 2% for bgwriter_all_maxpages and see if that helps things out. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Missing files under pg_data following power failure
On Fri, 2007-10-12 at 10:54 -0500, Scott Marlowe wrote: > On 10/12/07, Jack Orenstein <[EMAIL PROTECTED]> wrote: > > Our testing involves cutting power to machines running postgres 7.4, > > while under load (lots of reads and writes). When we do this, we often > > lose some files under pg_data storing table content. I.e., the file > > named for a table's pg_class.oid value is simply gone. This can affect > > many tables following a power outage. We know this problem has > > occurred when we are unable to access a table, e.g. > > > > ERROR: relation "xxx" does not exist > > > > The table is present in the system tables, but the file storing table > > content under pg_data is missing. > > > > Can anyone provide insight on this phenomenon? Why are these files > > lost? Are they really lost, or have they simply moved somewhere? What > > happens to the disk blocks formerly occupied by the files? > > > > Getting back in service following this file loss is not a problem; I'm > > just trying to understand how postgres gets into this state. > > First of all, this should not happen on a machine with proper > fsyncing. The possible causes are generally either fsync is off in > postgresql.conf or the drive array <--> OS layer is lying about fsync > operations. What filesystem are you using? I've seen similar problems on JFS2, it was something with logredo or fsck being busted on a Big Blue OS. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] young guy wanting (Postgres DBA) ammo
On Fri, 2007-11-02 at 00:03 -0400, Kevin Hunter wrote: > - More in line with the conversation with my friend, what/why is it that > Postgres needs a DBA while MySQL doesn't? I highly suspect that the > assumption that MySQL doesn't need a DBA is incorrect, but that's what > was posed to me and I couldn't agree or disagree. Before I was a DBA I worked as a developer in shop that had both PG and MySQL running. We had no DBA, or anyone at that time that really understood databases from a DBA side. The stuff that we had running in PG just worked. Period, no problems (and this was on PG 7.0). The stuff we had in MySQL, well, that "mysteriously" ground to a halt every night at the same time, making several customers applications unavailable. Without anyone on staff that could actually diagnose the issue, the only soution that they came up with (and I emphasise the word they, as I had no part in this :-)) was a cron job was that restarted the MySQL server every night. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql storage and performance questions
On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote: > There were a couple of things we noted. > 1. Tablesize twice as much than oracle-- Im not sure if postgres null > columns has any overhead since we have lots of null columns in our > tables.Does postgresql has lots of overhead for null columns? Did you by any chance have an aborted load of the data? If you load in a table, and that load fails or does not commit, it will still occupy the space until you vacuum. If you try to load again, the table will be twice the size. If you want to compact the physical space the table occupies, you can try running VACUUM FULL on it, and possibly a redindex afterwards. This will bring the physical space down to the minimum. Both of these operations will lock out access to the tables though. > 2. Oracle seems to be reading larger bocks than postgresql (when we > examined the iostat and vmstat) (we had set postgres' db block size as > 8 and oracle's is 16kb...) > Do you have any comments on this? 8k is the defualt. You can change the block size if you need to. You need to modify src/include/pg_config_manual.h recompile and re-initdb. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql storage and performance questions
On Tue, 2007-11-20 at 13:04 -0500, Josh Harrison wrote: > On Nov 20, 2007 11:13 AM, Brad Nicholson <[EMAIL PROTECTED]> wrote: > > On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote: > > > > > There were a couple of things we noted. > > > 1. Tablesize twice as much than oracle-- Im not sure if postgres null > > > columns has any overhead since we have lots of null columns in our > > > tables.Does postgresql has lots of overhead for null columns? > > > > Did you by any chance have an aborted load of the data? If you load in > > a table, and that load fails or does not commit, it will still occupy > > the space until you vacuum. If you try to load again, the table will be > > twice the size. > > > > If you want to compact the physical space the table occupies, you can > > try running VACUUM FULL on it, and possibly a redindex afterwards. This > > will bring the physical space down to the minimum. Both of these > > operations will lock out access to the tables though. > I ran vacuum full on this table already. I haven't re-indexed it. But > this will not affect the table size...right...since indexes are stored > separately? You are correct about the indexes. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Perceived weaknesses of postgres
Dawid Kuroczko wrote: Slony is good as long as there are no DDLs issued. And its easy to shoot oneself in the foot if one is not careful (some time ago I have lost all the triggers while upgrading from 8.1 to 8.2; it was my fault since I did pg_dump -s on a slave database, not on the master...). It can also be a very time consuming process. It often also involves having to juggle resources around, find extra disk space floating around somewhere to build extra replicas, or abandon your fail over target for a period of time while upgrade that. BTW, the trigger issue goes away with 8.3 and Slony 2.0. Brad. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to copy tables between databases?
On Tue, 2008-02-26 at 15:19 -0500, Kynn Jones wrote: > > Is there a simple way to copy a table from one database to another > without generating an intermediate dump file? > pg_dump -t | psql -d -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] page is uninitialized --- fixing
We just took a test database down (PG 8.1.11) fairly hard (pulled a SAN switch out while it was under load), which caused the DB to crash. It started up fine, when I vacuumed the DB, I saw the following messages. WARNING: relation "my_table" page 652139 is uninitialized --- fixing WARNING: relation "my_table" page 652140 is uninitialized --- fixing WARNING: relation "my_table" page 652940 is uninitialized --- fixing WARNING: relation "my_table" page 652941 is uninitialized --- fixing That sort of looks like it could be data loss, can someone explain what happened? Brad. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] page is uninitialized --- fixing
On Wed, 2008-03-26 at 15:31 -0400, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > We just took a test database down (PG 8.1.11) fairly hard (pulled a SAN > It could be that but not necessarily. These could be pages that were > allocated to put new tuples into, but the crash happened before the > inserting transaction committed (and, in fact, before any of the > associated WAL entries had made their way to disk -- else the empty > pages would've been initialized during WAL replay). > > It would be easier to believe that if the uninitialized pages were all > contiguous though. Do you know that this table was under heavy insert > load at the time? It was. This table is an insert only log table that was being heavily was being heavily written to at the time of the crash. Brad. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] page is uninitialized --- fixing
On Thu, 2008-03-27 at 10:29 -0300, Alvaro Herrera wrote: > Brad Nicholson wrote: > > On Wed, 2008-03-26 at 15:31 -0400, Tom Lane wrote: > > > Brad Nicholson <[EMAIL PROTECTED]> writes: > > > > We just took a test database down (PG 8.1.11) fairly hard (pulled a SAN > > > > It would be easier to believe that if the uninitialized pages were all > > > contiguous though. Do you know that this table was under heavy insert > > > load at the time? > > > > It was. This table is an insert only log table that was being heavily > > was being heavily written to at the time of the crash. > > Is it possible that there were *two* crashes? There was only one crash. However, there were two separate SAN switches that were pulled out from under the DB, not sure if that would matter. Brad. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] page is uninitialized --- fixing
On Thu, 2008-03-27 at 10:37 -0400, Tom Lane wrote: > > What do you mean by "two separate SAN switches pulled out" --- is the > DB spread across multiple SAN controllers? > It's using IO mutilpath through 2 HBAs. Both of those were taken down. Brad. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dbsize command
On Mon, 2008-06-23 at 10:50 -0700, Reynolds, Gregory A wrote: > In past versions there was a contrib/dbsize command that would tell > you how much storage space was being taken by a table. Is that is > still a supported feature in 8.3, and if so what is the syntax or > where is the documentation on how to use it? There are a series of functions in the database core that will tell you this now. http://www.postgresql.org/docs/8.3/interactive/functions-admin.html -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Average connections
On Wed, 2009-06-10 at 15:29 +0100, Pedro Doria Meunier wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Thank you Bill for your tip. > > As far as the table's name is concerned the only one I can find is > 'pg_statistic' (under pg_catalog). I'm using PGSQL 8.2.9 on x86_64. > > BR, http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE pg_stat_database also has the number of active server processes connected to a database. > > > Bill Moran wrote: > > In response to Pedro Doria Meunier : > > > >> -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > >> > >> Hi all > >> > >> I've googled for an answer but couldn't find one. Is there anyway > >> to monitor the average connections to a database? > >> > >> Thank you in advance for any insights. > > > > The pg_stat_activity table holds 1 row for each connection with > > information on what that connection is doing. > > > > It wouldn't be very difficult to write a passthrough script for > > something like MRTG to graph this data. > > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.7 (GNU/Linux) > Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org > > iD8DBQFKL8ND2FH5GXCfxAsRAu/XAJ43UGqlzv5gfzg1YgECbhvL2MaPzwCdEnt3 > GfewITsorV/t7cfpq3WxVqM= > =84cI > -END PGP SIGNATURE- > > -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_stat_reset() not resetting all statistics counters
On Wed, 2009-07-15 at 14:13 +0200, Rafael Martinez wrote: > Hello > > Should not the execution of pg_stat_reset() reset *all* statistics > counters everywhere in the database? It only resets the stats for the current database, not the cluster wide stats - pg_database is cluster wide. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] autovacuum and immediate shutdown issues
If you issue an immediate shutdown to the database, autovacumm will not
process tables that should be vacuumed until manually re-analyzed.
PG 8.3.8
Relevant settings:
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 10
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
test=# CREATE TABLE foo (id int);
test=# INSERT INTO foo SELECT generate_series(1,1000);
auto-analyze runs as expected
test=# SELECT last_autovacuum, last_autoanalyze from pg_stat_user_tables
where relname ='foo';
-[ RECORD 1 ]+--
last_autovacuum |
last_autoanalyze | 2009-10-19 14:14:47.791658+00
test=# DELETE FROM foo WHERE id IN (SELECT id FROM foo LIMIT 100);
test=# SELECT * from pgstattuple('foo');
-[ RECORD 1 ]--+--
table_len | 32768
tuple_count| 900
tuple_len | 25200
tuple_percent | 76.9
dead_tuple_count | 100
dead_tuple_len | 2800
dead_tuple_percent | 8.54
free_space | 656
free_percent | 2
dead_tuple_percent > 5% - autovacuum runs as expected on next pass:
test=# SELECT last_autovacuum, last_autoanalyze from pg_stat_user_tables
where relname ='foo';
-[ RECORD 1 ]+--
last_autovacuum | 2009-10-19 14:16:47.910177+00
last_autoanalyze | 2009-10-19 14:14:47.791658+00
--repopulate table
test=# TRUNCATE foo;
test=# INSERT INTO foo SELECT generate_series(1,1000);
Wait for autoanalyze, then before next run of autovacuum
test=# DELETE FROM foo WHERE id IN (SELECT id FROM foo LIMIT 100);
pg_ctl -D data -m immediate stop
restart the postmaster
stats are gone due to the immediate shutdown.
test=# SELECT last_autovacuum, last_autoanalyze from pg_stat_user_tables
where relname ='foo';
-[ RECORD 1 ]+-
last_autovacuum |
last_autoanalyze |
test=# SELECT * from pgstattuple('foo');
-[ RECORD 1 ]--+--
table_len | 32768
tuple_count| 900
tuple_len | 25200
tuple_percent | 76.9
dead_tuple_count | 100
dead_tuple_len | 2800
dead_tuple_percent | 8.54
free_space | 656
free_percent | 2
dead_tuple_percent > 5% of table is dead, autovacuum should pick it up,
but it doesn't (yes, I have waited longer enough).
autoanalyze does not process the table.
Autovacuum will not process this table again until one of the following
two conditions are met:
1: Manually analyze the table
2: an additional 5% (autovacuum_vacuum_scale_factor) of the tuples in
the are dirtied - which amounts to 10% (autovacuum_vacuum_scale_factor *
2) of the actual table being dirtied before it gets vacuumed.
In case 2 - reports from pgstattuple are odd. After deleting one row.
test=# DELETE FROM foo WHERE id IN (SELECT id FROM foo LIMIT 1);
DELETE 1
test=# SELECT * from pgstattuple('foo');
-[ RECORD 1 ]--+--
table_len | 32768
tuple_count| 899
tuple_len | 25172
tuple_percent | 76.82
dead_tuple_count | 1
dead_tuple_len | 28
dead_tuple_percent | 0.09
free_space | 3456
free_percent | 10.55
Stats start over.
The DB should be able to recover cleanly from an immediate shutdown
IMHO. If the stats are no longer there, I would expect autoanalyze to
run and regenerate them. This is the same behaviour as when a new table
is created and populated.
A few questions
1: Is this expected behaviour, or a bug?
2: If not a bug, why does autoanalyze not process these tables. It will
process newly loaded tables when they do not have any stats.
3: What is the best work around for this? When our HA solution triggers
a DB shutdown, we want it to be immediate. Currently we run a manual
analyze post recovery, this is bad for really for large databases.
4: after restart, why does pgstattuple shoe dead_tuple_percent = 8.54,
but after deleting one row, it shows dead_tuple_percent = 0.09?
5: on the missing stats - does this mean my query plans are potentially
bad until the stats are regenerated?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] autovacuum and immediate shutdown issues
On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote: > Brad Nicholson writes: > > If you issue an immediate shutdown to the database, autovacumm will not > > process tables that should be vacuumed until manually re-analyzed. > > AFAICS this is an unsurprising consequence of flushing stats on a crash. > If you don't like it, avoid immediate shutdowns --- they are not > especially good practice in any case. > > > 3: What is the best work around for this? When our HA solution triggers > > a DB shutdown, we want it to be immediate. > > That seems like a fundamentally stupid idea, unless you are unconcerned > with the time and cost of getting the DB running again, which seemingly > you are. > I disagree that this is fundamentally stupid. We are talking about a situation where the server is about to die, HA solution kicks in and moves it to standby. If we wait for a clean shutdown instead, and the server dies before it completes (which is entirely possible), Postgres crashes and the exact same behaviour will happen. It also means that if any server crashes (HA aside, shutdown method aside), the database will come up, but functionality may be impacted until manual intervention. At the very least. shouldn't autoanalyze not correct the lack of statistics? To me, this looks like the database will not come up cleanly after crashing. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] autovacuum and immediate shutdown issues
On Mon, 2009-10-19 at 10:53 -0600, Scott Marlowe wrote: > On Mon, Oct 19, 2009 at 10:44 AM, Tom Lane wrote: > > Brad Nicholson writes: > >> On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote: > >>> That seems like a fundamentally stupid idea, unless you are unconcerned > >>> with the time and cost of getting the DB running again, which seemingly > >>> you are. > > > >> I disagree that this is fundamentally stupid. We are talking about a > >> situation where the server is about to die, HA solution kicks in and > >> moves it to standby. > > > > Moving it to standby immediately is a good idea, but it does not follow > > that you need to hit the DB over the head with a hammer. A fast-mode > > shutdown seems perfectly adequate. If it isn't, you're going to need > > nontrivial recovery effort anyhow. > > All of this is completely besides the point that a database that's > been shutdown immediately / had the power cord yanked comes back up > and doesn't start autovacuuming automatically, which seems a > non-optimal behaviour. It's also not going to endear us very much to the VLDB crowd - it will amounts to a multi-hour crash recovery for those folks while analyze regenerates statistics. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] autovacuum and immediate shutdown issues
On Mon, 2009-10-19 at 11:16 -0600, Scott Marlowe wrote: > On Mon, Oct 19, 2009 at 11:06 AM, Brad Nicholson > wrote: > > On Mon, 2009-10-19 at 10:53 -0600, Scott Marlowe wrote: > >> On Mon, Oct 19, 2009 at 10:44 AM, Tom Lane wrote: > >> > Brad Nicholson writes: > >> >> On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote: > >> >>> That seems like a fundamentally stupid idea, unless you are unconcerned > >> >>> with the time and cost of getting the DB running again, which seemingly > >> >>> you are. > >> > > >> >> I disagree that this is fundamentally stupid. We are talking about a > >> >> situation where the server is about to die, HA solution kicks in and > >> >> moves it to standby. > >> > > >> > Moving it to standby immediately is a good idea, but it does not follow > >> > that you need to hit the DB over the head with a hammer. A fast-mode > >> > shutdown seems perfectly adequate. If it isn't, you're going to need > >> > nontrivial recovery effort anyhow. > >> > >> All of this is completely besides the point that a database that's > >> been shutdown immediately / had the power cord yanked comes back up > >> and doesn't start autovacuuming automatically, which seems a > >> non-optimal behaviour. > > > > It's also not going to endear us very much to the VLDB crowd - it will > > amounts to a multi-hour crash recovery for those folks while analyze > > regenerates statistics. > > But this would be AOK behaviour for small transactional databases? Defiantly not. > Again, besides the point, but important. The real point is a database > that doesn't run autovac after an emergency shutdown is broken by > design, and not just for one use case. This behaviour is also undocumented AFAIK. I would bet that a lot of users would have no idea that they are in this state post crash-recovery. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] autovacuum and immediate shutdown issues
On Mon, 2009-10-19 at 11:35 -0700, Christophe Pettus wrote: > On Oct 19, 2009, at 11:26 AM, Scott Marlowe wrote: > > That could be a pretty bad worst case scenario for certain types of > > tables / usage patterns. > > Given that (presumably) the database server is not failing repeatedly > without some kind of operator notification, isn't it at least somewhat > reasonable to just make "start an vacuum upon recovery from an > immediate shutdown" an operational procedure, rather than something PG > does automatically? > It's not a vacuum you want, it's an analyze. Once the stats are back, autovacuum will vacuum accordingly. autoanalyze will automatically analyze new tables when they don't have stats. It seems logical that it should handle this case where the table also does not have stats. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] autovacuum and immediate shutdown issues
On Mon, 2009-10-19 at 15:01 -0400, Tom Lane wrote: > Brad Nicholson writes: > > autoanalyze will automatically analyze new tables when they don't have > > stats. It seems logical that it should handle this case where the table > > also does not have stats. > > It will autoanalyze once a sufficient number of inserts have occurred. > The effect of a crash is just to reset the inserts-since-last-analyze > counters. You really haven't made the case why that's so awful that > we need to do things that are unpleasant along other dimensions in order > to avoid it. (The only ways of avoiding it that I can see would result > in very significant overhead added to the stats collection mechanism.) What about the question that Scott and I both asked - what about query plans. Will they be affected? If so, you could have a table that does not get updated, that no longer generates relevant query plans, and won't until manual intervention. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] autovacuum and immediate shutdown issues
On Mon, 2009-10-19 at 15:09 -0400, Brad Nicholson wrote: > On Mon, 2009-10-19 at 15:01 -0400, Tom Lane wrote: > > Brad Nicholson writes: > > > autoanalyze will automatically analyze new tables when they don't have > > > stats. It seems logical that it should handle this case where the table > > > also does not have stats. > > > > It will autoanalyze once a sufficient number of inserts have occurred. > > The effect of a crash is just to reset the inserts-since-last-analyze > > counters. You really haven't made the case why that's so awful that > > we need to do things that are unpleasant along other dimensions in order > > to avoid it. (The only ways of avoiding it that I can see would result > > in very significant overhead added to the stats collection mechanism.) > > What about the question that Scott and I both asked - what about query > plans. Will they be affected? > > If so, you could have a table that does not get updated, that no longer > generates relevant query plans, and won't until manual intervention. > Actually, isn't there also a condition (although unlikely to occur) here that could result in transaction wraparound? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] checkpoints/bgwriter tuning verification
On Thu, 2009-10-29 at 14:46 -0400, Vick Khera wrote: > On my primary DB I'm observing random slowness which just doesn't make > sense to me. The I/O system can easily do 40MB/sec writes, but I'm > only seeing a sustained 5MB/sec, even as the application is stalling > waiting on the DB. > > My only guess is that I'm getting hit by checkpoints too often, and > this is causing some delays. I did a lot of reading and came across > this most excelelent article by Greg Smith > (http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm). > Reading that and the Pg manual and looking at the statistics, here is > what I think I need to do. (Greg, do you have a performance tuning > book? If so, I really want to buy it! Your articles are awesome.) > > current settings for checkpoints: > checkpoint_segments = 32 > checkpoint_timeout = 900 > checkpoint_completion_target = 0.9 > log_checkpoints = on > > In my logs I see this pretty consistently during times of slowness: > > Oct 29 14:17:38 d04 postgres[54902]: [10990-1] LOG: checkpoint starting: xlog > Oct 29 14:22:04 d04 postgres[54902]: [10991-1] LOG: checkpoint > complete: wrote 52828 buffers (24.3%); 0 transaction log file(s) > added, 0 removed, 32 recycled; write=265.881 > Oct 29 14:22:57 d04 postgres[54902]: [10992-1] LOG: checkpoint starting: xlog > Oct 29 14:26:56 d04 postgres[54902]: [10993-1] LOG: checkpoint > complete: wrote 52773 buffers (24.3%); 0 transaction log file(s) > added, 0 removed, 32 recycled; write=234.846 > Oct 29 14:28:32 d04 postgres[54902]: [10994-1] LOG: checkpoint starting: xlog > Oct 29 14:33:32 d04 postgres[54902]: [10995-1] LOG: checkpoint > complete: wrote 53807 buffers (24.7%); 0 transaction log file(s) > added, 0 removed, 32 recycled; write=299.170 > > > basically, the next checkpoint starts within a few seconds of the > prior one completing. That's most likely due to having checkpoint_completion target set near the maximum. You are telling it to stretch the checkpoint out as long as possible. > > The stats show this: > > # select * from pg_stat_bgwriter; > -[ RECORD 1 ]--+--- > checkpoints_timed | 3236 > checkpoints_req| 83044 > buffers_checkpoint | 1376460896 > buffers_clean | 59124159 > maxwritten_clean | 304410 > buffers_backend| 285595787 > buffers_alloc | 6643047623 > > Based on Greg's article and the above number showing that most > checkpoints are triggered by running out of WAL segments, I should > increase my checkpoint_buffers. Also, based on the article, I should > increase the bgwriter_lru_maxpages (currently at default 100). > > My plan is to bump checkpoint_segments to 48 and reduce > checkpoint_completion_target to 0.7, That's what I would do. I think you getting hit by checkpointing too frequently or stretching the checkpoints out too long (or a combo of both). I'd also keep bumping checkpoint_segements up until you are checkpointing on the timeout. This will give you a longer window between checkpoints - which gives more time to smooth the the checkpoint out. > and bump the > bgwriter_lru_maxpages to 500. I would probably start by increasing the number of WAL segments and decreasing the checkpoint_completion_timeout and see if that solves the problem. I wouldn't change the bgwriter_lru_maxpages at the same time, and wouldn't necessarily increase it by a factor of 5 when doing so. It does look like you need to increase it though. > Can the checkpoint operation actually cause the DB to stop responding > for a few seconds at a time? That seems to be what I observe. > Sometimes for 5 or more seconds one transaction will just stall. Absolutely. The smoothing is supposed to alleviate that, but I'm not sure how well it works with aggressive activity driven checkpoints like you have. Keep up posted, I'm curious. > Thanks for any ideas. > -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] checkpoints/bgwriter tuning verification
On Fri, 2009-10-30 at 07:15 -0400, Steve Clark wrote: > On 10/29/2009 04:42 PM, Scott Marlowe wrote: > > On Thu, Oct 29, 2009 at 12:46 PM, Vick Khera wrote: > >> On my primary DB I'm observing random slowness which just doesn't make > >> sense to me. The I/O system can easily do 40MB/sec writes, but I'm > >> only seeing a sustained 5MB/sec, even as the application is stalling > >> waiting on the DB. > > > > Just one point on top of everything else you'll hear. 40 MB/sec > > sequential throughput does not equal 40MB/sec random PLUS checkpoint > > throughput. Random access is gonna lower that 40MB/sec way down real > > fast. > > > > First step to speed things up is putting pg_xlog on its own disk(s). > Hi Scott, > > How exactly do you do this? By creating a link to the new location or > is there a config option somewhere that says where the pg_xlog resides? There is an option to do this during initdb. If you want to do it after the DB is created, move the contents of pg_xlog/ (when the DB is shut down) and make a symlink to the new directory. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgcrypto in separate schema
I want to put the functions from pgcrypto into a separate schema, but pgcrypto.sql is explicitly setting the search path to public. Is there a reason it does this that I should be aware of? Is it fine to change that and install the functions in a separate schema? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A thought about other open source projects
Scott Marlowe wrote: As with phrases like, "the quickest way to grill a unicorn steak," that it can be stated in a few words does not make in possible. Exactly. The big issue here is that nobody's saying what kind of app they want to write. Or what sort of performance requirements are tied to that app. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On Mon, 2010-07-12 at 14:57 +0100, Thom Brown wrote: > On 12 July 2010 14:50, Tom Wilcox wrote: > > Hi Thom, > > > > I am performing update statements that are applied to a single table that is > > about 96GB in size. These updates are grouped together in a single > > transaction. This transaction runs until the machine runs out of disk space. As you are updating this table, you are leaving dead tuples behind for each of the updates that are not hot updates and the table is getting bloated. That is most likely why you are running out of disk space. Turning off fsync will not help you with this. What will help you is trying to get the database to use hot updates instead, or batching the updates and letting the table get vacuumed often enough so that the dead tuples can get marked for re-use. Hot updates would be very beneficial, even if batch updating. They will happen if their is no index on the updated column and there is enough space in the physical page to keep the tuple on the same page. You can adjust the fillfactor to try and favour this. You can check if you are doing hot updates by looking at pg_stat_user_tables for the number of hot updates. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?
On 10-07-29 02:57 PM, Tom Lane wrote: Samantha Atkins writes: Why is MySQL so much more popular right now, especially in the OpenSource community? I think it's strictly historical. The mysql bias you see in so many web tools was established in the late 90s, a time when mysql worked reasonably well (at least according to the mysql developers' notion of "reasonably well") whereas postgres was still pretty slow and buggy. It took us a long time to get from the original academically-oriented code to something of real production quality. We're definitely competitive now, but I don't know if we'll ever fully overcome that historical disadvantage. regards, tom lane Postgres also had a reputation of being slow compared to MySQL. This was due to a lot of really poor MySQL vs Postgres benchmarks floating around in the early 2000's. They generally tested stock configurations (MySQL had a less restrictive out of the box configuration) and they tended to test things like how fast can a single client insert/update/delete data from a table. Unsurprisingly, MySQL won, as Postgres imposed all sorts of pesky behind the scenes protection for your data that MySQL didn't worry about. No one really tested it in a way that mattered, which was how the two databases performed under concurrent load, where Postgres won hands down. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?
On 10-07-29 08:54 PM, Greg Smith wrote: Brad Nicholson wrote: Postgres also had a reputation of being slow compared to MySQL. This was due to a lot of really poor MySQL vs Postgres benchmarks floating around in the early 2000's. I think more of those were fair than you're giving them credit for. I'm sure some where, but I recall a lot that were not. The main problems I recall is that they took the stock postgresql.conf (which was far to restrictive) and measured it against a much better MySQL config. They then measured some unrealistic test for most applications and declared MySQL the clear winner for everything and Postgres slow as a dog. It's one thing for database folks to look at that see the problems and/or limitations with those sorts of tests. But a lot of developers were taking these to heart and siding with MySQL and slagging Postgres as being slow - often unjustly. For many common loads, up until PG 8.1 came out--November 8.1--MySQL really was faster. That was the release with the killer read scalability improvements, then 8.3 piled on again with all the write-heavy stuff too. MySQL 4 vs. PG 8.0? MySQL won that fair and square sometimes. Oh, I agree that MySQL was faster for some stuff, but not everything. Back in those days, I routinely saw web sites backed by MySQL 3.x (forget the exact version) grind to an absolute halt under concurrent access due to table level locking in MyISAM. Moving those over to the earlier branches of 7.x improved things drastically. That said, I also saw the opposite, where MySQL was a lot faster than Postgres. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?
On 10-07-29 08:54 PM, Greg Smith wrote: Brad Nicholson wrote: Postgres also had a reputation of being slow compared to MySQL. This was due to a lot of really poor MySQL vs Postgres benchmarks floating around in the early 2000's. I think more of those were fair than you're giving them credit for. For many common loads, up until PG 8.1 came out--November 8.1--MySQL really was faster. That was the release with the killer read scalability improvements, then 8.3 piled on again with all the write-heavy stuff too. MySQL 4 vs. PG 8.0? MySQL won that fair and square sometimes. oh, btw - I'm talking about MySQL 3.x w/MyISAM vs Postgres 7.1/7.2 days. By the time MySQL 4.0/PG 8.0 was around, I was long off MySQL. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] idle process & autovacuum
On 10-07-30 10:52 AM, Gauthier, Dave wrote: Hi: I have a perl/dbi script that's loading a lot of data into a DB (probably about 8,000,000 records). pg_stat_activity shows the script's activity alongside another process with current_query as... autovacuum: ANALYZE public.instance_pins The total instance_pins table cardinality is about 60,000,000. The script inserts records but never updates or deletes anything. And before the script runs, the table is truncated. The script's process seems to be stalled in the meantime. The analyze that is running on that table will block the truncate of that until the analyze completes. You can check pg_locks for to see if there are any ungranted locks relating to the script you are running. This is a new phenomena as of yesterday. The only change is that I added an index on that table. But I created the new index yesterday (create index...) and it completed OK. What does this message mean in that context? Is this indicative of someting being misconfigured in the DB? The message means that the data in instance_pins has changed enough for autovacuum to run an analyze on the table. It's doing this to re-generate statistics for the table for the query planner to use. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
Re: [GENERAL] Need help understanding vacuum verbose output
On 10-08-06 11:45 AM, Gordon Shannon wrote: OK, so if it knew that all vacuumable tuples could be found in 492 pages, and it scanned only those pages, then how could it be that it reports 16558 removable tuples from those 492 pages, when it has already reported earlier that it removed 45878 tuples -- a number we know in fact to be correct? How could both statements be correct? It found 45878 dead tuples in 396 pages for the index authors_archive_pkey. It found 16558 dead tuples in 492 pages for the table authors_archive. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Binary Replication and Slony
On 10-09-20 12:49 PM, Bruce Momjian wrote: John Cheng wrote: Congrats on the 9.0 release of PostgreSQL. One of the features I am really interested in is the built-in binary replication. Our production environment has been using PostgreSQL for more than 5 years (since this project started). We have been using Slony-I as our replication mechanism. I am interested to find out the pros and cons of Slony vs the built-in replication in 9.0. Based on what I understand: * Slony has a higher overhead than the binary replication in 9.0 * When using Slony, schema change must be applied via slonik (in most cases) * Unfortunately, IMO it is easy to make a mistake when applying schema changes in Slony, fortunately, it is easy to drop and recreate the replication sets * Slony is an asynchronous replication mechanism * Slony allows you to replication some tables, while ignoring others * PostgreSQL 9.0 with hot standby& streaming replication is an asynchronous replication mechanism * Overhead is low compared to Slony Are there some cases where it is better to use Slony, for example, when you must specifically exclude tables from replication? I believe our system will be better off using the built-in replication mechanism of 9.0, and I am guessing most people will be in the same boat. You have summarized the differences well. Streaming replication has lower overhread, but doesn't allow per-table granularity or allow replication between different versions of Postgres. Slony will also allow you to: -run custom schema (like extra indexes) on replicas -replicate between different hardware architectures and OS's -run lengthy queries against replicas having to worry about trade offs surrounding query cancellation vs standby lagging. -switch roles of two nodes without entering a degraded state or worrying about STONITH. If you switch roles in a controlled manner, both nodes remain in the cluster. Slony prevents writes against the replica. I do agree that for most, Slony is overkill and streaming replication and hot standby will be the better choice. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "Healing" a table after massive updates
On Thu, 2008-09-11 at 07:01 -0700, Gauthier, Dave wrote: > Hi: > > > > I have a job that loads a large table, but then has to “update” about > half the records for various reasons. My perception of what happens > on update for a particular recors is... > > - a new record will be inserted with the updated value(s). > > - The old record is marked as being obselete. > > - Not sure what happens to index elements that pointed to the original > (now obselete) record. Is it updated to point directly at the newly > inserted record? Or does it use the obselete record as a “link” to > the newly inserted record? Depends on the version of Postgres. Prior to 8.3, the obsolete tuples and index entries are dead. In 8.3, the updates are HOT updates, it will not leave the dead tuples or index. 8.3 might be a big help for you. It could remove the need to vacuum this table entirely. > My concern is that the resulting table is not in optimal shape for > queries. I would like to get rid of the obseleted records (vacuum I > believe) but also “heal” the table in terms of filling in the holes > left where those deleted records used to be (will gather more records > per disk block read if record density on disk is greater). Is there a > way to do this? Regular VACUUM is the correct operation to get rid of the dead tuples. If you want to compact the the table, you either need to use CLUSTER or VACUUM FULL + REINDEX. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cacti + PostgreSQL Graphing
On Mon, 2010-03-01 at 14:43 -0400, Marc G. Fournier wrote: > First, thanks for the pointer to the Check_postgres stuff, definitely alot > of good stuff in there ... but ... that is a totally different beast then > I'm looking at from Cacti (or, so I believe) ... for instance, > Check_posgres:database_size will alert me if I go over X size, which is > useful but with Cacti, I get a graph of waht the size is over a 5 min > period of time, so I can watch growth ... Check out Hyperic. We're currently evaluating it for profiling out systems, and I'm pretty impressed with it so far. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Work Mem Allocation Questions
1: Is the default work_mem pre-allocated to the Postgres processes - or does it get allocated when needed? Say I have work_mem at 30MB - will Postgres allocate that 30MB on connection, or only when it needed by a query? 2: If work_mem is, say, 30MB, and my query needs 10MB - will Postgres allocate all 30MB, or just the 10MB I need? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.10 Changes
Could someone please point me towards the changes for 8.3.10 that was mentioned on -announce this morning? Also, any idea when this is going to be released? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Solid State Drives with PG
On Fri, 2010-03-26 at 15:27 -0400, Merlin Moncure wrote: > On Fri, Mar 26, 2010 at 2:32 PM, Greg Smith wrote: > > Merlin Moncure wrote: > >> > >> So flash isn't yet a general purpose database solution, and wont be until > >> the write performance problem is fixed in a way that doesn't > >> compromise on volatility. > > > > Flash drives that ship with a supercapacitor large enough to ensure orderly > > write cache flushing in the event of power loss seem to be the only solution > > anyone is making progress on for this right now. That would turn them into > > something even better even than the traditional approach of using regular > > disk with a battery-backed write caching controller. Given the relatively > > small write cache involved and the fast write speed, it's certainly feasible > > to just flush at power loss every time rather than what the BBWC products > > do--recover once power comes back. > > right -- unfortunately there is likely going to be a fairly high cost > premium on these devices for a good while yet. right now afaik you > only see this stuff on boutique type devices...yeech. TMS RamSan products have more than adequate capacitor power to handle failure cases. They look like a very solid product. In addition to this, they have internal RAID across the chips to protect against chip failure. Wear-leveling is controlled on the board instead of offloaded to the host. I haven't gotten my hands on one yet, but should at some point in the not to distant future. I'm not sure what the price point is though. But when you factor in the cost of the products they are competing against from a performance perspective, I'd be surprise if they aren't a lot cheaper. Especially when figuring in all the other costs that go along with disk arrays - power, cooling, rack space costs. Depends on the your vantange point I guess. I'm looking at these as potential alternatives to some high end, expensive storage products, not a cheap way to get really fast disk. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Lifekeeper
Hi, Is anyone using Lifekeeper for Linux availability with Postgres? If so, what are your thoughts on it? Work as advertised? Any dangerous gotchas? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_stat_reset not resetting all stats
Is there a reason that pg_stat_reset doesn't reset the stats in pg_stat_bgwriter and pg_stat_database? PG 8.3 (obviously). The call to pg_stat_reset works, as my other stats tables are clear. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] clearing the buffer cache
On Fri, 2009-02-13 at 17:49 +, Sam Mason wrote: > Hi, > > I was doing some performance checks and wondered what the best way to > clear out the shared buffers is? With the recent improvements in buffer > management it appears a simple SELECT * FROM large_table; doesn't help > here! > > I was hoping for a function I could call, or maybe some variable I write > to, that would cause the contents to be invalidated. Restart the database. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Selling an 8.1 to 8.3 upgrade
On Fri, 2009-04-24 at 12:01 -0500, Josh Trutwin wrote: > I've been asked to put together a list of reasons to upgrade a db > from 8.1 to 8.3 and I've looked over the changelog, but they want a > bullet list of 4-5 top things. I'm curious what others would say the > most 5 important updates from 8.1 to 8.3 are. What is the reason that you want to upgrade? Do you have a particular problem you are trying to solve? Are there improvements for developers that you think will be beneficial, or are you simply wanting to upgrade from a 4.5 year old DB platform? You'll probably have the best luck if you sell it based on pains that you are facing, improvements that can be made, or money that can be saved. > I can say "performance improvevents" but I'm not sure how to sell > that better other than listing what's in the release notes. I also > think the autovac was greatly improved in these releases as well? Or > maybe I'm thinking 8.0 to 8.1? Are you experiencing performance issues, or do you have reason to suspect you will? Performance improvements are great if you need them. It can be a hard sell to business folks though when things are performing just fine. > Sorry this is so vague, I'm frustrated with this request as I figured > just the amount of bug-fixes alone would be adequate reasoning. Unfortunately, what seems adequate to us technical folks is seldom is to the business folks. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bloated Table
On Wed, 2009-05-27 at 11:15 -0400, Tom Lane wrote: > =?iso-8859-1?Q?Alexander_Sch=F6cke?= writes: > > I'm using a view > > (http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html) to > > display the bloat (unused disk space) of the tables in a PostgreSQL > > database. > > I wouldn't trust the calculations that view does in the least. > You might look at contrib/pgstattuple if you want numbers that > have some relationship to reality (and are correspondingly more > expensive to get :-() > > regards, tom lane Is the referenced query reliable for even estimating, or is it flat our wrong? Co-workers that were PGCon are saying that this is becoming a popular/accepted way to check for bloated tables. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony v. DBMirror
Peter Wilson wrote: Grant McLean wrote: On Thu, 2005-05-05 at 14:16 -0400, Jeff - wrote: One of the biggest things for Slony is that you can install slony, set things up and it will bring the slave(s) "up to speed". You don't need to do an initial data dump (I think you still need to load the schema on the slaves, but not the data). That is a BIG win for us folks who can't take a machine down while pg_dump runs (and while it is restored on hte slave) Why would you need to take anything down to run pg_dump? And surely bringing a slave up to speed using Slony would be much slower than dump/restore? You don't need to take Postgres down to use pg_dump - it works just fine. The problem with replication (with DBmirror at least) is that you have to create a backup in a very specific order to make sure your new backup ends up in sync and transactions are neither replicated more than once, or not replicated at all: Not the case with Slony. When you subscribe a new set, it it does a copy of the data up to the the point in time when you've issued the subscribe command. While it's copying date, events to the node are being logged. Once the copy is completed, the events are applied, in the proper order, to bring the set up to date. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Question about running "Vacuum" through JDBC
Ying Lu wrote: Greetings, I tried to "Vacuum table" through JDBC. I got the following errors: ERROR: VACUUM cannot run inside a transaction block Does it mean I should only run vacuum through the command line such as "psql> vacuum TableName "? Nope, it means exactly what the error says. You are trying to run vacum inside a transaction, which can't be done. JDBC is likely opening a transaction for you automatically. Stop it from doing this, and you should be able to vacuum. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Slony v. DBMirror
Peter Wilson wrote: Andrew Sullivan wrote: On Thu, May 05, 2005 at 03:35:27PM +0100, Peter Wilson wrote: Looking at Slony now, can someone tell me what the benefits of Slony are over DBmirror? As far as I can see: + both are async Master->multiple slaves + both (I think) can do cascaded replication This isn't really true of dbmirror. You can do it, but if you lose the intermediate system, you also lose the cascaded ones (the downstream one doesn't know about the state of the top-level origin). Slony is designed to cover that case (it was one of several criteria we had to satisfy). That's true. The alternative is to simply replicate from master to both slaves, but this places an additional load on the master. + neither replicate schema changes This is sort of false, too. Slony has a facility for injecting the schema changes at just the right spot in the replication sets, so that you really can push your schema changes through Slony. (This isn't to say you should regularly do this -- it's designed for production systems, where schema changes should be relatively rare.) I got the lack of schema changes from the Slony documentation. I guess there must be some manual intervention though to say 'make these schema chagnes now'? This is what you'd want to look at for schema changes: http://gborg.postgresql.org/project/slony1/genpage.php?slonik_commands#stmt_ddl_script You want to be absolutly, 100% sure that the schema changes are going to work on all nodes before firing them through this, because you only get one shot. The accepted best practice for doing this is to put the schema changes in a transacation block that rolls back, and run them via psql against all the nodes, and make sure nothing breaks. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing column data type on an existing table
Something like BEGIN; LOCK table_name; ALTER TABLE table_name RENAME col_a to col_a_old; ALTER TABLE table_name ADD COLUMN col_a text; UPDATE table_name SET col_a=col_a_old; ALTER TABLE table_name DROP COLUMN col_a_old; COMMIT; If you have any referential integrity on the column, you'll have to mess with that first. Joe Audette wrote: Hi, I have an app that I released with a particular field as varchar 255. Can someone give me a script example I can use to make an upgrade script to change it to text or at least to larger varchar without losing existing data? I support 3 different dbs in my app, Postgre is the newest and least familiar to me but I am trying to learn. Any help much appreciated. Joe Audette joe_audette [at] yahoo dotcom http://www.joeaudette.com http://www.mojoportal.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql books
Gevik babakhani wrote: Dear All, Beside the documentation, which pg book would you recommend? Which one is your personal favorite pg book? Regards Gevik. PostgreSQL by Korry Douglas and Susan Douglas is an excellent book, but a little out of date. I beleive a new edition is in the works. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Postgresql replication
Chris Travers wrote: Carlos Henrique Reimer wrote: I read some documents about replication and realized that if you plan on using asynchronous replication, your application should be designed from the outset with that in mind because asynchronous replication is not something that can be easily “added on” after the fact. Am I right? Depending on your needs, you may find pgpool and Slony to be a workable combination. This is better when you have a lot of reads and only occasional writes. This way writes get redirected back to the master, and read-only transactions get run on the slaves. As long as you don't have any functions that write to the db. pgpool could (and likely would) redirect some of these to the subscriber. Slony would prevent the data from being written (which would prevent the subscriber from being corrupted). -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgresql replication
Bohdan Linda wrote: I would have a slight offtopic question, this is issue only of pgsql or there are some other db solutions which have good performance when doing this kind of replication across the world. It all depends on the quality of the connection Node A to Node B. If connectivity is poor, then it is impossible to have good performance doing anything across that connection. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Email Verfication Regular Expression
Does anybody have regular expression handy to verfiy email addresses? -- Brad Nicholson Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql rising
On Wed, 2006-09-20 at 16:38 -0500, Philip Hallstrom wrote: > > On Wed, Sep 20, 2006 at 10:10:56AM -0500, Tony Caduto wrote: > >> For a high level corp manager all they ever hear about is MS SQL Server, > >> Oracle and DB2, and the more it costs the more they think it is what > >> they need :-) > > > > I think that description is false. At a certain point in the > > management hierarchy, the only way anyone has the ability to evaluate > > something is on the basis of reputation. > > I think that description is false. At a certain point in the management > hierarchy, the only way anyone has the ability to evaluate something is on > the basis of > > - if there is someone they can sue. Good luck attempting to sue Microsoft, Oracle or IBM for deficiencies in their database products. Brad. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How does PG access wal files?
Is it by file name or by inode? Brad. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Stats Collector Won't Start
Can someone please provide a bit of information where the following error is coming from? This is PG 8.1.3 on AIX 5.3 LOG: could not bind socket for statistics collector: Permission denied LOG: disabling statistics collector for lack of working socket What exactly does the PG user not have appropriate permissions to? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stats Collector Won't Start
On Wed, 2006-10-18 at 13:00 -0400, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > Can someone please provide a bit of information where the following > > error is coming from? This is PG 8.1.3 on AIX 5.3 > > > LOG: could not bind socket for statistics collector: Permission denied > > That's bizarre. What error conditions does your man page for bind(2) > document as yielding EACCES? The only one mentioned on my systems is > "protected address", but we aren't requesting a reserved port number ... > > regards, tom lane "The requested address is protected, and the current user does not have permission to access it." -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Stats Collector Won't Start
On Wed, 2006-10-18 at 14:31 -0400, Tom Lane wrote:
> Brad Nicholson <[EMAIL PROTECTED]> writes:
> > On Wed, 2006-10-18 at 13:00 -0400, Tom Lane wrote:
> >> That's bizarre. What error conditions does your man page for bind(2)
> >> document as yielding EACCES? The only one mentioned on my systems is
> >> "protected address", but we aren't requesting a reserved port number ...
>
> > "The requested address is protected, and the current user does not have
> > permission to access it."
>
> That's what the Linux man page says, too. Could getaddrinfo somehow be
> returning bogus data that includes a reserved port number?
>
> Would you try strace'ing postmaster start to see what gets passed to the
> socket() and bind() calls just before this message comes out?
Here is the relative output from truss.
open("/etc/hosts", O_RDONLY)= 7
kioctl(7, 22528, 0x, 0x) Err#25 ENOTTY
kfcntl(7, F_SETFD, 0x0001) = 0
kioctl(7, 22528, 0x, 0x) Err#25 ENOTTY
kread(7, " # @ ( # ) 4 7\t 1 . 1".., 4096)= 2770
kread(7, " # @ ( # ) 4 7\t 1 . 1".., 4096)= 0
close(7)= 0
socket(2, 2, 0) = 7
bind(7, 0x0001100E3BF0, 16) Err#13 EACCES
kwrite(2, " 2 0 0 6 - 1 0 -", 8)= 8
kwrite(2, " 1 8 1 9 : 0 1", 8)= 8
kwrite(2, " : 0 1 . 7 2 5 ", 8)= 8
kwrite(2, " U T C [ 2 8 2", 8)= 8
kwrite(2, " 9 3 2 ]", 8)= 8
kwrite(2, " L O G : c o", 8)= 8
kwrite(2, " u l d n o t ", 8)= 8
kwrite(2, " b i n d s o c", 8)= 8
kwrite(2, " k e t f o r ", 8)= 8
kwrite(2, " s t a t i s t i", 8)= 8
kwrite(2, " c s c o l l e", 8)= 8
kwrite(2, " c t o r : P e", 8)= 8
kwrite(2, " r m i s s i o n", 8)= 8
kwrite(2, " d e n i e d\n", 8)= 8
close(7)= 0
kwrite(2, " 2 0 0 6 - 1 0 -", 8)= 8
kwrite(2, " 1 8 1 9 : 0 1", 8)= 8
kwrite(2, " : 0 1 . 7 2 8 ", 8)= 8
kwrite(2, " U T C [ 2 8 2", 8)= 8
kwrite(2, " 9 3 2 ]", 8)= 8
kwrite(2, " L O G : d i", 8)= 8
kwrite(2, " s a b l i n g ", 8)= 8
kwrite(2, " s t a t i s t i", 8)= 8
kwrite(2, " c s c o l l e", 8)= 8
kwrite(2, " c t o r f o r", 8)= 8
kwrite(2, " l a c k o f", 8)= 8
kwrite(2, " w o r k i n g", 8)= 8
kwrite(2, " s o c k e t\n", 8)= 8
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Stats Collector Won't Start
On Wed, 2006-10-18 at 15:59 -0400, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > On Wed, 2006-10-18 at 14:31 -0400, Tom Lane wrote: > >> Would you try strace'ing postmaster start to see what gets passed to the > >> socket() and bind() calls just before this message comes out? > > > Here is the relative output from truss. > > > socket(2, 2, 0) = 7 > > bind(7, 0x0001100E3BF0, 16) Err#13 EACCES > > Well, that's pretty odd --- I'd have expected to see something involving > 127.0.0.1 (ie, 7F01 in one byte order or another). Does your > /etc/hosts file map "localhost" to something other than 127.0.0.1? Nope, it maps localhost to 127.0.0.1 > Also, do regular connections to this postmaster work across TCP/IP? > If getaddrinfo() is broken I'd expect there to be problems binding > to the postmaster's listen socket too ... Yes, TCP/IP connections work fine. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] log_duration and JDBC V3 Preparded Statements
I'm wondering what that status of the fix for this is. Looking at the archives, it looks like Bruce had a patch http://beta.linuxports.com/pgsql-jdbc/2006-08/msg00036.php I don't see anything in the release notes though. What's the status on this? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] log_duration and JDBC V3 Preparded Statements
On Mon, 2006-10-30 at 10:14 -0500, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > I'm wondering what that status of the fix for this is. > > AFAIK it all works ... grab beta2 and try it. > Will do. Is this strictly an 8.2 patch, or will it be back-ported to 8.1 and 7.4? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] log_duration and JDBC V3 Preparded Statements
On Mon, 2006-10-30 at 10:27 -0500, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > Will do. Is this strictly an 8.2 patch, or will it be back-ported to > > 8.1 and 7.4? > > We aren't going to change the behavior of logging that much in existing > releases --- or were you just thinking of that core-dump condition in > 8.1? That's fixed in 8.1.5. Nope, I was specifically thinking of the issue with prepared statements with JDBC V3 not logging their duration. Thanks. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Request for replication advice
On Sat, 2006-11-11 at 06:34 +1100, Brendan Jurd wrote: > So, my question for the list is: is Slony + log shipping the direction > I should be investigating, or is there something else out that I ought > to consider? My understanding of WAL-based replication is that the This is certainly the direction to look at. The speed of which replication keeps on on a log shipped node is going to depend on the frequency of inserts/updates/deletes, you class of hardware and network connectivity. You also need to be absolutely certain that long running queries and 'idle in transaction' transactions are minimized as much as possible (or completely eliminated). Anything that prevents vacuums from doing there job properly is going to hurt a slony replica. Oh, and regarding log shipping itself, I recommend waiting for the release of 1.1.6 or 1.2.1, there is a fairly serious bug in the current version that causes log shipping to fall over if you have more than 2 nodes in your config (not just log shipped nodes). If you have more questions, please sign up for the Slony list. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Request for replication advice
On Fri, 2006-11-10 at 15:07 -0500, Tom Lane wrote: > "Brendan Jurd" <[EMAIL PROTECTED]> writes: > > So, my question for the list is: is Slony + log shipping the direction > > I should be investigating, or is there something else out that I ought > > to consider? > > Those are two different methods: you'd use one or the other, not both. Slony has its own log shipping, I think that was what he was referring to. > Slony-I is much the more battle-tested of the two at the moment. In > theory WAL log shipping should be higher performance for heavy-update > scenarios, but its latency is variable (low update rate = higher > latency), and not easy to put a bound on pre-8.2. I'm not entirely sure how battle tested the Slony log shipping stuff actually is. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Request for replication advice
On Fri, 2006-11-10 at 15:16 -0500, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > On Fri, 2006-11-10 at 15:07 -0500, Tom Lane wrote: > >> Those are two different methods: you'd use one or the other, not both. > > > Slony has its own log shipping, I think that was what he was referring > > to. > > Oh, OK, I was thinking of the trigger-driven version. Same thing, actually. There's an option that tells the slon daemon to write the data syncs to files instead of applying directly to a subscriber. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Unrecognized time zone name error.
This seems odd. Any idea what's going on here? template1=# SET TimeZone TO 'GMT'; ERROR: unrecognized time zone name: "GMT" template1=# SELECT version(); version - PostgreSQL 8.1.5 on powerpc-ibm-aix5.3.0.0, compiled by GCC gcc (GCC) 3.3.2 (1 row) -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Unrecognized time zone name error.
On Tue, 2006-12-12 at 11:13 -0500, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > This seems odd. Any idea what's going on here? > > > template1=# SET TimeZone TO 'GMT'; > > ERROR: unrecognized time zone name: "GMT" > > Worksforme. Perhaps you are missing the /usr/share/pgsql/timezone/ > directory (your path might vary)? > > regards, tom lane So it's documented in the archives, the GMT file was missing from the directory you mentioned. Thx. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recording insert, updates, and deletes
On Wed, 2007-01-10 at 16:51 +0100, Andy Dale wrote: > If anyone can help or offer advice on how to achieve my objective it > would be greatly appreciated. Slony log shipping will do this -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_dump and buffer usage
Question about pg_dump and Postgres 8.1. Assuming you've let you buffers settle, and then you dump your database. Will this clobber your shared buffers like a seq scan against a large table will? -- Brad Nicholson 416-673-4106[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres and geographically diverse replication
On Wed, 2007-04-18 at 16:43 -0400, Bill Moran wrote: > In response to "Drew Myers" <[EMAIL PROTECTED]>: > > > > I've been given a task to build a couple of geographically separate > > servers, which are capable of replicating data between each other. > > > > I've surfed through various google results, and most of what I've found > > seems to be a bit dated, so I thought I'd pose my question here, perhaps > > for more detailed and more up-to-date info. > > > > Is this normally done in a push/pull scenario within the postgres > > installations themselves, or is additional software required? What are > > the various replication capabilities? > > > > I apologize for the general nature of my questions, I'm new to postgres > > and to geographically separate replication. Any tips, books, whitepapers > > or other resources you might be able to point me to is most appreciated. > > Generally speaking, when you're talking geographically separate, Slony > is your best bet. We're using it to maintain data on opposites sides of > the US with good success. Successfully using slony over a wide area is going to depend on how much data you are replicating, how fast the connection between the two sites is, and how stable the connection between the two sites is. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Regarding WAL
On Tue, 2007-04-24 at 09:02 +0530, Mageshwaran wrote: > Hi , > > I want to do replication using WAL , please tell the methods by which > log shipping is done ie moving the wal files to slaves and executing it. http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Dynamically Allocated System Resources
Running PG8.1 - will it recognize CPU and memory that are added dynamically to the server when the postmaster is running? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dangers of fsync = off
On Wed, 2007-05-09 at 08:26 -0600, Scott Ribe wrote: > > I still wouldn't trust Slony with fsync off. Another scenario would be > > the Slony trigger writes a change to the Slony DB, the db crashes before > > it gets committed to disk. When the DB is started, no errors prevent > > startup, but that transaction is lost. > > I'm not sure, but I think the questioner was proposing a policy of "if it > crashes, we go to the standby, no attempt at recovery, ever", and I think > that would be safe. Just make sure that there is no way that the database would come back up after the crash. If it did, the slons could pick up and cause you trouble. If you disable all start up scripts, and operate under the assumption that crash=corruption=failover to Slony replica, you should be okay. You will lose whatever transactions were not replicated to the subscriber, but that's inherent to async replication. > And, personally, given my experience with pg, I think that's reasonable. > Because the day I see pg crash I'm going to assume I have a hardware problem > ;-) If you care about your data, leave fsync on. Period. If you can accept the potential for data loss, and you've proven that there is a worthwhile performance benefit from turning it off (which there may not be), and you gotten your boss/clients/stakeholders to sign off (preferably in writing) that data loss is acceptable if the db crashes, then go ahead and turn it off. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] how to capture query?
To log all queries - in your postgresql.conf file, set the following: log_statement = true Mark wrote: Hi, I'm getting some errors in log file saying "invalid character at position #20..." I know that this is most likely that query is wrong. Is it possible to capture all queries that get send or at least the invalid queries? I'm using postgresql 7.4.3 on Red Hat 9 Thanks, Mark __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Brad Nicholson 416-673-4106[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Syncing Databases Weekly
Joshua D. Drake wrote: Benjamin Arai wrote: Hi, I am familiar with applications like slony for replications, I am looking for a technique for syncing a very large database every week for backup and redundancy purposes. More specifically, I do all my writes to a main server once a week, at the end of the updates I would like to automatically copy all of the data to a second server as a live backup in case the main system ever goes down. You could use batch replication via Mammoth Replicator or PITR. Slony's log shipping is another option. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Temporal Databases
Simon Riggs wrote: A much easier way is to start a serialized transaction every 10 minutes and leave the transaction idle-in-transaction. If you decide you really need to you can start requesting data through that transaction, since it can "see back in time" and you already know what the snapshot time is (if you record it). As time moves on you abort and start new transactions... but be careful that this can effect performance in other ways. We're currently prototyping a system (still very much in it's infancy) that uses the Slony-I shipping mechanism to build an off line temporal system for point in time reporting purposes. The idea being that the log shipping files will contain only the committed inserts, updates and deletes. Those log files are then applied to an off line system which has a trigger defined on each table that re-write the statements, based on the type of statement, into a temporally sensitive format. If you want to get an exact point in time snapshot with this approach, you are going to have to have timestamps on all table in your source database that contain the exact time of the statement table. Otherwise, a best guess (based on the time the slony sync was generated) is the closest that you will be able to come. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication & web apps
Jeff Amiel wrote: > There are other techniques to balance the load of the database calls so > that some go to one box and some to others, yet keep the data in synch... > Continuent makes a commercial p/cluster product as well as an open > source product called Sequoia that sit in the JDBC layer and direct > traffic and control the load balancing. pgpool allows you to load balance queries between two servers. While it doesn't solve the issue of trying to read a row that hasn't been replicated, it does offer you a bit of control that may be of use to you. Any statement that is in a transaction will automatically go to the master DB. If some queries are sensitive to this issue, and some aren't, then you might be able to make pgpool work for you by wrapping the sensitive ones in a transaction. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] log_duration - exactly what does it measure?
In version 7.4, could someone please tell me the start and end points are for measuring the execution time of a query when log_duration is enabled? I need to know exactly what gets measured in this time. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Strange Behavior with Serializable Transcations
I'm seeing something fairly unintuitive about serializable transactions. Taking the following test case: CREATE TABLE foo (id integer); t1 t2 -- BEGIN; -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO foo (id) -- VALUES (1); -- -- SELECT * from foo; The select in t2 sees the row inserted from t1, which it shouldn't. http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html "When a transaction is on the serializable level, a SELECT query sees only data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions." Now, if I modify the case as such: t1 t2 -- BEGIN; -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- SELECT * from foo; INSERT INTO foo (id) -- VALUES (1); -- -- SELECT * from foo; The select in t2 (the last one, obviously) does not see the insert from t1. What's up? -- Brad Nicholson 416-673-4106[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Using currval() in an insert statement...
On Tue, 2006-07-25 at 10:45 -0700, Redefined Horizons wrote:
> I'm having trouble figuring out how to use the currval() function for
> sequences in an INSERT statement. I did some searching online, but
> couldn't find what I was looking for.
>
> I have two Schemas in my Database:
>
> metadata
> geometry
>
> I have a table named "metadata_geometries" in the metadata schema.
> I have a table named "geometry_polaris_numbers" in the geometry schema.
>
> Both tables have bigserial columns set up as primary keys.
>
> There is a one-to-one relationship between records in the
> metadata_geometries table and the geometry.polaris_numbers table.
>
> Here is what I'd like to do in my query:
>
> Insert a value into the "metadata.metadata_geometries" table.
>
> Select the value just inserted in to the primary key column,
> "pk_dbuid" of that table, and insert it into the
> "geometry.polaris_numbers" table in the "metadata" column.
>
> I had the query set up like this:
>
> INSERT INTO metadata.metadata_geometries (value)
> VALUES ('This is a test.');
>
> INSERT INTO geometry.polaris_numbers (pnuid, number_of_digits, error,
> value, metadata)
> VALUES (2305, 7, 100, 1000,
> currval(metadata.metadata_geometries.pk_dbuid);
>
> However, when I try and execute the query I get the following error message:
>
> ERROR: Missing FROM-clause for table "metadata_geometries"
You need to give currval the name of the sequence that is being
incremented.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Best approach for a "gap-less" sequence
On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote: > Jorge Godoy wrote: > > > Chris <[EMAIL PROTECTED]> writes: > > > > > >>I'm not sure what type of lock you'd need to make sure no other transactions > >>updated the table (see > >>http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in > >>theory" > >>something like this should work: > >> > >>begin; > >>select id from table order by id desc limit 1; > >>insert into table (id, blah) values (id+1, 'blah'); > >>commit; > > > > > > This is part of the solution, yes. But I would still need locking this > > table > > so that no other concurrent transaction gets another "id". I don't want to > > lock the main table -- > > Wouldn't SELECT ... FOR UPDATE give you the row lock you need without > locking the table? Nope, concurrent transactions won't work. Let current max id = x Transaction 1 (t1) does a select max(id) for update, gets a lock on the last tuple at the time of the select, and gets x as a value for max id Transaction 2 (t2) does a select max(id) for update, has to wait for t1 to release its lock. t1 inserts (x+1) as the new max id of the table. t1 releases its lock t2 is granted the lock on the tuple it has been waiting for, which contains the max id of x t2 tries to insert a value of x+1, insert fails (if it doesn't, you really want to have a close look at your constraints :-) Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best approach for a "gap-less" sequence
On Thu, 2006-08-17 at 12:12 -0400, Merlin Moncure wrote: > On 8/17/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote: > > On 8/17/06, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > > On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote: > > > > -- then create a function to retrieve the values: > > > > CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$ > > > > DECLARE > > > >n integer; > > > > BEGIN > > > >SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t > > > > FOR UPDATE; > > > >UPDATE gapless_seq SET gapless_value = n WHERE gseq_name = t; > > > >RETURN n; > > > > END; > > > > $$ STABLE LANGUAGE PLpgsql; > > > > > > > > > > the problem here is if you have two concurrent transactions which call > > > this funtion, it is possible for them both to return the same sequence > > > number in read comitted mode. Using this funtion outside of > > > transactions is no different that using a sequence except that it is > > > slower. > > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE; > > The first-to-obtain the gapless sequence transaction will establish > > a lock onthe "tax_id" row. The other transaction will block until > > the first transaction finishes (and the row is updated) and will > > establish the row lock on it. > > yes, you are right...i didnt think the problem through properly. Lets just hope the performance on a concurrent system is not a requirement of such a system... Brad. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best approach for a "gap-less" sequence
On Thu, 2006-08-17 at 16:07 -0400, Merlin Moncure wrote: > On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote: > > > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE; > > > > The first-to-obtain the gapless sequence transaction will establish > > > > a lock onthe "tax_id" row. The other transaction will block until > > > > the first transaction finishes (and the row is updated) and will > > > > establish the row lock on it. > > > > > > yes, you are right...i didnt think the problem through properly. > > > > Lets just hope the performance on a concurrent system is not a > > requirement of such a system... > > > > right, if the transations are long running, there is a big problem as > they are serialized around access to the sequence. however this is > better than the control record approach because control record have > problems with mvcc bloat. concurrent performance will of course be > awful. This effect will be magnified if there other long running transactions (pg_dump and pre 8.2 vacuum, I'm looking at you), as the dead tuples from the updates will start to pile up, and reads to the table slow down, locks persist for longer... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best approach for a "gap-less" sequence
On Thu, 2006-08-17 at 15:13 -0500, Scott Marlowe wrote: > On Thu, 2006-08-17 at 15:07, Merlin Moncure wrote: > > On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote: > > > > > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE; > > > > > The first-to-obtain the gapless sequence transaction will establish > > > > > a lock onthe "tax_id" row. The other transaction will block until > > > > > the first transaction finishes (and the row is updated) and will > > > > > establish the row lock on it. > > > > > > > > yes, you are right...i didnt think the problem through properly. > > > > > > Lets just hope the performance on a concurrent system is not a > > > requirement of such a system... > > > > > > > right, if the transations are long running, there is a big problem as > > they are serialized around access to the sequence. however this is > > better than the control record approach because control record have > > problems with mvcc bloat. concurrent performance will of course be > > awful. > > > > a good compomise in some cases is to save off canceled transactions > > ids' in a free list you would still have to deal with transactions > > that were not gracefully cancelled though. > > Is it not possible in some circumstances to create the invoice first, > THEN assign a sequential ID after creation? If speed of access was an issue, that's how I'd look at doing it - batch assign them after the fact. Brad. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
