[GENERAL] Need help in tuning
My PG server is still going down. After spending the weekend doing a CLUSTER of my largest table (it's a RAID 1 system with SATA hard disks and 4 GB memory, mostly devoted to PG) I still have this issue. When I do a top command, 99% of the CPU and about 15% of the memory is being taken by PG. When I press a c in the top UI, I see that postmaster is doing some CLUSTER. However, I don't do any more clustering. The only automatic setting I can think of are autovacuum. So, question: to the degree that my system allows for performance, what steps can I take to find out what's happening? I see some things mentioned: I/O, vacuum settings, pg_stats, pg_activity -- is there a simple guide somewhere which shows me step by step what to do? Google hasn't been much help. Postgresql performance tunings brings a lot of esoteric articles. 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] Reindex taking forever, and 99% CPU
Thank you for the very specific idea of pg_stat_user. This is what I see (the output is also included in email below, but this is easier to read) -- https://gist.github.com/anonymous/53f748a8c6c454b804b3 The output here (might become a jumbled mess)-- =# SELECT * from pg_stat_user_tables where relname='bigtb'; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze ++-+--+--+--+---+---+---+---+---+++---+-+---+-- 105954 | public | bigtb |0 |0 | 220396 | 89781 | 63516 | 6 | 910 | 1 | 634879579 | 39 | 2014-08-06 20:12:47.163055-04 | | 2014-08-06 20:19:40.317771-04 | (1 row) Time: 50.844 ms We spent some time to do some massive cleaning of the data from this table. Brought it down to around 630 million rows. Overall size of the table including indexes is about 120GB anyway. More stats that we could manage are pretty-pasted here: https://gist.github.com/anonymous/21aaeae10584013c3820 The biggest table (bigtb -- codename for pasting on public forum) stores some URLs. The most important index is for this table is the alias column, which is varchar(35) as you can see. Table definition also pasted below: Table public.bigtb Column |Type |Modifiers -+-+- alias | character varying(35) | not null url | text| not null user_registered | boolean | private_key | character varying(6)| default NULL::character varying modify_date | timestamp without time zone | default now() ip | bigint | url_md5 | text| Indexes: idx_bigtb_pkey PRIMARY KEY, btree (alias) idx_bigtb_ip_url UNIQUE, btree (ip, url_md5) idx_bigtb_modify_date btree (modify_date) idx_bigtb_urlmd5 btree (url_md5) Check constraints: bigtb_alias_check CHECK (alias::text ~ '[-.~a-z0-9_]'::text) Referenced by: TABLE bigtb_registered CONSTRAINT fk_bigtb_registered FOREIGN KEY (alias) REFERENCES bigtb(alias) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE TABLE interesting CONSTRAINT interesting_alias_fkey FOREIGN KEY (alias) REFERENCES bigtb(alias) MATCH FULL ON UPDATE CASCADE Rules: __track_bigtb_deleted AS ON DELETE TO bigtb WHERE NOT (EXISTS ( SELECT bigtb_deleted.alias FROM bigtb_deleted WHERE bigtb_deleted.alias::text = old.alias::text)) DO INSERT INTO bigtb_deleted (alias, url, user_registered, modify_date) VALUES (old.alias, old.url, old.user_registered, old.modify_date) What else could I do here? As you will see in the code shared above (GIST Github link) the stats for this table are: bigtb - row count: 634,879,168 inserted: 65613 updated: 6 deleted: 1013 There are recent numbers. The DB has been going down often. But deletions would be around 20,000 per week. Updates are lowest. INSERT and SELECT are huge, with of course SELECT being the biggest activity (high traffic website). We did put PGBouncer for some pooling benefits, and memcached for taking some load off the postgresql server. As of this writing, the memcached thing is caching around 200,000 URLs which would otherwise have been a query based on the index on the alias column -- idx_bigtb_pkey. What other info can I share? Suppose we might have to explore partitioning, which would probably be via first letter of the alias? This would lead to around 26 + 9 = 35 sub-tables. Is this too many? My CONFIG settings: max_connections = 180 # Was 250! - http://www.php.net/manual/en/function.pg-pconnect.php#20309 superuser_reserved_connections = 5 shared_buffers = 512MB effective_cache_size= 1200MB # Nov 11 2011, was 1500MB temp_buffers= 32MB # min 800kB maintenance_work_mem= 320MB# min 1MB, was 128MB work_mem= 64MB wal_buffers = 20MB # min 32kB fsync = on # turns forced synchronization on or off checkpoint_segments = 128 # was 128 checkpoint_timeout = 1000 # was 1000 enable_indexscan= on log_min_duration_statement = 1000 Much appreciate any further ideas! On Sun, Aug 3, 2014 at 9:29 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/02/2014 07:37 PM, Phoenix Kiula wrote
Re: [GENERAL] Upgrading from 9.0.11 to 9.3.5 on CentOS 6 (64 bit)
Thanks...comments below. assuming you installed 9.0 from the yum.postgresql.com respositories, then, `yum update postgresql90-server` and restart the postgresql-9.0 service should do nicely. This worked. Took me to 9.0.17 for some reason. I'm OK with this. But the vacuum full was a terrible idea. I just spent 2 tranches of 5 hours each waiting for it to work. Many websites/blogs mention NOT to run vacuum full at all. Instead, run cluster. Is this better then? My table is huge. Over a billion rows. The idea of pg_dump and then pg_restore of a table might work, followed by reindexing. But that would also cause serious downtime. Anything else I can do? Just adjust the autovacuum information for example? My current settings are as follows: autovacuum = on autovacuum_max_workers = 5 autovacuum_vacuum_cost_delay= 20ms autovacuum_vacuum_cost_limit= 350 The table in question has over a billon rows. HOW do I know if this table is causing the issues? This is the only table that's heavily queried. Recently many times the PG server has been locked, and the pending queries have led to server outage. When I manually vacuumdb, this is the table where the process has stuck for hours. So I need to tune this table back to its usual performance. Appreciate any ideas! I'm sure there are much larger tables in the world than mine. What do they do? (Apart from replication etc) 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] Reindex taking forever, and 99% CPU
Hi. I've been patient. PG is 9.0.17, updated via Yum yesterday. One of my large tables (101 GB on disk, about 1.1 billion rows) used to take too long to vacuum. Not sure if it's an index corruption issue. But I tried VACUUM FULL ANALYZE as recommended in another thread yesterday, which took 5 hours on the two times I tried, without finishing. Now the REINDEX TABLE has taken over 6 hours as I decided to be patient and just let something finish. Not sure this is normal though! How do production level DBAs do this if it takes so long? If I open another SSH window to my server and try select * from pg_stats_activity it just hangs there, as the REINDEX I presume is taking up all the memory? I basically can't do anything else on this server. Just in case it helps, a segment of my postgresql.conf is below. Would appreciate any tips on what I can do. (I did a pg_dump of just this table, which also took about 2 hours, then I renamed the original table in the database, and tried to pg_restore just the table, but it gave me an error message about the archive being in the wrong format !!! So REINDEX or something like it seems to be the only idea?) Thanks for any help! PS: CentOS 6 64 bit, 4 GB of RAM, Raid 1 Raptor disks. Postgresql.conf and TOP output during the running of the REINDEX are below.. POSTGRESQL.CONF- max_connections = 180 superuser_reserved_connections = 5 shared_buffers = 512MB effective_cache_size= 1200MB temp_buffers= 32MB maintenance_work_mem= 320MB work_mem= 128MB wal_buffers = 20MB fsync = on checkpoint_segments = 128 checkpoint_timeout = 1000 enable_indexscan= on # AUTOVAC autovacuum = on autovacuum_max_workers = 5 # max number of autovacuum subprocesses #autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum autovacuum_vacuum_cost_delay= 20ms autovacuum_vacuum_cost_limit= 350 ... --TOP OUTPUT (db name changed for privacy, with the word MYDOMAIN) --- top - 21:18:51 up 22 days, 7:43, 2 users, load average: 1.20, 1.17, 1.18 Tasks: 214 total, 3 running, 211 sleeping, 0 stopped, 0 zombie Cpu(s): 25.1%us, 1.6%sy, 0.0%ni, 71.9%id, 1.1%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 4046644k total, 4022324k used,24320k free, 9880k buffers Swap: 2096440k total, 177144k used, 1919296k free, 2526536k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 21044 postgres 25 0 1102m 513m 76m R 97.7 13.0 432:03.46 postgres: MYDOMAIN_MYDOMAIN MYDOMAIN [local] REINDEX 8812 root 18 0 1403m 53m 3344 S 2.3 1.4 377:33.38 ./jre/bin/java -Djava.compiler=NONE -cp /usr/StorMan/RaidMan.jar com. 8319 named 24 0 317m 37m 1860 S 1.3 0.9 319:11.26 /usr/sbin/named -u named -4 -t /var/named/chroot 14184 nobody15 0 266m 15m 5156 S 1.0 0.4 4:13.43 nginx: worker process 14181 nobody15 0 279m 34m 5160 S 0.7 0.9 4:13.93 nginx: worker process 30285 root 15 0 12760 1188 820 R 0.7 0.0 0:00.03 top 282 root 10 -5 000 S 0.3 0.0 184:37.48 [kswapd0] 25093 nobody16 0 334m 15m 5124 S 0.3 0.4 0:01.00 /usr/local/apache/bin/httpd -k restart -DSSL 25095 nobody15 0 334m 15m 5256 S 0.3 0.4 0:00.94 /usr/local/apache/bin/httpd -k restart -DSSL 25102 nobody15 0 334m 15m 5120 S 0.3 0.4 0:00.93 /usr/local/apache/bin/httpd -k restart -DSSL 25106 nobody15 0 334m 15m 5416 S 0.3 0.4 0:00.99 /usr/local/apache/bin/httpd -k restart -DSSL 25109 nobody15 0 334m 15m 5424 S 0.3 0.4 0:00.94 /usr/local/apache/bin/httpd -k restart -DSSL 25113 nobody16 0 334m 15m 4980 S 0.3 0.4 0:00.93 /usr/local/apache/bin/httpd -k restart -DSSL 25115 nobody16 0 334m 15m 5192 S 0.3 0.4 0:00.95 /usr/local/apache/bin/httpd -k restart -DSSL 25117 nobody16 0 334m 15m 4988 S 0.3 0.4 0:00.97 /usr/local/apache/bin/httpd -k restart -DSSL 25119 nobody16 0 334m 15m 5028 S 0.3 0.4 0:00.96 /usr/local/apache/bin/httpd -k restart -DSSL 31759 root 15 0 000 S 0.3 0.0 0:35.37 [pdflush] 1 root 15 0 10368 592 556 S 0.0 0.0 0:04.29 init [3] 2 root RT -5 000 S 0.0 0.0 0:06.24 [migration/0] 3 root 34 19 000 S 0.0 0.0 0:08.72 [ksoftirqd/0] 4 root RT -5 000 S 0.0 0.0 0:00.00 [watchdog/0] 5 root RT -5 000 S 0.0 0.0 0:05.27 [migration/1] 6 root 34 19 000 S 0.0 0.0 3:49.89 [ksoftirqd/1] 7 root RT -5 000 S 0.0 0.0 0:00.00 [watchdog/1] -- 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] Reindex taking forever, and 99% CPU
Thanks John. So what're the right settings? Anyway, right now Postgresql is servicing only one main connection, which is the REINDEX. All other stuff is switched off, no one else is connecting to the DB. My issue with this table was the vaccum process would stop at this table, and take hours. So I thought something was wrong with this table. My version of PG was 9.0.11, and googling for similar issues brought up an old post by Tom Lane that suggested to the poster of that thread to upgrade. So now I have, and am at 9.0.17 -- I recognize this is not 9.3.5, but not sure we have the appetite right now for a massive upgrade. So what I'm trying to do is reindex this specific table. iostat Linux 2.6.18-238.9.1.el5 (coco.MYDOMAIN.com) 08/02/2014 avg-cpu: %user %nice %system %iowait %steal %idle 10.630.103.11 13.420.00 72.74 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 284.10 43828.59 5729.27 84628259628 11062603842 sda1 0.00 0.00 0.00 2272 10 sda2 3.0844.97 989.21 86838949 1910058506 sda3 7.65 193.0484.34 372745356 162860428 sda4 0.00 0.00 0.00 6 0 sda5 1.5831.15 6.84 60140845 13208874 sda6 0.8220.28 0.88 391611381693104 sda7 1.1015.5924.32 30101692 46962204 sda8 2.7744.8820.07 86661146 38754800 sda9267.11 43478.67 4603.61 83952607992 8889065916 On Sun, Aug 3, 2014 at 9:56 AM, John R Pierce pie...@hogranch.com wrote: On 8/2/2014 6:20 PM, Phoenix Kiula wrote: PS: CentOS 6 64 bit, 4 GB of RAM, Raid 1 Raptor disks. Postgresql.conf and TOP output during the running of the REINDEX are below.. POSTGRESQL.CONF- max_connections = 180 superuser_reserved_connections = 5 shared_buffers = 512MB effective_cache_size= 1200MB temp_buffers= 32MB maintenance_work_mem= 320MB work_mem= 128MB with 4GB of ram, and 180 connections, if you actually had all 180 connections busy at once, you could use over 180 times work_mem, 180*128MB in 4GB would be fatal. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex taking forever, and 99% CPU
In your original post you said it was stopping on pg_class so now I am confused. No need to be confused. The vacuum thing is a bit tricky for laymen like myself. The pg_class seemed to be associated to this table. Anyway, even before the upgrade, the vacuum was stopping at this table and taking forever. The question is: what now. Where can I give you information from? IOSTAT I've already shared. Will the work_mem settings affect the manual REINDEX that's still running? What can I do to speed up the REINDEX? Should I change my autovacuum settings for this table specifcally (it's the only mammoth table in the DB, and our main one)? 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] Upgrading from 9.0.11 to 9.3.5 on CentOS 6 (64 bit)
Hello, I have Postgresql from a few years ago. That's 9.0.11. During the vacuum it's basically crawling to its knees. While googling for this (it stops at pg_classes forever) I see Tom Lane suggested upgrading. So now I must. In doing so, can I follow these instructions? https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-a-centos-vps I want to make sure all my data remains exactly as it is, and the pgbouncer on top of PG (helps us a lot) also remains on the same port etc. Just want to confirm that whether I update via the RPM method, or the YUM method, that the settings in all the places will remain? Ideally, I don't want to be linking new paths and so on as I see in online instructions on blogs. Many of them (e.g., the official post here - http://wiki.postgresql.org/wiki/FAQ#What_is_the_upgrade_process_for_PostgreSQL.3F ) also speak of clusters. I don't have any, or is my PG basically one cluster? Sorry for the noob question, but it would be great to get some simple to follow, step by step guidance. MySQL etc are so simple to upgrade! Many thanks, PK -- 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] Upgrading from 9.0.11 to 9.3.5 on CentOS 6 (64 bit)
Thank you John. you can upgrade to 9.0.18 painlessly. 9.1 or .2 or .3, not quite so painless. What's the best way to get to 9.0.18, as a start? Is there a simple single command I can use? I'm on CentOS 6, 64bit. have you tried a vacuum full of the whole cluster, with your applications shut down? Not yet, not with the apps shut down entirely, but in read mode, yes. No new rows being added. SELECTs have to work as it's a high traffic website. you will need to either pg_dumpall your old database 'cluster' and load this into the new version, or use pg_upgrade, which is a fair bit trickier but can do an in-place upgrade.if your databases aren't much over a few dozen gigabytes, pg_dumpall is probably simpler than pg_upgrade. if your databases are large, pg_dumpall - psql restore may take a LONG time, so the pg_upgrade process may be more efficient. Dread to use pg_upgrade after that confirmation of my fears. Our DB is around 200 GB. Even with pg_upgrade, will I have to once again tinker with all the conf files and authentication (which is md5 right now), change my passwords and do template1 database stuff again? I saw the doc page on the postgresql.org site for pg_upgrade, but it presumes a lot of things in terms of knowledge. I just have one database instance or cluster. The 10-15 tables are not complex. But they're large, as in over a billion rows now. All I need is for the upgrade to happen automatically, retaining my config and paths and whatnot (or clear instructions that work, step by step). 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] How to REMOVE an on delete cascade?
Hi. Hard to find this command in the documentation - how should I alter a table to REMOVE the on delete cascade constraint from a table? Thanks.
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack w...@uen.org wrote:
I use pgpool but some of the problem you listed are same as I had with pgpool
Thanks Wolf, for the thoughts.
I would not run pgbouner in /var/run/pbbouner. Every time you reboot the
directory will get deleted. I set my parameter to another directory the would
not
get deleted after a reboot.
OK, but this is not a showstopper here. Right?
/var/log/pgbouncer.log:
what is the permission on /var/log? If you don't have write permission on the
directory then you cannot write to the file.
Permissions:
/var/run/pgbouncer --
70058074 drwxr-xr-x 2 pgbouncer postgres 4.0K Oct 2 06:17 pgbouncer/
/var/log --
145686529 drwxr-xr-x 17 root root 4.0K Oct 5 04:29 log/
Please note that whatever the settings, they were working before a
server reboot. What settings do I need to give /var/log (currently
root) so the pgbouncer process can write to it? Why are these special
permissions needed-- I mean Apache, MysQL, Nginx etc...all of them can
write to the logs in this log folder.
Psql: ERROR: No such user:
You have to create the user in postgres, check you users
postgres=# /du
Yes, this user exists in the postgres database.
List of roles
Role name|Attributes | Member of
-+---+---
postgres| Superuser, Create role, Create DB | {}
rvadmin | | {}
MYSITE | | {}
MYSITE_MYSITE | Superuser, Create DB | {}
And the authfile also has permissions for pgbouncer:postgres.
What 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: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack w...@uen.org wrote: You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a file in /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. As root create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then test by creating a file in /var/log/pgbouncer as user pgbouncer Wolf, I think you missed the earlier posts in this thread. The /var/log/pgbouncer.log already has those permissions. Note this important fact: the same permissions have been working for nearly 2 years. Anyway, I created a directory: /var/log/pgbouncer/, put the pgbouncer.log file in it. chown -R pgbouncer:postgres /var/log/pgbouncer chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log chmod 777 /var/log/pgbouncer/pgbouncer.log As was already happening, pgbouncer starts. No problem. It's now that I cannot connect to PSQL via pgbouncer (of course I can connect to psql directly) because it fails with this error: psql: ERROR: No such user: MYSITE_MYSITE Which is weird, because that user does exist. Both inside the postgres database when I do \du as you suggested, and of course in the pgbouncer authfile too -- chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt cat /var/lib/pgsql/pgbouncer.txt MYSITE_MYSITE md5 pass MYSITE_MYSITE raw pass postgres md5fd6313191fec7887f88c31a85c43df21 So now. What? Why is this otherwise very useful tool coded so poorly that there's reams of such permissions and all of these threads online? Would love to have some help or guidance. 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: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver adrian.kla...@gmail.com wrote: ...snip... What are the contents of your pgbouncer.ini file? Thanks Adrian. I mentioned the full ini file details above in the thread, but here they are again. (Please do not comment about port numbers. This is a public list so I change the numbers, but they are very much on track everywhere they need to be.) Thanks for any pointers... [databases] * = host=127.0.0.1 port=5432 [pgbouncer] listen_addr = * listen_port = 6389 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 800 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 unix_socket_dir = /tmp ignore_startup_parameters = application_name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver adrian.kla...@gmail.com wrote: One thing I see above: http://pgbouncer.projects.postgresql.org/doc/config.html \* acts as fallback database Notice the backslash. Ok, but: (1) The exact same INI file was working so far. (2) Why do I need a fallback database? I want to be precise about database names if possible. (3) I did try and change the config to have the backslash, but when I restart, I get this error: 2012-10-05 22:30:06.882 27442 ERROR syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2), stopping loading Now? -- 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] Again, problem with pgbouncer
On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have postgres user permissions,please assign it and then start the pgbouncer. The /var/run/pgbouncer/ directory has chown -R postgres:postgres .. The port number everywhere is already 6789. What else? And just to be safe, I also added pgbouncer user to postgres group: usermod -a -G postgres pgbouncer Now when I restart the pgbouncess service, it fails. The log has this message: 2012-10-01 23:25:24.004 21037 FATAL Cannot open logfile: '/var/log/pgbouncer.log': Permission denied That file is owned by postgres:postgres as indicated in a gazillion threads and documentation online (none of which is comprehensive) but just to be sure I also did this: chown :postgres /var/log/pgbouncer.log Still the same permission error. Seriously, why can't the log message be a little more useful? Why can't it say clearly WHICH USER is looking for permission to the log file? Both pgbouncer and postgres have permissions (through the group postgres) on that file. So which is it? I made the port number 6389 everywhere. I changed the permissions of the pgbouncer.log to: chown pgbouncer:postgres /var/log/pgbouncer.log Now at least the service starts. But when I try and connect via the pgbouncer ID: psql -p 6389 -U snipurl_snipurl snipurl I get this error: psql: ERROR: No such user: MYSITE_MYSITE And yet, the authfile has this: MYSITE_MYSITE md5 of raw password MYSITE_MYSITE raw password postgres md5 of string MYSITE_pgbouncer The authfile permissions are: 283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 /var/lib/pgsql/pgbouncer.txt What else? No response. Is there anyone who can help me with pgbouncer? What are the permissions for the authfile, etc? -- 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] Again, problem with pgbouncer
On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have postgres user permissions,please assign it and then start the pgbouncer. The /var/run/pgbouncer/ directory has chown -R postgres:postgres .. The port number everywhere is already 6789. What else? And just to be safe, I also added pgbouncer user to postgres group: usermod -a -G postgres pgbouncer Now when I restart the pgbouncess service, it fails. The log has this message: 2012-10-01 23:25:24.004 21037 FATAL Cannot open logfile: '/var/log/pgbouncer.log': Permission denied That file is owned by postgres:postgres as indicated in a gazillion threads and documentation online (none of which is comprehensive) but just to be sure I also did this: chown :postgres /var/log/pgbouncer.log Still the same permission error. Seriously, why can't the log message be a little more useful? Why can't it say clearly WHICH USER is looking for permission to the log file? Both pgbouncer and postgres have permissions (through the group postgres) on that file. So which is it? I made the port number 6389 everywhere. I changed the permissions of the pgbouncer.log to: chown pgbouncer:postgres /var/log/pgbouncer.log Now at least the service starts. But when I try and connect via the pgbouncer ID: psql -p 6389 -U snipurl_snipurl snipurl I get this error: psql: ERROR: No such user: MYSITE_MYSITE And yet, the authfile has this: MYSITE_MYSITE md5 of raw password MYSITE_MYSITE raw password postgres md5 of string MYSITE_pgbouncer The authfile permissions are: 283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 /var/lib/pgsql/pgbouncer.txt What else? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Again, problem with pgbouncer
Hi, - PG 9.0.10 - Pgbouncer version 1.4.2 Not long ago, during the last server reboot for us, we had fixed the really painful (and largely mysterious) process of setting up pgbouncer. File permissions and other mysteries were solved with help from Raghavendra: http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/854 After a long we rebooted our server today and again, as if on cue, pgbouncer has problems yet again :( PG itself is running without problems. The Pgbouncer process starts properly too. All the auth file, log file etc are setup as mentioned in that URL above. We haven't changed anything at all! At first, just connecting via pgbouncer port was giving the no user error. Which is funny, because the authfile has been working without problems forever. The .pgpass file had the same problems, and is still the same all this time. So, upon reading that old thread again, I guessed that the postgres user permissions were needed, so I did this: chown -R postgres:postgres /etc/pgbouncer chown -R postgres:postgres /var/run/pgbouncer/ chown postgres:postgres /var/log/pgbouncer.log chown postgres:postgres /var/lib/pgsql/pgbouncer.txt Then restarted both PG and Pgbouncer. Now pgbouncer won't do anything at all. Trying to connect to psql via the pgbouncer port gives this error: psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.6789? And in the log is this line: 2012-10-01 06:12:00.703 3754 FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: Permission denied [13] What now? Would appreciate some pointers. 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] Again, problem with pgbouncer
Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have postgres user permissions,please assign it and then start the pgbouncer. The /var/run/pgbouncer/ directory has chown -R postgres:postgres .. The port number everywhere is already 6789. What else? 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] Again, problem with pgbouncer
On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have postgres user permissions,please assign it and then start the pgbouncer. The /var/run/pgbouncer/ directory has chown -R postgres:postgres .. The port number everywhere is already 6789. What else? And just to be safe, I also added pgbouncer user to postgres group: usermod -a -G postgres pgbouncer Now when I restart the pgbouncess service, it fails. The log has this message: 2012-10-01 23:25:24.004 21037 FATAL Cannot open logfile: '/var/log/pgbouncer.log': Permission denied That file is owned by postgres:postgres as indicated in a gazillion threads and documentation online (none of which is comprehensive) but just to be sure I also did this: chown :postgres /var/log/pgbouncer.log Still the same permission error. Seriously, why can't the log message be a little more useful? Why can't it say clearly WHICH USER is looking for permission to the log file? Both pgbouncer and postgres have permissions (through the group postgres) on that file. So which is it? Much appreciate any pointers. 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] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 2:59 PM, Raghavendra raghavendra@enterprisedb.com wrote: . Add it in pgbouncer.auth file as per your .ini file parameter. auth_file = /var/lib/pgsql/pgbouncer.txt Seems you already did this. I believe you are connecting as postgres user not from root, if yes, then check .pgpass file too (it will be in postgres user home directory). Thanks, but this is not helping. I have the exact same info in three files: 1. The root .pgpass 2. The .pgpass for the postgres user 3. The authfile specified inside pgbouncer -- /var/lib/pgsql/pgbouncer.txt Now what? It's not telling me that the user is unrecognized, so it's likely not that md5 or trust or plain password issue. Pgbouncer is not even recognizing the user! What now? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGBouncer help (how to get it working)
I had pgbouncer working somehow, but we have switched servers recently and now I cannot for the life of me figure out again how to set it up. Online guides say things like create a user ID. Well, where? Inside PG the database? Or in my CentOS system? Here's my /etc/pgbouncer.ini: [databases] * = port = 5432 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = postgres,MYSITE_pgbouncer pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 1000 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 And here's my authfile, /var/lib/pgsql/pgbouncer.txt -- MYSITE_pgbouncer Is there something else I need to do? What steps am I missing? When I start pgbouncer at the command line, I see this error: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Thanks for any tips! -- 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] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Pretty sure the error is just the perms on that file. Set them to 0600 and try again. I had already done this. Doesn't do anything. Pgbouncer starts (service pgbouncer restart) but when I try to connect, it tells me psql: ERROR: No such user: MYSITE_pgbouncer Where should i create the MYSITE_pgbouncer user? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign Key with an OR condition (and two concatenated columns)?
Hi. Hope I'm articulating the question correctly. I currently have a foreign key like this: fk_table2 FOREIGN KEY (id) REFERENCES table1(id) ON DELETE CASCADE; Given the peculiar needs of this db, it seems that in some cases we will need to track the id of table2 against a concatenation of two columns in table2. So in pseudocode, it would look something like this. fk_table2 FOREIGN KEY (id) REFERENCES table1(id OR id||'.'||column2) ON DELETE CASCADE; Do I need a separate constraint for this? Many thanks for any pointers! -- 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] URGENT: temporary table not recognized?
On Fri, Jan 6, 2012 at 10:38 PM, John R Pierce pie...@hogranch.com wrote: you should check your attitude at the door. this isn't Microsoft Pay per Incident Tech Support. I saw the door. Found some other attitudes that were allowed to be let in. Like asking me to write my own patch. You see, attitudes come in different shapes. what you're asking for is right on that very URL you so conveniently quoted, both the configuration file format (Global Configuration Section), and where the sample pgloader.conf can be found... No it isn't. The config file is not clear. And it's available in three different forms in three different places, which I found only thanks to Google. It's a community project, I get it. Moving on. I'll do the awk/sed thing. COPY from other databases already has several such convenient features. Postgresql is not interested in implementing them. Sure. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] URGENT: temporary table not recognized?
Hi. I'm using Postgresql 9.0.5, and the connection is made via pgbouncer. I create a temporary table, and then want to import data into this table via a COPY command. Yet, this just created table is not being recognized. What's up? From my terminal: mydb=# mydb=# create temporary table vl ( alias varchar(35) ,dates timestamp without time zone ,referers text null ); CREATE TABLE Time: 1.871 ms mydb=# copy vl from '/backup/data.txt' WITH DELIMITER AS '|'; ERROR: relation vl does not exist mydb=# Weird! Welcome any thoughts. 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] URGENT: temporary table not recognized?
On Fri, Jan 6, 2012 at 11:46 AM, Tom Lane t...@sss.pgh.pa.us wrote: Phoenix Kiula phoenix.ki...@gmail.com writes: Hi. I'm using Postgresql 9.0.5, and the connection is made via pgbouncer. Perhaps pgbouncer is redirecting the second command to a different session? Thanks Tom. I'm in the exact same session in my terminal, and the commands are entered within 2 seconds of each other. With copy/paste, maybe split microseconds of each other. How can I make sure pgbouncer takes it all in the same session? I also tried the two commands within a transaction. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] URGENT: temporary table not recognized?
On Fri, Jan 6, 2012 at 4:24 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 01/06/2012 01:11 PM, Phoenix Kiula wrote: On Fri, Jan 6, 2012 at 11:46 AM, Tom Lanet...@sss.pgh.pa.us wrote: Phoenix Kiulaphoenix.ki...@gmail.com writes: Hi. I'm using Postgresql 9.0.5, and the connection is made via pgbouncer. Perhaps pgbouncer is redirecting the second command to a different session? Thanks Tom. I'm in the exact same session in my terminal, and the commands are entered within 2 seconds of each other. With copy/paste, maybe split microseconds of each other. How can I make sure pgbouncer takes it all in the same session? I also tried the two commands within a transaction. Sounds like you are using statement pooling - every statement can be assigned to a different server connection. You may need transaction pooling or session pooling: http://pgbouncer.projects.postgresql.org/doc/usage.html Thanks Steve. YES! I changed it to transaction pooling and now it works. Another problem through. I need to COPY a huge text file into a table, with about 350 million lines in the file (i.e., 350 million rows in the table). While copying, some lines do not have data. They are empty values. How can I specify in COPY command that if data is not found, it should be ignored? In my temp table definition, I set this column as NULL anyway, so it should be ok if this column was left empty! What can I do in my COPY command to circumvent this? 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] URGENT: temporary table not recognized?
On Fri, Jan 6, 2012 at 6:20 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
http://www.postgresql.org/docs/9.0/interactive/sql-copy.html
Search for
NULL
Thanks Adrian.
Without examples, it's hard to predict syntax. If the value after a
pipe is missing altogether, I suppose the missing value is \n
(newline). But this doesn't work:
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL '\n';
None of these work either:
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL \n;
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL \\n;
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL '';
The first two give errors, the third one throws the same missing value
for column error.
The data is stored like this:
123|big string here|189209209|US|2001-01-01
123|big string here|189209209|US|2001-01-01
123|big string here|189209209|US|2001-01-01
123|big string here|189209209|US|2001-01-01
But sometimes, the strings are:
|big string here|189209209|US|2001-01-01
|big string here|189209209|US|2001-01-01
Or
123|big string here|189209209|US
123|big string here|189209209|US|
So you see either the first column, which is the ID in a way, is
missing so the missing character is probably a blank (''?). In this
case I want COPY to just ignore this line.
Or the last column is missing, where the missing character can be a
newline I suppose?
So how do I specify this in the COPY command so that it doesn't croak?
If a line's ID is missing, it should ignore the line and go on instead
of not doing anything by throwing an error for EVERYTHING!
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] URGENT: temporary table not recognized?
On Fri, Jan 6, 2012 at 6:53 PM, Steve Crawford
scrawf...@pinpointresearch.com wrote:
On 01/06/2012 03:42 PM, Phoenix Kiula wrote:
On Fri, Jan 6, 2012 at 6:20 PM, Adrian Klaveradrian.kla...@gmail.com
wrote:
http://www.postgresql.org/docs/9.0/interactive/sql-copy.html
Search for
NULL
Thanks Adrian.
Without examples, it's hard to predict syntax. If the value after a
pipe is missing altogether, I suppose the missing value is \n
(newline). But this doesn't work:
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL '\n';
None of these work either:
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL \n;
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL \\n;
copy vl from 'data.txt' WITH DELIMITER AS '|' NULL '';
The first two give errors, the third one throws the same missing value
for column error.
The data is stored like this:
123|big string here|189209209|US|2001-01-01
123|big string here|189209209|US|2001-01-01
123|big string here|189209209|US|2001-01-01
123|big string here|189209209|US|2001-01-01
But sometimes, the strings are:
|big string here|189209209|US|2001-01-01
|big string here|189209209|US|2001-01-01
Or
123|big string here|189209209|US
123|big string here|189209209|US|
So you see either the first column, which is the ID in a way, is
missing so the missing character is probably a blank (''?). In this
case I want COPY to just ignore this line.
Or the last column is missing, where the missing character can be a
newline I suppose?
So how do I specify this in the COPY command so that it doesn't croak?
If a line's ID is missing, it should ignore the line and go on instead
of not doing anything by throwing an error for EVERYTHING!
Thanks.
Missing data is one thing, missing delimiters is another. Try doing a small
copy of data with just a few lines to see which variants are actually
causing the error. My money is on the one that has a mismatch between the
table column count and the data column count. I.e., the row with three
delimiters instead of four:
23|big string here|189209209|US
When you say ignore, do you mean that you want PostgreSQL to assume a null
value for the missing column or to not import that row at all?
In general, when you have data scrubbing issues like this, grep/sed/awk/...
are your friends. Clean it up then import it.
I suppose you could import all rows into a big text field and process it in
PostgreSQL but I doubt you will find that to be an optimal solution.
Thanks Steve.
The file has 350 million lines. Sed, Awk etc are a little painful when
the file is 18GB witht hat many lines.
I'd want Postgresql to ignore the line altogether when something is
missing. Is this an option we can use, or are rules hoisted on us?
--
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] URGENT: temporary table not recognized?
On Fri, Jan 6, 2012 at 6:54 PM, Adrian Klaver adrian.kla...@gmail.com wrote: Try: copy vl from 'data.txt' WITH CSV DELIMITER '|'; Doesn't work. Can't see what the different in CSV is from a text file. Same errors are thrown. If that doesn't work take a look at pgloader: http://pgfoundry.org/projects/pgloader/ Wow, another geeky tool. Hard to find documentation. Archaic presentation, no simple steps to install and get using. Anyway doesn't seem to provide the options I need (http://pgloader.projects.postgresql.org/) -- a) Ability to assign more than one NULL value b) Ability to ignore lines altogether that have any problem Really, other databases have mechanisms to ignore problem lines while copying. Does Postgresql allow me to *ignore* lines while COPYING? -- 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] Foreign keys question (performance)
On Sun, Dec 4, 2011 at 7:41 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. I have a problem. Here's my table designs. The problem is that if Table 1 (stores) has a foreign key reference from another child table (stores_registered), then when I update Table 1, it throws an error that referential intergrity is being violate because Table 2 depends on Table 1. However, if I update Table 2 first, it tells me that the fkey in Table 1 doesn't exist (of course). Any ideas? What am I missing? How do updates work in terms of CASCADE? Thanks! mydb=# \d stores Table public.stores Column |Type |Modifiers -+-+- strid | character varying(35) | not null plc | text| not null user_registered | boolean | private_key | character varying(6)| default NULL::character varying modify_date | timestamp without time zone | default now() ip | bigint | plc_md5 | text| Indexes: idx_stores_pkey PRIMARY KEY, btree (strid) idx_stores_ip_plc UNIQUE, btree (ip, plc_md5) idx_stores_modify_date btree (modify_date) idx_stores_plcmd5 btree (plc_md5) Check constraints: stores_strid_check CHECK (strid::text ~ '[-.~a-z0-9_]'::text) Referenced by: TABLE stores_registered CONSTRAINT fk_stores_registered FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE TABLE stores_stats CONSTRAINT fk_stats FOREIGN KEY (strid) REFERENCES stores(strid) ON DELETE CASCADE TABLE interesting CONSTRAINT interesting_strid_fkey FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE mydb=# \d stores_registered Column|Type |Modifiers --+-+- strid| character varying(35) | not null plc | text| not null user_id | character varying(30) | not null modify_date | timestamp without time zone | default now() plc_md5 | text| Indexes: idx_stores_registered_pkey PRIMARY KEY, btree (strid) idx_stores_registered_userid_plc UNIQUE, btree (user_id, plc_md5) Check constraints: stores_strid_check CHECK (strid::text ~ '[-.~a-z0-9_]'::text) stores_plc_check CHECK (plc ''::text) Foreign-key constraints: fk_stores_registered FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE stores_registered_users_fkey FOREIGN KEY (user_id) REFERENCES users(id) MATCH FULL ON DELETE CASCADE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign keys question (performance)
Hi. I have a foreign key as such: ALTER TABLE child_table ADD CONSTRAINT fk_child FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL ON DELETE CASCADE ; Questions: 1. Is MATCH FULL adding any value here? If the foreign key is just on an id column, what purpose does it serve? Without it, the results would be the same? Does it affect performance or should I leave it be? (Note that the id is a alphanumeric value) 2. More importantly, in this case basically the child_table cannot have any keys that the parent_table doesn't have either. Will INSERTs and UPDATEs to the parent_table be slower? Or will the foreign key check happen only when INSERT or UPDATE happen to the child_table? 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] Weird behavior: deleted row still in index?
Hi.
I have deleted a row from a table. Confirmed by SELECT. All
associated children tables don't have this key value either.
Yet, when I insert this row back again, the primary key index on this
table gives me a duplicate error.
As demonstrated below. PGSQL version is 9.0.5.
Is this common? I have vacuum analyzed the table three times. Still
same problem. Why is the primary key index keeping a value that was
deleted?
Short of a REINDEX (which will lock the entire tableit's a large
one) is there anything I can do to clear up the index?
Thanks!
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 0.759 ms
mydb=# INSERT INTO stores (id) VALUES ('20xrrs3');
mydb-#
ERROR: duplicate key value violates unique constraint idx_stores_pkey
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=#
--
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] Foreign keys question (performance)
On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. Thanks Albert. Very useful. I had ON DELETE...ALSO DELETE rules earlier and in some cases they let some keys go by in associated tables. Hope foreign key constraint is more reliable! PK -- 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] Weird behavior: deleted row still in index?
On Sun, Dec 4, 2011 at 7:55 PM, Szymon Guz mabew...@gmail.com wrote:
.
and then show us the whole table structure, especially any rules or
triggers.
Not many rules or triggers. See below.
I ran a REINDEX on the key allegedly being violated, and it finished
it in 30 mins or so, but still the same problem:
In fact, I deleted one rule -- and maybe I cancelled it before it
finished, but it does look gone now. Could it be not entirely deleted
and maybe corrupted somewhere out of sight?
The row is surely not in the table. Below some things..
.
VACUUM
Time: 366952.162 ms
mydb=#
mydb=#
mydb=# select * from stores where id = '20xrrs3';
id | url | user_registered | private_key | modify_date | ip | url_md5
---+-+-+-+-++-
(0 rows)
Time: 90.711 ms
mydb=#
mydb=#
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 2.519 ms
mydb=#
mydb=#
mydb=# INSERT INTO stores (id) values ('20xrrs3');
ERROR: duplicate key value violates unique constraint idx_stores_pkey
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=# \d stores
Table public.stores
Column |Type |Modifiers
-+-+-
id | character varying(35) | not null
modify_date | timestamp without time zone | default now()
ip | bigint |
Indexes:
idx_stores_pkey PRIMARY KEY, btree (id)
idx_stores_modify_date btree (modify_date)
Check constraints:
stores_id_check CHECK (id::text ~ '[-.~a-z0-9_]'::text)
Referenced by:
TABLE stores_stats CONSTRAINT fk_stats FOREIGN KEY (id)
REFERENCES stores(id) ON DELETE CASCADE
Rules:
__track_stores_deleted AS
ON DELETE TO stores
WHERE NOT (EXISTS ( SELECT stores_deleted.id
FROM stores_deleted
WHERE stores_deleted.id = old.id)) DO INSERT INTO
stores_deleted (id, modify_date, ip)
VALUES (old.id, old.modify_date, old.ip)
Any other ideas?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sporadic query not returning anything..how to diagnose?
Hi. (My pgbouncer is finally working and has results in at least a 3-fold site speed increase! YAY! Thanks to everyone who helped.) Now, a new small problem. In my PHP code I have a condition that checks for the existence of a record, and if not found, it INSERTs a new one. Here's the first SQL to check existence: # SELECT ip FROM links WHERE ip = 1585119341 AND url_md5 = 'cd4866fa5fca31dfdb07c29d8d80731c' LIMIT 1 QUERY PLAN --- Limit (cost=0.00..26.20 rows=1 width=8) (actual time=0.054..0.054 rows=1 loops=1) - Index Scan using idx_links_ip_url on links (cost=0.00..26.20 rows=1 width=8) (actual time=0.053..0.053 rows=1 loops=1) Index Cond: ((ip = 1585119341) AND (url_md5 = 'cd4866fa5fca31dfdb07c29d8d80731c'::text)) Total runtime: 0.078 ms (4 rows) About 5% of the times (in situations of high traffic), this is not returning a value in my PHP code. Because it's not found, the code tries to INSERT a new record and there's a duplicate key error, which is in the logs. The traffic to the site is much higher than the number of these entries in my log, which means it's only happening sometimes--my guess is for 5% of all queries, which is still quite significant (about 60,000 a day). I began logging these missed SELECT queries, and when I manually go into the postgresql terminal and execute those queries, the record is indeed found. No problem. So my question: is this related to some timeout or something with pgbouncer, where I suppose the connection is held for a split-second longer than it would, and therefore the query doesn't return anything? Probably an inane guess. Just wondering aloud. Welcome any thoughts on how to debug this. Btw, the logging is happening in the postgresql usual log file, the pgbouncer log just has hordes of one-liners stating how many requests per minute... 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 Thu, Nov 24, 2011 at 10:42 AM, Adrian Klaver adrian.kla...@gmail.com wrote: Also, how can I tell the pgbouncer log not to log proper connections and their closing. Right now it's filling up with nonsense. I only want it to log when there's a warning or error. http://pgbouncer.projects.postgresql.org/doc/config.html#_log_settings Thanks. Much nicer to NOT have the connect and disconnect. Question: my log is filled up with these messages every few seconds: --- 2011-11-24 07:10:02.349 12713 LOG Stats: 0 req/s, in 49 b/s, out 70 b/s,query 10743 us --- Does the 0 reqs mean that nothing is being server through 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] Table Design question for gurus (without going to NoSQL)...
On Mon, Nov 21, 2011 at 6:14 PM, Tomas Vondra t...@fuzzy.cz wrote: . An index on (a, b) can be used for queries involving only a but not for those involving only b. That is not true since 8.2 - a multi-column index may be used even for queries without conditions on leading columns. It won't be as effective as with conditions on leading columns, because the whole index must be scanned, but it's usually much cheaper than keeping two indexes (memory requirements, overhead when inserting data etc.) Check this: http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html Thanks Tomas. VERY useful information. I've decided to go with a unique multicolumn index for now. Will ask the experts here if I see some issues.. 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] Incremental backup with RSYNC or something?
On Wed, Nov 23, 2011 at 6:13 AM, Alex Thurlow alex-repo...@blastro.com wrote: On 11/22/2011 3:28 PM, Merlin Moncure wrote: .. How long is this backup taking? I have a ~100GB database that I back up with pg_dump (which compresses as it dumps if you want it to) and that only takes 35 minutes. Granted, I have it on some fast SCSI drives in RAID 1, but even a single SATA drive should still finish in a decent amount of time. Hi Alex, could you share what exact command you use? Mine are SCSI too, in RAID 10, but the dump takes over 2-3 hours (60 GB database) and the CPU consumption during this time is huge. 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] Incremental backup with RSYNC or something?
On Thu, Nov 24, 2011 at 11:53 PM, Benjamin Henrion b...@udev.org wrote: On Thu, Nov 24, 2011 at 4:49 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Wed, Nov 23, 2011 at 6:13 AM, Alex Thurlow alex-repo...@blastro.com wrote: On 11/22/2011 3:28 PM, Merlin Moncure wrote: .. How long is this backup taking? I have a ~100GB database that I back up with pg_dump (which compresses as it dumps if you want it to) and that only takes 35 minutes. Granted, I have it on some fast SCSI drives in RAID 1, but even a single SATA drive should still finish in a decent amount of time. Hi Alex, could you share what exact command you use? Mine are SCSI too, in RAID 10, but the dump takes over 2-3 hours (60 GB database) and the CPU consumption during this time is huge. I wrote a bunch of shell scripts tools to backup postgres 9.1 with rsync/ccollect (another hardlink tool), I might find the time to publish it on github once I find the time. Thanks Ben. Look forward to it. Will the script be different for version 9.0.5? Would love to have rsync working. Even without a script, just the commands will 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 Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: .. The information in the pgbouncer pseudo-database is helpful, here (psql -U youradminuser -h 127.0.0.1 pgbouncer). Thanks, I finally got it connecting. Where's the pgbouncer database. Do I need to install it? It's not installed. -- 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 Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford ... Thanks, I finally got it connecting. Where's the pgbouncer database. Do I need to install it? It's not installed. (How else should I tell the load and utilization?) Also, how can I tell the pgbouncer log not to log proper connections and their closing. Right now it's filling up with nonsense. I only want it to log when there's a warning or error. 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 4:49 PM, John R Pierce pie...@hogranch.com wrote: On 11/19/11 11:42 PM, Phoenix Kiula wrote: does this firewall block localhost at all? many don't. (I'm not at all familiar with this CSF/LFD thing) if you enable a port for TCP_IN, does it automatically allow replies back? postgres uses no UDP. The firewall is set to: 1. Ignore the process pgbouncer (in fact the entire directory in which pgbouncer sits) 2. Allow 127.0.0.1 for everything, no limitations 3. Yes, it can allow replies back (the same settings work with postgresql, should pgbouncer be any different?) I tried disabling the firewall completely. Same thing -- pgbouncer still does not work. It's not the firewall. It isn't blocking anything. Nothing in the logs related to pgbouncer. I merely mentioned it as a step. -- 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 7:43 PM, Scott Mead sco...@openscg.com wrote: On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: 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? ISTM that either your connect string is bad to the database or you already have too many clients connected to the db. Have you tried: show max_clients; select count(1) from pg_stat_activity; In postgres? Is it possible that there are just too many clients already connected? You may be on to something. And the queries results are below. (5 connections are reserved for superusers so you may be right.) MYDB=# show max_connections; max_connections - 150 (1 row) Time: 0.517 ms MYDB=# select count(1) from pg_stat_activity; count --- 144 (1 row) Time: 1.541 ms But isn't the point to connect to pgbouncer (instead of PG directly) and have it manage connections? Even when I restart PG so that its connection count is fresh and low, and immediately try to connect to pgbouncer, it still shows me an error. How can I debug that the connections are the problem? The error message in the pgbouncer log points to some FATAL password authentication. If not, then it's probably just your connect string ( in pgbouncer.ini) not being quite right. You are using 127.0.0.1 for connecting, is postgres even listening? netstat -lntp | grep 5432 Yes. It is. netstat -lntp | grep 5432 tcp0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 26220/postmaster tcp0 0 :::5432 :::* LISTEN 26220/postmaster netstat -lntp | grep 6432 tcp0 0 127.0.0.1:6432 0.0.0.0:* LISTEN 10854/pgbouncer Any ideas? -- 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 7:52 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 7:43 PM, Scott Mead sco...@openscg.com wrote: On Sun, Nov 20, 2011 at 6:21 AM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 20, 2011 at 8:32 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: 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? ISTM that either your connect string is bad to the database or you already have too many clients connected to the db. Have you tried: show max_clients; select count(1) from pg_stat_activity; In postgres? Is it possible that there are just too many clients already connected? You may be on to something. And the queries results are below. (5 connections are reserved for superusers so you may be right.) MYDB=# show max_connections; max_connections - 150 (1 row) Time: 0.517 ms MYDB=# select count(1) from pg_stat_activity; count --- 144 (1 row) Time: 1.541 ms But isn't the point to connect to pgbouncer (instead of PG directly) and have it manage connections? Even when I restart PG so that its connection count is fresh and low, and immediately try to connect to pgbouncer, it still shows me an error. How can I debug that the connections are the problem? The error message in the pgbouncer log points to some FATAL password authentication. If not, then it's probably just your connect string ( in pgbouncer.ini) not being quite right. You are using 127.0.0.1 for connecting, is postgres even listening? netstat -lntp | grep 5432 Yes. It is. netstat -lntp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 26220/postmaster tcp 0 0 :::5432 :::* LISTEN 26220/postmaster netstat -lntp | grep 6432 tcp 0 0 127.0.0.1:6432 0.0.0.0:* LISTEN 10854/pgbouncer Any ideas? Just to add, the connection string I try for pgbouncer is EXACTLY the same as the one I use to connect directly to PG, but I add the port number. For Direct PG (works) -- pg_connect(host=localhost dbname=$db user=myuser password=mypass); For Pgbouncer (does NOT work) -- pg_connect(host=localhost dbname=$db port=6432 user=myuser password=mypass); Given that both PG and postgresql are alive and kicking on 5432 and 6432 ports respectively, as shown in the netstat output above, I wonder if the connection string is the 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] Huge number of INSERTs
On Sun, Nov 20, 2011 at 2:11 PM, Tomas Vondra t...@fuzzy.cz wrote:
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%).
Thanks Tomas. And others.
Some observations and questions from my ongoing saga.
I have disabled all ADDing of data (INSERT + UPDATE) and just allowed
SELECTs so far. Site is under maintenance.
For a moment there, I unleashed the valve and allowed the INSERT
functionality. The log was immediately flooded with this:
LOG: duration: 6851.054 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6848.266 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6846.672 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6853.451 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6991.966 ms statement: select nextval('maintable_id_seq')
LOG: duration: 8244.315 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6991.071 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6990.043 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6988.483 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6986.793 ms statement: select nextval('maintable_id_seq')
LOG: duration: 6985.802 ms statement: select nextval('maintable_id_seq')
...
I hope it's just because of too much load that even a simple query
such as this was taking so much time?
Other queries taking too much time are also indexed queries!
Anyway, right now, with that valve closed, and only SELECTs allowed,
here's the stats:
vmstat 5 10
procs ---memory-- ---swap-- -io --system--
-cpu--
r b swpd free buff cache si sobibo in cs us sy id wa st
7 1 1352 47596 26412 618996033 5228 243 17 10 51
19 26 4 0
16 1 1352 45520 26440 619165600 1230 3819 1368 65722 68
31 1 0 0
9 0 1352 61048 26464 617468800 1000 4290 1370 65545 67
32 1 0 0
27 1 1352 51908 26508 618385200 1332 3916 1381 65684 68
32 1 0 0
29 0 1352 48380 26536 618576400 977 3983 1368 65684 67
32 1 0 0
24 1 1352 46436 26576 618908000 220 4135 1373 65743 66
33 1 0 0
25 1 1352 46204 26616 619145200 0 3963 1348 66867 67
32 1 0 0
13 1 1352 57444 26692 61932200024 4038 1436 66891 66
32 2 0 0
22 1 1352 51300 26832 619673600 439 5088 1418 66995 66
31 2 0 0
26 1 1352 51940 26872 619838400 0 3354 1385 67122 67
31 2 0 0
iostat -d -x 5 3
Linux 2.6.18-238.9.1.el5 (host.MYDB.com)11/20/2011
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 86.34 151.41 392.90 92.36 41796.00 1949.66
90.15 1.593.27 0.40 19.65
sda1 0.00 0.00 0.00 0.00 0.01 0.0022.38
0.003.04 3.01 0.00
sda2 0.27 8.20 0.06 0.22 3.3567.05 255.22
0.01 34.36 3.02 0.08
sda3 1.0213.83 3.29 3.65 165.35 139.7543.96
0.16 22.52 7.32 5.08
sda4 0.00 0.00 0.00 0.00 0.00 0.00 2.00
0.000.00 0.00 0.00
sda5 0.57 3.63 0.64 0.7226.5234.7245.16
0.02 11.26 4.67 0.63
sda6 0.21 0.57 0.41 0.2713.79 6.7630.24
0.02 24.31 16.51 1.12
sda7 0.24 5.36 0.11 0.44 1.9246.3286.94
0.02 44.21 7.99 0.44
sda8 2.24 2.25 1.22 0.9827.6225.8324.33
0.06 27.61 18.20 4.00
sda9 81.79 117.57 387.18 86.08 41557.45 1629.24
91.25 1.302.75 0.39 18.30
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda
[GENERAL] Table Design question for gurus (without going to NoSQL)...
Hi. Want to start another thread, loosely related to the performance problems thread I have going. Need some DB design guidance from the gurus here. My big table now has about 70 million rows, with the following columns: alias | character varying(35) url | text modify_date | timestamp without time zone ip | bigint For each IP address (user of my application) I want to have a unique URL. So I used to have a UNIQUE constraint on IP, URL. But the index based on this became huge, as some URLs are gigantic. so I introduced an md5 of the URL: url_md5 | varchar(32) I now have two scenarios: 1. To have an index (unique?) on (ip, url_md5) 2. To not have an index on just the ip. This way a query that tries to match ...WHERE ip = 999 AND url_md5 = 'md5 here'... will still look only at the ip bit of the index, then refine it with the url_md5. The good thing about #2 is the size of index remains very small with only a bigint field (ip) being indexed. The bad thing about #2 is that each query of ...WHERE ip = 999 AND url_md5 = 'md5 here'... will have to refine the indexed IP. If one IP address has put in a lot of URLs, then this becomes a bit slow. As is now happening, where I have users who have over 1 million URLs each! Questions: 1. Instead of md5, is there any integer hashing algorithm that will allow me to have a bigint column and save a lot hopefully in both storage space and speed? (Some very useful points mentioned here: http://stackoverflow.com/questions/1422725/represent-md5-hash-as-an-integer ) 2. If I do go with the above scenario #1 of a joint index, is there any way I can save space and maintain speed? Partitioning etc are out of the question. With a growing web database, I am sure many people face this situation. Are nosql type databases the only sane solution to such massive volumes and throughput expectations (e.g., CouchDb's MemBase)? Many thanks for any ideas or pointers! -- 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] Table Design question for gurus (without going to NoSQL)...
On Sun, Nov 20, 2011 at 9:33 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Hi. Want to start another thread, loosely related to the performance problems thread I have going. Need some DB design guidance from the gurus here. My big table now has about 70 million rows, with the following columns: alias | character varying(35) url | text modify_date | timestamp without time zone ip | bigint For each IP address (user of my application) I want to have a unique URL. So I used to have a UNIQUE constraint on IP, URL. But the index based on this became huge, as some URLs are gigantic. so I introduced an md5 of the URL: url_md5 | varchar(32) I now have two scenarios: 1. To have an index (unique?) on (ip, url_md5) 2. To not have an index on just the ip. This way a query that tries to match ...WHERE ip = 999 AND url_md5 = 'md5 here'... will still look only at the ip bit of the index, then refine it with the url_md5. The good thing about #2 is the size of index remains very small with only a bigint field (ip) being indexed. The bad thing about #2 is that each query of ...WHERE ip = 999 AND url_md5 = 'md5 here'... will have to refine the indexed IP. If one IP address has put in a lot of URLs, then this becomes a bit slow. As is now happening, where I have users who have over 1 million URLs each! Questions: 1. Instead of md5, is there any integer hashing algorithm that will allow me to have a bigint column and save a lot hopefully in both storage space and speed? (Some very useful points mentioned here: http://stackoverflow.com/questions/1422725/represent-md5-hash-as-an-integer ) 2. If I do go with the above scenario #1 of a joint index, is there any way I can save space and maintain speed? Partitioning etc are out of the question. With a growing web database, I am sure many people face this situation. Are nosql type databases the only sane solution to such massive volumes and throughput expectations (e.g., CouchDb's MemBase)? Many thanks for any ideas or pointers! I thought of adding a bigserial (serial8) column instead of varchar(32) for the md5. But postgresql tells me that: -- ERROR: type bigserial does not exist -- Why is this? Why can't I create a column with this type? Whats the current syntax? 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 Mon, Nov 21, 2011 at 4:13 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On Sunday, November 20, 2011 3:56:18 am Phoenix Kiula wrote: Any ideas? Just to add, the connection string I try for pgbouncer is EXACTLY the same as the one I use to connect directly to PG, but I add the port number. That may be the problem. The Postgres server and pgbouncer are not the same thing. Visual aids: Client -- pgbouncer -- Postgres server Client credentials pgbouncer auth Postgres auth auth file Pg pg_shadow Thanks for this. (1) Do I need to create a new user for Pgbouncer then? (2) What info goes in the auth_file -- the Pgbouncer user/password or the Postgres user/password? In any case, I have kept both the user name and passwords the same for now. But I have not created anything for Pgbouncer specifically other than to put the info in auth_file. Have I missed a step? -- 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 Mon, Nov 21, 2011 at 10:18 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 03:33, Amitabh Kant napsal(a): On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula phoenix.ki...@gmail.com 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. No, it shouldn't. It should contain credentials for connecting to the pgbouncer. The database credentials should go to the connection string in '[databases]' section of your ini file. Thanks Tomas and everyone. I have the following passwords: 1. Pgbouncer.ini file [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYDB_MYDB client_encoding=utf8 port=5432 password=='bypass' 2. In the auth_file (with auth_type set to md5) auth_type = md5 auth_file = /var/lib/pgsql/pgbouncer.txt Inside the auth_file: me an md5 string 3. In the PHP file where I need to call with pg_connect() function. This is the postgresql database user as usual. pg_connect(host=127.0.0.1 dbname=$db port=6432 user=$user password=$pass); Questions: a. For #2, the pgbouncer password, do I need to create this me user somewhere, or just writing here in the auth_file is fine? I have not created this user anywhere else yet. Just written the user name and md5 of the password in the auth_file. b. In the connection string in #3 above, I need to be mentioning the pgbouncer user name, right? Will the password then be md5 as in auth_file? Or nothing? -- 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] Table Design question for gurus (without going to NoSQL)...
On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: How about having 2 indexes: one on each of ip url_md5? Pg will combine the indexes as required, or will just use one if that is best. Thanks Gavin. Question: what if I have a joined index? If from a joined index I only use the first column (say, ip) will a joined index still be used? It is cleaner to create two indexes for the two columns. Which is recommended? -- 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 Mon, Nov 21, 2011 at 10:54 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 21.11.2011 02:44, Phoenix Kiula napsal(a): Thanks Tomas and everyone. I have the following passwords: 1. Pgbouncer.ini file [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYDB_MYDB client_encoding=utf8 port=5432 password=='bypass' 2. In the auth_file (with auth_type set to md5) auth_type = md5 auth_file = /var/lib/pgsql/pgbouncer.txt Inside the auth_file: me an md5 string 3. In the PHP file where I need to call with pg_connect() function. This is the postgresql database user as usual. pg_connect(host=127.0.0.1 dbname=$db port=6432 user=$user password=$pass); I guess the $user is 'me' (as stated in pgbouncer.txt) and the password corresponds to pgbouncer.txt (i.e. when MD5-hashed the result is equal to the value in the file). Questions: a. For #2, the pgbouncer password, do I need to create this me user somewhere, or just writing here in the auth_file is fine? I have not created this user anywhere else yet. Just written the user name and md5 of the password in the auth_file. No. The user is created by listing the username/password in the auth_file. b. In the connection string in #3 above, I need to be mentioning the pgbouncer user name, right? Will the password then be md5 as in auth_file? Or nothing? You need to put the pgbouncer user name (as listed in the auth_file). The password has to be the actual value, not the hash. Otherwise it'd be equal to auth_type=plain. Very clear. So all the passwords are now correct. Now, when I do service pgbouncer restart, it shows me FAILED. I'm on CentOS 5, 64 bit. PG is 9.0.5. The PG log has nothing about this. The pgbouncer log has nothing either, just a huge list of: 2011-11-20 09:03:46.855 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us 2011-11-20 09:04:46.856 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us 2011-11-20 09:05:46.856 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us 2011-11-20 09:06:46.857 10854 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us Any ideas on how I can determine what's going wrong? -- 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?
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
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] 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] 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] 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 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 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] 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] 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
Re: [GENERAL] Huge number of INSERTs
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. What version of PostgreSQL? What OS? What OS tuning, if any, have you done? (Have you increased readahead? Changed swappiness, turned off atime on your mounts, made syslogging asynchronous, etc?). Does your RAID have battery-backed cache? What are the cache settings? PG 9.0.5 CentOS 5 64 bit OS tuning - lots of it since the beginning of time. What specifically would you like to know? Please let me know and I can share info. Like SHM Max and Min variables type of things? RAID has the 3Com battery backed cache, yes. Not reporting any errors. What is the nature of the queries? Single record inserts or bulk? Same for the selects. Have you run analyze on them and optimized the queries? Simple INSERTs. Into a table with 6 columns. Column 1 is a primary key, column 5 is a date. There are two indexes on this table, on the pkey (col1) and one on the date (col5). SELECTs are simple straight selects, based on pkey with limit 1. No joins, no sorting. What is the typical duration of your queries? Are lots of queries duplicated (caching candidates)? The bulk of the big SELECTs are in memcached. Much faster than PG. It's INSERTs I don't know what to do with. Memcached is not a good solution for INSERTs, which do need to go into a proper DB. What is the size of your database? Do you have any bandwidth bottleneck to the Internet? Full DB: 32GB The big table referenced above: 28 GB It's inserts into this one that are taking time. Is this your database server only or is it running web and/or other processes? How long does a typical web-request take to handle? How can I measure the time taken per web request? Nginx is super fast, based on apache bench. Apache -- how do I test it? Don't want to do fake inserts. With selects, apache bench uses memcached instead.. At first blush, and shooting in the dark, I'll guess there are lots of things you can do. Your shared_buffers seems a bit low - a rough starting point would be closer to 25% of your available RAM. If I make shared_buffers too high, (2GB25% of my 8GB RAM), this brings the server to its knees instantly. Probably because I have apache, nginx, memcached running on the same server. Nginx and memcached are negligible in terms of memory consumption. You are a prime candidate for using a connection pooler. I have had good luck with pgbouncer but there are others. Will pgbouncer or pgpool help with INSERTs? Note: bandwidth bottlenecks can screw everything up. Your web processes stay alive dribbling the data to the client and, even though they don't have much work to do, they are still holding database connections, using memory, etc. Such cases can often benefit from a reverse proxy. In addition to nginx proxying to apache, I am using CloudFlare. Is this a problem? Many thanks for the informative seeking of information. Hope the above details shed more light? I've currently disabled any INSERT functions on my website...but even with disabled INSERTs and only SELECTs alive, I still see the psql: FATAL: sorry, too many clients already message. Btw, I don't see any PG logs. What could be the problem? The config says that it should store it in the directory pg_log, but this directory is empty. Also, here's the output of vmstat 5 10 vmstat 5 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 11 3 17672 44860 24084 655934800 147 275 17 63 64 26 9 1 0 14 3 14376 48820 24208 6555968 4380 24374 1287 1529 56176 73 26 1 0 0 13 2 14112 47320 24344 6555916 102 27350 1219 1523 57979 72 27 1 0 0 20 2 14100 46672 24468 655342023 28473 1172 1499 59492 71 27 1 0 0 17 3 10400 46284 24524 6548520 7301 22237 1164 1482 59761 68 31 1 0 0 18 2 7984 45708 24712 6552308 4780 26966 1164 1487 58218 69 30 1 0 0 12 2 7980 47636 24816 654902021 25210 1134 1486 57972 71 27 1 1 0 18 1 7924 44300 25108 654883610 25918 1310 1515 60067 70 28 1 1 0 18 2 7812 45444 25288 6543668 260 26474 1326 1465 62633 70 29 1 0 0 22 2 7800 46852 25488 654236000 25620 1258 1510 63181 69 29 1 1 0 vmstat 5 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 10 4 7712 46420 27416 644962800 167 275 18 114 64 26 9 1 0 18 2 7704 47196 27580 644825240 22546 1146 1507 55693 68 26 3 4 0 20 2 7724 47616 27628 644408431 25419 1114 1424 58069 72 27 1 0 0 15 2 7840 47240 27852 6443056
[GENERAL] Installed. Now what?
Hi. I use CentOS 5, 64bit. PG is 9.0.5. I did yum install pgbouncer and got this: --- Running Transaction Installing : libevent 1/2 Installing : pgbouncer 2/2 warning: /etc/pgbouncer.ini created as /etc/pgbouncer.ini.rpmnew Installed: pgbouncer.x86_64 0:1.4.2-1.rhel5 Dependency Installed: libevent.x86_64 0:2.0.12-1.rhel5 --- 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? 2. Does pgbouncer start automatically every time PG starts, or do I have to setup a script to do so? How does pgbouncer start and keep running? 3. How do I access pgbouncer inside my PHP code? Do I need to change anything at all, can I just use the usual pg_connect() function? 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] Incremental backup with RSYNC or something?
On Mon, Nov 14, 2011 at 1:45 PM, Venkat Balaji venkat.bal...@verse.in wrote: Question: what can I do to rsync only the new additions in every table starting 00:00:01 until 23:59:59 for each day? A table level replication (like Slony) should help here. Slony needs more than one physical server, right? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Huge number of INSERTs
Hi. I have a massive traffic website. I keep getting FATAL: Sorry, too many clients already problems. It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10, with RAM of 8GB. Server is Nginx backed by Apache for the php. Postgresql just has to do about 1000 SELECTs a minute, and about 200 INSERTs a minute. Maybe 10-20 UPDATEs. My conf file is below. My vmstat + top are below too. What else can I do? max_connections = 350 shared_buffers = 256MB effective_cache_size= 1400MB # Nov 11 2011, was 1500MB temp_buffers= 16MB # min 800kB maintenance_work_mem= 256MB# min 1MB wal_buffers = 12MB # min 32kB fsync = on # turns forced synchronization on or off checkpoint_segments = 128 # was 128 checkpoint_timeout = 1000 # was 1000 enable_indexscan= on #- LOGGING -- log_directory = 'pg_log' log_filename= 'pglog.postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age= 1d log_min_messages= 'error' log_min_error_statement = 'error' log_min_duration_statement = 5000 # In milliseconds client_min_messages = 'warning' log_duration= off #- AUTOVAC -- autovacuum = on autovacuum_max_workers = 5 # max number of autovacuum subprocesses autovacuum_vacuum_cost_delay= 10ms autovacuum_vacuum_cost_limit= 350 vmstat procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 6 1 4044 101396 84376 556959200 168 221 326 200 55 22 21 1 0 top - 19:43:49 up 7:33, 3 users, load average: 19.63, 19.61, 19.25 Tasks: 663 total, 19 running, 644 sleeping, 0 stopped, 0 zombie Cpu(s): 65.8%us, 15.5%sy, 0.0%ni, 1.7%id, 0.1%wa, 0.0%hi, 17.0%si, 0.0%st Mem: 8177444k total, 8062608k used, 114836k free,84440k buffers Swap: 2096440k total, 4044k used, 2092396k free, 5572456k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 6337 postgres 15 0 397m 100m 97m S 2.3 1.3 0:16.56 postgres: MYDB_MYDB MYDB 127.0.0.1(60118) SELECT 424 postgres 15 0 397m 101m 98m S 2.0 1.3 1:01.79 postgres: MYDB_MYDB MYDB 127.0.0.1(37036) SELECT 2887 postgres 15 0 397m 100m 98m S 2.0 1.3 0:34.55 postgres: MYDB_MYDB MYDB 127.0.0.1(57710) SELECT 3030 postgres 15 0 397m 101m 98m S 2.0 1.3 0:32.35 postgres: MYDB_MYDB MYDB 127.0.0.1(45574) SELECT 5273 postgres 15 0 397m 100m 98m S 2.0 1.3 0:22.38 postgres: MYDB_MYDB MYDB 127.0.0.1(52143) SELECT 5560 postgres 15 0 397m 100m 98m S 2.0 1.3 0:20.05 postgres: MYDB_MYDB MYDB 127.0.0.1(56767) SELECT 5613 postgres 16 0 397m 100m 98m S 2.0 1.3 0:19.51 postgres: MYDB_MYDB MYDB 127.0.0.1(57745) SELECT 5652 postgres 15 0 397m 100m 98m S 2.0 1.3 0:19.76 postgres: MYDB_MYDB MYDB 127.0.0.1(58464) SELECT 32062 postgres 15 0 397m 101m 98m S 2.0 1.3 1:55.79 postgres: MYDB_MYDB MYDB 127.0.0.1(55341) SELECT 358 postgres 15 0 397m 101m 98m S 1.6 1.3 1:04.11 postgres: MYDB_MYDB MYDB 127.0.0.1(35841) SELECT 744 postgres 15 0 397m 101m 98m S 1.6 1.3 0:53.01 postgres: MYDB_MYDB MYDB 127.0.0.1(50058) SELECT 903 postgres 15 0 397m 101m 98m S 1.6 1.3 0:50.79 postgres: MYDB_MYDB MYDB 127.0.0.1(51258) SELECT 976 postgres 15 0 397m 101m 98m S 1.6 1.3 0:48.24 postgres: MYDB_MYDB MYDB 127.0.0.1(52828) SELECT 1011 postgres 15 0 397m 101m 98m S 1.6 1.3 0:48.20 postgres: MYDB_MYDB MYDB 127.0.0.1(53503) SELECT 2446 postgres 15 0 397m 101m 98m S 1.6 1.3 0:38.97 postgres: MYDB_MYDB MYDB 127.0.0.1(51982) SELECT 2806 postgres 16 0 397m 100m 98m R 1.6 1.3 0:34.83 postgres: MYDB_MYDB MYDB 127.0.0.1(57204) SELECT 3361 postgres 15 0 397m 101m 98m R 1.6 1.3 0:30.32 postgres: MYDB_MYDB MYDB 127.0.0.1(48782) idle 3577 postgres 15 0 397m 100m 98m S 1.6 1.3 0:27.92 postgres: MYDB_MYDB MYDB 127.0.0.1(52019) SELECT 3618 postgres 15 0 397m 101m 98m S 1.6 1.3 0:27.53 postgres: MYDB_MYDB MYDB 127.0.0.1(41291) SELECT 3704 postgres 15 0 397m 100m 98m S 1.6 1.3 0:25.70 postgres: MYDB_MYDB MYDB 127.0.0.1(43642) SELECT 5073 postgres 15 0 397m 100m 98m S 1.6 1.3 0:23.92 postgres: MYDB_MYDB MYDB 127.0.0.1(47398) SELECT 5185 postgres 15 0 397m 100m 98m S 1.6 1.3 0:23.03 postgres: MYDB_MYDB MYDB 127.0.0.1(49137) SELECT 5528 postgres 15 0 397m 100m 98m S 1.6 1.3 0:20.81 postgres: MYDB_MYDB MYDB 127.0.0.1(55531) SELECT 5549 postgres 15 0 397m 100m 98m S 1.6 1.3 0:20.71 postgres: MYDB_MYDB MYDB 127.0.0.1(56391)
[GENERAL] Incremental backup with RSYNC or something?
Hi. I currently have a cronjob to do a full pgdump of the database every day. And then gzip it for saving to my backup drive. However, my db is now 60GB in size, so this daily operation is making less and less sense. (Some of you may think this is foolish to begin with). Question: what can I do to rsync only the new additions in every table starting 00:00:01 until 23:59:59 for each day? Searching google leads to complex things like incremental WAL and whatnot, or talks of stuff like pgcluster. I'm hoping there's a more straightforward core solution without additional software or PHD degrees. Many thanks for any ideas! PK -- 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] Incremental backup with RSYNC or something?
On Sun, Nov 13, 2011 at 8:42 PM, Robins Tharakan
robins.thara...@comodo.com wrote:
Hi,
Well, the 'complex' stuff is only as there for larger or high-traffic DBs.
Besides at 60GB that is a largish DB in itself and you should begin to try
out a few other backup methods nonetheless. That is moreso, if you are
taking entire DB backups everyday, you would save a considerable lot on
(backup) storage.
Thanks. I usually keep only the last 6 days of it. And monthly backups
as of Day 1. So it's not piling up or anything.
What other methods do you recommend? That was in fact my question.
Do I need to install some modules?
Anyway, as for pgdump, we have a DB 20x bigger than you mention (1.3TB) and
it takes only half a day to do a pgdump+gzip (both). One thing that comes to
mind, how are you compressing? I hope you are doing this in one operation
(or at least piping pgdump to gzip before writing to disk)?
I'm gzipping with this command (this is my backup.sh)--
BKPFILE=/backup/pg/dbback-${DATA}.sql
pg_dump MYDB -U MYDB_MYDB -f ${BKPFILE}
gzip --fast ${BKPFILE}
Is this good enough? Sadly, this takes up over 97% of the CPU when it's running!
--
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] Incremental backup with RSYNC or something?
On Sun, Nov 13, 2011 at 10:45 PM, Andy Colson a...@squeakycode.net wrote:
On 11/13/2011 07:51 AM, Gregg Jaskiewicz wrote:
pg_dump -Fc already compresses, no need to pipe through gzip
I dont think that'll use two core's if you have 'em. The pipe method will
use two cores, so it should be faster. (assuming you are not IO bound).
I am likely IO bound. Anyway, what's the right code for the pipe
method? I think the earlier recommendation had a problem as -Fc
already does compression.
Is this the right code for the FASTEST possible backup if I don't care
about the size of the dump, all I want is that it's not CPU-intensive
(with the tables I wish excluded) --
BKPFILE=/backup/pg/dbback-${DATA}.sql
pg_dump MYDB -T excludetable1 -T excludetable2 -U MYDB_MYDB | gzip
--fast ${BKPFILE}
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] Foreign key check only if not null?
Hi, I bet this is a simple solution but I have been racking my brains. I have a column in my table: user_id varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise, NULL is ok. (Because the functionality in question is open to both unregistered and registered users). Any idea on how I can implement a FOREIGN KEY constraint? Or do I need a pre-insert and pre-update RULE for this? 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] Regexp match not working.. (SQL help)
On Wed, May 11, 2011 at 11:18 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I have a text column in a table, which I want to search through -- seeking the occurrence of about 300 small strings in it. Let's say the table is like this: table1 ( id bigint primary key ,mytext text ,mydate timestamp without time zone ); I am using this SQL: SELECT id FROM table1 WHERE mytext ~* E'sub1|sub2|sub3|sub4...' LIMIT 10; This is basically working, but some of the mytext columns being returned that do not contain any of these substrings. Am I doing the POSIX regexp wrongly? This same thing works when I try it in PHP with preg_match. But not in Postgresql. I have tried several variations too: WHERE mytext ~* E'(sub1)(sub2)(sub3)(sub4)...' None of this is working. I cannot seem to get out the results that do NOT contain any of those strings. Appreciate any pointers! Thanks! My bad. I figured out that the pipe should only separate the strings to be searched. I had one stray pipe at the end: SELECT id FROM table1 WHERE mytext ~* E'sub1|sub2|sub3|subXY|' LIMIT 10; This meant that it was matching, well basically anything. Sorry. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Massive delete from a live production DB
Hi Been reading some old threads (pre 9.x version) and it seems that the consensus is to avoid doing massive deletes from a table as it'll create so much unrecoverable space/gaps that vacuum full would be needed. Etc. Instead, we might as well do a dump/restore. Faster, cleaner. This is all well and good, but what about a situation where the database is in production and cannot be brought down for this operation or even a cluster? Any ideas on what I could do without losing all the live updates? I need to get rid of about 11% of a 150 million rows of database, with each row being nearly 1 to 5 KB in size... Thanks! Version is 9.0.4. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Massive delete from a live production DB
[snip] You can easily install it as a contrib . Just read the installation guide or the man Page. Thanks Eric. How though? The instructions here -- http://reorg.projects.postgresql.org/pg_reorg.html -- are woefully incomplete. I have a standard PG install on WHM/Cpanel type server. I know the path to pgsql. I can download the pg_reorg.1.1.5.tar.gz into this folder and untar it. Then what? A make and make install does not work -- the usual ./config stuff is not available. Sorry, I need more detailed steps. I googled and found this: http://www.postgresql.org/docs/9.0/static/contrib.html But the recommended steps: gmake gmake install ...don't work either. Here's what I see: [mydomain] src cd pg_reorg-1.1.5 [mydomain] pg_reorg-1.1.5 gmake Makefile:13: ../../src/Makefile.global: No such file or directory gmake: *** No rule to make target `../../src/Makefile.global'. Stop. [mydomain] pg_reorg-1.1.5 What am I missing? PS. If pg_reorg is such a useful contribution, why can't it be included with PG? Seems like a very useful tool anyway! No? 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] Regexp match not working.. (SQL help)
I have a text column in a table, which I want to search through -- seeking the occurrence of about 300 small strings in it. Let's say the table is like this: table1 ( id bigint primary key ,mytext text ,mydate timestamp without time zone ); I am using this SQL: SELECT id FROM table1 WHERE mytext ~* E'sub1|sub2|sub3|sub4...' LIMIT 10; This is basically working, but some of the mytext columns being returned that do not contain any of these substrings. Am I doing the POSIX regexp wrongly? This same thing works when I try it in PHP with preg_match. But not in Postgresql. I have tried several variations too: WHERE mytext ~* E'(sub1)(sub2)(sub3)(sub4)...' None of this is working. I cannot seem to get out the results that do NOT contain any of those strings. Appreciate any pointers! 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] NULL saves disk space?
On Thu, Apr 28, 2011 at 10:59 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Apr 27, 2011 at 5:24 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Possibly a dumb question but there isn't much about this. http://www.google.com/search?sourceid=chromeie=UTF-8q=postgresql+null+value+disk+space I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I save disk space by having them as NULL instead of FALSE? So my application would have conditional code for NULL and TRUE, instead of FALSE and TRUE. Thanks... Yes, NULL values take no additional space, but the row needs a null bitmap so it is possible that if this was the only NULL then it could occupy more space. If you have multiple columns, then you should use NULLs. Thanks Simon. (And others for good advice, but that was not my question. I already know using boolean as TRUE/FALSE is sensible. But I have a peculiar reason for asking what I am.) Simon, if I understand you correctly -- more than one column in a row should have NULL for NULL to be useful in saving space? What if in a row there are many columns but only one will be NULL? 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] Index not being used for UPDATE?
Hi. Just want to check why, in an UPDATE sql, the JOIN condition is not making use of the index? In both tables being joined, the column in question is in fact the primary key! Table structure and query below. All I want is to take values from a smaller accesscount table and update from it the values in the TABLE1 table, which is a larger table. The query plan shows sequential scan of both the tables. Why is this and how can I work around it? Thanks! * Table public.TABLE1* Column|Type |Modifiers --+-+- alias| character varying(35) | not null som | text| not null user_id | character varying(30) | not null modify_date | timestamp without time zone | default now() volatility | character varying(32) | acount | integer | Indexes: idx_TABLE1_pkey PRIMARY KEY, btree (alias) idx_TABLE1_userid btree (user_id) CLUSTER *Table public.accesscount * Column| Type | Modifiers --+---+--- alias| character varying(35) | not null acount | integer | Indexes: idx_9 PRIMARY KEY, btree (alias) *=# explain *update TABLE1 set acount = v.acount from accesscount v where TABLE1.alias = v.alias ; * * *QUERY PLAN * -- Update (cost=22985.69..1088981.66 rows=613453 width=173) - Hash Join (cost=22985.69..1088981.66 rows=613453 width=173) Hash Cond: ((TABLE1.alias)::text = (v.alias)::text) - Seq Scan on TABLE1 (cost=0.00..410625.10 rows=12029410 width=159) - Hash (cost=11722.53..11722.53 rows=613453 width=21) - Seq Scan on accesscount v (cost=0.00..11722.53 rows=613453 width=21) (6 rows) Time: 0.848 ms
[GENERAL] NULL saves disk space?
Possibly a dumb question but there isn't much about this. http://www.google.com/search?sourceid=chromeie=UTF-8q=postgresql+null+value+disk+space I have some BOOLEAN columns. 90% of the cases of the columns is FALSE. Do I save disk space by having them as NULL instead of FALSE? So my application would have conditional code for NULL and TRUE, instead of FALSE and TRUE. Thanks...
Re: [GENERAL] Partitioning an existing table
On Tue, Apr 26, 2011 at 8:28 PM, Greg Smith g...@2ndquadrant.com wrote: On 04/25/2011 10:10 AM, Vick Khera wrote: Basically, you create your partitions and set up the necessary triggers you want (I re-write the app to insert directly into the correct partition). Then all new data starts going into the partitions. Next, write a program that loops over the current master table, and moves the data into each partition some small hunk at a time, in a transaction. This can take a long time. For us, it took about 7 days to move O(100m) rows. Then, when you're done, truncate the master table, and enforce that no new data is allowed to be inserted into it. Vick's presentation at http://cdn.mailermailer.com/documents/PGCon2008TablePartitioningCaseStudy.pdf Thanks everyone for the excellent suggestions. Vick/Greg, thanks in particular for this reference. The doc gives me ideas for other things too! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Tue, Apr 26, 2011 at 3:24 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Apr 25, 2011 at 8:50 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tuesday, April 26, 2011, Tomas Vondra t...@fuzzy.cz wrote: Dne 25.4.2011 18:16, Phoenix Kiula napsal(a): Sorry, spoke too soon. I can COPY individual chunks to files. Did that by year, and at least the dumping worked. Now I need to pull the data in at the destination server. If I COPY each individual file back into the table, it works. Slowly, but seems to work. I tried to combine all the files into one go, then truncate the table, and pull it all in in one go (130 million rows or so) but this time it gave the same error. However, it pointed out a specific row where the problem was: COPY links, line 15272357: 16426447 9s2q7 9s2q7 N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;i...; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Is this any use at all? Would appreciate any pointers! So the dump worked fina and it fails when loading it back into the DB? Have you checked the output file (just see the tail). Can you post the part that causes issues? Just the line 16426447 and few lines around. regards Tomas From the old server: Yearly COPY files worked. Pg_dumpall was giving problems. In the new server: COPY FROM worked. All files appear to have been copied. Then I create the primary key index, and another index. Many records are there, but many are not there! There's no error, just that some records/rows just didn't make it. Are you sure you're getting all the data out of the source (broken) database you think you are? Are you sure those rows are in the dump? Actually I am not. Some rows are missing. Will a COUNT(*) on the two databases -- old and new -- be sufficient and reliable information about the number of rows that went AWOL? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 9.0 - Default postgresql.conf?
Hi, Is there any place I can download the default postgresql.conf that comes with 9.0? 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] PG 9.0 - Default postgresql.conf?
On Wed, Apr 27, 2011 at 1:18 AM, Magnus Hagander mag...@hagander.net wrote: On Tue, Apr 26, 2011 at 19:11, Phoenix Kiula phoenix.ki...@gmail.com wrote: Hi, Is there any place I can download the default postgresql.conf that comes with 9.0? http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=src/backend/utils/misc/postgresql.conf.sample;h=a3880da7dac5d8ff55dc8f5f7a3f1fc8052627b0;hb=REL9_0_STABLE (note that initdb will make a few changes to it depending on your system) If you have installed PostgreSQL, you should also be able to locate a postgresql.conf.sample file on your local system. Exactly were it is depends on how you intalled PostgreSQL. Thanks Magnus. My PGSQL was installed on Linux using the Yum repositories. There's no file called *.conf.sample. What changes does initdb make -- can I make them by myself? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG 9.0 - Default postgresql.conf?
On Wed, Apr 27, 2011 at 1:53 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Wed, Apr 27, 2011 at 1:18 AM, Magnus Hagander mag...@hagander.net wrote: On Tue, Apr 26, 2011 at 19:11, Phoenix Kiula phoenix.ki...@gmail.com wrote: Hi, Is there any place I can download the default postgresql.conf that comes with 9.0? http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=src/backend/utils/misc/postgresql.conf.sample;h=a3880da7dac5d8ff55dc8f5f7a3f1fc8052627b0;hb=REL9_0_STABLE (note that initdb will make a few changes to it depending on your system) If you have installed PostgreSQL, you should also be able to locate a postgresql.conf.sample file on your local system. Exactly were it is depends on how you intalled PostgreSQL. Thanks Magnus. My PGSQL was installed on Linux using the Yum repositories. There's no file called *.conf.sample. What changes does initdb make -- can I make them by myself? Btw, the default file is FAILING. The server does not restart. How can I check what the problem is? I don't see any errors at least on the command line.All I see is: service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [FAILED] 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] Partitioning an existing table
Hi. The partitioning documentation in PG is very clear on how to partition a new table. Create child tables, and have triggers that manage INSERT, UPDATE and DELETE commands. How about doing this with existing massive tables? (Over 120 million rows) I could create a new parent table with child tables, and then INSERT all these millions of rows to put them into the right partition. But is that recommended? 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 8:35 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 8:20 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: In the pg_dumpall backup process, I get this error. Does this help? Well, not really - it's just another incarnation of the problem we've already seen. PostgreSQL reads the data, and at some point it finds out it needs to allocate 4294967293B of memory. Which is strange, because it's actually a negative number (-3 AFAIK). It's probably caused by data corruption (incorrect length for a field). There are ways to find out more about the cause, e.g. here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php but you need to have a pg compiled with debug support. I guess the packaged version does not support that, but maybe you can get the sources and compile them on your own. If it really is a data corruption, you might try to locate the corrupted blocks like this: -- get number of blocks SELECT relpages FROM pg_class WHERE relname = 'table_name'; -- get items for each block (read the problematic column) FOR block IN 1..relpages LOOP SELECT AVG(length(colname)) FROM table_name WHERE ctid = '(block,0)'::ctid AND ctid '(block+1,0)'::ctid; Thanks for this. Very useful. What is this -- a function? How should I execute this query? It's a pseudocode - you need to implement that in whatever language you like. You could do that in PL/pgSQL but don't forget it's probably going to crash when you hit the problematic block so I'd probably implement that in outside the DB (with a logic to continue the loop once the connection dies). And 'ctid' is a pseudocolumn that means '(block#, row#)' i.e. it's something like a physical location of the row. regards Tomas A question. Is data dumped from COPY TO command any use? It has taken me days, but I have managed to COPY my large table in chunks. If I subsequently COPY FROM these files, would this be a workable solution? My fear based on my ignorance is that maybe the data corruption, if any exists, will also get COPY-ied and therefore transferred into the fresh database. Is this fear justified, or is COPY a viable alternative? 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] Help - corruption issue?
On Mon, Apr 25, 2011 at 9:19 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Fri, Apr 22, 2011 at 8:35 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 8:20 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: In the pg_dumpall backup process, I get this error. Does this help? Well, not really - it's just another incarnation of the problem we've already seen. PostgreSQL reads the data, and at some point it finds out it needs to allocate 4294967293B of memory. Which is strange, because it's actually a negative number (-3 AFAIK). It's probably caused by data corruption (incorrect length for a field). There are ways to find out more about the cause, e.g. here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php but you need to have a pg compiled with debug support. I guess the packaged version does not support that, but maybe you can get the sources and compile them on your own. If it really is a data corruption, you might try to locate the corrupted blocks like this: -- get number of blocks SELECT relpages FROM pg_class WHERE relname = 'table_name'; -- get items for each block (read the problematic column) FOR block IN 1..relpages LOOP SELECT AVG(length(colname)) FROM table_name WHERE ctid = '(block,0)'::ctid AND ctid '(block+1,0)'::ctid; Thanks for this. Very useful. What is this -- a function? How should I execute this query? It's a pseudocode - you need to implement that in whatever language you like. You could do that in PL/pgSQL but don't forget it's probably going to crash when you hit the problematic block so I'd probably implement that in outside the DB (with a logic to continue the loop once the connection dies). And 'ctid' is a pseudocolumn that means '(block#, row#)' i.e. it's something like a physical location of the row. regards Tomas A question. Is data dumped from COPY TO command any use? It has taken me days, but I have managed to COPY my large table in chunks. If I subsequently COPY FROM these files, would this be a workable solution? My fear based on my ignorance is that maybe the data corruption, if any exists, will also get COPY-ied and therefore transferred into the fresh database. Is this fear justified, or is COPY a viable alternative? Thanks! Sorry, spoke too soon. I can COPY individual chunks to files. Did that by year, and at least the dumping worked. Now I need to pull the data in at the destination server. If I COPY each individual file back into the table, it works. Slowly, but seems to work. I tried to combine all the files into one go, then truncate the table, and pull it all in in one go (130 million rows or so) but this time it gave the same error. However, it pointed out a specific row where the problem was: COPY links, line 15272357: 16426447 9s2q7 9s2q7 N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;i...; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Is this any use at all? Would appreciate any pointers! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Tue, Apr 26, 2011 at 1:56 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 25.4.2011 19:31, Alban Hertroys napsal(a): On 25 Apr 2011, at 18:16, Phoenix Kiula wrote: If I COPY each individual file back into the table, it works. Slowly, but seems to work. I tried to combine all the files into one go, then truncate the table, and pull it all in in one go (130 million rows or so) but this time it gave the same error. However, it pointed out a specific row where the problem was: COPY links, line 15272357: 16426447 9s2q7 9s2q7 N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;i...; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Is this any use at all? Would appreciate any pointers! I didn't follow the entire thread, so maybe someone mentioned this already, but... Usually if we see error messages like those it turns out the OS is killing the postgres process with it's equivalent of a low-on-memory-killer. I know Linux's got such a beast, and that you can turn it off. It's a frequently recurring issue on this list, there's bound to be some pointers in the archives ;) Not sure if this COPY failure is caused by the same issue as before, but the original issue was caused by this pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967293 pg_dump: The command was: COPY public.links (id, link_id, alias, aliasentered, url, user_known, user_id, url_encrypted, title, private, private_key, status, create_date, modify_date, disable_in_statistics, user_running_id, url_host_long) TO stdout; pg_dumpall: pg_dump failed on database snipurl, exiting i.e. a bad memory alloc request (with negative size). That does not seem like an OOM killing the backend. Most likely you're right. I did a COPY FROM and populated the entire table. In my hard disk, the space consumption went up by 64GB. Yet, when I do a SELECT * FROM mytable LIMIT 1 the entire DB crashes. There is no visible record. What's this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Tuesday, April 26, 2011, Tomas Vondra t...@fuzzy.cz wrote: Dne 25.4.2011 18:16, Phoenix Kiula napsal(a): Sorry, spoke too soon. I can COPY individual chunks to files. Did that by year, and at least the dumping worked. Now I need to pull the data in at the destination server. If I COPY each individual file back into the table, it works. Slowly, but seems to work. I tried to combine all the files into one go, then truncate the table, and pull it all in in one go (130 million rows or so) but this time it gave the same error. However, it pointed out a specific row where the problem was: COPY links, line 15272357: 16426447 9s2q7 9s2q7 N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;i...; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Is this any use at all? Would appreciate any pointers! So the dump worked fina and it fails when loading it back into the DB? Have you checked the output file (just see the tail). Can you post the part that causes issues? Just the line 16426447 and few lines around. regards Tomas From the old server: Yearly COPY files worked. Pg_dumpall was giving problems. In the new server: COPY FROM worked. All files appear to have been copied. Then I create the primary key index, and another index. Many records are there, but many are not there! There's no error, just that some records/rows just didn't make it. I did the COPY FROM in a transaction block. If there had been an error, then commit would have rolledback, right? It didn't. It committed. No errors. Just that some data has not come in. How can I get more info on why? Tomas, the line where it crashed, here are the 10 or so lines around it: head -15272350 /backup/links/links_all.txt | tail -20 164264229s2pi 9s2pi N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=Cannibal+Corpse+-+Split+Wide+Openamp;linkCode=ur2amp;tag=dmp3-20 0 121.214.194.133 7a69d5842739e20b56c0103d1a6ec172e58f9e07 \N Y 2009-01-10 20:59:31.135881 2009-01-10 20:59:31.135881 \N \N 164264239s2pj 9s2pj N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=Juana+Fe+-+la+murga+finalamp;linkCode=ur2amp;tag=dmp3-20 0 201.215.6.104 5e2ae1f363c7854c13a101a60b32a9a1ade26767 \N Y 2009-01-10 20:59:31.593474 2009-01-10 20:59:31.593474 Y \N \N 158978629gqva 9gqva N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=Boyz+II+Men+-+Ill+Make+Love+To+Youamp;linkCode=ur2amp;tag=dmp3-20 0 76.10.185.873c840fa5428c0464556dccb7d1013a6ec53d1743 N Y 2009-01-04 19:40:50.734967 2009-01-10 20:59:32.286937 N \N \N 1513014990ahx 90ahx N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=The+Killers+-+All+The+Pretty+Facesamp;linkCode=ur2amp;tag=dmp3-20 0 65.25.74.1415eb2a1bb48d4926d8eaf946fb544ce11c50a9e5b N Y 2008-12-22 14:54:20.813923 2009-01-10 20:59:33.896232 N \N \N 164264259s2pl 9s2pl N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=Freddy+Quinn+-+Junge%2C+Komm+Bald+Wiederamp;linkCode=ur2amp;tag=dmp3-20 0 123.100.137.226 fb7af64a4b886f074a6443b8d43f571c3083f51c \N Y 2009-01-10 20:59:33.986764 2009-01-10 20:59:33.986764 Y \N \N 163917569rbyk 9rbyk N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=Closure+In+Moscow+-+Ofelia...+Ofeliaamp;linkCode=ur2amp;tag=dmp3-20 0 71.233.18.39a4f95f246b89523785b736530fb4b3a335195c4b N Y 2009-01-10 13:20:54.86346 2009-01-10 20:59:34.641193 N \N \N 162299289nv3c 9nv3c N http://www.amazon.com/gp/search?camp=1789amp;creative=9325amp;ie=UTF8amp;index=digital-musicamp;keywords=Ministry+of+Sound+-+Freestylers+%2F+Push+Upamp;linkCode=ur2amp;tag=dmp3-20 0 24.60.222.70b455933eb976b39313f5da56afcd9db29d3f7bde N Y 2009-01-08 19:35:19.842463 2009-01-10 20:59:35.343552 N \N \N 164264279s2pn 9s2pn N http://www.annehelmond.nl/2007/11/26/celebrating-two-thousand-delicious-bookmarks/ 195.190.28.97 22a06537e25985273297471dbeb3fb6ae217cb90 \N Y 2009-01-10 20:59
Re: [GENERAL] Help - corruption issue?
On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 12:06 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Fri, Apr 22, 2011 at 12:51 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 21.4.2011 07:16, Phoenix Kiula napsal(a): Tomas, I did a crash log with the strace for PID of the index command as you suggested. Here's the output: http://www.heypasteit.com/clip/WNR Also including below, but because this will wrap etc, you can look at the link above. Thanks for any ideas or pointers! Process 15900 attached - interrupt to quit Nope, that's the psql process - you need to attach to the backend process that's created to handle the connection. Whenever you create a connection (from a psql), a new backend process is forked to handle that single connection - this is the process you need to strace. You can either see that in 'ps ax' (the PID is usually +1 with respect to the psql process), or you can do this SELECT pg_backend_pid(); as that will give you PID of the backend for the current connection. Thanks. Did that. The crash.log is a large-ish file, about 24KB. Here's the last 10 lines though. Does this help? ~ tail -10 /root/crash.log read(58, `\1\0\0\230\337\0\343\1\0\0\0P\0T\r\0 \3 \374\236\2\2T\215\312\1\354\235\32\2..., 8192) = 8192 write(97, 213.156.60\0\0 \0\0\0\37\0\364P\3\0\34@\22\0\0\000210, 8192) = 8192 read(58, `\1\0\0\274\362\0\343\1\0\0\0T\0\210\r\0 \3 0\217\352\1\240\236\272\0024\235\322\2..., 8192) = 8192 read(58, [\1\0\0\354)c*\1\0\0\0T\0\214\r\0 \3 \254\236\242\2\340\220\342\2\\\235\232\2..., 8192) = 8192 read(58, \\\1\0\0\200\245\207\32\1\0\0\0\\\0\340\r\0 \3 \237\272\1\304\235\262\2\340\215\322\1..., 8192) = 8192 read(58, \350\0\0\0\274\311x\323\1\0\0\0\\\\r\0 \3 \200\236\372\2(\235\252\2\34\234\22\2..., 8192) = 8192 read(58, ;\1\0\0|#\265\30\1\0\0\0`\0h\r\0 \3 \324\236R\2\314\235\n\2h\215\362\1..., 8192) = 8192 read(58, c\1\0\\24%u\1\0\0\0\230\0\210\r\0 \3 \240\226\32\16\260\235\252\1p\222Z\10..., 8192) = 8192 --- SIGSEGV (Segmentation fault) @ 0 (0) --- Process 17161 detached The full crash.log file is here if needed: https://www.yousendit.com/download/ VnBxcmxjNDJlM1JjR0E9PQ Btw, this happens when I try to create an index on one of the columns in my table. Just before this, I had created another index on modify_date (a timestamp column) and it went fine. Does that mean anything? Thanks Probably a dumb and ignorant question, but should I be reseting the xlog? http://postgresql.1045698.n5.nabble.com/SIGSEGV-when-trying-to-start-in-single-user-mode-td1924418.html Nope, that's a different problem I guess - you don't have problems with starting up a database (when the logs are replayed), so this would not help (and it might cause other issues). Anyway I haven't found anything useful in the strace output - it seems it works fine, reads about 500MB (each of the 'read' calls corresponds to 8kB of data) of data and then suddenly ends. A bit strange is the last line is not complete ... Anyway, this is where my current knowledge of how processes in PostgreSQL ends. If I was sitting at the terminal, I'd probably continue by try and error to find out more details about the segfault, but that's not very applicable over e-mail. So let's hope some of the pg gurus who read this list will enlighten us with a bit more knowledge. regards Tomas In the pg_dumpall backup process, I get this error. Does this help? pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967293 pg_dump: The command was: COPY public.links (id, link_id, alias, aliasentered, url, user_known, user_id, url_encrypted, title, private, private_key, status, create_date, modify_date, disable_in_statistics, user_running_id, url_host_long) TO stdout; pg_dumpall: pg_dump failed on database snipurl, exiting 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 8:20 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: In the pg_dumpall backup process, I get this error. Does this help? Well, not really - it's just another incarnation of the problem we've already seen. PostgreSQL reads the data, and at some point it finds out it needs to allocate 4294967293B of memory. Which is strange, because it's actually a negative number (-3 AFAIK). It's probably caused by data corruption (incorrect length for a field). There are ways to find out more about the cause, e.g. here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php but you need to have a pg compiled with debug support. I guess the packaged version does not support that, but maybe you can get the sources and compile them on your own. If it really is a data corruption, you might try to locate the corrupted blocks like this: -- get number of blocks SELECT relpages FROM pg_class WHERE relname = 'table_name'; -- get items for each block (read the problematic column) FOR block IN 1..relpages LOOP SELECT AVG(length(colname)) FROM table_name WHERE ctid = '(block,0)'::ctid AND ctid '(block+1,0)'::ctid; Thanks for this. Very useful. What is this -- a function? How should I execute this query? 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 12:51 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 21.4.2011 07:16, Phoenix Kiula napsal(a): Tomas, I did a crash log with the strace for PID of the index command as you suggested. Here's the output: http://www.heypasteit.com/clip/WNR Also including below, but because this will wrap etc, you can look at the link above. Thanks for any ideas or pointers! Process 15900 attached - interrupt to quit Nope, that's the psql process - you need to attach to the backend process that's created to handle the connection. Whenever you create a connection (from a psql), a new backend process is forked to handle that single connection - this is the process you need to strace. You can either see that in 'ps ax' (the PID is usually +1 with respect to the psql process), or you can do this SELECT pg_backend_pid(); as that will give you PID of the backend for the current connection. Thanks. Did that. The crash.log is a large-ish file, about 24KB. Here's the last 10 lines though. Does this help? ~ tail -10 /root/crash.log read(58, `\1\0\0\230\337\0\343\1\0\0\0P\0T\r\0 \3 \374\236\2\2T\215\312\1\354\235\32\2..., 8192) = 8192 write(97, 213.156.60\0\0 \0\0\0\37\0\364P\3\0\34@\22\0\0\000210, 8192) = 8192 read(58, `\1\0\0\274\362\0\343\1\0\0\0T\0\210\r\0 \3 0\217\352\1\240\236\272\0024\235\322\2..., 8192) = 8192 read(58, [\1\0\0\354)c*\1\0\0\0T\0\214\r\0 \3 \254\236\242\2\340\220\342\2\\\235\232\2..., 8192) = 8192 read(58, \\\1\0\0\200\245\207\32\1\0\0\0\\\0\340\r\0 \3 \237\272\1\304\235\262\2\340\215\322\1..., 8192) = 8192 read(58, \350\0\0\0\274\311x\323\1\0\0\0\\\\r\0 \3 \200\236\372\2(\235\252\2\34\234\22\2..., 8192) = 8192 read(58, ;\1\0\0|#\265\30\1\0\0\0`\0h\r\0 \3 \324\236R\2\314\235\n\2h\215\362\1..., 8192) = 8192 read(58, c\1\0\\24%u\1\0\0\0\230\0\210\r\0 \3 \240\226\32\16\260\235\252\1p\222Z\10..., 8192) = 8192 --- SIGSEGV (Segmentation fault) @ 0 (0) --- Process 17161 detached The full crash.log file is here if needed: https://www.yousendit.com/download/ VnBxcmxjNDJlM1JjR0E9PQ Btw, this happens when I try to create an index on one of the columns in my table. Just before this, I had created another index on modify_date (a timestamp column) and it went fine. Does that mean anything? 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 12:06 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Fri, Apr 22, 2011 at 12:51 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 21.4.2011 07:16, Phoenix Kiula napsal(a): Tomas, I did a crash log with the strace for PID of the index command as you suggested. Here's the output: http://www.heypasteit.com/clip/WNR Also including below, but because this will wrap etc, you can look at the link above. Thanks for any ideas or pointers! Process 15900 attached - interrupt to quit Nope, that's the psql process - you need to attach to the backend process that's created to handle the connection. Whenever you create a connection (from a psql), a new backend process is forked to handle that single connection - this is the process you need to strace. You can either see that in 'ps ax' (the PID is usually +1 with respect to the psql process), or you can do this SELECT pg_backend_pid(); as that will give you PID of the backend for the current connection. Thanks. Did that. The crash.log is a large-ish file, about 24KB. Here's the last 10 lines though. Does this help? ~ tail -10 /root/crash.log read(58, `\1\0\0\230\337\0\343\1\0\0\0P\0T\r\0 \3 \374\236\2\2T\215\312\1\354\235\32\2..., 8192) = 8192 write(97, 213.156.60\0\0 \0\0\0\37\0\364P\3\0\34@\22\0\0\000210, 8192) = 8192 read(58, `\1\0\0\274\362\0\343\1\0\0\0T\0\210\r\0 \3 0\217\352\1\240\236\272\0024\235\322\2..., 8192) = 8192 read(58, [\1\0\0\354)c*\1\0\0\0T\0\214\r\0 \3 \254\236\242\2\340\220\342\2\\\235\232\2..., 8192) = 8192 read(58, \\\1\0\0\200\245\207\32\1\0\0\0\\\0\340\r\0 \3 \237\272\1\304\235\262\2\340\215\322\1..., 8192) = 8192 read(58, \350\0\0\0\274\311x\323\1\0\0\0\\\\r\0 \3 \200\236\372\2(\235\252\2\34\234\22\2..., 8192) = 8192 read(58, ;\1\0\0|#\265\30\1\0\0\0`\0h\r\0 \3 \324\236R\2\314\235\n\2h\215\362\1..., 8192) = 8192 read(58, c\1\0\\24%u\1\0\0\0\230\0\210\r\0 \3 \240\226\32\16\260\235\252\1p\222Z\10..., 8192) = 8192 --- SIGSEGV (Segmentation fault) @ 0 (0) --- Process 17161 detached The full crash.log file is here if needed: https://www.yousendit.com/download/ VnBxcmxjNDJlM1JjR0E9PQ Btw, this happens when I try to create an index on one of the columns in my table. Just before this, I had created another index on modify_date (a timestamp column) and it went fine. Does that mean anything? Thanks Probably a dumb and ignorant question, but should I be reseting the xlog? http://postgresql.1045698.n5.nabble.com/SIGSEGV-when-trying-to-start-in-single-user-mode-td1924418.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On a fast network it should only take a few minutes. Now rsyncing live 2.4 TB databases, that takes time. :) Your raptors, if they're working properly, should be able to transfer at around 80 to 100Megabytes a second. 10 to 15 seconds a gig. 30 minutes or so via gig ethernet. I'd run iostat and see how well my drive array was performing during a large, largely sequential copy. OK. An update. We have changed all the hardware except disks. REINDEX still gave this problem: -- server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. -- So I rebooted and logged back in a single user mode. All services stopped. All networking stopped. Only postgresql started. I tried the REINDEX again. Same problem :( This means the problem is likely with data? I do have a pg_dumpall dump from 1 day before. Will lose some data, but should have most of it. Is it worth it for me to try and restore from there? What's the best thing to do right now? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Thu, Apr 21, 2011 at 7:27 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.4.2011 22:11, Tomas Vondra napsal(a): There's a very nice guide on how to do that http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html It sure seems like the problem you have (invalid alloc request etc.). The really annoying part is locating the block, as you have to scan through the table (which sucks with such big table). And yes, if there's corruption, there might be more corrupted blocks. BTW, there's a setting 'zero_damaged_pages' that might help with this http://www.postgresql.org/docs/8.2/static/runtime-config-developer.html see this talk for more details how to use it http://www.casitconf.org/casitconf11/Tech_track_2_files/cascadia_postgres_rbernier.pdf Anyway don't play with this without the file backup, as this will zero the blocks. Tomas Thanks Tomas. Very handy info. FIRST: is there anyone on this list who offers PG admin support? Please write to me directly. Second, for the strace, which process should I use? ps auxwww|grep ^postgres postgres 4320 0.0 0.1 440192 10824 ? Ss 08:49 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 4355 0.0 0.0 11724 964 ?Ss 08:49 0:00 postgres: logger process postgres 4365 0.0 0.0 440396 3268 ? Ss 08:49 0:00 postgres: writer process postgres 4366 0.0 0.0 11860 1132 ?Ss 08:49 0:00 postgres: stats collector process postgres 15795 0.0 0.0 7136 1440 pts/0S22:44 0:00 -bash postgres 15900 0.0 0.0 7860 1956 pts/0S+ 22:44 0:00 psql -h localhost MYDOMAIN -E MYDOMAIN_MYDOMAIN postgres 15901 0.0 0.0 441124 3072 ? Ss 22:44 0:00 postgres: MYDOMAIN_MYDOMAIN MYDOMAIN 127.0.0.1(34346) idle Third, I have the backup in two ways: 1. I took a backup of the entire /pgsql/data folder. PG was shutdown at the time. 2. I have a pg_dumpall file but it is missing one day's data (still useful as last resort). Will #1 have corrupt data in it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Thu, Apr 21, 2011 at 11:49 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Thu, Apr 21, 2011 at 7:27 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.4.2011 22:11, Tomas Vondra napsal(a): There's a very nice guide on how to do that http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html It sure seems like the problem you have (invalid alloc request etc.). The really annoying part is locating the block, as you have to scan through the table (which sucks with such big table). And yes, if there's corruption, there might be more corrupted blocks. BTW, there's a setting 'zero_damaged_pages' that might help with this http://www.postgresql.org/docs/8.2/static/runtime-config-developer.html see this talk for more details how to use it http://www.casitconf.org/casitconf11/Tech_track_2_files/cascadia_postgres_rbernier.pdf Anyway don't play with this without the file backup, as this will zero the blocks. Tomas Thanks Tomas. Very handy info. FIRST: is there anyone on this list who offers PG admin support? Please write to me directly. Second, for the strace, which process should I use? ps auxwww|grep ^postgres postgres 4320 0.0 0.1 440192 10824 ? Ss 08:49 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 4355 0.0 0.0 11724 964 ? Ss 08:49 0:00 postgres: logger process postgres 4365 0.0 0.0 440396 3268 ? Ss 08:49 0:00 postgres: writer process postgres 4366 0.0 0.0 11860 1132 ? Ss 08:49 0:00 postgres: stats collector process postgres 15795 0.0 0.0 7136 1440 pts/0 S 22:44 0:00 -bash postgres 15900 0.0 0.0 7860 1956 pts/0 S+ 22:44 0:00 psql -h localhost MYDOMAIN -E MYDOMAIN_MYDOMAIN postgres 15901 0.0 0.0 441124 3072 ? Ss 22:44 0:00 postgres: MYDOMAIN_MYDOMAIN MYDOMAIN 127.0.0.1(34346) idle Third, I have the backup in two ways: 1. I took a backup of the entire /pgsql/data folder. PG was shutdown at the time. 2. I have a pg_dumpall file but it is missing one day's data (still useful as last resort). Will #1 have corrupt data in it? Tomas, I did a crash log with the strace for PID of the index command as you suggested. Here's the output: http://www.heypasteit.com/clip/WNR Also including below, but because this will wrap etc, you can look at the link above. Thanks for any ideas or pointers! Process 15900 attached - interrupt to quit read(0, r, 1) = 1 write(1, r, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, e, 1) = 1 write(1, e, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, i, 1) = 1 write(1, i, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, n, 1) = 1 write(1, n, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, d, 1) = 1 write(1, d, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, e, 1) = 1 write(1, e, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, x, 1) = 1 write(1, x, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, , 1) = 1 write(1, , 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, l, 1) = 1 write(1, l, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, i, 1) = 1 write(1, i, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, n, 1) = 1 write(1, n, 1)= 1 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0 read(0, \177, 1) = 1 write(1, \10\33[K, 4) = 4 rt_sigprocmask(SIG_BLOCK, NULL
[GENERAL] Questions about Partitioning
While I fix some bigger DB woes, I have learned a lesson. Huge indexes and tables are a pain. Which makes me doubly keen on looking at partitioning. Most examples I see online are partitioned by date. As in months, or quarter, and so on. This doesn't work for me as I don't have too much logic required based on time. The biggest, highest volume SELECT in my database happens through an alias column. This is an alphanumeric column. The second-biggest SELECT happens through the userid column -- because many users check their account every day. A rough table definition can be considered as follows: CREATE TABLE maintable idSERIAL primary key alias VARCHAR(42) ... user_id VARCHAR(30) user_registered BOOLEAN statusVARCHAR(1) My questions: 1. Which column should I partition by -- the alias because it's the largest contributor of queries? This should be OK, but my concern is that when user_id queries are happening, then the data for the same user will come through many subtables that are partitioned by alias -- will this happen automatically (presuming constraint exclusion is on)? How does partitioning by one column affect queries on others. Will there be subtable-by-subtable indexes on both alias and 2. How does SERIAL type work with partitions? Will INSERT data go into the respective partitions and yet maintain an overall sequence -- I mean, the *same* overall sequence for the parent table distributed automagically across subtables? 3. If I partition using a%, b% etc up to z% as the partition condition, is this an issue -- are about 26 subtables too many partitions? Mine are static partitions as in they will be the same forever, unlike data-based partitions. And each partition will continue to grow. If I include that aliases can begin with numbers and allowed symbols too, then this may be 45 partitions? What's the limit of partitions -- not only official limit, but practical limit in terms of performance? 4. Given that it's a wildcard LIKE condition (with a %) will this affect the index and subsequent SELECT speed? Are partition conditions recommended to be = or type operators only or is LIKE ok?? 5. Does partitioning need to happen only through one column? Can I have a condition containing two columns instead? CREATE TABLE subtable_a ( PRIMARY KEY (id) CHECK ( user_id LIKE 'a%' and user_registered IS TRUE) ) INHERITS (maintable); CREATE TABLE subtable_b ( PRIMARY KEY (id), CHECK ( user_id LIKE 'b%' and user_registered IS TRUE) ) INHERITS (maintable); ..etc 6. Triggers - how do they affect speed? Everything, insert, update, select will happen through this conditional trigger. I will likely be writing this in PLSQL, but I read in several websites that C triggers are much faster than PLSQL triggers. Is this a concern? 7. Constraint exclusion - is it recommended to have this in the pg.conf, or will I need to do this before every SQL? I prefer the pg.conf way, but want to confirm that there are no downsides for other regular SQL operations with this setting? 8. How will JOIN work? I have different tables JOINing with the parent table now. With partitioned subtables, will constraint exclusion automatically do what's needed and my SQL does not need to change? Or will there be triggers required for each and every query I currently have? Eight questions is enough for my first post in this partitioning thread :) Thanks much! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
