[GENERAL] CREATE TABLE table_name AS EXECUTE name WITH DATA becomes syntax error.
Hi, Accroding to http://www.postgresql.org/docs/9.1/interactive/sql-createtableas.html , CREATE TABLE table_name AS EXECUTE name WITH DATA seems a right syntax, but,this statement becomes a SYNTAX ERROR. Is this a specification? --- naoya=# SELECT VERSION(); version --- PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit (1 row) naoya=# PREPARE GETONE AS SELECT * FROM SEED WHERE ID=1; PREPARE naoya=# EXECUTE GETONE; id |date + 1 | 2011-11-24 11:24:49.675427 (1 row) naoya=# CREATE TABLE NEW_SEED AS EXECUTE GETONE; SELECT 1 naoya=# CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA; ERROR: syntax error at or near WITH DATA at character 42 STATEMENT: CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA; --- Regards. --- Naoya Anzai -- 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] Recommendations for SSDs in production?
On 2011-11-04 16:24, David Boreham wrote: On 11/4/2011 8:26 AM, Yeb Havinga wrote: First, if your'e interested in doing a test like this yourself, I'm testing on ubuntu 11.10, but even though this is a brand new distribution, the smart database was a few months old. 'update-smart-drivedb' had as effect that the names of the values turned into something useful: instead of #LBA's written, it now shows #32MiB's written. Also there are now three 'workload' related parameters. I submitted the patch for these to smartmontools a few weeks ago and it is now in the current db but not yet in any of the distro update packages. I probably forgot to mention in my post here that you need the latest db for the 710. Also, if you pull the trunk source code and build it yourself it has the ability to decode the drive stats log data (example pasted below). I haven't yet found a use for this myself, but it does seem to have a little more informaiton than the SMART attributes. (Thanks to Christian Franke of the smartmontools project for implementing this feature) Your figures from the workload wear roughly match mine. In production we don't expect to subject the drives to anything close to 100% of the pgbench workload (probably around 1/10 of that on average), so the predicted wear life of the drive is 10+ years in our estimates, under production loads. The big question of course is can the drive's wearout estimate be trusted ? A little more information from Intel about how it is calculated would help allay concerns in this area. TLDR: some numbers after three week media wear testing on a software mirror with intel 710 and ocz vertex 2 pro. The last couple of weeks I've been running pgbench for an hour then sleep for 10 minutes in an infinite loop, just to see how values would grow. This is the intel 710 mirror leg: 225 Host_Writes_32MiB 0x0032 100 100 000Old_age Always - 3020093 226 Workld_Media_Wear_Indic 0x0032 100 100 000Old_age Always - 2803 227 Workld_Host_Reads_Perc 0x0032 100 100 000Old_age Always - 0 228 Workload_Minutes0x0032 100 100 000Old_age Always - 21444 232 Available_Reservd_Space 0x0033 100 100 010Pre-fail Always - 0 233 Media_Wearout_Indicator 0x0032 098 098 000Old_age Always - 0 241 Host_Writes_32MiB 0x0032 100 100 000Old_age Always - 3020093 242 Host_Reads_32MiB0x0032 100 100 000Old_age Always - 22259 Note: raw value of 226 (E2) = 2803. According to http://www.tomshardware.com/reviews/ssd-710-enterprise-x25-e,3038-4.html you have to divide it by 1024 to get a percentage. That would be 2%. This matches with 098 of the (not raw) value at 233 (E9). This is the ocz vertex 2 PRO mirror leg: 5 Retired_Block_Count 0x0033 100 100 003Pre-fail Always - 0 12 Power_Cycle_Count 0x0032 100 100 000Old_age Always - 22 100 Gigabytes_Erased0x0032 000 000 000Old_age Always - 21120 170 Reserve_Block_Count 0x0032 000 000 000Old_age Always - 34688 177 Wear_Range_Delta0x 000 000 000Old_age Offline - 3 230 Life_Curve_Status 0x0013 100 100 000Pre-fail Always - 100 231 SSD_Life_Left 0x0013 100 100 010Pre-fail Always - 0 232 Available_Reservd_Space 0x 000 000 000Old_age Offline - 33 233 SandForce_Internal 0x 000 000 000Old_age Offline - 21184 234 SandForce_Internal 0x0032 000 000 000Old_age Always - 94656 235 SuperCap_Health 0x0033 100 100 002Pre-fail Always - 0 241 Lifetime_Writes_GiB 0x0032 000 000 000Old_age Always - 94656 242 Lifetime_Reads_GiB 0x0032 000 000 000Old_age Always - 960 Here the 177 (B1) wear range delta is on a raw value of 3 - this isn't ssd life left, but Delta between most-worn and least-worn Flash blocks. I really wonder at which point SSD life left will change to 99 on this drive.. regards, Yeb Havinga -- 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 display the progress of query
Hi.
I try to monitor a progress of the insert statement:
insert into table1
(id,other fields)
select
id+0*nextval('public.progress'),other fields
From second session i run:
select nextval('public.progress');
but sequence 'progress' looks unchanged.
How to display number of processed rows ?
pasman
--
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 get normalized data from tekst column
Project table contains salesman names and percents as shown
below. Single comment column contains 1-2 salesman names and commissions.
How select normalized data from this table ?
Andrus.
CREATE TABLE project (
id char(10) primary key,
comment char(254)
);
insert into test values ('2010-12', 'Aavo 19%, Peedu 15%');
insert into test values ('2010-22', 'Lauri-21%,Peedu 15%');
insert into test values ('2011-33', 'Taavi 21%');
How to create select statement in Postgresql 8.1.23 which
selects this data as normalized table like
CREATE TABLE commission (
projectid char(10),
salesman char(5),
commission n(2) )
result using data above should be
'2010-12', 'Aavo', 19
'2010-12', 'Peedu', 15
'2010-22', 'Lauri', 21
'2010-22', 'Peedu', 15
'2011-33', 'Taavi', 21
[GENERAL] General performance/load issue
Hello everyone, I'm having some troubles with a Postgresql server. We're using PG has a database backend for a very big website (lots of data and much traffic). The issue : server suddenly (1H after restart) becomes slow (queries not responding), load rises (20 instead of 1), iowait rises (20 to 70%) Version : 9.0.5 Server : Dual Xeon X5650 (24 cores total) Memory : 48 GB Disks : SSD Top when overloaded : 21537 postgres 20 0 6420m 899m 892m D 22 1.9 0:01.86 postgres 21534 postgres 20 0 6554m 1.3g 1.1g D 14 2.7 0:01.72 postgres 21518 postgres 20 0 6419m 2.1g 2.1g D 10 4.6 0:02.10 postgres 21038 postgres 20 0 6716m 3.8g 3.5g D 10 8.0 0:45.46 postgres 21103 postgres 20 0 6571m 3.7g 3.6g D9 7.8 0:32.19 postgres 21079 postgres 20 0 6575m 3.8g 3.7g D8 8.1 0:36.39 postgres 21359 postgres 20 0 6563m 1.9g 1.8g D4 4.1 0:09.10 postgres 21422 postgres 20 0 6563m 1.9g 1.7g D4 3.9 0:08.34 postgres 19656 postgres 20 0 6727m 5.3g 5.0g D3 11.1 1:58.25 postgres 21418 postgres 20 0 6685m 2.1g 1.9g D2 4.5 0:12.42 postgres 21413 postgres 20 0 6693m 2.1g 1.8g D2 4.4 0:11.06 postgres 21541 postgres 20 0 6421m 719m 711m D1 1.5 0:00.48 postgres 14044 postgres 20 0 6418m 755m 750m D1 1.6 0:04.71 postgres 21326 postgres 20 0 6685m 2.2g 2.0g D1 4.7 0:15.82 postgres 21031 postgres 20 0 6688m 3.6g 3.4g D1 7.7 0:44.18 postgres 21055 postgres 20 0 6575m 4.1g 3.9g D1 8.6 0:39.11 postgres 21357 postgres 20 0 6693m 2.3g 2.0g D1 4.9 0:13.74 postgres Memory when overloaded : ~ # free -m total used free sharedbuffers cached Mem: 48339 47087 1251 0248 38720 -/+ buffers/cache: 8118 40220 Swap: 8190 1 8189 Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB effective_cache_size = 12GB max_files_per_process = 8192 fsync = off checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 seq_page_cost = 2.0 random_page_cost = 2.0 Did I do anything wrong? Any idea? Regards, Gaëtan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reassign value of IN parameter in 9.1.1
This works in 9.1.1 but seems like a bug to me: create function xout(_x INTEGER) returns integer as $$ begin _x = _x * 2; return _x; end; $$ LANGUAGE plpgsql; select xout(4); It would not have compiled in version 8. I came across such a reassignement doing a code review and was surprised it compiled. Is there a reason for the change in behaviour?
Re: [GENERAL] General performance/load issue
On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Hello everyone, I'm having some troubles with a Postgresql server. We're using PG has a database backend for a very big website (lots of data and much traffic). The issue : server suddenly (1H after restart) becomes slow (queries not responding), load rises (20 instead of 1), iowait rises (20 to 70%) Version : 9.0.5 Server : Dual Xeon X5650 (24 cores total) Memory : 48 GB Disks : SSD Top when overloaded : Top is not the most useful tool here, I guess. Use iotop (will show you which processes are doing the I/O) and tools like vmstat / iostat. Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. effective_cache_size = 12GB Why have you set it like this? According to the free output you've posted the cache has about 38G, so why just 12G here? There are possible reasons, but I don't think this is the case. fsync = off A really bad idea. I guess your data are worthless to you, right? seq_page_cost = 2.0 random_page_cost = 2.0 Eh? First of all, what really matters is the relative value of those two values, and it's good habit to leave seq_page_cost = 1.0 and change just the other values. Plus the random I/O is not as cheap as sequential I/O even on SSD drives, so I't recommend something like this: seq_page_cost = 1.0 random_page_cost = 2.0 (or maybe 1.5) Anyway this needs to be tested properly - watch the performance and tune if needed. Did I do anything wrong? Any idea? Not sure. My guess is you're getting bitten by a checkpoint. We need to know a few more details. 1) What is dirty_background_ratio / dirty_ratio (see /proc/sys/vm/ directory) 2) enable log_checkpoints in postgresql.conf and see how it correlates to the bad performance 3) check which processes are responsible for the I/O (use iotop) Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reassign value of IN parameter in 9.1.1
On 24 November 2011 14:52, Gavin Casey gpjca...@googlemail.com wrote: This works in 9.1.1 but seems like a bug to me: create function xout(_x INTEGER) returns integer as $$ begin _x = _x * 2; I would expect an error here, as having an expression without a context (an if-statement, for example) should be illegal. An assignment should be fine though: _x := _x * 2; I'm guessing people make errors like this frequently enough that the parser was relaxed to accept this expression as an assignment, even though the syntax for those is slightly different. There is no other possible explanation for such a line, after all, the author of this code clearly meant to put an assignment there. return _x; end; $$ LANGUAGE plpgsql; select xout(4); What is the output? I'm guessing it's 8, since there was no syntax error. That would be the right answer too, in that case. Function-local variables don't matter outside the function, after all. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reassign value of IN parameter in 9.1.1
On 24 November 2011 14:12, Alban Hertroys haram...@gmail.com wrote: On 24 November 2011 14:52, Gavin Casey gpjca...@googlemail.com wrote: This works in 9.1.1 but seems like a bug to me: create function xout(_x INTEGER) returns integer as $$ begin _x = _x * 2; I would expect an error here, as having an expression without a context (an if-statement, for example) should be illegal. An assignment should be fine though: _x := _x * 2; I'm guessing people make errors like this frequently enough that the parser was relaxed to accept this expression as an assignment, even though the syntax for those is slightly different. There is no other possible explanation for such a line, after all, the author of this code clearly meant to put an assignment there. return _x; end; $$ LANGUAGE plpgsql; select xout(4); What is the output? I'm guessing it's 8, since there was no syntax error. That would be the right answer too, in that case. Function-local variables don't matter outside the function, after all. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. It was actually the reassignment of an IN parameter that I was questioning, the '=' sign on it's own was my typo, apologies for confusion.
Re: [GENERAL] General performance/load issue
Hi Thomas, I will be using iotop ;) Right now, most i/o come from postgres: wal writer process. - effective_cache_size Okay, I'll rise it to 32Gb. - fsync : changed to on ;) - seq_pages : i'll run tests. Thanks. - dirty : cat /proc/sys/vm/dirty_ratio 20 cat /proc/sys/vm/dirty_background_ratio10 Thanks a lot Tomas. You're really helpful! Gaëtan Le 24/11/11 15:09, « Tomas Vondra » t...@fuzzy.cz a écrit : On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Hello everyone, I'm having some troubles with a Postgresql server. We're using PG has a database backend for a very big website (lots of data and much traffic). The issue : server suddenly (1H after restart) becomes slow (queries not responding), load rises (20 instead of 1), iowait rises (20 to 70%) Version : 9.0.5 Server : Dual Xeon X5650 (24 cores total) Memory : 48 GB Disks : SSD Top when overloaded : Top is not the most useful tool here, I guess. Use iotop (will show you which processes are doing the I/O) and tools like vmstat / iostat. Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. effective_cache_size = 12GB Why have you set it like this? According to the free output you've posted the cache has about 38G, so why just 12G here? There are possible reasons, but I don't think this is the case. fsync = off A really bad idea. I guess your data are worthless to you, right? seq_page_cost = 2.0 random_page_cost = 2.0 Eh? First of all, what really matters is the relative value of those two values, and it's good habit to leave seq_page_cost = 1.0 and change just the other values. Plus the random I/O is not as cheap as sequential I/O even on SSD drives, so I't recommend something like this: seq_page_cost = 1.0 random_page_cost = 2.0 (or maybe 1.5) Anyway this needs to be tested properly - watch the performance and tune if needed. Did I do anything wrong? Any idea? Not sure. My guess is you're getting bitten by a checkpoint. We need to know a few more details. 1) What is dirty_background_ratio / dirty_ratio (see /proc/sys/vm/ directory) 2) enable log_checkpoints in postgresql.conf and see how it correlates to the bad performance 3) check which processes are responsible for the I/O (use iotop) Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] General performance/load issue
On 24 Listopad 2011, 15:27, Gaëtan Allart wrote: Hi Thomas, I will be using iotop ;) Right now, most i/o come from postgres: wal writer process. What do you mean by most I/O - how much data is it writing? Is there a vacuum running at the same time? What other processes are doing I/O? Post a few lines of iostat -x 5 so we know what kind of I/O we're dealing with. - dirty : cat /proc/sys/vm/dirty_ratio 20 cat /proc/sys/vm/dirty_background_ratio 10 This means the cache may contain up to 3.2GB of data before the system starts to write them out on background, and about 6.4GB before the processes can't use the write cache. What about /proc/sys/vm/dirty_expire_centiseconds? Is there something interesting in the postgresql.log? Autovacuum maybe? Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get normalized data from tekst column
On Nov 24, 2011, at 8:47, Andrus kobrule...@hot.ee wrote:
Project table contains salesman names and percents as shown
below. Single comment column contains 1-2 salesman names and commissions.
How select normalized data from this table ?
Andrus.
CREATE TABLE project (
id char(10) primary key,
comment char(254)
);
insert into test values ('2010-12', 'Aavo 19%, Peedu 15%');
insert into test values ('2010-22', 'Lauri-21%,Peedu 15%');
insert into test values ('2011-33', 'Taavi 21%');
How to create select statement in Postgresql 8.1.23 which
selects this data as normalized table like
CREATE TABLE commission (
projectid char(10),
salesman char(5),
commission n(2) )
result using data above should be
'2010-12', 'Aavo', 19
'2010-12', 'Peedu', 15
'2010-22', 'Lauri', 21
'2010-22', 'Peedu', 15
'2011-33', 'Taavi', 21
Regular Expressions are your friend here. If you do not know them you should
learn them; though if you ask nicely someone may just provide you the solution
you need.
Split-to-array and unnest may work as well.
All this said, you are currently using an unsupported version of PostgreSQL and
I do not know what specific functionality you have to work with.
David J.
Re: [GENERAL] How to get normalized data from tekst column
David, Regular Expressions are your friend here. If you do not know them you should learn them; though if you ask nicely someone may just provide you the solution you need. Split-to-array and unnest may work as well. Thank you very much. I don’t know regexps. Can you provide example, please for 8.1. Or maybe CASE WHEN and substring testing can also used. Andrus.
Re: [GENERAL] Reassign value of IN parameter in 9.1.1
Hello 2011/11/24 Gavin Casey gpjca...@googlemail.com: This works in 9.1.1 but seems like a bug to me: create function xout(_x INTEGER) returns integer as $$ begin _x = _x * 2; return _x; end; $$ LANGUAGE plpgsql; select xout(4); It would not have compiled in version 8. I came across such a reassignement doing a code review and was surprised it compiled. Is there a reason for the change in behaviour? Read only parameters was confusing for people without knowledge classic SP languages. Typical programming languages allows it. More this limit has not real reason in PL/pgSQL and after remove , the parameters are little bit more usable - try to implement buble sort. Regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] General performance/load issue
On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Hello everyone, I'm having some troubles with a Postgresql server. We're using PG has a database backend for a very big website (lots of data and much traffic). The issue : server suddenly (1H after restart) becomes slow (queries not responding), load rises (20 instead of 1), iowait rises (20 to 70%) Version : 9.0.5 Server : Dual Xeon X5650 (24 cores total) Memory : 48 GB Disks : SSD Top when overloaded : Top is not the most useful tool here, I guess. Use iotop (will show you which processes are doing the I/O) and tools like vmstat / iostat. Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. Robert Treat conjecture: xzilla.net consulting: omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 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. -- Benjamin Henrion bhenrion at ffii.org FFII Brussels - +32-484-566109 - +32-2-4148403 In July 2005, after several failed attempts to legalise software patents in Europe, the patent establishment changed its strategy. Instead of explicitly seeking to sanction the patentability of software, they are now seeking to create a central European patent court, which would establish and enforce patentability rules in their favor, without any possibility of correction by competing courts or democratically elected legislators. -- 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] General performance/load issue
On 24 Listopad 2011, 16:39, Robert Treat wrote: On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. Yes, checkpoints were my first thought too. OTOH the OP reported that most of the I/O is caused by WAL writer - that's not exactly the part that does the work during checkpoint. Plus the WAL may not be postponed, as it's usually O_DIRECT and fsynced, right. You're right that the writes are postponed, but I generally see that as a good thing when combined with spread checkpoints. And even with those vm settings (about 3.2GB for background writes), I wouldn't expect this behaviour (because the page cache usually expires after 30 seconds). Say you need 100% of the shared buffers is dirty and need to be written. You have 27 minutes (30*0.9) to do that - that means about 8MB/s. With 30 seconds expire there might be about 240MB before the pdflush starts to write the data to the SSD. And that can surely handle more than 50MB/s. So why the long delay? The question is what else is going on there. But all this is just guessing - I want to see the log_checkpoint message, iostat results etc. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. This is one of the reasons why effective_cache_size should be lower than 32GB, probably ... Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] General performance/load issue
Hi Robert, Thanks for your help as well. You're right about checkpoints, it's running pretty good at start then encounter heavy i/os. I've changed theses settings and also reduced work_mem a little and reduced effective_cache_size btw. LOG: parameter work_mem changed to 96MB LOG: parameter effective_cache_size changed to 24GB LOG: parameter checkpoint_segments changed to 40 LOG: parameter checkpoint_timeout changed to 5min Apparently, it's been running fine since I made the first changes recommenced by Tomas. Let's wait for a couple of hours again to confirm this. Gaëtan Le 24/11/11 16:39, « Robert Treat » r...@xzilla.net a écrit : On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Hello everyone, I'm having some troubles with a Postgresql server. We're using PG has a database backend for a very big website (lots of data and much traffic). The issue : server suddenly (1H after restart) becomes slow (queries not responding), load rises (20 instead of 1), iowait rises (20 to 70%) Version : 9.0.5 Server : Dual Xeon X5650 (24 cores total) Memory : 48 GB Disks : SSD Top when overloaded : Top is not the most useful tool here, I guess. Use iotop (will show you which processes are doing the I/O) and tools like vmstat / iostat. Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. Robert Treat conjecture: xzilla.net consulting: omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] General performance/load issue
Tomas, I've enabled logging of checkpoints. I'm waiting for the next i/o crisisŠ Gaëtan Le 24/11/11 17:02, « Tomas Vondra » t...@fuzzy.cz a écrit : On 24 Listopad 2011, 16:39, Robert Treat wrote: On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. Yes, checkpoints were my first thought too. OTOH the OP reported that most of the I/O is caused by WAL writer - that's not exactly the part that does the work during checkpoint. Plus the WAL may not be postponed, as it's usually O_DIRECT and fsynced, right. You're right that the writes are postponed, but I generally see that as a good thing when combined with spread checkpoints. And even with those vm settings (about 3.2GB for background writes), I wouldn't expect this behaviour (because the page cache usually expires after 30 seconds). Say you need 100% of the shared buffers is dirty and need to be written. You have 27 minutes (30*0.9) to do that - that means about 8MB/s. With 30 seconds expire there might be about 240MB before the pdflush starts to write the data to the SSD. And that can surely handle more than 50MB/s. So why the long delay? The question is what else is going on there. But all this is just guessing - I want to see the log_checkpoint message, iostat results etc. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. This is one of the reasons why effective_cache_size should be lower than 32GB, probably ... Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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] How to get normalized data from tekst column
Andrus kobrule...@hot.ee writes: David, Regular Expressions are your friend here. If you do not know them you should learn them; though if you ask nicely someone may just provide you the solution you need. Split-to-array and unnest may work as well. Thank you very much. I dona**t know regexps. Can you provide example, please for 8.1. Or maybe CASE WHEN and substring testing can also used. The query SELECT id, a[1] AS name, a[2] AS percent FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a FROM project ) AS dummy should work un every halfway recent PostgreSQL version - dunno about 8.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] CREATE TABLE table_name AS EXECUTE name WITH DATA becomes syntax error.
On Thursday, November 24, 2011 1:55:53 am Naoya Anzai wrote:
Hi,
Accroding to
http://www.postgresql.org/docs/9.1/interactive/sql-createtableas.html ,
CREATE TABLE table_name AS EXECUTE name WITH DATA seems a right syntax,
but,this statement becomes a SYNTAX ERROR.
Is this a specification?
---
naoya=# SELECT VERSION();
version
---
PostgreSQL 9.1.1 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-48), 64-bit (1 row)
naoya=# PREPARE GETONE AS SELECT * FROM SEED WHERE ID=1;
PREPARE
naoya=# EXECUTE GETONE;
id |date
+
1 | 2011-11-24 11:24:49.675427
(1 row)
naoya=# CREATE TABLE NEW_SEED AS EXECUTE GETONE;
SELECT 1
naoya=# CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA;
ERROR: syntax error at or near WITH DATA at character 42
STATEMENT: CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA;
Order of execution?
Example from link above:
PREPARE recentfilms(date) AS
SELECT * FROM films WHERE date_prod $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
EXECUTE recentfilms('2002-01-01');
So for your case:
CREATE TABLE NEW_SEED2 WITH DATA AS EXECUTE GETONE;
---
Regards.
---
Naoya Anzai
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reassign value of IN parameter in 9.1.1
Gavin Casey gpjca...@googlemail.com writes: It was actually the reassignment of an IN parameter that I was questioning, That was changed in 9.0, per the release notes: * Allow input parameters to be assigned values within PL/pgSQL functions (Steve Prentice) Formerly, input parameters were treated as being declared CONST, so the function's code could not change their values. This restriction has been removed to simplify porting of functions from other DBMSes that do not impose the equivalent restriction. An input parameter now acts like a local variable initialized to the passed-in value. As for := versus =, plpgsql has always accepted both. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Convert string to UNICODE migration FROM 7.4 to 9.1
Hi, I'm migrating a PG 7.4 database (encoded in LATIN1) to PG 9.1. The upgrade is just fine, except a problem with a conversion to UNICODE for which I was not able to find a solution yet: I have a function under PG 7.4 that returns an xml structure, with the content encoded in UNICODE. The function is like: - CREATE OR REPLACE FUNCTION get_xml() RETURNS text AS $$ DECLARE output text; BEGIN -- We fill the output variable with xml ... -- We return it in unicode RETURN convert(output, 'LATIN1', 'UTF8'); END; $$ LANGUAGE 'plpgsql'; - After migrating to PG 9.1, I noticed that convert now requires a bytea, and not a text. I tried different things: - Change the output variable to a bytea - Use RETURN convert(convert_to(output, 'LATIN1'), 'LATIN1', 'UTF8'); - Encode the database in UTF8 instead of LATIN1 ... but no output is similar to what I had under PG 7.4. More precisely, I had under PG 7.4 something like (notice the Name Hélène converted into Hélène) ?xml version=1.0 encoding=UTF-8? DispatchAuftrag Versicherungsnehmer NameMartelli/Name VornameHélène/Vorname Strasserue des Comptes/Strasse LandSuisse/Land PLZ123456/PLZ OrtFribourg/Ort Email./Email TelMobil./TelMobil TelPrivat./TelPrivat TelGeschaeft./TelGeschaeft Fax./Fax /Versicherungsnehmer /DispatchAuftrag And now I get something like: ?xml version=1.0 encoding=UTF-8?\012DispatchAuftrag\012 /Versicherungsnehmer\012 Geschaedigter\012NameEtat du Valais/Name\012Vorname/Vorname\012StrasseIndivis / Centre entretien Autoroute/Strasse\012LandSuisse/Land\012 PLZ1906/PLZ\012OrtCharrat/Ort\012Email/Email\012 TelMobil/TelMobil\012TelPrivat027 747 61 00/TelPrivat\012 TelGeschaeft./TelGeschaeft\012Fax/Fax\012 /Geschaedigter\012 Schadendaten\012SchadenDatum2005-01-23/SchadenDatum\012 SchadenNrJR/41123-208/JPS/SchadenNr\012 GeschaetzteSchadenhoehe/GeschaetzteSchadenhoehe\012 SchadenAmFzDommages aux installations routi\303\250res/SchadenAmFz\012 Bemerkung/Bemerkung\012 /Schadendaten\012 Fahrzeugstandort\012 Name/Name\012Vorname/Vorname\012Strasse/Strasse\012 Land/Land\012PLZ/PLZ\012Ort/Ort\012 Telefon/Telefon\012Fax/Fax\012Email/Email\012 /Fahrzeugstandort\012/DispatchAuftrag\012 Newlines don't seem to be handled properly, and I'm unable to find out how to change that. UTF8 encoding is not good either. Any idea how to correct that? Thanks! Philippe - Attik System web: http://www.attiksystem.ch Philippe Lang phone : +41 26 422 13 75 rte de la Fonderie 2 gsm: +41 79 351 49 94 1700 Fribourg twitter: @philippelang pgp: http://keyserver.pgp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CREATE TABLE table_name AS EXECUTE name WITH DATA becomes syntax error.
Naoya Anzai anzai-na...@mxu.nes.nec.co.jp writes: Accroding to http://www.postgresql.org/docs/9.1/interactive/sql-createtableas.html , CREATE TABLE table_name AS EXECUTE name WITH DATA seems a right syntax, but,this statement becomes a SYNTAX ERROR. Hmm ... it looks like WITH [NO] DATA is actually only implemented for the query = SelectStmt case, not the query = ExecuteStmt case. We need a less klugy implementation to support EXECUTE :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] General performance/load issue
Le 24 novembre 2011 17:02, Tomas Vondra t...@fuzzy.cz a écrit : On 24 Listopad 2011, 16:39, Robert Treat wrote: On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. Yes, checkpoints were my first thought too. OTOH the OP reported that most of the I/O is caused by WAL writer - that's not exactly the part that does the work during checkpoint. Plus the WAL may not be postponed, as it's usually O_DIRECT and fsynced, right. You're right that the writes are postponed, but I generally see that as a good thing when combined with spread checkpoints. And even with those vm settings (about 3.2GB for background writes), I wouldn't expect this behaviour (because the page cache usually expires after 30 seconds). Say you need 100% of the shared buffers is dirty and need to be written. You have 27 minutes (30*0.9) to do that - that means about 8MB/s. With 30 seconds expire there might be about 240MB before the pdflush starts to write the data to the SSD. And that can surely handle more than 50MB/s. So why the long delay? The question is what else is going on there. But all this is just guessing - I want to see the log_checkpoint message, iostat results etc. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. This is one of the reasons why effective_cache_size should be lower than 32GB, probably ... according to 'free' output, 38GB is what is here right now. effective_cache_size is just informative, so you can put it to 1TB without memory issue. And, it is OS cache+PG cache. There is not enougth information yet to be sure on what's happening. log_checkpoint output will help for sure. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- 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] General performance/load issue
Finally, it crashed againŠ :-( Here's the output of iotop while databased was inaccessible : 32361 be/4 postgres0.00 B/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34847) idle 32244 be/4 postgres 163.48 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34660) SELECT 32045 be/4 postgres7.78 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(33765) SELECT 32158 be/4 postgres7.78 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34112) SELECT 32242 be/4 postgres7.78 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34632) SELECT 32372 be/4 postgres0.00 B/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(38858) idle in transaction 32231 be/4 postgres 15.57 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(38602) SELECT 28811 be/4 postgres3.89 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(40594) SELECT 32190 be/4 postgres3.89 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(38497) SELECT And the latest logs : LOG: checkpoint complete: wrote 3192 buffers (0.2%); 0 transaction log file(s) added, 1 removed, 0 recycled; write=262.636 s, sync=135.456 s, total=416.630 s LOG: checkpoint complete: wrote 716 buffers (0.0%); 0 transaction log file(s) added, 1 removed, 0 recycled; write=165.497 s, sync=17.111 s, total=193.199 s WARNING: pgstat wait timeout STATEMENT: SELECT 'commit',sum(pg_stat_get_db_xact_commit(oid)) FROM pg_database WHERE datname=$1 UNION ALL SELECT 'rollback',sum(pg_stat_get_db_xact_rollback(oid)) FROM pg_database WHERE datname=$2 LOG: checkpoint complete: wrote 699 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=203.023 s, sync=119.037 s, total=335.012 s LOG: checkpoint complete: wrote 348 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=120.412 s, sync=0.020 s, total=120.435 s Does this help? Gaëtan Le 24/11/11 17:02, « Tomas Vondra » t...@fuzzy.cz a écrit : On 24 Listopad 2011, 16:39, Robert Treat wrote: On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. Yes, checkpoints were my first thought too. OTOH the OP reported that most of the I/O is caused by WAL writer - that's not exactly the part that does the work during checkpoint. Plus the WAL may not be postponed, as it's usually O_DIRECT and fsynced, right. You're right that the writes are postponed, but I generally see that as a good thing when combined with spread checkpoints. And even with those vm settings (about 3.2GB for background writes), I wouldn't expect this behaviour (because the page cache usually expires after 30 seconds). Say you need 100% of the shared buffers is dirty and need to be written. You have 27 minutes (30*0.9) to do that - that means about 8MB/s. With 30 seconds expire there might be about 240MB before the pdflush starts to write the data to the SSD. And that can surely handle more than 50MB/s. So why the long delay? The question is what else is going on there. But all this is just guessing - I want to see the log_checkpoint message, iostat results etc. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. This is one of the reasons why effective_cache_size should be lower than 32GB, probably ... Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Compiler does not detect support for 64 bit integers
Il 23/11/2011 22:44, Tom Lane ha scritto: Antonio Franzosoantoniofranz...@yahoo.it writes: I'm using Code::block with MinGW on a Windows Seven x64 and I get these errors: ..\..\..\Program Files\PostgreSQL\9.0\include\server\c.h|284|error: #error must have a working 64-bit integer datatype| Um ... did you run the configure script? In a MinGW environment the build process is basically like Unix, it's not the MSVC way. regards, tom lane Thanks for reply. Should I run a configure script even I've specified in the build option where compiler can find the header files? Sorry, but I'm a newbie. -- 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] Convert string to UNICODE migration FROM 7.4 to 9.1
Philippe Lang philippe.l...@attiksystem.ch writes: I have a function under PG 7.4 that returns an xml structure, with the content encoded in UNICODE. Basically, the only reason you got away with that in 7.4 is that 7.4 is so lax about encodings. In general, in modern releases, all text strings inside the backend are in the database's specified encoding. Full stop, no exceptions. If you think you need something different, you need to rethink your assumptions. I'd try just dropping the convert() step and see what happens. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get normalized data from tekst column
Harald, Thank you. The query SELECT id, a[1] AS name, a[2] AS percent FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a FROM project ) AS dummy should work un every halfway recent PostgreSQL version - dunno about 8.1. I tried it but got error in 8.1: ERROR: function regexp_split_to_table(text, unknown) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. how to get data in 8.1 ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] General performance/load issue
Le 24 novembre 2011 20:38, Gaëtan Allart gae...@nexylan.com a écrit : Finally, it crashed againŠ :-( Here's the output of iotop while databased was inaccessible : 32361 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34847) idle 32244 be/4 postgres 163.48 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34660) SELECT 32045 be/4 postgres 7.78 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(33765) SELECT 32158 be/4 postgres 7.78 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34112) SELECT 32242 be/4 postgres 7.78 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(34632) SELECT 32372 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(38858) idle in transaction 32231 be/4 postgres 15.57 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(38602) SELECT 28811 be/4 postgres 3.89 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(40594) SELECT 32190 be/4 postgres 3.89 K/s 0.00 B/s 0.00 % 99.99 % postgres: mydb mydb host(38497) SELECT And the latest logs : LOG: checkpoint complete: wrote 3192 buffers (0.2%); 0 transaction log file(s) added, 1 removed, 0 recycled; write=262.636 s, sync=135.456 s, total=416.630 s LOG: checkpoint complete: wrote 716 buffers (0.0%); 0 transaction log file(s) added, 1 removed, 0 recycled; write=165.497 s, sync=17.111 s, total=193.199 s WARNING: pgstat wait timeout STATEMENT: SELECT 'commit',sum(pg_stat_get_db_xact_commit(oid)) FROM pg_database WHERE datname=$1 UNION ALL SELECT 'rollback',sum(pg_stat_get_db_xact_rollback(oid)) FROM pg_database WHERE datname=$2 LOG: checkpoint complete: wrote 699 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=203.023 s, sync=119.037 s, total=335.012 s LOG: checkpoint complete: wrote 348 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=120.412 s, sync=0.020 s, total=120.435 s Does this help? yes. It seem you have an issue with your checkpoint syncing time, it is fixed in 9.1 and backported in 9.0 here : http://projects.2ndquadrant.com/backports It is possible you have other problems that explains the issue you have. An immediate solution before trying a patch is to reduce your shared_buffer setting to something very low, like 1GB. Gaëtan Le 24/11/11 17:02, « Tomas Vondra » t...@fuzzy.cz a écrit : On 24 Listopad 2011, 16:39, Robert Treat wrote: On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra t...@fuzzy.cz wrote: On 24 Listopad 2011, 14:51, Gaëtan Allart wrote: Postgresql.conf : max_connections = 50 shared_buffers = 12G temp_buffers = 40MB work_mem = 128MB maintenance_work_mem = 256MB max_files_per_process = 8192 checkpoint_segments = 256 checkpoint_timeout = 30min checkpoint_completion_target = 0.9 Fine. Let's see the options that look suspicious. I think you missed some suspicious settings... I'd recommend setting shared buffers to 8gb, and I'd likely reduce checkpoint segements to 30 and set the checkpoint timeout back to 5 minutes. Everything about the way this server is configured (including those vm settings) is pushing it towards delaying the WAL/Buffer/Checkpoint as long as possible, which matches with the idea of good performance initial followed by a period of poor performance and heavy i/o. Yes, checkpoints were my first thought too. OTOH the OP reported that most of the I/O is caused by WAL writer - that's not exactly the part that does the work during checkpoint. Plus the WAL may not be postponed, as it's usually O_DIRECT and fsynced, right. You're right that the writes are postponed, but I generally see that as a good thing when combined with spread checkpoints. And even with those vm settings (about 3.2GB for background writes), I wouldn't expect this behaviour (because the page cache usually expires after 30 seconds). Say you need 100% of the shared buffers is dirty and need to be written. You have 27 minutes (30*0.9) to do that - that means about 8MB/s. With 30 seconds expire there might be about 240MB before the pdflush starts to write the data to the SSD. And that can surely handle more than 50MB/s. So why the long delay? The question is what else is going on there. But all this is just guessing - I want to see the log_checkpoint message, iostat results etc. On a side note, I'd guess your work_mem is probably too high. 50 (connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM, which is 25% of total ram on the box. That doesn't necessarily mean game over, but it seem like it wouldn't be that hard to get thrashing being set up that way. YMMV. This is one of the reasons why effective_cache_size should be lower than 32GB, probably ... Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --
Re: [GENERAL] How to get normalized data from tekst column
On Nov 24, 2011, at 15:40, Andrus Moor eetas...@online.ee wrote: Harald, Thank you. The query SELECT id, a[1] AS name, a[2] AS percent FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a FROM project ) AS dummy should work un every halfway recent PostgreSQL version - dunno about 8.1. I tried it but got error in 8.1: ERROR: function regexp_split_to_table(text, unknown) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. how to get data in 8.1 ? Andrus. You seem to have 2 options: 1. Upgrade to at least 8.4 and use the regexp functions. 2. Write something in PL/pgsql Whether you can write a sufficient function with 8.1 features I do not know. You main issue is you need to be able to output multiple records from a single input record and doing so before 8.4 seems problematic since functions like substring cannot do that. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get normalized data from tekst column
David, Thank you. Whether you can write a sufficient function with 8.1 features I do not know. You main issue is you need to be able to output multiple records from a single input record and doing so before 8.4 seems problematic since functions like substring cannot do that. comment field contain 0.. 2 salemans, no more: 'Aavo 19%, Peedu 15%' 'Lauri-21%,Peedu 15%' 'Taavi 21%' Maybe in 8.1 it is possible to write 2 select statements. First will extract first item and second select will extract second item if second item exists ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
