Re: [GENERAL] Comparing txid_current() to xmin
On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote: I am trying to make a trigger that updates a row once and only once per transaction (even if this trigger gets fired multiple times). The general idea is that for a user we have a version number. When we modify the user's data, the version number is incremented then set on the object. We only need to increment the version number once. I am thinking about doing something like: update user set version=version+1 where txid_current() != xmin and user_id = 352395; So I guess my questions are: How dirty is this? Will I run into issues? It won't work in the presenence of subtransactions and is a bit more complicated if you inserted the row in the same transaction. -- 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] Memory issue on FreeBSD
Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4096M Total, 828K Used, 4095M Free For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The data + indexes are about 240GB on disk. This server only runs postgres aside from the basic system processes. Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free Swap: 4096M Total, 272K Used, 4096M Free I agree with the conclusion that the shared memory segments are confusing the output of top. There are no memory leaks, and FreeBSD doesn't lose any memory. There are some scripts floating around that read values from sysctl vm.stats.vm and format them nicely to tell you how much memory is used up and free. Try the one referenced here: http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/ - Achilleas Mantzios IT DEPT
Re: [GENERAL] Memory issue on FreeBSD
Hey, this is really cool. I directly tried the script and there's a line from the output that caught my eye: mem_gap_vm: + 8812892160 ( 8404MB) [ 26%] Memory gap: UNKNOWN is this the shared buffers? I guess so, but I want to confirm my guess ... Frank Am 2012-11-07 09:26, schrieb Achilleas Mantzios: Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4096M Total, 828K Used, 4095M Free For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The data + indexes are about 240GB on disk. This server only runs postgres aside from the basic system processes. Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free Swap: 4096M Total, 272K Used, 4096M Free I agree with the conclusion that the shared memory segments are confusing the output of top. There are no memory leaks, and FreeBSD doesn't lose any memory. There are some scripts floating around that read values from sysctl vm.stats.vm and format them nicely to tell you how much memory is used up and free. Try the one referenced here: http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/ - Achilleas Mantzios IT DEPT -- Frank BRONIEWSKI METRICO s.à r.l. géomètres technologies d'information géographique rue des Romains 36 L-5433 NIEDERDONVEN tél.: +352 26 74 94 - 28 fax.: +352 26 74 94 99 http://www.metrico.lu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to evaluate explain analyze correctly soon after explain for the same statement ?
Hi all: I have one question about the cache clearing. If I use the following soon after database startup(or first time I use it): postgres=# explain analyze select id,deptno from gaotab where id=200; QUERY PLAN --- Index Only Scan using idx_id_dept on gaotab (cost=0.00..8.27 rows=1 width=8) ( actual time=30.912..30.915 rows=1 loops=1) Index Cond: (id = 200) Heap Fetches: 1 Total runtime: 47.390 ms (4 rows) postgres=# The result is: the above explain analyze got a total runtime of 47 ms. But If I restart the database again, and then execute the following: postgres=# explain select id,deptno from gaotab where id=200; QUERY PLAN --- Index Only Scan using idx_id_dept on gaotab (cost=0.00..8.27 rows=1 width=8) Index Cond: (id = 200) (2 rows) postgres=# explain analyze select id,deptno from gaotab where id=200; QUERY PLAN - Index Only Scan using idx_id_dept on gaotab (cost=0.00..8.27 rows=1 width=8) ( actual time=0.052..0.053 rows=1 loops=1) Index Cond: (id = 200) Heap Fetches: 1 Total runtime: 0.074 ms (4 rows) This time I got the total runtime of 0.074ms, obviously the explain analyze benefit from the explain statement. It might not be a big problem in a small system. But when in a production environment, When I want to use explain and then , soon use explain analyze for the same statement, How can I avoid the influence of cache and get the right answer for evaluating purpose? It is not a good idea to restart the database again and again I think. I wonder is there any method of clearing cache or even clear only a particular part of the cache? In my test environment, I can get the following: postgres=# show seq_page_cost; seq_page_cost --- 1 (1 row) postgres=# show cpu_tuple_cost; cpu_tuple_cost 0.01 (1 row) postgres=# show cpu_operator_cost; cpu_operator_cost --- 0.0025 (1 row) And my table is like that： postgres=# analyze; ANALYZE postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid; relpages | reltuples | relfilenode | reltype | typname --+---+-+-+- 7 | 1000 | 16396 | 16386 | gaotab (1 row) Thanks in advance.
Re: [GENERAL] Memory issue on FreeBSD
On Τετ 07 Νοε 2012 09:42:47 Frank Broniewski wrote:
Hey, this is really cool. I directly tried the script and there's a line
from the output that caught my eye:
mem_gap_vm: + 8812892160 ( 8404MB) [ 26%] Memory gap: UNKNOWN
is this the shared buffers? I guess so, but I want to confirm my guess ...
Hmm, that would be ideal, (from an understanding perspective) but at least in
my system (FreeBSD-8.3), no.
psql -q -t -c show shared_buffers | grep -v -e '^$' | awk '{print $1}'
3840MB
SYSTEM MEMORY INFORMATION:
mem_gap_vm: +996843520 (950MB) [ 5%] Memory gap: UNKNOWN
$mem_gap_vm = $mem_all - ($mem_wire + $mem_active + $mem_inactive + $mem_cache
+ $mem_free);
mem_all is some rounded and more rationalized version less than hw.physmem :
$mem_all = $sysctl-{vm.stats.vm.v_page_count} * $sysctl-{hw.pagesize};
Anyway, this is not so postgresql related at the moment. The correct thing to
do (since you run production servers on FreeBSD) is to post to the relevant
FreeBSD list and/or forum. freebsd-questi...@freebsd.org and
freebsd-sta...@freebsd.org would be a good start.
Also the forums : http://forums.freebsd.org/forumdisplay.php?f=3
Only after gathering substantial info from there, would it make sense to come
back here and maybe ask more questions.
And since we are observing different percentages of gaps (mine is 5%, yours is
26%), i think maybe you should look into it on the FreeBSD camp.
Please drop the link to the relevant thread there, if you decide to do so.
I would like to follow this.
Thanx!
Frank
Am 2012-11-07 09:26, schrieb Achilleas Mantzios:
Vick,
fantastic script, thanx! FreeBSD sysctl system is awesome!
On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote:
On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote:
and this is after a few hours of running:
Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
Swap: 4096M Total, 828K Used, 4095M Free
For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with
Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The
data + indexes are about 240GB on disk. This server only runs postgres
aside from the basic system processes.
Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free
Swap: 4096M Total, 272K Used, 4096M Free
I agree with the conclusion that the shared memory segments are confusing
the output of top. There are no memory leaks, and FreeBSD doesn't lose
any memory.
There are some scripts floating around that read values from sysctl
vm.stats.vm and format them nicely to tell you how much memory is used up
and free. Try the one referenced here:
http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/
-
Achilleas Mantzios
IT DEPT
-
Achilleas Mantzios
IT DEPT
Re: [GENERAL] How to evaluate explain analyze correctly soon after explain for the same statement ?
高健 wrote: I have one question about the cache clearing. If I use the following soon after database startup(or first time I use it): postgres=# explain analyze select id,deptno from gaotab where id=200; The result is: the above explain analyze got a total runtime of 47 ms. But If I restart the database again, and then execute the following: postgres=# explain select id,deptno from gaotab where id=200; postgres=# explain analyze select id,deptno from gaotab where id=200; This time I got the total runtime of 0.074ms, obviously the explain analyze benefit from the explain statement. The EXPLAIN will not have a noticable effect on the performance of the EXPLAIN ANALYZE. If you actually restarted the PostgreSQL server like you said, then the difference must be that the file is cached in the file system cache. You can verify that be omitting the EXPLAIN in the second run. It might not be a big problem in a small system. But when in a production environment, When I want to use explain and then , soon use explain analyze for the same statement, How can I avoid the influence of cache and get the right answer for evaluating purpose? It is not a good idea to restart the database again and again I think. I wonder is there any method of clearing cache or even clear only a particular part of the cache? The only way of clearing the cache in database shared memory is to restart the server. That's just a simple pg_ctl restart -m fast -D datadir. I think that's simple enough for tests. You should also empty the file system cache. On recent Linux systems that would be sync; echo 3 /proc/sys/vm/drop_caches You'd have to consult the documentation for other OSs. Yours, Laurenz Albe -- 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] How to evaluate explain analyze correctly soon after explain for the same statement ?
=?UTF-8?B?6auY5YGl?= luckyjack...@gmail.com writes: It might not be a big problem in a small system. But when in a production environment, When I want to use explain and then , soon use explain analyze for the same statement, How can I avoid the influence of cache and get the right answer for evaluating purpose? I think this question is based on a false premise. Why do you feel that the behavior with cold caches is the right answer, and not the behavior with warm caches? A short-duration query like this one is not going to be interesting at all for performance unless it's executed quite a lot, and if it's executed quite a lot then the warm-cache result ought to be the more representative one. In general, trying to tune for cold-cache cases seems backwards to me. It's much more productive to try to ensure that the caches are warm. regards, tom lane -- 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] Unexpectedly high disk space usage
On Tue, Nov 6, 2012 at 11:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@gmail.com writes: On Tue, Nov 6, 2012 at 10:49 AM, Lists li...@benjamindsmith.com wrote: I followed your example, the result is at the bottom. Based on this it would seem that there are 3-4 databases that seem to be the culprit. How could I get more depth/detail on what specifically is the problem? If you have installed the contrib modules (oid2name specifically), you can use that to get the name of the bloated database: oid2name | fgrep 607471 Or, if you didn't install contrib, try select datname from pg_database where oid = 607471 Thanks, I knew there had to be a more direct way to do that. If the name of the database doesn't give you any insight, then look for large files in the directory base/607471 that whose names all start with the same digits and use oid2name to get the names of the relations for those files. oid2name -d name of database -o base name of large files For this you can try select relname from pg_class where relfilenode = whatever Or let the database do the work: select relname, pg_relation_size(oid) from pg_class order by 2 desc; Ben described using something like this method originally and not finding the space, so I wanted to work backwards from certain knowledge of where the OS says the space is being used. But now I think maybe his scripts to aggregate table sizes over all databases (and also his script to load pg_dumps of those databases into a new cluster) are accidentally omitting some databases--the largest ones. Is there a simple query for a super-user to get a list of all relation sizes over all databases cluster-wide? If \l+ can get the size of databases other than the one currently connected to, maybe there is a way to extend that to tables in those other databases. It would at least be nice to be able to get the sizes of all databases. Since '\l+' doesn't sort by size and I don't know how to make it do so, I pulled the query from psql source code and modified it: SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as Size FROM pg_catalog.pg_database d order by CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END desc nulls first; (And discovered a long forgotten unused database I had sitting around taking up space) Ben, did you ever figure out where the space was going? Cheers, Jeff -- 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] Postgres no longer starts
came across this problem myself. turned out after much playing around that it was a change to the pg_hba.conf was a syntax error in the all all posgres trust sameuser line. deleted it and postgres fired up from /etc/init.d or as a service. just my very late twopenneth -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-no-longer-starts-tp5710560p5731011.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Memory issue on FreeBSD
Vick, fantastic script, thanx! FreeBSD sysctl system is awesome! On Τρι 06 Νοε 2012 14:33:43 Vick Khera wrote: On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote: and this is after a few hours of running: Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free Swap: 4096M Total, 828K Used, 4095M Free For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7. The data + indexes are about 240GB on disk. This server only runs postgres aside from the basic system processes. Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free Swap: 4096M Total, 272K Used, 4096M Free I agree with the conclusion that the shared memory segments are confusing the output of top. There are no memory leaks, and FreeBSD doesn't lose any memory. There are some scripts floating around that read values from sysctl vm.stats.vm and format them nicely to tell you how much memory is used up and free. Try the one referenced here: http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/ - Achilleas Mantzios IT DEPT
Re: [GENERAL] Parallel Insert and Delete operation
Ramkumar Yelai wrote: [is worried that a database might become inconsistent if conflicting INSERTs and DELETEs occur] @Albe - I got you first point. The second point is little skeptical because postgres could have been avoided this lock by using MVCC. Please correct me if I am wrong? Which lock could have been avoided? PostgreSQL locks rows when the data change. That has little to do with MVCC. If you INSERT into a table that has a foreign key, the referenced row in the referenced table gets a SHARE lock that conflicts with the EXCLUSIVE lock required for a DELETE. So they cannot execute concurrently. Yours, Laurenz Albe Thanks very much Albe. I am not aware of that, delete will lock the table. -- 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] How to verify pg_dump files
On 07/11/2012 13:01, Gary wrote: Can anyone suggest how I could verify that the files created by pg_dump are okay? They are being created for backup purposes, and the last thing I want to do is find out that the backups themselves are in some way corrupt. I know I can check the output of the command itself, but what if.. I don't know... if there are problems with the disc it writes to, or something like that. Is there any way to check whether the output file is valid in the sense that it is complete and syntactically correct? Reload it and test your application against it? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Plug-pull testing worked, diskchecker.pl failed
On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith g...@2ndquadrant.com wrote: In general, through, diskchecker.pl is the more sensitive test. If it fails, storage is unreliable for PostgreSQL, period. It's good that you've followed up by confirming the real database corruption implied by that is also visible. In general, though, that's not needed. Diskchecker says the drive is bad, you're done--don't put a database on it. Doing the database level tests is more for finding false positives: where diskchecker says the drive is OK, but perhaps there is a filesystem problem that makes it unreliable, one that it doesn't test for. Thanks. That's the conclusion we were coming to too, though all I've seen is lost transactions and not any other form of damage. What SSD are you using? The Intel 320 and 710 series models are the only SATA-connected drives still on the market I know of that pass a serious test. The other good models are direct PCI-E storage units, like the FusionIO drives. I don't have the specs to hand, but one of them is a Kingston drive. Our local supplier is out of 320 series drives, so we were looking for others; will check out the 710s. It's crazy that so few drives can actually be trusted. Yes. Welcome to our craziness! -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] 9.1 to 9.2 requires a dump/reload?
On Tue, Oct 23, 2012 at 09:41:20AM -0400, Nikolas Everett wrote: On Mon, Oct 22, 2012 at 6:17 PM, Alan Hodgson ahodg...@simkin.ca wrote: On Monday, October 22, 2012 05:55:07 PM Nikolas Everett wrote: I see that pg_upgrade is an option. Having never used how long should I expect pg_upgrade to take? Obviously we'll measure it in our environment, but it'd be nice to have a ballpark figure. pg_upgrade using hard links should only take a minute or 2. You'll also need to shuffle around packages and services and config files. The slowest part for any decent sized database will be doing an analyze after bringing it up under 9.2, though. So however long that takes for your db, plus maybe 10-15 minutes or so, if you've practiced. Yikes! Analyze will certainly take the longest time - we'll have to build some kind of strategy for which tables to analyze first and how many to analyze at once. pg_upgrade 9.2 creates a script that incrementally produces more accurate statistics, which should help. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] help with upgrade from 9.1 to 9.2
On Thu, Oct 25, 2012 at 02:39:09AM -0200, Aníbal Pacheco wrote: I could after some work, what I want to ask now is this: In the middle of the pg_restore process I had to stop it (^Z) and remove one problematic and not needed database from the generated pg_upgrade_dump_db.sql file and then continue the process with fg, of course it failed BUT: I started the new server and checked for my only needed database and it seems to be ok, can I be sure that this database was restored correctly? I think that is very probably that the answer is yes because the pg_restore process probably restores the databases in sequence like transactions, I'm right? thanks! Pg_upgrade is quite complex. I would not trust this as a valid upgrade. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Extra space when converting number with to_char
On Fri, Oct 26, 2012 at 04:11:42PM -0400, Samuel Gilbert wrote: Thank you, it works. The documentation gave me the impression that the FM modifier only applied to date/time since it was under Usage notes for date/time formatting: Uh, I see: entryliteralFM/literal prefix/entry entryfill mode (suppress padding blanks and trailing zeroes)/entry entryliteralFM/literal/entry Does that need clarification? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Unexpectedly high disk space usage
On 11/07/2012 09:01 AM, Jeff Janes wrote:
Ben, did you ever figure out where the space was going?
I think we've found where the space is going, but I still don't yet know
how to resolve it. I modified your query thusly in order to get a total
of space used, and got an answer that matches closely:
with stuff as (SELECT d.datname as Name,
pg_catalog.pg_get_userbyid(d.datdba) as Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE -1
END as Size
FROM pg_catalog.pg_database d
order by
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END desc nulls first) SELECT sum(size) AS overall from stuff;
Result: 171,276,369,124
# du -sbc /var/lib/pgsql/9.1/data/*
Result: 172,087,129,512
Now, the question is, I see several databases that uses disk usage with
sizes that are dramatically different than I get from a dump/restore to
another machine:
Production:
santarosa444| postgres | 44 GB
Dump/Restore:
santarosa444| postgres | 685 MB
Now, here's where it gets weird. From the disk space usage wiki,
(http://wiki.postgresql.org/wiki/Disk_Usage) you'll recognize how I used
it to get a total disk space used result:
with mytable AS (
SELECT
nspname || '.' || relname AS relation,
pg_total_relation_size(C.oid) AS size
FROM
pg_class C
LEFT JOIN pg_namespace N ON
(N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind 'i'
AND nspname !~ '^pg_toast'
ORDER BY
pg_total_relation_size(C.oid) DESC
)
SELECT sum(size) AS size FROM mytable
... but the total result is 747,569,152 which is close to the
dump/restore value, not the production server value, even though I'm
running this query on the production server. So there's *something* that
the latter query isn't identifying that the former is.
On a hunch, ran this query:
with mytable AS (
SELECT
nspname || '.' || relname AS relation,
pg_total_relation_size(C.oid) AS size
FROM
pg_class C
LEFT JOIN pg_namespace N ON
(N.oid = C.relnamespace) WHERE (true or
(nspname NOT IN ('pg_catalog', 'information_schema')))
AND C.relkind 'i'
AND nspname !~ '^pg_toast'
ORDER BY
pg_total_relation_size(C.oid) DESC
)
SELECT sum(size) AS size FROM mytable;
And the result is 46,771,216,384! Removing the mytable wrapper stuff,
here are the top results:
pg_catalog.pg_attribute | 36727480320
pg_catalog.pg_attrdef| 3800072192
pg_catalog.pg_depend | 2665930752
pg_catalog.pg_class | 1508925440
pg_catalog.pg_type | 1113038848
public.att_claims| 451698688
public.stgrades | 127639552
pg_catalog.pg_index | 107806720
Google returns this page:
http://www.postgresql.org/docs/9.1/static/catalog-pg-attribute.html
which doesn't help me much. So, am I doing something wrong with admin?
Our current process is that every night in the middle of the night, a
script connects to each database on each server and runs a query to get
all tables in each database and, for each, run
VACUUM ANALYZE $table
for each table in the database.
And then once a week:
psql -U postgres -c \l | grep -Po (\w+444) | xargs -t -i psql -U
postgres {} -c REINDEX DATABASE {};
(note: there is a database for the postgres user on each DB server)
The script is a remnant from PG 8.x days, so am I missing something
fundamental about 9.x? I will note that autovacuum is off because it
occasionally causes transactions and queries to hang when an update
causes a vacuum mid-day, effectively taking us offline randomly. Our
scenario is pretty much a worst-possible case of transactions, prepared
transactions, temp tables, and concurrent read/write queries.
--
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] How to verify pg_dump files
-Original Message- From: Gary [mailto:listgj...@yahoo.co.uk] Sent: Wednesday, November 07, 2012 8:02 AM To: pgsql-general@postgresql.org Subject: How to verify pg_dump files Can anyone suggest how I could verify that the files created by pg_dump are okay? They are being created for backup purposes, and the last thing I want to do is find out that the backups themselves are in some way corrupt. I know I can check the output of the command itself, but what if.. I don't know... if there are problems with the disc it writes to, or something like that. Is there any way to check whether the output file is valid in the sense that it is complete and syntactically correct? -- GaryPlease do NOT send me 'courtesy' replies off-list. The only 100% fool-proof test would be to restore from your backup files. Regards, Igor Neyman -- 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] Why PGDLLIMPORT is needed
On Mon, Oct 29, 2012 at 04:41:05PM +0800, Craig Ringer wrote: On 10/29/2012 02:05 PM, 高健 wrote: On /src/include/storage/proc.h: I saw the following line: extern PGDLLIMPORT PGPROC *MyProc; I want to know why PGDLLIMPORT is used here? Does it mean: exten PGPROC *MyProc; right? What platform are you working on? On Windows it's required to allow the static linker to generate the correct symbol tables and the runtime/dynamic linker to correctly link binaries. See: http://support.microsoft.com/kb/132044 http://msdn.microsoft.com/en-us/library/8fskxacy(v=vs.80).aspx http://msdn.microsoft.com/en-us/library/a90k134d(v=vs.80).aspx For other platforms the same macros can be used for symbol visibility filtering, but are usually set to evaluate to nothing so they have no effect. Thanks, those URLs are helpful, and I added them as C comments to win32.h. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Problem with streaming replication over SSL
Magnus Hagander wrote: I have streaming replication configured over SSL, and there seems to be a problem with SSL renegotiation. [...] After that, streaming replication reconnects and resumes working. Is this an oversight in the replication protocol, or is this working as designed? This sounds a lot like the general issue with SSL renegotiation, just that it tends to show itself more often on replication connections since they don't disconnect very often... Have you tried disabling SSL renegotiation on the connection (ssl_renegotation=0)? If that helps, then the SSL library on one of the ends still has the problem with renegotiation... It can hardly be the CVE-2009-3555 renegotiation problem. Both machines have OpenSSL 1.0.0, and RFC 5746 was implemented in 0.9.8m. It certainly *sounds* like that problem though. Maybe RedHat carried along the broken fix? It would surprise me, but given that it's openssl, not hugely much so :) It would be worth trying with ssl_renegotiation=0 to see if the problem goes away. I tried, and that makes the problem go away. This is to be expected of course, because no renegotiation will take place with that setting. But I'll try to test if normal connections have the problem too. That would be a useful datapoint. All settings around this *should* happen at a lower layer than the difference between a replication connection and a regular one, but it would be good to confir mit. I tried, and a normal data connection does not have the problem. I transferred more than 0.5 GB of data (at which point renegotiation should take place), and there was no error. Does it make sense to try and take a stack trace of the problem, on primary or standby? Yours, Laurenz Albe -- 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] How to verify pg_dump files
On 07/11/2012 18:57, Ryan Delaney wrote: On Wed, Nov 7, 2012 at 10:11 AM, Raymond O'Donnell r...@iol.ie mailto:r...@iol.ie wrote: On 07/11/2012 13:01, Gary wrote: Can anyone suggest how I could verify that the files created by pg_dump are okay? They are being created for backup purposes, and the last thing I want to do is find out that the backups themselves are in some way corrupt. I know I can check the output of the command itself, but what if.. I don't know... if there are problems with the disc it writes to, or something like that. Is there any way to check whether the output file is valid in the sense that it is complete and syntactically correct? Reload it and test your application against it? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie mailto:r...@iol.ie Would that entail over-writing good data with possibly bad data? No, reload your database into a newly-created database - on a test server, not on the production server! Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Unexpectedly high disk space usage
Lists li...@benjamindsmith.com writes: pg_catalog.pg_attribute | 36727480320 Ouch. Our current process is that every night in the middle of the night, a script connects to each database on each server and runs a query to get all tables in each database and, for each, run VACUUM ANALYZE $table for each table in the database. (note: there is a database for the postgres user on each DB server) The script is a remnant from PG 8.x days, so am I missing something fundamental about 9.x? I will note that autovacuum is off ... So you've turned off autovacuum, and are carefully not vacuuming the system catalogs. That's your problem all right. Is there a particularly good reason why this script isn't a one-liner VACUUM? Are you sure that once-a-day vacuuming is sufficient, even if it was covering the system catalogs? If you've managed to bloat pg_attribute to 36GB, I suspect you've got enough churn (perhaps from temp tables) that you really need the catalogs vacuumed more often. My advice is dump, reload, and *don't* turn off autovacuum. ... because it occasionally causes transactions and queries to hang when an update causes a vacuum mid-day, effectively taking us offline randomly. I suspect this claim is based on ancient and no longer very relevant experience. regards, tom lane -- 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] Does PostgreSQL have complete functional test cases?
On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote: 2012/11/6 Tianyin Xu t...@cs.ucsd.edu: Thanks, Pavel! I see. So the regress test cases are the complete functional testing? Am I right? yes Those tests are hardly complete, as in testing every possible input and output. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] How to verify pg_dump files
On Wed, Nov 7, 2012 at 10:11 AM, Raymond O'Donnell r...@iol.ie wrote: On 07/11/2012 13:01, Gary wrote: Can anyone suggest how I could verify that the files created by pg_dump are okay? They are being created for backup purposes, and the last thing I want to do is find out that the backups themselves are in some way corrupt. I know I can check the output of the command itself, but what if.. I don't know... if there are problems with the disc it writes to, or something like that. Is there any way to check whether the output file is valid in the sense that it is complete and syntactically correct? Reload it and test your application against it? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie Would that entail over-writing good data with possibly bad data? Ryan
[GENERAL] Unique/Primary key not inherited in partition, workaround?
Regarding the caveats here http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS I am attempting to logically structure my location data. Say for example I have cities/states/countries. I have objects that reference a location, but at any level. An object may reference a city or it may reference a state, depending on how granular we know its location to be. If the city is known for an object, the state and country can be inferred, so the object need not point to all levels in fact that would be redundant and require checking consistency. Ideally, the object would have a foreign key reference to a generic location table (which would have child tables: cities, states, countries). The cities, states and countries could then have foreign keys pointing to each other (cities point to states, which point to countries). Does anyone know a workaround for my problem? Initially I was thinking a single locations table with a location type and a parent location id (references itself) although that's not ideal because I then have to do checks to ensure location type country does not end up being contained in a city. But perhaps I can write checks that ensure that never happens.
Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed
On Wed, Nov 7, 2012 at 11:59 AM, Bruce Momjian br...@momjian.us wrote: On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith g...@2ndquadrant.com wrote: In general, through, diskchecker.pl is the more sensitive test. If it fails, storage is unreliable for PostgreSQL, period. It's good that you've followed up by confirming the real database corruption implied by that is also visible. In general, though, that's not needed. Diskchecker says the drive is bad, you're done--don't put a database on it. Doing the database level tests is more for finding false positives: where diskchecker says the drive is OK, but perhaps there is a filesystem problem that makes it unreliable, one that it doesn't test for. Thanks. That's the conclusion we were coming to too, though all I've seen is lost transactions and not any other form of damage. What SSD are you using? The Intel 320 and 710 series models are the only SATA-connected drives still on the market I know of that pass a serious test. The other good models are direct PCI-E storage units, like the FusionIO drives. I don't have the specs to hand, but one of them is a Kingston drive. Our local supplier is out of 320 series drives, so we were looking for others; will check out the 710s. It's crazy that so few drives can actually be trusted. Yes. Welcome to our craziness! Is there a comprehensive list of drives that have been tested on the wiki somewhere? Our current choices seem to be the Intel 3xx series which STILL suffer from the whoops I'm now an 8MB drive bug and the very expensive SLC 7xx series Intel drives, the Hitachi Ultrastar SSD400M, and the OCZ Vertex 2 Pro. Any particular recommendations from those or other series from anyone would be greatly appreciated. -- 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] Unexpectedly high disk space usage
Hi Greg, I've added you to the cc list because I'm proposing to change
some wiki content which you wrote
On Wed, Nov 7, 2012 at 11:54 AM, Lists li...@benjamindsmith.com wrote:
On 11/07/2012 09:01 AM, Jeff Janes wrote:
Ben, did you ever figure out where the space was going?
Now, here's where it gets weird. From the disk space usage wiki,
(http://wiki.postgresql.org/wiki/Disk_Usage) you'll recognize how I used it
to get a total disk space used result:
with mytable AS (
SELECT
nspname || '.' || relname AS relation,
pg_total_relation_size(C.oid) AS size
FROM
pg_class C
LEFT JOIN pg_namespace N ON
(N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind 'i'
AND nspname !~ '^pg_toast'
ORDER BY
pg_total_relation_size(C.oid) DESC
)
SELECT sum(size) AS size FROM mytable
I question the wisdom of that where clause (from the wiki)
If the pg_catalog relations are big, then they are big and why
shouldn't they get reported as such? And if they are not big, that is
why the limit is there on the wiki page.
I'm tempted to go change it, but maybe there is a good reason it is
there which I do not understand.
...
Google returns this page:
http://www.postgresql.org/docs/9.1/static/catalog-pg-attribute.html which
doesn't help me much. So, am I doing something wrong with admin? Our current
process is that every night in the middle of the night, a script connects to
each database on each server and runs a query to get all tables in each
database and, for each, run
VACUUM ANALYZE $table
for each table in the database.
I take it your script that does that is not including the pg_catalog tables?
Why not just run vacuum analyze and let it do the entire database?
I will note that autovacuum is off because it occasionally causes
transactions and queries to hang when an update causes a vacuum mid-day,
effectively taking us offline randomly.
Hang as in they are blocking on locks? Or they just get slow because
the autovacuum is consuming too much IO?
Cheers,
Jeff
--
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] Unexpectedly high disk space usage
On Wed, Nov 7, 2012 at 1:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lists li...@benjamindsmith.com writes: ... because it occasionally causes transactions and queries to hang when an update causes a vacuum mid-day, effectively taking us offline randomly. I suspect this claim is based on ancient and no longer very relevant experience. My experience is that if autovac is causing problems with stalled queries etc you're either A: running ancient pg versions (pre 8.3), B: Running WAY too aggressive settings in autovac (100 threads, no nap time, cost limit of 10 etc.) or C: Your IO subsystem is absolute crap. On any modern server, default autovac settings from 8.3 and on should only have the possible problem of not being tuned aggressively enough. -- 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] Plug-pull testing worked, diskchecker.pl failed
On Wed, Nov 7, 2012 at 01:53:47PM -0700, Scott Marlowe wrote: On Wed, Nov 7, 2012 at 11:59 AM, Bruce Momjian br...@momjian.us wrote: On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith g...@2ndquadrant.com wrote: In general, through, diskchecker.pl is the more sensitive test. If it fails, storage is unreliable for PostgreSQL, period. It's good that you've followed up by confirming the real database corruption implied by that is also visible. In general, though, that's not needed. Diskchecker says the drive is bad, you're done--don't put a database on it. Doing the database level tests is more for finding false positives: where diskchecker says the drive is OK, but perhaps there is a filesystem problem that makes it unreliable, one that it doesn't test for. Thanks. That's the conclusion we were coming to too, though all I've seen is lost transactions and not any other form of damage. What SSD are you using? The Intel 320 and 710 series models are the only SATA-connected drives still on the market I know of that pass a serious test. The other good models are direct PCI-E storage units, like the FusionIO drives. I don't have the specs to hand, but one of them is a Kingston drive. Our local supplier is out of 320 series drives, so we were looking for others; will check out the 710s. It's crazy that so few drives can actually be trusted. Yes. Welcome to our craziness! Is there a comprehensive list of drives that have been tested on the wiki somewhere? Our current choices seem to be the Intel 3xx series which STILL suffer from the whoops I'm now an 8MB drive bug and the very expensive SLC 7xx series Intel drives, the Hitachi Ultrastar SSD400M, and the OCZ Vertex 2 Pro. Any particular recommendations from those or other series from anyone would be greatly appreciated. No, I know of no official list. Greg Smith and I have tried to document some of this on the wiki: http://wiki.postgresql.org/wiki/Reliable_Writes -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Plug-pull testing worked, diskchecker.pl failed
On Wed, Nov 7, 2012 at 2:01 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Nov 7, 2012 at 01:53:47PM -0700, Scott Marlowe wrote: On Wed, Nov 7, 2012 at 11:59 AM, Bruce Momjian br...@momjian.us wrote: On Sat, Oct 27, 2012 at 05:41:02PM +1100, Chris Angelico wrote: On Sat, Oct 27, 2012 at 4:26 PM, Greg Smith g...@2ndquadrant.com wrote: In general, through, diskchecker.pl is the more sensitive test. If it fails, storage is unreliable for PostgreSQL, period. It's good that you've followed up by confirming the real database corruption implied by that is also visible. In general, though, that's not needed. Diskchecker says the drive is bad, you're done--don't put a database on it. Doing the database level tests is more for finding false positives: where diskchecker says the drive is OK, but perhaps there is a filesystem problem that makes it unreliable, one that it doesn't test for. Thanks. That's the conclusion we were coming to too, though all I've seen is lost transactions and not any other form of damage. What SSD are you using? The Intel 320 and 710 series models are the only SATA-connected drives still on the market I know of that pass a serious test. The other good models are direct PCI-E storage units, like the FusionIO drives. I don't have the specs to hand, but one of them is a Kingston drive. Our local supplier is out of 320 series drives, so we were looking for others; will check out the 710s. It's crazy that so few drives can actually be trusted. Yes. Welcome to our craziness! Is there a comprehensive list of drives that have been tested on the wiki somewhere? Our current choices seem to be the Intel 3xx series which STILL suffer from the whoops I'm now an 8MB drive bug and the very expensive SLC 7xx series Intel drives, the Hitachi Ultrastar SSD400M, and the OCZ Vertex 2 Pro. Any particular recommendations from those or other series from anyone would be greatly appreciated. No, I know of no official list. Greg Smith and I have tried to document some of this on the wiki: http://wiki.postgresql.org/wiki/Reliable_Writes Well I may get a budget at work to do some testing so I'll update that list etc. This has been a good thread to get me motivated to get started. -- 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] Plug-pull testing worked, diskchecker.pl failed
On Wed, Nov 7, 2012 at 02:12:39PM -0700, Scott Marlowe wrote: I don't have the specs to hand, but one of them is a Kingston drive. Our local supplier is out of 320 series drives, so we were looking for others; will check out the 710s. It's crazy that so few drives can actually be trusted. Yes. Welcome to our craziness! Is there a comprehensive list of drives that have been tested on the wiki somewhere? Our current choices seem to be the Intel 3xx series which STILL suffer from the whoops I'm now an 8MB drive bug and the very expensive SLC 7xx series Intel drives, the Hitachi Ultrastar SSD400M, and the OCZ Vertex 2 Pro. Any particular recommendations from those or other series from anyone would be greatly appreciated. No, I know of no official list. Greg Smith and I have tried to document some of this on the wiki: http://wiki.postgresql.org/wiki/Reliable_Writes Well I may get a budget at work to do some testing so I'll update that list etc. This has been a good thread to get me motivated to get started. Yes, it seems database people are the few who care about device sync reliability (or know to care). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Unique/Primary key not inherited in partition, workaround?
Le 2012-11-07 à 13:58, Nicholas Wilson a écrit : Regarding the caveats here http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS I am attempting to logically structure my location data. Say for example I have cities/states/countries. I have objects that reference a location, but at any level. An object may reference a city or it may reference a state, depending on how granular we know its location to be. If the city is known for an object, the state and country can be inferred, so the object need not point to all levels in fact that would be redundant and require checking consistency. Ideally, the object would have a foreign key reference to a generic location table (which would have child tables: cities, states, countries). The cities, states and countries could then have foreign keys pointing to each other (cities point to states, which point to countries). Does anyone know a workaround for my problem? Initially I was thinking a single locations table with a location type and a parent location id (references itself) although that's not ideal because I then have to do checks to ensure location type country does not end up being contained in a city. But perhaps I can write checks that ensure that never happens. Could you use the following: locations Country, Region, City USA, New York, New York USA, New York, GLOBAL USA, GLOBAL, GLOBAL GLOBAL, GLOBAL, GLOBAL If your locations table had that, then you can use foreign key checks to ensure the value is indeed present. Bye, François
Re: [GENERAL] Unexpectedly high disk space usage
On 11/07/2012 12:42 PM, Tom Lane wrote: So you've turned off autovacuum, and are carefully not vacuuming the system catalogs. That's your problem all right. Is there a particularly good reason why this script isn't a one-liner VACUUM? Back in the 8.x days, we experienced vacuum full analyze occasionally causing other processes to hang/timeout. In an attempt to minimize the impact of the locking, we updated the script to vacuum one table at a time, which seemed to work well throughout the 8.x series. I'd happily accept that this conclusion may have simply have been wrong, but it worked well enough that nobody complained and life was good. After switching to 9.x, we read that the full vacuum was less useful and so the script was changed to vacuum analyze $table rather than vacuum full analyze $table. Are you sure that once-a-day vacuuming is sufficient, even if it was covering the system catalogs? If you've managed to bloat pg_attribute to 36GB, I suspect you've got enough churn (perhaps from temp tables) that you really need the catalogs vacuumed more often. The only thing that I could find in the docs even mentioning the idea of vacuuming catalogs is this sentence: (A manual VACUUM should fix the problem, as suggested by the hint; but note that the VACUUM must be performed by a superuser, else it will fail to process system catalogs and thus not be able to advance the database's datfrozenxid.) http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html This does NOT clearly say that the end user could vacuum catalogs, let alone that it's necessary or even a good idea. Otherwise, the only mention is of tables, and there's no mention of the idea that tables are anything but user space. My advice is dump, reload, and *don't* turn off autovacuum. ... because it occasionally causes transactions and queries to hang when an update causes a vacuum mid-day, effectively taking us offline randomly. I suspect this claim is based on ancient and no longer very relevant experience. We tried several times to turn on autovacuum with 9.1 and had problems every time. If our use case is particularly special, I'd love to work with you to get autovacuum to work in our situation too as it would make life easier for us! But for the past few months, every time we've turned it on, we've had our phones swamped with customers who are unable to use our system while our application monitors scream bloody murder, at least weekly. From what we could tell (under extreme pressure to get it all working again ASAP, mind you) it seemed that when doing a large update from within a transaction, autovacuum would get triggered before the transaction completed, causing the transaction to hang or at least slow way down, causing timeouts to occur with load balancers, so customers would then try again, compounding the ongoing problem. Pretty soon you have not only I/O issues, but also locking issues and upset customers. This issue may be compounded because we make fairly extensive use of dblink and temp tables to aggregate data for our customers who have multiple sites. -Ben -- 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] Unexpectedly high disk space usage
On 11/07/2012 12:58 PM, Scott Marlowe wrote: My experience is that if autovac is causing problems with stalled queries etc you're either A: running ancient pg versions (pre 8.3), B: Running WAY too aggressive settings in autovac (100 threads, no nap time, cost limit of 10 etc.) or C: Your IO subsystem is absolute crap. On any modern server, default autovac settings from 8.3 and on should only have the possible problem of not being tuned aggressively enough. A) We are running PG 9.1. B) We used the default settings in the RPMs provided by yum.postgresql.org. At the bottom of this message is information about the RPMs we currently are using. C) I/O subsystem for PG is twin SATA III SSDs in a RAID 1 configuration, capable of tens of thousands of IO operations per second. Servers are recent, SATA III, 16-core Xeons with 128 GB ECC RAM in 1U rackmount cases. As stated previously, we make extensive use of temp tables, transactions, and dblink, but had no trouble with catalog table bloat in 8.x; this is a new phenomenon for us. # rpm -qi postgresql91-9.1.5-3PGDG.rhel6.x86_64 Name: postgresql91 Relocations: (not relocatable) Version : 9.1.5 Vendor: (none) Release : 3PGDG.rhel6 Build Date: Sun 02 Sep 2012 12:13:18 PM UTC Install Date: Wed 12 Sep 2012 03:04:24 AM UTC Build Host: koji-sl6-x86-64-pg91 Group : Applications/DatabasesSource RPM: postgresql91-9.1.5-3PGDG.rhel6.src.rpm Size: 5193673 License: PostgreSQL Signature : DSA/SHA1, Sun 02 Sep 2012 12:13:20 PM UTC, Key ID 1f16d2e1442df0f8 URL : http://www.postgresql.org/ Summary : PostgreSQL client programs and libraries Description : PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). The postgresql package includes the client programs and libraries that you'll need to access a PostgreSQL DBMS server. These PostgreSQL client programs are programs that directly manipulate the internal structure of PostgreSQL databases on a PostgreSQL server. These client programs can be located on the same machine with the PostgreSQL server, or may be on a remote machine which accesses a PostgreSQL server over a network connection. This package contains the command-line utilities for managing PostgreSQL databases on a PostgreSQL server. If you want to manipulate a PostgreSQL database on a local or remote PostgreSQL server, you need this package. You also need to install this package if you're installing the postgresql91-server package. -- 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] Plug-pull testing worked, diskchecker.pl failed
On Wed, Nov 7, 2012 at 3:53 PM, Scott Marlowe scott.marl...@gmail.comwrote: Is there a comprehensive list of drives that have been tested on the wiki somewhere? Our current choices seem to be the Intel 3xx series which STILL suffer from the whoops I'm now an 8MB drive bug and the very expensive SLC 7xx series Intel drives, the Hitachi Ultrastar SSD400M, and the OCZ Vertex 2 Pro. Any particular recommendations from those or other series from anyone would be greatly appreciated. My most recent big box(es) are built using all Intel 3xx series drives. Like you said, the 7xx series was way too expensive. The 5xx series looks totally right on paper, until you find out they don't have a durable cache. That just doesn't make sense in any universe... but that's the way they are. They seem to be doing really well so far. I connected them to LSI RAID controllers, with the Fastpath option. I think they are pretty speedy. On my general purpose boxes, I now spec the 3xx drives for boot (software RAID) and use other drives such as Seagate Constellation for data with ZFS. Sometimes I think that the ZFS volumes are faster than the SSD RAID volumes, but it is not a fair comparison because the RAID systems are CentOS 6 and the ZFS systems are FreeBSD 9.
Re: [GENERAL] How to verify pg_dump files
On 11/07/2012 11:56 AM, Igor Neyman wrote: The only 100% fool-proof test would be to restore from your backup files. Regards, Igor Neyman Our internal process is to back up production databases regularly, and then use the backups offsite to populate copies of databases for developer use. This allows us to test with real data, identifying real world bugs that would not appear with often-limited, manually created, sample data, as well as verify our backups on a regular, daily basis. I'd strongly recommend something similar if it works for you. -Ben -- 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] Unexpectedly high disk space usage
On 11/7/12 3:58 PM, Jeff Janes wrote:
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
I question the wisdom of that where clause (from the wiki)
If the pg_catalog relations are big, then they are big and why
shouldn't they get reported as such? And if they are not big, that is
why the limit is there on the wiki page.
The idea was that in a new database with a relatively small number of
tables, your own tables will be lost among the catalog data unless you
filter them out. Testing against an install with a single real table,
the query there will show something like this right now:
relation| total_size
+
public.t | 3568 kB
public.t_k_seq | 8192 bytes
But if the filter on pg_catalog is removed, you get this instead:
relation | total_size
-+
public.t| 3568 kB
pg_catalog.pg_depend| 808 kB
pg_catalog.pg_proc | 752 kB
pg_catalog.pg_attribute | 568 kB
pg_catalog.pg_rewrite | 464 kB
pg_catalog.pg_description | 392 kB
pg_catalog.pg_statistic | 328 kB
pg_catalog.pg_operator | 208 kB
pg_catalog.pg_collation | 152 kB
pg_catalog.pg_type | 152 kB
pg_catalog.pg_amop | 136 kB
pg_catalog.pg_class | 136 kB
pg_catalog.pg_constraint| 112 kB
pg_catalog.pg_conversion| 104 kB
pg_catalog.pg_index | 88 kB
pg_catalog.pg_amproc| 80 kB
pg_catalog.pg_opclass | 80 kB
pg_catalog.pg_ts_config_map | 80 kB
pg_catalog.pg_cast | 80 kB
pg_catalog.pg_authid| 72 kB
That is overload for a lot of people, and confusing to new users.
That's why I opted for the shorter version.
There's no perfect answer to all use cases here. This sort of thing is
why there's three sets of queries for pg_stat_user_tables,
pg_stat_sys_tables, and pg_stat_all_tables. The wiki disk space queries
aim to be like the user tables version from that trio.
Adding a note pointing out that you might want to remove pg_catalog and
see the size of those relations would be appropriate. I wouldn't make
that the default case though, due to the issue highlighted above. I'd
rather optimize the initially suggested query so that new users get
simple output, even if it means that might hide problems on larger
installs, where the catalog data became big.
The other way I sometimes balance these two requirements--want to show
all the big data, but not clutter small installs with the catalog--is to
make the filter size-based instead:
SELECT nspname || '.' || relname AS relation,
pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('information_schema')
AND C.relkind 'i'
AND nspname !~ '^pg_toast'
AND relpages 100
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
On my trivial test install that gives me just the one user table:
relation | total_size
--+
public.t | 3568 kB
While still showing larger catalog tables if they grow to be noticeable.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
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] Unexpectedly high disk space usage
Jeff Janes jeff.ja...@gmail.com writes:
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind 'i'
AND nspname !~ '^pg_toast'
I question the wisdom of that where clause (from the wiki)
If the pg_catalog relations are big, then they are big and why
shouldn't they get reported as such?
Agreed, please change it.
(The index and toast exclusions are reasonable, since those will be
accounted for in pg_total_relation_size of the parent. Personally I'd
code the toast exclusion using relkind not a namespace check though.)
regards, tom lane
--
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] Unexpectedly high disk space usage
On Wed, Nov 7, 2012 at 1:58 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Nov 7, 2012 at 1:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lists li...@benjamindsmith.com writes: ... because it occasionally causes transactions and queries to hang when an update causes a vacuum mid-day, effectively taking us offline randomly. I suspect this claim is based on ancient and no longer very relevant experience. My experience is that if autovac is causing problems with stalled queries etc you're either A: running ancient pg versions (pre 8.3), B: Running WAY too aggressive settings in autovac (100 threads, no nap time, cost limit of 10 etc.) or C: Your IO subsystem is absolute crap. On any modern server, default autovac settings from 8.3 and on should only have the possible problem of not being tuned aggressively enough. Oh another failure scenario up there is that you're running DDL in production, which is stalling behind an autovac, and in turn the two are stalling other queries. This has happened for me once or twice on more modern versions (8.3 and 8.4) -- 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] Plug-pull testing worked, diskchecker.pl failed
On 11/7/2012 3:17 PM, Vick Khera wrote: My most recent big box(es) are built using all Intel 3xx series drives. Like you said, the 7xx series was way too expensive. I have to raise my hand to say that for us 710 series drives are an unbelievable bargain and we buy nothing else now for production servers. When you compare vs the setup you'd need to achieve the same tps using rotating media, and especially considering the power and cooling saved, they're really cheap. YMMV of course.. -- 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] Unexpectedly high disk space usage
On Wed, Nov 7, 2012 at 3:15 PM, Lists li...@benjamindsmith.com wrote: On 11/07/2012 12:42 PM, Tom Lane wrote: So you've turned off autovacuum, and are carefully not vacuuming the system catalogs. That's your problem all right. Is there a particularly good reason why this script isn't a one-liner VACUUM? Back in the 8.x days, we experienced vacuum full analyze occasionally causing other processes to hang/timeout. That was your first mistake. By 8.0 the need for vacuum full was almost zero. Except for instances where bloat got out of hand, vacuum full should generally be avoided after 8.0. Regular vacuum should be plenty, whether it's run by autovacuum daemon or a cron job. Until 8.3 autovacuum was single threaded so therefore often had trouble keeping up with bloat. While vacuum full is a blocking operation plain vacuums are not, so unless you REALLY need a vacuum full they should be avoided. In an attempt to minimize the impact of the locking, we updated the script to vacuum one table at a time, which seemed to work well throughout the 8.x series. I'd happily accept that this conclusion may have simply have been wrong, but it worked well enough that nobody complained and life was good. Yeah you still had blocking but it was probably less noticeable. After switching to 9.x, we read that the full vacuum was less useful and so the script was changed to vacuum analyze $table rather than vacuum full analyze $table. Yeah at that point you'd have been better off tuning autovacuum to be more aggressive and let it do the job. Generally the time to call vacuum by hand is right after you've done something like delete half the rows in a large table. -- 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] How to evaluate explain analyze correctly soon after explain for the same statement ?
Hi tom At frist I have thought that the database parsed my explain statement, so the pre-compiled execution plan will be re-used , which made the statement's second run quick. I think that what you said is right. Thank you 2012/11/7 Tom Lane t...@sss.pgh.pa.us =?UTF-8?B?6auY5YGl?= luckyjack...@gmail.com writes: It might not be a big problem in a small system. But when in a production environment, When I want to use explain and then , soon use explain analyze for the same statement, How can I avoid the influence of cache and get the right answer for evaluating purpose? I think this question is based on a false premise. Why do you feel that the behavior with cold caches is the right answer, and not the behavior with warm caches? A short-duration query like this one is not going to be interesting at all for performance unless it's executed quite a lot, and if it's executed quite a lot then the warm-cache result ought to be the more representative one. In general, trying to tune for cold-cache cases seems backwards to me. It's much more productive to try to ensure that the caches are warm. regards, tom lane
Re: [GENERAL] Does PostgreSQL have complete functional test cases?
2012/11/7 Bruce Momjian br...@momjian.us: On Tue, Nov 6, 2012 at 09:24:19AM +0100, Pavel Stehule wrote: 2012/11/6 Tianyin Xu t...@cs.ucsd.edu: Thanks, Pavel! I see. So the regress test cases are the complete functional testing? Am I right? yes Those tests are hardly complete, as in testing every possible input and output. sure - but almost all implemented important functionality is covered Pavel -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How is execution plan cost calculated for index scan
Hi all: I want to see the explain plan for a simple query. My question is : How is the cost calculated? The cost parameter is: random_page_cost= 4 seq_page_cost = 1 cpu_tuple_cost =0.01 cpu_operator_cost =0.0025 And the table and its index physical situation are as following: postgres=# select relpages, reltuples from pg_class where relname = 'pg_proc'; relpages | reltuples --+--- 62 | 2490 postgres=# select relpages, reltuples from pg_class where relname = 'pg_proc_oid_index'; relpages | reltuples --+--- 9 | 2490 The explain plan is: postgres=# explain SELECT * FROM pg_proc where oid=1; QUERY PLAN --- Index Scan using pg_proc_oid_index on pg_proc (cost=0.00..8.27 rows=1 width=548) Index Cond: (oid = 1::oid) (2 rows) I think in the worst situation , Firstly, database need to search for 9 index pages by sequential to find the index entry. For each index page in memory, every “index tuple” need to be scanned. Then , using the key entry, it need to make a random page read for the real data into memory, then scan the data tuple is scanned until the reall one is found (or just directly locate to the data block after read the data page into memory ) So at least the evaluated max cost should be bigger than 9 index pages * seq_page_cost , so it should be bigger than 9. Here I haven't added the random page read cost for data. But what I got is max is 8.27. How is the result of 8.27 be calculated? Furthermore, I tried to find the logic in source code, I think it might be costsize.c in src/backend/optimizer/, by debugging it, I found that: When I use [ explain SELECT * FROM pg_proc where oid=1;] , I can found that cost_index function is called. The result returned for path-path.total_cost is86698968.And 86698968/1024/1024 = 82.68258 . If devided by 10 , is near 8.27. but this is still a little odd. In the above case,can I say that the cost formula for index scan is in-- the cost_index function ? Thanks in advance
[GENERAL] Use order by clause, got index scan involved
Hi all: What confused me is that: When I select data using order by clause, I got the following execution plan: postgres=# set session enable_indexscan=true; SET postgres=# explain SELECT * FROM pg_proc ORDER BY oid; QUERY PLAN Index Scan using pg_proc_oid_index on pg_proc (cost=0.00..321.60 rows=2490 width=552) (1 row) postgres=# My Question is : If I want to find record using the where clause which hold the id column, the index scan might be used. But I just want to get all the records on sorted output format, Why index scan can be used here? I can’t imagine that: Step 1 Index is read into memory, then for each tuple in it, Step 2 Then we got the address of related data block, and then access the data block . Step 2 will be repeated for many times. I think it is not efficient. But comparing with sort , I got that even index scan with all the entry , the cost is still lower than sort operation: postgres=# set session enable_indexscan=false; SET postgres=# explain SELECT * FROM pg_proc ORDER BY oid; QUERY PLAN --- Sort (cost=843.36..849.59 rows=2490 width=552) Sort Key: oid - Seq Scan on pg_proc (cost=0.00..86.90 rows=2490 width=552) (3 rows) postgres=# That is to say: cost of seq scan + sortcost of index scan for every index entry + cost of access for every related data ? Maybe the database system is clever enough to accumulate data access for same physical page, and reduce the times of physical page acess ? And can somebody kindly give some more detailed information which help to know the execution plan calculation process? Thanks in advance.
[GENERAL] find a substring on a text (data type) column
I was trying to find a substring on a text (data type) column like 'cat foo dog ...'. I use the query below SELECT id FROM table WHERE name LIKE '% foo %'; Sometimes the query return with nTuples=0 but there are matching rows. On retry, the query return with expected results. Any ideas; (postgres ver 9.2, libpq - C Library)
Re: [GENERAL] find a substring on a text (data type) column
Hello 2012/11/8 pantelis vlachos vlacho...@gmail.com: I was trying to find a substring on a text (data type) column like 'cat foo dog ...'. I use the query below SELECT id FROM table WHERE name LIKE '% foo %'; Sometimes the query return with nTuples=0 but there are matching rows. On retry, the query return with expected results. Any ideas; isn't problem in spaces, so LIKE '% foo %' must fail when symbol is on begin or end? you can check ' ' || name || ' ' LIKE '% foo %' or better - use fulltext instead Regards Pavel Stehule (postgres ver 9.2, libpq - C Library) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
