Re: [GENERAL] Installed. Now what?
1. Do I need to set up the /etc/pgbouncer.ini.rpmnew as /etc/pgbouncer.ini and then change settings in it? What do I change? How? The FAQ is super geeky and unhelpful. As is the sparse info on the PG Wiki on pgbouncer. How can I tune pgbouner settings? Just a quick update. By googling for an hour, I basically set up a working ini file. It looks like this: [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid ; ip address or * which means all ip-s listen_addr = 127.0.0.1 listen_port = 6543 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt admin_users = postgres stats_users = stats, root pool_mode = session server_reset_query = DISCARD ALL ;;; Connection limits ; total number of clients that can connect max_client_conn = 100 default_pool_size = 20 So now pgbouncer basically starts. Both processes are running (psql and pgbouncer) -- service postgres start service pgbouncer start When the two services are started like the above, are they working together? The manual says psql should be restarted with the pgbouncer port number, for these to be working together. But what if my server does not have a psql process, but a service of postgres? From within my PHP code, if I add the port number of pgbouncer in my pg_connect() function, it does not work. Thanks for any insight. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 9.0.4 replication issue: FATAL: requested WAL segment 0000000100000B110000000D has already been removed
Hi,
Database streaming is not taking place. The WAL segment that slave is
looking for does not exist on Master.
Both Master and Slave are EC2 instances with Postgres version 9.0.04 and
Ubuntu 10.04. As per my understanding, DB replication was stalled for
around 3 months. On Master new 16 MB WAL is created in every 2-5 minutes.
For replication, I am following link:
http://wiki.postgresql.org/wiki/Streaming_Replication
I am also referring:
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html
http://www.depesz.com/index.php/2010/03/11/setting-wal-replication/
Before starting backup, I ensured the following:
- On Slave I cleared contents of 'pg_xlog/*'.
- Both master and Slave have following in postgresql.conf:
wal_level = archive
hot_standby = off
- In postgresql.conf master has:
max_wal_senders = 5
wal_keep_segments = 10
- On slave recovery.conf has following 3 parameters:
standby_mode = 'on'
primary_conninfo = 'host=10.218.61.143 port=5432 user=postgres'
trigger_file = '/data/db/trigger_failover'
I used following commands for backup. And as soon as backup finished, I
immediately started postgres on Slave.
psql -c SELECT pg_start_backup('label', true);
rsync -av --progress /data/db/main/ 10.40.89.9:/data/db/main/ --exclude
'pg_log/*' --exclude 'pg_xlog/*' --exclude postmaster.pid --exclude
pg_hba.conf --exclude postgresql.conf;
psql -c SELECT pg_stop_backup();
On Slave I see following process running:
$ ps -ef | grep postgres
postgres 1895 1 0 Nov18 ?00:00:00
/usr/lib/postgresql/9.0/bin/postgres -D /data/db/main -c
config_file=/etc/postgresql/9.0/main/postgresql.conf
postgres 1896 1895 0 Nov18 ?00:00:00 postgres: startup process
waiting for 00010B11000D
On Slave, log showd that it is unable to find the requested WAL segment
$ tail /var/log/postgresql/postgresql-9.0-main.log
2011-11-19 07:09:50 UTC LOG: streaming replication successfully connected
to primary
2011-11-19 07:09:50 UTC FATAL: could not receive data from WAL stream:
FATAL: requested WAL segment 00010B11000D has already been
removed
I confirmed that requested WAL segment 00010B11000D doesn't
exist on Master.
On Master, process listing shows:
$ ps -ef | grep postgres
postgres 25395 25389 0 Nov14 ?00:00:06 postgres: archiver
process last was 00010B1F0081
Log on master also indicate that requested WAL segment was removed:
$ tail postgresql-2011-11-18_221110.csv
2011-11-18 23:15:01.355
PST,postgres,,20523,10.40.89.9:46157,4ec75775.502b,1,authentication,2011-11-18
23:15:01 PST,5/703238,0,LOG,0,replication connection authorized:
user=postgres host=10.40.89.9 port=46157,
2011-11-18 23:15:01.356
PST,postgres,,20523,10.40.89.9:46157,4ec75775.502b,2,startup,2011-11-18
23:15:01 PST,5/0,0,FATAL,58P01,requested WAL segment
00010B11000D has already been removed,
On Slave, I even tried deleting everything under /data/db/main, and took
backup again but the issue still persists.
It seems it is not an issue because slow Slave is not able to catch to
master. Because,
1) This happens as soon as Slave DB is started. So slave doesn't even get
the first WAL file.
2) Both machines are in same zone of EC2 and backup happens at fairly good
speed. So network connectivity issues are also ruled out.
I searched on various forums, where people encountered similar error,
however in all such issues WAL file existed on Master. In this case Master
is not retaining the WAL file required by the Slave.
I am unable to understand as to why Master is not retaining the WAL files.
Any pointer/suggestions would be helpful.
Thanks for attention.
Ashish
Re: [GENERAL] How to install latest stable postgresql on Debian
On 18/11/2011 23:58, Andrus wrote: How did you uninstall 8.4? From below it would seem it is still around. Thank you. After adding -t switch to apt-get I was able to install 9.1. To start it I invoked /etc/init.d/postgresql manually. How to force it to start after server is rebooted automatically ? IIRC, when you install it via apt-get it should be set to start automatically - I could be wrong, it's been a while. To check whether this is the case, look in the appropriate /etc/rc*.d directory for the runlevel you're using, and see if there is an S symlink to /etc/init.d/postgresql-9.1 (or something like that) in there. 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
[GENERAL] Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
I just upgraded from Fedora 14 to Fedora 15. (Which is Postgres 8.4.? to
9.0.5)
I tried starting Postgres, and then as directed, I installed the
postgresql-upgrade package. (My postgresql packages are all current, at
version 9.0.5-1.fc15.x86_64)
I then ran service postgresql upgrade, which chugged away for a while,
and then failed, with this in the log:
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster
psql:/var/lib/pgsql
/pg_upgrade_dump_db.sql:4333: ERROR: column t.tgisconstraint does not
exist
LINE 2: ...RE ((t.tgname ~ '_alert_notify$'::text) AND ((NOT t.tgiscons...
So upon closer inspection I've got two statements in my databases that
reference tgisconstraint. My 2 questions are:
1) Can anyone suggest equivalent PG9 replacement for those statements, or
at least give me some hints?
CREATE VIEW alert_notify_enabled_objects AS
SELECT replace((cc.relname)::text, 'tbl_'::text, ''::text) AS
alert_object_code, initcap(replace(replace((cc.relname)::text,
'tbl_'::text, ''::text), '_'::text, ' '::text)) AS description FROM
(pg_trigger t LEFT JOIN pg_class cc ON ((t.tgrelid = cc.oid))) WHERE
((t.tgname ~ '_alert_notify$'::text) AND ((NOT t.tgisconstraint) OR (NOT
(EXISTS (SELECT 1 FROM (pg_depend d JOIN pg_constraint c ON (((d.refclassid
= c.tableoid) AND (d.refobjid = c.oid WHERE d.classid = t.tableoid)
AND (d.objid = t.oid)) AND (d.deptype = 'i'::char)) AND (c.contype =
'f'::char)));
CREATE VIEW table_log_enabled_tables AS
SELECT cc.relname AS table FROM (pg_trigger t LEFT JOIN pg_class cc
ON ((t.tgrelid = cc.oid))) WHERE ((t.tgname ~ '_log_chg$'::text) AND ((NOT
t.tgisconstraint) OR (NOT (EXISTS (SELECT 1 FROM (pg_depend d JOIN
pg_constraint c ON (((d.refclassid = c.tableoid) AND (d.refobjid =
c.oid WHERE d.classid = t.tableoid) AND (d.objid = t.oid)) AND
((d.deptype)::text = ('i'::character(1))::text)) AND ((c.contype)::text =
('f'::character(1))::text)));
and,
2) Assuming I get the offending statements fixed and edit the
pg_upgrade_dump_db.sql, is there a way to continue the postgresql-upgrade
process?
Thanks in advance!
Ken Tanzer
Re: [GENERAL] Postgres 9.0.4 replication issue: FATAL: requested WAL segment 0000000100000B110000000D has already been removed
Hi, On 19 Listopad 2011, 10:44, Ashish Gupta wrote: I searched on various forums, where people encountered similar error, however in all such issues WAL file existed on Master. In this case Master is not retaining the WAL file required by the Slave. I am unable to understand as to why Master is not retaining the WAL files. Any pointer/suggestions would be helpful. Thanks for attention. The cause is very simple - the standby needs all WAL segments created since the backup started, but the master removes some of them. There are two ways to fix this: 1) increase the wal_keep_segments so that enough segments is kept It seems that the slave asked for B11000D when master already created B11000D. That's almost 4000 segments if I'm counting correctly. That means your database is either quite busy or the backup takes very long time. This stores all the data on master, so you'll have to keep that in mind when planning the capacity. For example the 4000 segments are almost 64GB. 2) Set up a WAL archive - a separate instance where the WAL segments are kept. See how the archive_command works. And there's pg_archivecleanup for maintenance of the archive. Tomas -- 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 install latest stable postgresql on Debian
To check whether this is the case, look in the appropriate /etc/rc*.d directory for the runlevel you're using, and see if there is an S symlink to /etc/init.d/postgresql-9.1 (or something like that) in there. Thank you. There are S18postgresql symlinks in may rc?.d directories. They seems to be version independent so hopefully server is started on boot. Should shared_buffers and other values changed from installations defaults in postgresql.conf file to increase performance ? How to run enterprice db tuner or other utility to chenge them automatically ? Virtual machine seems to have 2 GB of ram. Andrus. -- 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] Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
Ken Tanzer ken.tan...@gmail.com writes: 1) Can anyone suggest equivalent PG9 replacement for those statements, or at least give me some hints? Per http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=9a915e596 I also replaced the tgisconstraint column with tgisinternal; the old meaning of tgisconstraint can now be had by testing for nonzero tgconstraint, while there is no other way to get the old meaning of nonzero tgconstraint, namely that the trigger was internally generated rather than being user-created. It's not real clear to me whether your views actually want tgconstraint = 0, which would be the exact translation, or NOT tgisinternal, which might be a closer approximation to their intention. 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] Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
Not being the author of that view, I confess some ignorance of pg
internals, and just what the intended nuance was.
As a little more explanation, the view is meant to list all the tables that
have a trigger ending in _alert_notify, as created per this function:
CREATE OR REPLACE FUNCTION alert_notify_enable(varchar,varchar) RETURNS
boolean AS $$
if {[info exists 1]} {
set TABLE $1
} else {
elog ERROR no table passed to alert_notify()
return false
}
if {[info exists 2]} {
set CUSTOM_COLUMN $2
} else {
set CUSTOM_COLUMN
}
set cre_exec CREATE TRIGGER ${TABLE}_alert_notify
AFTER INSERT OR UPDATE OR DELETE ON ${TABLE}
FOR EACH ROW EXECUTE PROCEDURE table_alert_notify(${CUSTOM_COLUMN})
spi_exec $cre_exec
return true
$$ LANGUAGE pltcl;
(The second view, about table_logs, is conceptually similar).
Here's the slightly more readable source for the view:
CREATE OR REPLACE VIEW alert_notify_enabled_objects AS
SELECT REPLACE(cc.relname,'tbl_','') AS alert_object_code,
INITCAP(REPLACE(REPLACE(cc.relname,'tbl_',''),'_',' ')) AS
description
FROM pg_catalog.pg_trigger t
LEFT JOIN pg_catalog.pg_class cc ON ( t.tgrelid = cc.oid )
WHERE t.tgname ~ '_alert_notify$'
AND (NOT tgisconstraint OR NOT EXISTS
(SELECT 1 FROM pg_catalog.pg_depend d
JOIN pg_catalog.pg_constraint c ON
(d.refclassid = c.tableoid AND d.refobjid = c.oid)
WHERE d.classid = t.tableoid AND d.objid = t.oid
AND d.deptype = 'i' AND c.contype = 'f')
);
If that clarifies the intention, please let me know! Also, what about
question #2--is there an easy/built-in way to edit the
pg_upgrade_dump_db.sql and continue the postgresql-upgrade process?
Thanks!
Ken
On Sat, Nov 19, 2011 at 7:44 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Ken Tanzer ken.tan...@gmail.com writes:
1) Can anyone suggest equivalent PG9 replacement for those statements,
or
at least give me some hints?
Per
http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=9a915e596
I also replaced the tgisconstraint column
with tgisinternal; the old meaning of tgisconstraint can now be had
by
testing for nonzero tgconstraint, while there is no other way to get
the old meaning of nonzero tgconstraint, namely that the trigger was
internally generated rather than being user-created.
It's not real clear to me whether your views actually want tgconstraint
= 0, which would be the exact translation, or NOT tgisinternal, which
might be a closer approximation to their intention.
regards, tom lane
[GENERAL] invalid byte sequence for encoding UTF8: 0x00
Hi, Is there any way I can store NULL character (\u) in string ? Or there is only one option that I have change every text field to bytea. Regards, Paweł -- View this message in context: http://postgresql.1045698.n5.nabble.com/invalid-byte-sequence-for-encoding-UTF8-0x00-tp5007173p5007173.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] Installed. Now what?
On Saturday, November 19, 2011 12:20:07 am Phoenix Kiula wrote: service postgres start service pgbouncer start When the two services are started like the above, are they working together? The manual says psql should be restarted with the pgbouncer port number, for these to be working together. But what if my server does not have a psql process, but a service of postgres? Not all that confusing. Clients talk to pgbouncer, which in turn talks to server. All the manual is saying is that you need to redirect your requests to the pgbouncer port from the Postgres port, using psql as an example. From within my PHP code, if I add the port number of pgbouncer in my pg_connect() function, it does not work. Did you take a look at: http://pgbouncer.projects.postgresql.org/doc/config.html I have never used pgbouncer, but from above it would seem you need to set up a [databases] section to tie pgbouncer to the Postgres server. See: SECTION [databases] Thanks for any insight. -- Adrian Klaver adrian.kla...@gmail.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] How to install latest stable postgresql on Debian
On Friday, November 18, 2011 3:58:26 pm Andrus wrote: How did you uninstall 8.4? From below it would seem it is still around. Thank you. After adding -t switch to apt-get I was able to install 9.1. To start it I invoked /etc/init.d/postgresql manually. How to force it to start after server is rebooted automatically ? free -g returns total used free sharedbuffers cached Mem: 2 0 2 0 0 0 -/+ buffers/cache: 0 2 Swap:3 0 3 Which parameters in postgresql.conf needs to be changed to work it as fast as posssible ? This is dedicated server. The ones marked greased lightning:) Sorry, could not resist. For tuning tips a good start is: http://wiki.postgresql.org/wiki/Performance_Optimization In particular: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server For books take a look at: http://www.postgresql.org/docs/books/ Andrus. -- Adrian Klaver adrian.kla...@gmail.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] How to install latest stable postgresql on Debian
Adrian, For tuning tips a good start is: http://wiki.postgresql.org/wiki/Performance_Optimization In particular: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server For books take a look at: http://www.postgresql.org/docs/books/ thank you very much. Reading them requires lot of time. I'm looking for quick optimization for 2 GB RAM. postgresql.conf contains shared_buffers = 24MB # min 128kB #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature #work_mem = 1MB # min 64kB #maintenance_work_mem = 16MB# min 1MB #max_stack_depth = 2MB # min 100kB Probably only few settings like shared_buffers needs adjusting for 2 gb In windows I use enterpicedb tuning wizard which does this automatically. Maybe something works in Debian also. Or this there quick guide how to change most important settings. Andrus. -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver adrian.kla...@gmail.com wrote: http://pgbouncer.projects.postgresql.org/doc/config.html I have never used pgbouncer, but from above it would seem you need to set up a [databases] section to tie pgbouncer to the Postgres server. See: SECTION [databases] Thanks Adrian. All this is done. The config file link just describes what each option means. There's zero information about how to actually tweak or wisely set the stuff! :( Anyway, with half a day of googling or so, and looking at sundry blogs and such, I have pgbouncer running on port 6432. PG runs on the usual 5432. I still keep seeing the Sorry, too many clients already error. From my PHP code, what line should I use? This does NOT work: $link = pg_connect(host=localhost dbname=$db user=$user password=$pass); If I remove the port number, it works. Is it then connecting straight to the DB? What am I missing? Pgbouncer is working, but not accepting PHP pg_connect() call. The username and password are correct for sure. Any thoughts? -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 2:39 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver adrian.kla...@gmail.com wrote: http://pgbouncer.projects.postgresql.org/doc/config.html I have never used pgbouncer, but from above it would seem you need to set up a [databases] section to tie pgbouncer to the Postgres server. See: SECTION [databases] Thanks Adrian. All this is done. The config file link just describes what each option means. There's zero information about how to actually tweak or wisely set the stuff! :( Anyway, with half a day of googling or so, and looking at sundry blogs and such, I have pgbouncer running on port 6432. PG runs on the usual 5432. I still keep seeing the Sorry, too many clients already error. From my PHP code, what line should I use? This does NOT work: $link = pg_connect(host=localhost dbname=$db user=$user password=$pass); If I remove the port number, it works. Is it then connecting straight to the DB? What am I missing? Pgbouncer is working, but not accepting PHP pg_connect() call. The username and password are correct for sure. Any thoughts? I mean this does not work: $link = pg_connect(host=localhost port=6432 dbname=$db user=$user password=$pass); When I remove that port number, it works. I suppose it connects directly to PG. And this is still leading to too many connections. Also, this does NOT work: psql snipurl -E snipurl_snipurl -p 6543 Shows me this error: psql: ERROR: no working server connection How come? The pgbouncer is on! ps aux | grep pgbouncer postgres 5567 0.0 0.0 16880 508 ?R13:50 0:00 pgbouncer -d /etc/pgbouncer.ini root 5583 0.0 0.0 61188 764 pts/2R+ 13:50 0:00 grep pgbouncer Any thoughts? How can I make my PHP connect to the pgbouncer? -- 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 could I find the last modified procedure in the database?
Try this: select proname,oid, xmin from pg_catalog.pg_proc order by xmin::text desc; regards Robert Bernier Andreas Kretschmer akretschmer(at)spamfence(dot)net writes: Dhimant Patel drp4kri(at)gmail(dot)com wrote: I also created several procedures/functions and now I don't remember the last procedure I worked on! - I thought I could always get this from metadata. Now I'm stuck - couldn't find this details anywhere in catalog tables! Is there anyway I can get this information? Maybe with this query: select proname from pg_proc order by oid desc limit 1; but i'm not really sure ... tias (try it and see) The OIDs would tell you the creation order, but they don't change during CREATE OR REPLACE FUNCTION; so depending on what the OP means by worked on, this query might not be very useful to him. I'd try looking to see which row in pg_proc has the latest xmin. Unfortunately you can't ORDER BY xmin ...
Re: [GENERAL] How could I find the last modified procedure in the database?
Correction, try this: select proname,oid, xmin from pg_catalog.pg_proc order by xmin::text::int desc; regards Robert Bernier Andreas Kretschmer akretschmer(at)spamfence(dot)net writes: Dhimant Patel drp4kri(at)gmail(dot)com wrote: I also created several procedures/functions and now I don't remember the last procedure I worked on! - I thought I could always get this from metadata. Now I'm stuck - couldn't find this details anywhere in catalog tables! Is there anyway I can get this information? Maybe with this query: select proname from pg_proc order by oid desc limit 1; but i'm not really sure ... tias (try it and see) The OIDs would tell you the creation order, but they don't change during CREATE OR REPLACE FUNCTION; so depending on what the OP means by worked on, this query might not be very useful to him. I'd try looking to see which row in pg_proc has the latest xmin. Unfortunately you can't ORDER BY xmin ...
Re: [GENERAL] Huge number of INSERTs
On Fri, Nov 18, 2011 at 10:41 AM, Tomas Vondra t...@fuzzy.cz wrote: That has nothing to do with the inserts, it means the number of connection requests exceeds the max_connections. You've set it to 350, and that seems too high - the processes are going to struggle for resources (CPU, I/O and memory) and the scheduling gets expensive too. A good starting point is usually 2*(number of cores + number of drives) which is 16 or 24 (not sure what a dual server is - probably dual CPU). You may increase that if the database more or less fits into memory (so less I/O is needed). Ok, there's just too much conflicting info on the web. If I reduce the max_connections to 16, how does this reflect on the Apache MaxClients? There's a school of thought that recommends that MaxClients in Apache should be the same as max_connection in PGSQL. But 16 for MaxClients with a prefork MPM would be disastrous. No? Anyway, even if I do try 16 as the number, what about these settings: work_mem shared_buffers effective_cache_size With nginx and apache, and some other sundries, I think about 4-5GB is left for PGSQL. This is 9.0.5. And all other details - Centos 5 on 64 bit, SCSI disks with RAID 10, 3Ware RAID controller...etc. Any help on settings appreciated. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Logging not working
Hi. PG 9.0.5, on CentOS 5 with 64 bit. Here's the logging related items from my config file: log_directory = 'pg_log' log_filename= 'pglog.postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age= 1d log_min_duration_statement = 5000 # In milliseconds That's it. The directory /var/lib/pgsql/data/pg_log is chowned by postgres:postgres. And it's even chmodded 777, just to be sure that anyone can write to it. (For now) Why's there no log then? What can I do? Thanks! -- 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] Installed. Now what?
On Saturday, November 19, 2011 10:39:42 am Phoenix Kiula wrote: On Sun, Nov 20, 2011 at 2:13 AM, Adrian Klaver adrian.kla...@gmail.com wrote: http://pgbouncer.projects.postgresql.org/doc/config.html I have never used pgbouncer, but from above it would seem you need to set up a [databases] section to tie pgbouncer to the Postgres server. See: SECTION [databases] Thanks Adrian. All this is done. Well in the .ini file you posted there is no [databases] section. From what I read lack of one would explain the problem you are seeing. -- Adrian Klaver adrian.kla...@gmail.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] Logging not working
On 19 Listopad 2011, 20:29, Phoenix Kiula wrote: Hi. PG 9.0.5, on CentOS 5 with 64 bit. Here's the logging related items from my config file: log_directory = 'pg_log' log_filename= 'pglog.postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age= 1d log_min_duration_statement= 5000 # In milliseconds That's it. The directory /var/lib/pgsql/data/pg_log is chowned by postgres:postgres. And it's even chmodded 777, just to be sure that anyone can write to it. (For now) Why's there no log then? What can I do? What about log_destination and log_collector? Tomas -- 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 install latest stable postgresql on Debian
On Saturday, November 19, 2011 10:36:37 am Andrus wrote: Adrian, For tuning tips a good start is: http://wiki.postgresql.org/wiki/Performance_Optimization In particular: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server For books take a look at: http://www.postgresql.org/docs/books/ thank you very much. Reading them requires lot of time. I'm looking for quick optimization for 2 GB RAM. postgresql.conf contains shared_buffers = 24MB # min 128kB #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature #work_mem = 1MB # min 64kB #maintenance_work_mem = 16MB# min 1MB #max_stack_depth = 2MB # min 100kB Probably only few settings like shared_buffers needs adjusting for 2 gb In windows I use enterpicedb tuning wizard which does this automatically. Maybe something works in Debian also. Or this there quick guide how to change most important settings. From here(look familiar): http://wiki.postgresql.org/wiki/Performance_Optimization http://linuxfinances.info/info/quickstart.html http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm Andrus. -- Adrian Klaver adrian.kla...@gmail.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] How to install latest stable postgresql on Debian
On Saturday, November 19, 2011 5:34:59 am Andrus wrote: To check whether this is the case, look in the appropriate /etc/rc*.d directory for the runlevel you're using, and see if there is an S symlink to /etc/init.d/postgresql-9.1 (or something like that) in there. Thank you. There are S18postgresql symlinks in may rc?.d directories. They seems to be version independent so hopefully server is started on boot. I have found sysv-rc-conf to be useful. dpkg -l|grep sysv-rc-conf will determine if it is already installed apt-get install sysv-rc-conf otherwise When run (as root) with no arguments it will show what services are available and which ones are configured to stop and start. You can also give it a service name and either turn that service on or off. Andrus. -- Adrian Klaver adrian.kla...@gmail.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] How to install latest stable postgresql on Debian
From here(look familiar): http://wiki.postgresql.org/wiki/Performance_Optimization http://linuxfinances.info/info/quickstart.html http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm Thank you. I tried # sysctl -w kernel.shmmax=419430400 kernel.shmmax = 419430400 # sysctl -n kernel.shmmax 419430400 according to http://www.postgresql.org/docs/current/static/kernel-resources.html this value should be written to /etc/sysctl.conf I opened /etc/sysctl.conf but it does not contain this value. How to make this setting persistent ? Andrus. -- 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] Huge number of INSERTs
On 19 Listopad 2011, 20:27, Phoenix Kiula wrote: On Fri, Nov 18, 2011 at 10:41 AM, Tomas Vondra t...@fuzzy.cz wrote: That has nothing to do with the inserts, it means the number of connection requests exceeds the max_connections. You've set it to 350, and that seems too high - the processes are going to struggle for resources (CPU, I/O and memory) and the scheduling gets expensive too. A good starting point is usually 2*(number of cores + number of drives) which is 16 or 24 (not sure what a dual server is - probably dual CPU). You may increase that if the database more or less fits into memory (so less I/O is needed). Ok, there's just too much conflicting info on the web. If I reduce the max_connections to 16, how does this reflect on the Apache MaxClients? There's a school of thought that recommends that MaxClients in Apache should be the same as max_connection in PGSQL. But 16 for MaxClients with a prefork MPM would be disastrous. No? See, the max_connections is the maximum number of allowed connections. So if there are 16 open connections and someone asks for another one, he'll receive too many clients. So decreasing the max_connections without MaxClients would make your problem even worse. I'm not sure about the Apache prefork worker - why are you using it instead the threaded worker? Anyway as I asked before, do you have a proof the current MaxClient value provides the best performance? It seems to me you've just used some very high values in belief that it will give better performance. Have you performed some stress test to verify the settings. I'm not saying you should set MaxClients to 16, but 350 probably is too high? But if you can't set MaxClients to the same value as max_connections (or actually a bit lower, because there are connections reserved for superuser etc.), that's exactly the proof that you need a pooler - see pgbouncer. Anyway, even if I do try 16 as the number, what about these settings: work_mem shared_buffers effective_cache_size With nginx and apache, and some other sundries, I think about 4-5GB is left for PGSQL. This is 9.0.5. And all other details - Centos 5 on 64 bit, SCSI disks with RAID 10, 3Ware RAID controller...etc. The phrase I think suggests that you actually don't know how much memory is available. Suggestions with this number of components are going to be a bit unreliable. Can you post a few lines of vmstat 5 and free? The recommended shared_buffers size is usually 25% of RAM, that's about 1GB of RAM. I see you've originally set it to 256MB - have you checked the cache hit ratio, i.e. how many requests were resolved using the cache? SELECT datname, (100 * blks_hit) / (blks_hit + blks_read + 1) hit_ratio FROM pg_stat_database; Again, this is a point where an application benchmark would really help. What is the database size, anyway? It's difficult to recommend a work_mem without deeper knowledge of your queries and how much memory is available. Using less connections actually allows you to grant more memory to each of them, i.e. setting higher work_mem. Say you have 1GB available, 20 connections - that's about 50MB per connection. The work_mem is actually per node, so if a query sorts twice it may allocate 2x work_mem etc. So a conservative estimate would be work_mem=20MB or something, so that even if all the connections start sorting at the same time you won't get OOM. But is that really enough or too much for your queries? I have no idea. I recommend to set a conservative work_mem value (e.g. 4MB), log slow queries and check if they'd benefit from higher work_mem values. Regarding the effective_cache_size - this is just a hint how much data might be cached. What does free says about the cache size? I see you've decreased the size from 1500MB to 1400MB on (Nov 11 2011). Why? Tomas -- 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 install latest stable postgresql on Debian
2011/11/19 Andrus kobrule...@hot.ee: From here(look familiar): http://wiki.postgresql.org/wiki/Performance_Optimization http://linuxfinances.info/info/quickstart.html http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm Thank you. I tried # sysctl -w kernel.shmmax=419430400 kernel.shmmax = 419430400 # sysctl -n kernel.shmmax 419430400 according to http://www.postgresql.org/docs/current/static/kernel-resources.html this value should be written to /etc/sysctl.conf I opened /etc/sysctl.conf but it does not contain this value. How to make this setting persistent ? Just add them to the bottom then run sudo sysctl -p to make them take effect. btw, the standard way to control rc stuff is update-rc.d Odd name but it's pretty easy, just look up the man page. -- 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] Is it ever necessary to vacuum a table that only gets inserts/updates?
On 19/11/11 11:32, Adam Cornett wrote: On Fri, Nov 18, 2011 at 2:56 PM, Gavin Flower gavinflo...@archidevsys.co.nz mailto:gavinflo...@archidevsys.co.nz wrote: On 18/11/11 04:59, Tom Lane wrote: Craig Ringerring...@ringerc.id.au mailto:ring...@ringerc.id.au writes: On Nov 17, 2011 1:32 PM, Tom Lanet...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us wrote: If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and not any VACUUMs. Won't a VACUUM FREEZE (or autovac equivalent) be necessary eventually, to handle xid wraparound? Sure, but if he's continually adding new rows, I don't see much point in launching extra freeze operations. regards, tom lane Just curious... Will the pattern of inserts be at all relevant? For example random inserts compared to apending records. I thought that random inserts would lead to bloat, as there would be lots of blocks far from the optimum fill factor. Regards, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I might be wrong (I'm sure Tom will correct me if so), but Postgres does not store tuples in an ordered format on disk, they are on disk in the order they are inserted, unless the table is re-ordered by cluster http://www.postgresql.org/docs/current/interactive/sql-cluster.html, which only does a one time sort. Table bloat (and the table fill factor) are usually associated with deletes and updates. If you delete a row, or update it so that it takes up less room (by say removing a large text value) then postgres could use the now free space on that page to store a new tuple. -Adam HI Adam, I suspect that you are right - noiw I come to think of it- I think I got caught out by the ghost of VSAM creeping up on me )You seriously do NOT want to know about IBM's VSAM!). Regards, Gavin
Re: [GENERAL] Huge number of INSERTs
On Sat, Nov 19, 2011 at 1:29 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 11/18/2011 04:30 AM, Phoenix Kiula wrote: On Fri, Nov 18, 2011 at 9:25 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: Database only? Or is it also your webserver? It's my webserver and DB. Webserver is nginx, proxying all PHP requests to apache in the backend. You still didn't answer what massive traffic means. Thousands of website hits per minute. (At peak time) Average is a few hundred per minute. 3Com? Perhaps you mean 3Ware? And (again) what are the RAID cache *settings*? In particular, the write-back/write-through setting. Yes 3Ware. RAID cache settings: -- Logical device information -- Logical device number 0 Logical device name : RAID10-A RAID level : 10 Status of logical device : Optimal Size : 1906678 MB Stripe-unit size : 256 KB Read-cache mode : Enabled MaxIQ preferred cache setting : Disabled MaxIQ cache setting : Disabled Write-cache mode : Enabled (write-back) Write-cache setting : Enabled (write-back) when protected by battery/ZMM Partitioned : Yes Protected by Hot-Spare : No Bootable : Yes Failed stripes : No Power settings : Disabled Logical device segment information Group 0, Segment 0 : Present (0,0) 9QJ00FMB Group 0, Segment 1 : Present (0,1) 9QJ1R3NW Group 1, Segment 0 : Present (0,2) 9QJ00L58 Group 1, Segment 1 : Present (0,3) 9QJ01JJ5 So most of your selects aren't hitting the database. Since we are talking db tuning, it would have been nice to know how many queries are hitting the database, not the number of requests hitting the webserver. But the question was what is the typical duration of the queries - specifically the queries hitting the database. Right now single SELECTs with just that one WHERE indexed_column = 'Value' LIMIT 1 type queries are taking 3.0 of CPU, and so on. Why should these queries be taking so much time and resources? Earlier you said you were doing 200 inserts/minute. Is that an average throughout the day or is that at peak time. Peak load is really what is of interest. 200 inserts/minute is not even 4/second. As above. Look at your log. If it isn't set to record request time, set it to do so. I set my Apache servers to log request time in microseconds. Could you specify how precisely you have set up this log? Through CustomLog? Thanks! Thanks! -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@gmail.com wrote: Well in the .ini file you posted there is no [databases] section. From what I read lack of one would explain the problem you are seeing. Yes. Because that's private to post on a public mailing list like this. Here's my INI file below, with the private DB name etc sanitizes -- and trust me, all info related to password and ports is absolutely correctly entered. Both pgbouncer and postgresql are live and running. Just that pg_connect() function in PHP is not working if I point is to pgbouncer's port instead of the direct postgresql port. [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 ;; Configuation section [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid ; ip address or * which means all ip-s listen_addr = 127.0.0.1 listen_port = 6543 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt admin_users = postgres stats_users = stats, root pool_mode = session server_reset_query = DISCARD ALL ;;; Connection limits ; total number of clients that can connect max_client_conn = 1500 default_pool_size = 50 -- 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] Is it ever necessary to vacuum a table that only gets inserts/updates?
On Sat, Nov 19, 2011 at 12:53 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 19/11/11 11:32, Adam Cornett wrote: On Fri, Nov 18, 2011 at 2:56 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 18/11/11 04:59, Tom Lane wrote: Craig Ringerring...@ringerc.id.au writes: On Nov 17, 2011 1:32 PM, Tom Lanet...@sss.pgh.pa.us wrote: If it's purely an insert-only table, such as a logging table, then in principle you only need periodic ANALYZEs and not any VACUUMs. Won't a VACUUM FREEZE (or autovac equivalent) be necessary eventually, to handle xid wraparound? Sure, but if he's continually adding new rows, I don't see much point in launching extra freeze operations. regards, tom lane Just curious... Will the pattern of inserts be at all relevant? For example random inserts compared to apending records. I thought that random inserts would lead to bloat, as there would be lots of blocks far from the optimum fill factor. Regards, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I might be wrong (I'm sure Tom will correct me if so), but Postgres does not store tuples in an ordered format on disk, they are on disk in the order they are inserted, unless the table is re-ordered by cluster, which only does a one time sort. Table bloat (and the table fill factor) are usually associated with deletes and updates. If you delete a row, or update it so that it takes up less room (by say removing a large text value) then postgres could use the now free space on that page to store a new tuple. -Adam HI Adam, I suspect that you are right - noiw I come to think of it- I think I got caught out by the ghost of VSAM creeping up on me )You seriously do NOT want to know about IBM's VSAM!). Careful, on a list with as many old timers as this one, you may be sending that message to the guy who wrote the original implementation. :) I only go as far back as Rexx and JCL and RBase 5000, but never used VSAM. ISAM yes. -- 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] Installed. Now what?
On Saturday, November 19, 2011 2:44:04 pm Phoenix Kiula wrote: On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@gmail.com wrote: Well in the .ini file you posted there is no [databases] section. From what I read lack of one would explain the problem you are seeing. Yes. Because that's private to post on a public mailing list like this. Here's my INI file below, with the private DB name etc sanitizes -- and trust me, all info related to password and ports is absolutely correctly entered. Both pgbouncer and postgresql are live and running. Just that pg_connect() function in PHP is not working if I point is to pgbouncer's port instead of the direct postgresql port. I would first work on establishing that psql works. From a previous post: Also, this does NOT work: psql snipurl -E snipurl_snipurl -p 6543 Shows me this error: psql: ERROR: no working server connection How come? The pgbouncer is on! Not sure what platform you are on but: http://www.postgresql.org/docs/9.0/interactive/app-psql.html Not all of these options are required; there are useful defaults. If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets You have pgbouncer listening on 127.0.0.1. In your psql connection string you are not specifying a host, so if you are on a Unix platform it is trying to connect to a socket which would account for the error. I found when working with new software explicit is better than implicit. Eliminate possible sources of error by fully qualifying everything. -- Adrian Klaver adrian.kla...@gmail.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] Logging not working
On Sun, Nov 20, 2011 at 3:38 AM, Tomas Vondra t...@fuzzy.cz wrote: What about log_destination and log_collector? Thanks. This was it! Much 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] Installed. Now what?
On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@gmail.com wrote:snip [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 ;; Configuation section [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid ; ip address or * which means all ip-s listen_addr = 127.0.0.1 listen_port = 6543 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt admin_users = postgres stats_users = stats, root pool_mode = session server_reset_query = DISCARD ALL ;;; Connection limits ; total number of clients that can connect max_client_conn = 1500 default_pool_size = 50 I am assuming the difference in the port numbers between your config file and php code is a typing error. Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login credentials to your database? If I remember correctly, it should have the username and password to your database. Amitabh
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 8:08 AM, Adrian Klaver adrian.kla...@gmail.com wrote: You have pgbouncer listening on 127.0.0.1. In your psql connection string you are not specifying a host, so if you are on a Unix platform it is trying to connect to a socket which would account for the error. I found when working with new software explicit is better than implicit. Eliminate possible sources of error by fully qualifying everything. Thanks for bearing. Specifying the host is not it. psql -h 127.0.0.1 MYDB -E MYDB_MYDB -p 6543 psql: ERROR: no working server connection ps aux | grep pgbou postgres 5567 0.0 0.0 17096 960 ?S13:50 0:00 pgbouncer -d /etc/pgbouncer.ini root 24437 0.0 0.0 61192 788 pts/0S+ 21:31 0:00 grep pgbou In the /var/log/pgbouncer.log I see a message about failing password. The pgbouncer password in the auth_file, does it need to be plain text? Auth_type in my case is trust. Do I need to md5 the password? -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 10:33 AM, Amitabh Kant amitabhk...@gmail.com wrote: I am assuming the difference in the port numbers between your config file and php code is a typing error. Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login credentials to your database? If I remember correctly, it should have the username and password to your database. Port numbers are correct. Auth_file has text in this format: username password in plain text username2 password2 in plain text .. Is this incorrect? -- 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] Installed. Now what?
On Saturday, November 19, 2011 6:35:11 pm Phoenix Kiula wrote: On Sun, Nov 20, 2011 at 8:08 AM, Adrian Klaver adrian.kla...@gmail.com wrote: You have pgbouncer listening on 127.0.0.1. In your psql connection string you are not specifying a host, so if you are on a Unix platform it is trying to connect to a socket which would account for the error. I found when working with new software explicit is better than implicit. Eliminate possible sources of error by fully qualifying everything. Thanks for bearing. Specifying the host is not it. psql -h 127.0.0.1 MYDB -E MYDB_MYDB -p 6543 psql: ERROR: no working server connection I don't see a user specified. You sure you are connecting as correct user? Remember absent a -U the user will be either your system user name or what is specified in a ENV variable. ps aux | grep pgbou postgres 5567 0.0 0.0 17096 960 ?S13:50 0:00 pgbouncer -d /etc/pgbouncer.ini root 24437 0.0 0.0 61192 788 pts/0S+ 21:31 0:00 grep pgbou In the /var/log/pgbouncer.log I see a message about failing password. The pgbouncer password in the auth_file, does it need to be plain text? Auth_type in my case is trust. Do I need to md5 the password? According to docs: http://pgbouncer.projects.postgresql.org/doc/config.html#_generic_settings auth_type How to authenticate users. md5: Use MD5-based password check. auth_file may contain both MD5-encrypted or plain-text passwords. This is the default authentication method. crypt Use crypt(3) based password check. auth_file must contain plain-text passwords. plain Clear-text password is sent over wire. trust No authentication is done. Username must still exist in auth_file. any Like the trust method, but the username given is ignored. Requires that all databases are configured to log in as specific user. Additionally, the console database allows any user to log in as admin. -- Adrian Klaver adrian.kla...@gmail.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] Installed. Now what?
On Sun, Nov 20, 2011 at 10:37 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 10:33 AM, Amitabh Kant amitabhk...@gmail.com wrote: I am assuming the difference in the port numbers between your config file and php code is a typing error. Does the auth file (/var/lib/pgsql/pgbouncer.txt) contain valid login credentials to your database? If I remember correctly, it should have the username and password to your database. Port numbers are correct. Auth_file has text in this format: username password in plain text username2 password2 in plain text .. Is this incorrect? I just did some testing. If the password is wrong, then it shows me the authentication failed message right in the terminal window, immediately. If the password is correct (plain text or md5 of that plain text -- both have similar requests), it shows me the second error no working connection below. [host] psql -h 127.0.0.1 MYDB -E MYDB_MYDB -p 6543psql: ERROR: password authentication failed for user MYDB_MYDB[coco] ~ [coco] ~ pico /var/lib/pgsql/pgbouncer.txt [host] ~ psql -h 127.0.0.1 MYDB -E MYDB_MYDB -p 6543 psql: ERROR: no working server connection But in the second case, the error in the pgbouncer log is the same -- authentication is failing. Why this inconsistent and utterly inane behavior from pgbouncer? Why can't we see transparently what the error is? Nowhere in the docs does it clearly specify with an example how the auth_file format should be. Any pointers please? I'm fresh out of google keywords to search for, two days later. Thank you! -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 10:51 AM, Adrian Klaver adrian.kla...@gmail.com wrote: I don't see a user specified. You sure you are connecting as correct user? Remember absent a -U the user will be either your system user name or what is specified in a ENV variable. Adrian, all this is not helping. To be sure, I tried this. Hope this command is MUCH simpler and puts this to rest: psql --host=127.0.0.1 --dbname=MYDB --username=MYDB_MYDB --port=6543 psql: ERROR: no working server connection tail -4 /var/log/pgbouncer.log 2011-11-19 22:16:49.139 26439 WARNING server login failed: FATAL password authentication failed for user MYDB_MYDB 2011-11-19 22:16:49.139 26439 LOG S-0x15b61fe0: MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0) 2011-11-19 22:17:13.490 26439 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us Please note that the word MYDB is a replacement of my private actual word. As you can see, the password is failing. I have read the segment of the manual you copy pasted, of course. I have auth_type = any auth_file = /var/lib/pgsql/pgbouncer.txt I have tried trust and md5 too. Same results as previously posted. Just for convenience, here's how the file looks: cat /var/lib/pgsql/pgbouncer.txt MYDB_MYDB mypassword here Anything else? -- 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] Huge number of INSERTs
Dne 19.11.2011 23:34, Phoenix Kiula napsal(a): On Sat, Nov 19, 2011 at 1:29 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: You still didn't answer what massive traffic means. Thousands of website hits per minute. (At peak time) Average is a few hundred per minute. This is pretty vague description of the workload, as we have no clue how demanding the request processing is. It might be a simple script that does almost nothing (in that case the thousands of hits is easy to handle) but it might be rather expensive. Anyway I personally see this as a rather unrelated to the problem we're trying to help you with - setting the right number of connections and maybe fixing some of the queries. 3Com? Perhaps you mean 3Ware? And (again) what are the RAID cache *settings*? In particular, the write-back/write-through setting. Yes 3Ware. RAID cache settings: -- Logical device information -- Logical device number 0 Logical device name : RAID10-A RAID level : 10 Status of logical device : Optimal Size : 1906678 MB Stripe-unit size : 256 KB Read-cache mode : Enabled MaxIQ preferred cache setting : Disabled MaxIQ cache setting : Disabled Write-cache mode : Enabled (write-back) Write-cache setting : Enabled (write-back) when protected by battery/ZMM Partitioned : Yes Protected by Hot-Spare : No Bootable : Yes Failed stripes : No Power settings : Disabled Logical device segment information Group 0, Segment 0 : Present (0,0) 9QJ00FMB Group 0, Segment 1 : Present (0,1) 9QJ1R3NW Group 1, Segment 0 : Present (0,2) 9QJ00L58 Group 1, Segment 1 : Present (0,3) 9QJ01JJ5 So how much write cache is there and what 3Ware model is that? Because I don't see this information there. So most of your selects aren't hitting the database. Since we are talking db tuning, it would have been nice to know how many queries are hitting the database, not the number of requests hitting the webserver. But the question was what is the typical duration of the queries - specifically the queries hitting the database. Right now single SELECTs with just that one WHERE indexed_column = 'Value' LIMIT 1 type queries are taking 3.0 of CPU, and so on. Why should these queries be taking so much time and resources? 3.0 of CPU? Is that seconds or what? Anyway post a more detailed description of the tables (columns, data types, indexes) and EXPLAIN ANALYZE of the queries (using explain.depesz.com). Earlier you said you were doing 200 inserts/minute. Is that an average throughout the day or is that at peak time. Peak load is really what is of interest. 200 inserts/minute is not even 4/second. As above. As above what? Please, be more specific, it's a bit difficult to know which paragraph above you're refering to. Again - post an EXPLAIN ANALYZE of the queries using explain.depesz.com. Look at your log. If it isn't set to record request time, set it to do so. I set my Apache servers to log request time in microseconds. Could you specify how precisely you have set up this log? Through CustomLog? Thanks! log_line_prefix = '%t' And you should probably add a few more fields (session ID, ...) Tomas -- 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] Huge number of INSERTs
Dne 18.11.2011 13:30, Phoenix Kiula napsal(a): Full DB: 32GB The big table referenced above: 28 GB It's inserts into this one that are taking time. Hm, in that case the shared_buffers is probably too low. It'd be nice to have at least the indexes on the table in the buffers, and I guess they're significantly over 256MB (your shared_buffers). But regarding the vmstat 5 10 output you've posted, you probably don't issue with I/O as the iowait is 0 most of the time. You do have a serious problem with CPU, though - most of the time, the CPU is almost 100% utilized. Not sure which process is responsible for this, but this might be the main problem problem. I'm not saying adding a row to the table (and indexes) is extremely expensive, but you do have an insane number of processes (350 connections, a lot of apache workers) and a lot of them are asking for CPU time. So once again: set the number of connections and workers to sane values, considering your current hardware. Those numbers are actually a handy throttle - you may increase the numbers until the CPU is reasonably utilized (don't use 100%, leave a reasonable margin - I wouldn't go higher than 90%). Tomas -- 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] Installed. Now what?
Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): tail -4 /var/log/pgbouncer.log 2011-11-19 22:16:49.139 26439 WARNING server login failed: FATAL password authentication failed for user MYDB_MYDB 2011-11-19 22:16:49.139 26439 LOG S-0x15b61fe0: MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0) 2011-11-19 22:17:13.490 26439 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us Please note that the word MYDB is a replacement of my private actual word. As you can see, the password is failing. I have read the segment of the manual you copy pasted, of course. I have auth_type = any auth_file = /var/lib/pgsql/pgbouncer.txt I have tried trust and md5 too. Same results as previously posted. Just for convenience, here's how the file looks: cat /var/lib/pgsql/pgbouncer.txt MYDB_MYDB mypassword here My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in the MYDB line, i.e. something like [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password='mypassword' The auth_file is used only for connecting to the pgbouncer, it's not forwarded to the database server - the pgbouncer opens the connection on behalf of the users, and you may actually have a completely different users on the connection pooler. Tomas -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): snip My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in the MYDB line, i.e. something like [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password='mypassword' The auth_file is used only for connecting to the pgbouncer, it's not forwarded to the database server - the pgbouncer opens the connection on behalf of the users, and you may actually have a completely different users on the connection pooler. Tomas I just checked my pgbouncer config file, and ye it does require a password in the db connection line. Amitabh
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): snip My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in the MYDB line, i.e. something like [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password='mypassword' The auth_file is used only for connecting to the pgbouncer, it's not forwarded to the database server - the pgbouncer opens the connection on behalf of the users, and you may actually have a completely different users on the connection pooler. OK. So I specified the password enclosed in double quotes. [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password=mypassword Then restarted pgbouncer: service pgbouncer restart And this shows up as this: lsof -i | grep pgbouncer pgbouncer 8558 postgres7u IPv4 26187618 TCP localhost:lds-distrib (LISTEN) Is this normal? Shouldn't the port number be somewhere? What's lds-distrib? Thanks for all the help. -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): snip My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in the MYDB line, i.e. something like [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password='mypassword' The auth_file is used only for connecting to the pgbouncer, it's not forwarded to the database server - the pgbouncer opens the connection on behalf of the users, and you may actually have a completely different users on the connection pooler. OK. So I specified the password enclosed in double quotes. [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password=mypassword Then restarted pgbouncer: service pgbouncer restart And this shows up as this: lsof -i | grep pgbouncer pgbouncer 8558 postgres 7u IPv4 26187618 TCP localhost:lds-distrib (LISTEN) Is this normal? Shouldn't the port number be somewhere? What's lds-distrib? I changed the port to the usual 6432 in the pgbouncer.ini. Restarted. Now I see this: lsof -i | grep pgbounc pgbouncer 10854 postgres7u IPv4 26257796 TCP localhost:6432 (LISTEN) So this is live and working. Pgbouncer is working. And yet, this is a problem: psql MYDB -E MYDB_MYDB -p 6432 -W Password for user MYDB_MYDB: psql: ERROR: no working server connection From the log file: 2011-11-20 01:28:57.775 10854 WARNING server login failed: FATAL password authentication failed for user MYDB_MYDB 2011-11-20 01:28:57.775 10854 LOG S-0x1ae2efc0: MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0) 2011-11-20 01:29:46.413 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us The password I am entering in the terminal is right for sure. I've tried it a few times, checked the caps lock, etc. Also, if the log carries this FATAL password authentication failed, why does the terminal give the vague error no working server connection? Thanks. -- 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] Performance degradation 8.4 - 9.1
More info: I upgraded the database from 8.4 to 9.1 using pg_upgrade, so I have no way of running explain using 8.4. I don't want to do an EXPLAIN ANALYZE because it would bog down the server for too long. I know what it is doing, it's doing a seqscan. This is a table with ~ 5.5 million rows and is ~100G in size. There are 4 indexes on this table, including one that matches what this query needs exactly. Pg finds this index, but goes with the other alternative (2), even though it thinks the index alternative (1) will be faster. I don't even know what that means. I've never seen an EXPLAIN like that before the 9.1 upgrade. I searched for alternative in the docs but didn't find anything. -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 12:02 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): snip My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in the MYDB line, i.e. something like [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password='mypassword' The auth_file is used only for connecting to the pgbouncer, it's not forwarded to the database server - the pgbouncer opens the connection on behalf of the users, and you may actually have a completely different users on the connection pooler. OK. So I specified the password enclosed in double quotes. [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password=mypassword Then restarted pgbouncer: service pgbouncer restart And this shows up as this: lsof -i | grep pgbouncer pgbouncer 8558 postgres7u IPv4 26187618 TCP localhost:lds-distrib (LISTEN) Is this normal? Shouldn't the port number be somewhere? What's lds-distrib? I changed the port to the usual 6432 in the pgbouncer.ini. Restarted. Now I see this: lsof -i | grep pgbounc pgbouncer 10854 postgres7u IPv4 26257796 TCP localhost:6432 (LISTEN) So this is live and working. Pgbouncer is working. And yet, this is a problem: psql MYDB -E MYDB_MYDB -p 6432 -W Password for user MYDB_MYDB: psql: ERROR: no working server connection From the log file: 2011-11-20 01:28:57.775 10854 WARNING server login failed: FATAL password authentication failed for user MYDB_MYDB 2011-11-20 01:28:57.775 10854 LOG S-0x1ae2efc0: MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0) 2011-11-20 01:29:46.413 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us The password I am entering in the terminal is right for sure. I've tried it a few times, checked the caps lock, etc. Also, if the log carries this FATAL password authentication failed, why does the terminal give the vague error no working server connection? Thanks. Just a trial: try password without quotes in your pgbouncer config file. That's how I have specified in mine, and it is working. Amitabh
Re: [GENERAL] Installed. Now what?
On Sun, Nov 20, 2011 at 2:45 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:02 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Just a trial: try password without quotes in your pgbouncer config file. That's how I have specified in mine, and it is working. Already done. Same problem. -- 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] Installed. Now what?
On Sun, Nov 20, 2011 at 2:32 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): snip My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in the MYDB line, i.e. something like [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password='mypassword' The auth_file is used only for connecting to the pgbouncer, it's not forwarded to the database server - the pgbouncer opens the connection on behalf of the users, and you may actually have a completely different users on the connection pooler. OK. So I specified the password enclosed in double quotes. [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 password=mypassword Then restarted pgbouncer: service pgbouncer restart And this shows up as this: lsof -i | grep pgbouncer pgbouncer 8558 postgres 7u IPv4 26187618 TCP localhost:lds-distrib (LISTEN) Is this normal? Shouldn't the port number be somewhere? What's lds-distrib? I changed the port to the usual 6432 in the pgbouncer.ini. Restarted. Now I see this: lsof -i | grep pgbounc pgbouncer 10854 postgres 7u IPv4 26257796 TCP localhost:6432 (LISTEN) So this is live and working. Pgbouncer is working. And yet, this is a problem: psql MYDB -E MYDB_MYDB -p 6432 -W Password for user MYDB_MYDB: psql: ERROR: no working server connection From the log file: 2011-11-20 01:28:57.775 10854 WARNING server login failed: FATAL password authentication failed for user MYDB_MYDB 2011-11-20 01:28:57.775 10854 LOG S-0x1ae2efc0: MYDB/MYDB_MYDB@127.0.0.1:5432 closing because: login failed (age=0) 2011-11-20 01:29:46.413 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us The password I am entering in the terminal is right for sure. I've tried it a few times, checked the caps lock, etc. Also, if the log carries this FATAL password authentication failed, why does the terminal give the vague error no working server connection? Thanks. Another idea. I use CSF/LFD firewall. For TCP_IN, I have enabled 6432 port number. Do I also need to enable it elsewhere, such as TCP_OUT or UDP_IN etc? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
