Re: [GENERAL] Up-to-date reports database
On Wed, May 23, 2012 at 5:11 PM, Herouth Maoz hero...@unicell.co.il wrote: A replication solution is not very good, either, because of course I can't define indexes differently, I don't want *all* transactions in all tables to be sent, and also, because I may want to cross reference data from different systems. So ideally, I want to have a reporting database, where specific tables (or maybe even just specific columns) from various databases are collected, and have a reporting tool connect to this database. But I want to push the data into into that database as close to real time as possible. Look at PgQ from Skytools. You can queue your OLTP data changes and restore specific columns only into your OLAP database. The most important data I am currently considering are two tables which have an average of 7,600 transactions per hour (standard deviation 10,000, maximum in May is 62,000 transactions per hour). There may be similar pairs of tables collected from more than one database. I assume this is not an uncommon scenario. What solutions would you recommend? Herouth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a way to start postgresql v907 as non daemon process
I have observed by default posgresql 8.x onwards starts as daemon process. Is there a way to avoid it. I want it to run as normal process. The parent of the server process should be the process that starts the server instead of being PID 1 ( init ). Till 7.4.2 ( Which was the older version used by us ) was using running in normal mode. The same behavior is necessary as we have our watchdog failing to identify that it server is actually running. ( Transcript to help you what I am talking about ) c670e04:rkananth 16] /usr/local/bin/postgres --version postgres (PostgreSQL) 9.0.7 c670e04:rkananth 11] postgres -D /data/db/postgres c670e04:rkananth 12] ps -A | grep post 65165 ?? Ss 0:00.01 /usr/local/bin/postgres 65167 ?? Ss 0:00.00 postgres: writer process(postgres) 65168 ?? Ss 0:00.00 postgres: wal writer process(postgres) 65169 ?? Ss 0:00.00 postgres: autovacuum launcher process (postgres) 65170 ?? Ss 0:00.00 postgres: stats collector process(postgres) 65224 0 S+ 0:00.00 grep post c670e04:rkananth 14] ps -p 65165 -o ppid PPID 1 c670e04:rkananth 15] ps 1 PID TT STAT TIME COMMAND 1 ?? SLs0:00.17 /sbin/init -- I would be happy to provide further information if required.
Re: [GENERAL] FATAL: lock file postmaster.pid already exists
On Thu, May 24, 2012 at 12:47 AM, Mark Dilger markdil...@yahoo.com wrote: I am running this code on Windows 2003. It appears that postgres has in src/port/dirent.c a port of readdir() that internally uses the WIN32_FIND_DATA structure, and the function FindNextFile() to iterate through the directory. Looking at the documentation, it seems that this function does collect file creation time, last access time, last write time, file size, etc., much like performing a stat. In my case, the code is iterating through roughly 56,000 files. Apparently, this is doing the equivalent of a stat on each of them. how did you end up with 56,000 files? Lots and lots and lots of tables? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] FATAL: lock file postmaster.pid already exists
On Thu, May 24, 2012 at 2:42 AM, Mark Dilger markdil...@yahoo.com wrote: FindFirstFile can take a wildcard filename pattern. It appears that we are effectively calling FindFirstFile without a pattern, getting all 56000 file names with complete stat information, doing a poor-man's regex on those names, and matching just the temporary files. If RemovePgTempFiles were modified to pass a filter, this code might perform better on Windows. I'll look into this. It might in that case be worthwhile looking at using scandir() on platforms that support that as well, so that other platforms can benefit from an optimization as well. Though I'm not sure how much that would actually help - ISTM that one actually scans the whole directory anyway, just you don't have to do it yourself... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] enhanced linestyles for psql
hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console http://postgres.cz/wiki/Pretty_borders_in_psql Regards Pavel Stehule -- 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] enhanced linestyles for psql
On 24 May 2012 15:33, Pavel Stehule pavel.steh...@gmail.com wrote: hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console Nice job! I'm not entirely enthusiastic about the option names though. Would it help to split the setting into several? For example: \pset borders-inside double single (double horizontal inside borders, single vertical) \pset borders-outside double Or, since you can't really mix outside border-styles: \pset border-style outside horizontal vertical I'm not sure whether it would be desirable to have more control over inside borders, for example per column or row (or even cells)? Just something to ponder ;) -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] enhanced linestyles for psql
2012/5/24 Alban Hertroys haram...@gmail.com: On 24 May 2012 15:33, Pavel Stehule pavel.steh...@gmail.com wrote: hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console Nice job! Thank you :) I'm not entirely enthusiastic about the option names though. Would it help to split the setting into several? I am sorry, but I cannot to change it. Now it is compatible with builtin functionality in console. A change means longer patch and more necessary work for porting to new versions. So sorry For example: \pset borders-inside double single (double horizontal inside borders, single vertical) \pset borders-outside double and this type of configuration is not possible - a syntax for psql settings should be very simple (primitive) Or, since you can't really mix outside border-styles: \pset border-style outside horizontal vertical I'm not sure whether it would be desirable to have more control over inside borders, for example per column or row (or even cells)? Just something to ponder ;) ook - if you have other ideas - other design, please, send it. Regards Pavel -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] enhanced linestyles for psql
hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console http://postgres.cz/wiki/Pretty_borders_in_psql Thank you. And really a great patch to psql console lover's... :) http://postgres.cz/wiki/Enhanced-psql Is this also compatible to 9.1 ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
On Wed, May 23, 2012 at 2:45 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 24/05/12 08:18, Lonni J Friedman wrote: On Wed, May 23, 2012 at 12:36 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 24/05/12 05:09, Lonni J Friedman wrote: On Wed, May 23, 2012 at 9:37 AM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: After banging my head on the wall for a long time, I happened to notice that khugepaged was consuming 100% CPU every time autovacuum was running. I did: echo madvise /sys/kernel/mm/transparent_hugepage/defrag and immediately the entire problem went away. Fascinating. In hindsight, sure. Before that, it was 2 days of horror. So this looks like a nasty Fedora16 kernel bug to me, or maybe postgresql Fedora16's default kernel settings are just not compatible? I agree, kernel bug. What kernel version are you using exactly? I'm using the stock 3.3.5-2.fc16.x86_64 kernel that is in Fedora updates. Is anyone else using Fedora16 PostgreSQL-9.1 ? I use an F16 box daily, but can't claim to have done major performance testing with it. Can you put together a summary of your nondefault Postgres settings? I wonder whether it only kicks in for a certain size of shared memory for instance. Oh yea, I'm quite certain that this is somehow related to my setup, and not a generic problem with all F16/pgsql systems. For starters, this problem isn't happening on any of the 3 standby systems, which are all otherwise identical to the master in every respect. Also when we had done some testing (prior to the upgrades), we never ran into any of these problems. However our test environment was on smaller scale hardware, with a much smaller number of clients (and overall load). Here are the non default settings in postgresql.conf : wal_level = hot_standby archive_mode = on archive_timeout = 61 max_wal_senders = 10 wal_keep_segments = 5000 hot_standby = on log_autovacuum_min_duration = 2500 autovacuum_max_workers = 4 maintenance_work_mem = 1GB checkpoint_completion_target = 0.7 effective_cache_size = 88GB work_mem = 576MB wal_buffers = 16MB checkpoint_segments = 64 shared_buffers = 8GB max_connections = 350 Let me know if you have any other questions. I'd be happy to provide as much information as possible if it can aid in fixing this bug. I think they will need details of things like: RAM, number/type processors, number type of disks, disk controllers any other hardware specs that might be relevant etc.- at very least: total RAM number of spindles 16 core Xeon X5550 2.67GHz 128GB RAM $PGDATA sits on a RAID5 array comprised of 3 SATA disks. Its Linux's md software RAID. How does this compare to your other machines running the same, or similar, databases? However, you do say that the other machines are indentical - but are the other machines different in any aspect, that might prove siginificant? Also anything else running on the box. nothing else. its dedicated exclusively to postgresql. Plus transaction load pattern - over time and read/write ratios. I'm not sure how I'd obtain this data. however, the patterns didn't change since the upgrade. If someone can point me in the right direction, I can at least obtain this data as its generated currently. type/nature of queries I need some clarification on specifically what you're asking for here. The complexity, structure, and features of the queries. Do you have lots of sub queries, and ORDER BY's? Also the number of tables accessed in a query. This is heading into the territory where others will be better placed to advise you as to what might be relevant! No, not lots of subqueries or ORDERing, and most queries only touch a single table. However, I'm honestly not sure that I'm following where you're going with this. The problem isn't triggered by explicit queries. I can disable all external access, and simply wait for autovacuum to kick off, and the box starts to die. -- 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] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
Lonni J Friedman netll...@gmail.com writes: No, not lots of subqueries or ORDERing, and most queries only touch a single table. However, I'm honestly not sure that I'm following where you're going with this. The problem isn't triggered by explicit queries. I can disable all external access, and simply wait for autovacuum to kick off, and the box starts to die. Can you correlate the performance hit with any specific part of autovacuum? In particular, I'm wondering if it matters whether vacuum is cleaning tables or indexes --- it alternates between the two, and the access patterns are a bit different. You could probably watch what the autovac process is doing with strace to see what it's accessing. 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] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
On Thu, May 24, 2012 at 12:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: No, not lots of subqueries or ORDERing, and most queries only touch a single table. However, I'm honestly not sure that I'm following where you're going with this. The problem isn't triggered by explicit queries. I can disable all external access, and simply wait for autovacuum to kick off, and the box starts to die. Can you correlate the performance hit with any specific part of autovacuum? In particular, I'm wondering if it matters whether vacuum is cleaning tables or indexes --- it alternates between the two, and the access patterns are a bit different. You could probably watch what the autovac process is doing with strace to see what it's accessing. Is there something specific I should be looking for in the strace output, or is this just a matter of correlating PID and FD to pg_class.relfilenode ? -- 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] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
Lonni J Friedman netll...@gmail.com writes: On Thu, May 24, 2012 at 12:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Can you correlate the performance hit with any specific part of autovacuum? In particular, I'm wondering if it matters whether vacuum is cleaning tables or indexes --- it alternates between the two, and the access patterns are a bit different. You could probably watch what the autovac process is doing with strace to see what it's accessing. Is there something specific I should be looking for in the strace output, or is this just a matter of correlating PID and FD to pg_class.relfilenode ? Nah, just match up the files it touches with pg_class.relfilenode. 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] significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
Tom Lane t...@sss.pgh.pa.us writes:
[...] Can you correlate the performance hit with any specific part
of autovacuum? In particular, I'm wondering if it matters whether
vacuum is cleaning tables or indexes [...]
In case it helps, this systemtap run will report on the top few
time-sampled call graphs of post* processes:
# debuginfo-install -y postgresql
# stap -V[...Fedora16...]
Systemtap translator/driver (version 1.7/0.153 non-git sources)
# cat post-prof.stp
probe timer.profile {
if (substr(execname(),0,4) != post) next
bt[sprint_ustack(ubacktrace())] 1
}
probe end {
foreach ([s] in bt- limit 50) { println(\n,@count(bt[s]),:,s) }
}
global bt[2]
# stap post-prof.stp -v -d /usr/bin/postgres -d /usr/bin/postmaster --ldd
--all-modules --suppress-handler-errors
[wait awhile during workload]
^C
1390:index_getnext+0x1f9 [postgres]
IndexNext+0x56 [postgres]
ExecScan+0x14e [postgres]
ExecProcNode+0x228 [postgres]
ExecLimit+0xb8 [postgres]
ExecProcNode+0xd8 [postgres]
standard_ExecutorRun+0x14a [postgres]
PortalRunSelect+0x287 [postgres]
PortalRun+0x248 [postgres]
PostgresMain+0x754 [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]
935:__lseek_nocancel+0x7 [libc-2.14.90.so]
FileSeek+0x127 [postgres]
_mdnblocks+0x20 [postgres]
mdnblocks+0x6b [postgres]
get_relation_info+0x5e2 [postgres]
build_simple_rel+0x169 [postgres]
add_base_rels_to_query+0x83 [postgres]
query_planner+0x159 [postgres]
grouping_planner+0xc0b [postgres]
subquery_planner+0x5b4 [postgres]
standard_planner+0xe4 [postgres]
pg_plan_query+0x1b [postgres]
pg_plan_queries+0x54 [postgres]
PostgresMain+0x848 [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
721:__send+0x22 [libc-2.14.90.so]
internal_flush+0x3b [postgres]
pq_flush+0x22 [postgres]
ReadyForQuery+0x29 [postgres]
PostgresMain+0x49e [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]
431:recv+0x22 [libc-2.14.90.so]
secure_read+0x1c6 [postgres]
pq_recvbuf+0x5f [postgres]
pq_getbyte+0x15 [postgres]
PostgresMain+0x4bf [postgres]
ServerLoop+0x799 [postgres]
PostmasterMain+0x647 [postgres]
main+0x71e [postgres]
__libc_start_main+0xed [libc-2.14.90.so]
_start+0x29 [postgres]
380:__lseek_nocancel+0x7 [libc-2.14.90.so]
FileSeek+0x127 [postgres]
_mdnblocks+0x20 [postgres]
mdnblocks+0x6b [postgres]
estimate_rel_size+0x7c [postgres]
get_relation_info+0x121 [postgres]
build_simple_rel+0x169 [postgres]
add_base_rels_to_query+0x83 [postgres]
query_planner+0x159 [postgres]
grouping_planner+0xc0b [postgres]
subquery_planner+0x5b4 [postgres]
standard_planner+0xe4 [postgres]
pg_plan_query+0x1b [postgres]
pg_plan_queries+0x54 [postgres]
PostgresMain+0x848 [postgres]
ServerLoop+0x799 [postgres]
[...]
--
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] FATAL: lock file postmaster.pid already exists
We have lots of partition tables that inherit from a smaller number of parents. Some, but not all of these tables also have indexes. The number actually varies depending on the data loaded. For some other database instances, fortunately on Linux, the number is in the millions. I have been testing with passing FindFirstFile a pattern to match the temporary file names, rather than letting FindFirstFile/FindNextFile return all names and then having postgres do the pattern match itself. So far, this looks very promising, with a stand-alone program that uses this technique cutting the runtime from 4 minutes down to less than a second. I have a fairly clean patch in the works that I will submit after I have verified it on Windows 2003, Windows 2008 and Linux. From: Magnus Hagander mag...@hagander.net To: Mark Dilger markdil...@yahoo.com Cc: Tom Lane t...@sss.pgh.pa.us; deepak deepak...@gmail.com; Alban Hertroys haram...@gmail.com; pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Thursday, May 24, 2012 3:58 AM Subject: Re: [GENERAL] FATAL: lock file postmaster.pid already exists On Thu, May 24, 2012 at 12:47 AM, Mark Dilger markdil...@yahoo.com wrote: I am running this code on Windows 2003. It appears that postgres has in src/port/dirent.c a port of readdir() that internally uses the WIN32_FIND_DATA structure, and the function FindNextFile() to iterate through the directory. Looking at the documentation, it seems that this function does collect file creation time, last access time, last write time, file size, etc., much like performing a stat. In my case, the code is iterating through roughly 56,000 files. Apparently, this is doing the equivalent of a stat on each of them. how did you end up with 56,000 files? Lots and lots and lots of tables? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] enhanced linestyles for psql
2012/5/24 Raghavendra raghavendra@enterprisedb.com: hello I wrote patch for PostgreSQL 9.1 and 9.2 that adds more linestyles and border styles to console http://postgres.cz/wiki/Pretty_borders_in_psql Thank you. And really a great patch to psql console lover's... :) http://postgres.cz/wiki/Enhanced-psql Is this also compatible to 9.1 ? no, this was prepared for 8.4. These features was just experiment and only smaller subset is in core now. Regards Pavel --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.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] Naming conventions
On Wed, May 16, 2012 at 10:08:03AM +0100, Raymond O'Donnell wrote: On 15/05/2012 22:50, Scott Briggs wrote: So this is purely anecdotal but I'm curious, what's with all the different naming conventions? There's psql (for database connections), pgsql (used for some dirs like /usr/pgsql-9.1 and this mailing list), postgres (user and other references), and postgresql (startup scripts). Uh, the original Berkeley project name was postgres, pgsql is used as a short-hand for PostgreSQL, and psql is short for 'PostgreSQL SQL Monitor'. ... and don't forget (maybe oddest of all) libpq! :-) libpq originally supported Postgres QUEL: http://en.wikipedia.org/wiki/QUEL_query_languages -- 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] missing pg_clog files after pg_upgrade
On Wed, May 16, 2012 at 01:27:18PM +0200, Christian J. Dietrich wrote: Hey all, I have a problem which I speculate to be due to the pg_upgrade bug [1]: ERROR: could not access status of transaction 13636 DETAIL: could not open file pg_clog/: No such file or directory The pg_clog directory contains files with names in the range from 004A to 0105. 004A dates January 2012, 0105 is as of today. my version of postgresql is 9.1.3-1PGDG.rhel6.x86_64 running on CentOS 6.2. We pg_upgraded from 8.4.2 to 9.0.2 (Feb 2011) and then to 9.1.1 (Oct 2011). Unfortunately, I do not have backups of the pg_clog before upgrading to 9.1. I immediately stopped the database when encountering these errors above. To me (naive) it looks like the pg_clog file names wrapped around and now start to re-use from on (which does not exist). Is there any chance I can fix this (and avoid data loss)? I did not apply the VACUUM FREEZE fix suggested in [1] because I could not restore missing pg_clog files. What can I do? Thanks in advance, Chris [1]: http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix Sorry, I don't know of any solution to this except perhaps creating all-committed clog files to match the missing file. -- 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] enhanced linestyles for psql
http://postgres.cz/wiki/Enhanced-psql Is this also compatible to 9.1 ? no, this was prepared for 8.4. These features was just experiment and only smaller subset is in core now. Oh ok. Thank you for the info. Multi-Headers, Macros of psql 8.4 were very interesting features, sustaining them in 9.1 would have matured psql console alot. --Raghav
Re: [GENERAL] enhanced linestyles for psql
2012/5/25 Raghavendra raghavendra@enterprisedb.com: http://postgres.cz/wiki/Enhanced-psql Is this also compatible to 9.1 ? no, this was prepared for 8.4. These features was just experiment and only smaller subset is in core now. Oh ok. Thank you for the info. Multi-Headers, Macros of psql 8.4 were very interesting features, sustaining them in 9.1 would have matured psql console alot. I believe so some features - like advanced formatting will be accepted sometime. Macros are little bit different story - it is relative difficult implement them in current psql, because code is complex - there are unclean and unreadable support of readline and interactive mode. I had a idea of noninteractive console, that can support macros - but there are lot of issues still. I would to work on some better integration between client and server inlined procedures, and on simpler using psql from bash. Implementing own full macro language is not necessary. Probably better way is enable extending console statements with possibility to call external scripts. Regards Pavel --Raghav -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
