Re: [PERFORM] Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
I did run each query several times, the results I posted are for ~10th run of the query. The zone reclaim mode is 0. On 2 November 2012 00:39, Scott Marlowe scott.marl...@gmail.com wrote: Two possibilities: caching. make sure to run each query several times in a row. zone reclaim mode. If this has gotten turned on turn it back off. How to tell: sysctl -n vm.zone_reclaim_mode Output should be 0. If it's not, then add this to /etc/sysctl.conf: vm.zone_reclaim_mode=0 and run: sudo sysctl -p and see if that helps.
[PERFORM] freebsd or linux
I'm running a server with lots of counts and calculations. currently its ubuntu server is freebsd faster? also this is a i386 machine. or linux and bsd is about the same. this is not to be an argument just looking. Current benchmarks to compare thanks
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
- I'm using ext4 - Kernel: Linux 2.6.32-279.9.1.el6.x86_64 #1 SMP Fri Aug 31 09:04:24 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux - I haven't tuned kernel in any way except setting kernel.shmmax and kernel.shmall to: kernel.shmmax = 68719476736 kernel.shmall = 4294967296 - We are using 15k drives (magnetic) connected through SAS in RAID10 setup, I don't know precise model numbers (I can find out), On 1 November 2012 15:40, Marcos Ortiz mlor...@uci.cu wrote: Regards, Petr. Tuning PostgreSQL is not just change the postgresql.conf, it includes more things like: - the filesystem that you are using - the kernel version that you using (particularly in Linux systems) - the tuning to kernel variables - the type of discs that you are using (SSDs are very fast, like you saw in your iMac system) On 10/30/2012 02:44 PM, Petr Praus wrote: I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down. Specifically: set work_mem='1MB'; select ...; // running time is ~1800 ms set work_mem='96MB'; select ...' // running time is ~1500 ms When I do exactly the same query (the one from my previous post) with exactly the same data on the server: I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB. The Mac has SSD so it's understandably faster, but it exhibits a behavior I would expect. What am I doing wrong here? Thanks. On 30 October 2012 14:08, Petr Praus p...@praus.net wrote: Hello, I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database, the disks are local RAID10. Given that the default postgresql.conf is quite conservative regarding memory settings, I thought it might be a good idea to allow Postgres to use more memory. To my surprise, following advice in the performance tuning guide on Postgres wiki[2] significantly slowed down practically every query I run but it's more noticeable on the more complex queries. I also tried running pgtune[1] which gave the following recommendation with more parameters tuned, but that didn't change anything. It suggests shared_buffers of 1/4 of RAM size which seems to in line with advice elsewhere (and on PG wiki in particular). default_statistics_target = 50 maintenance_work_mem = 960MB constraint_exclusion = on checkpoint_completion_target = 0.9 effective_cache_size = 11GB work_mem = 96MB wal_buffers = 8MB checkpoint_segments = 16 shared_buffers = 3840MB max_connections = 80 I tried reindexing the whole database after changing the settings (using REINDEX DATABASE), but that didn't help either. I played around with shared_buffers and work_mem. Gradually changing them from the very conservative default values (128k / 1MB) also gradually decreased performance. I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to be that Hash Join is significantly slower. It's not clear to me why. To give some specific example, I have the following query. It runs in ~2100ms on the default configuration and ~3300ms on the configuration with increased buffer sizes: select count(*) from contest c left outer join contestparticipant cp on c.id=cp.contestId left outer join teammember tm on tm.contestparticipantid=cp.id left outer join staffmember sm on cp.id=sm.contestparticipantid left outer join person p on p.id=cp.personid left outer join personinfo pi on pi.id=cp.personinfoid where pi.lastname like '%b%' or pi.firstname like '%a%'; EXPLAIN (ANALYZE,BUFFERS) for the query above: - Default buffers: http://explain.depesz.com/s/xaHJ - Bigger buffers: http://explain.depesz.com/s/Plk The tables don't have anything special in them The question is why am I observing decreased performance when I increase buffer sizes? The machine is definitely not running out of memory. Allocation if shared memory in OS is (`shmmax` and `shmall`) is set to very large values, that should not be a problem. I'm not getting any errors in the Postgres log either. I'm running autovacuum in the default configuration but I don't expect that has anything to do with it. All queries were run on the same machine few seconds apart, just with changed configuration (and restarted PG). I also found a blog post [3] which experiments with various work_mem values that run into similar behavior I'm experiencing but it doesn't really explain it. [1]: http://pgfoundry.org/projects/pgtune/ [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server [3]: http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/ Thanks, Petr Praus PS: I also posted the question here: http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-downbut a few people suggested
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On 3 November 2012 12:09, Gunnar Nick Bluth gunnar.bl...@pro-open.dewrote: Am 03.11.2012 16:20, schrieb Petr Praus: Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there. Ok, I've actually looked these up now... at the time these were current, I was in the lucky situation to only deal with Opterons. And actually, with these CPUs it is pretty possible that Scott Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the right direction. Did you check that? I did check that, it's zero. I responded to his message, but my messages to the mailing list are getting delayed by ~24 hours because somebody has to always bless them. Yes, same behaviour. I let the shared_buffers be the default (which is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same times as before). It really seems that the culprit is work_mem. Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-) Well, that's what I always thought too! :-) -- Gunnar Nick Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On 3 November 2012 05:31, Gunnar Nick Bluth gunnar.bl...@pro-open.dewrote: Am 02.11.2012 17:12, schrieb Petr Praus: Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there. Might be worth to - manually pin (with taskset) the session you test this in to a particular CPU (once on each socket) to see if the times change I tested this and it does not seem to have any effect (assuming I used taskset correctly but I think so: taskset 02 psql to pin down to CPU #1 and taskset 01 psql to pin to CPU #0). Well, that pinned your _client_ to the CPUs, not the server side session ;-) You'd have to spot for the PID of the new IDLE server process and pin that using taskset -p. Also, 01 and 02 are probably cores in the same package/socket. Try lscpu first and spot for NUMA node* lines at the bottom. Ah, stupid me :) But anyway... let's try something else first: - try reducing work_mem in the session you're testing in (so you have large SHM, but small work mem) Did this and it indicates to me that shared_buffers setting actually does not have an effect on this behaviour as I previously thought it has. It really boils down to work_mem: when I set shared_buffers to something large (say 4GB) and just play with work_mem the problem persists. This only confirms what we've seen before. As soon as your work_mem permits an in-memory sort of the intermediate result set (which at that point in time is where? In the SHM, or in the private memory of the backend? I can't tell, tbth), the sort takes longer than when it's using a temp file. What if you reduce the shared_buffers to your original value and only increase/decrease the session's work_mem? Same behaviour? Yes, same behaviour. I let the shared_buffers be the default (which is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same times as before). It really seems that the culprit is work_mem. Cheers, -- Gunnar Nick Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne
Re: [PERFORM] dbt2 performance regresses from 9.1.6 to 9.2.1
You are using prepared statements, this makes me think that this regression might be due to support for parameter specific plans for prepared statements. [1] Can you run the test on both versions without prepared statements and see if the regressions remains. Without prepare statement, we got 48837.33 avg notpm with 9.1.6 and 43264.54 avg notpm with 9.2.1. notps over time shows the slowdown of 9.2.1 is evident during the entire course of the run. Their profiles are posted on http://pgsql.privatepaste.com/b770f72967 (9.1.6) and http://pgsql.privatepaste.com/6fa8b7f174 (9.2.1). Thanks, Dong -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On 1 November 2012 18:25, Gunnar Nick Bluth gunnar.bl...@pro-open.dewrote: Am 01.11.2012 21:40, schrieb Marcos Ortiz: Regards, Petr. Tuning PostgreSQL is not just change the postgresql.conf, it includes more things like: - the filesystem that you are using - the kernel version that you using (particularly in Linux systems) - the tuning to kernel variables - the type of discs that you are using (SSDs are very fast, like you saw in your iMac system) On 10/30/2012 02:44 PM, Petr Praus wrote: I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down. Specifically: set work_mem='1MB'; select ...; // running time is ~1800 ms set work_mem='96MB'; select ...' // running time is ~1500 ms When I do exactly the same query (the one from my previous post) with exactly the same data on the server: I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB. Just some thoughts (interested in this, once seen a Sybase ASE come close to a halt when we threw a huge lot of SHM at it...). 8 cores, so probably on 2 sockets? What CPU generation? The processors are two quad core Intel x7350 Xeon at 2.93Ghz. It's somewhat older (released late 2007) but it's not absolute speed I'm after - it's the difference in speed when increasing work_mem. Both explain outputs show an amount of read buffers. Did you warm the caches before testing? I did warm the caches before testing. Maybe you're hitting a NUMA issue there? If those reads come from the OS' cache, the scheduler might decide to move your process to a different core (that can access the cache better), then moves it back when you access the SHM segment more (the ~4GB get allocated at startup, so probably close to the CPU the postmaster ist running on). A migration to a different cacheline is very expensive. The temp reads/writes (i.e., the OS cache for the temp files) would probably be allocated close to the CPU requesting the temp file. Just groping about in the dark though... but the iMac is obviously not affected by this, with one socket/memory channel/cache line. I made a test with Ubuntu 12.04 VM machine (vmware workstation 4.1.3 on the same iMac) with 4GB memory and shared_buffers=1GB. To my slight surprise, the query is faster on Ubuntu VM machine then on the OSX (~1050ms vs. ~1500ms with work_mem=1MB). This might be caused by effective_io_concurrency which is enabled on Ubuntu but can't be enabled on OSX because postgres does not support it there. The interesting thing is that increasing work_mem to 96MB on Ubuntu slows down the query to about ~1250ms from ~1050ms. Might be worth to - manually pin (with taskset) the session you test this in to a particular CPU (once on each socket) to see if the times change I tested this and it does not seem to have any effect (assuming I used taskset correctly but I think so: taskset 02 psql to pin down to CPU #1 and taskset 01 psql to pin to CPU #0). - try reducing work_mem in the session you're testing in (so you have large SHM, but small work mem) Did this and it indicates to me that shared_buffers setting actually does not have an effect on this behaviour as I previously thought it has. It really boils down to work_mem: when I set shared_buffers to something large (say 4GB) and just play with work_mem the problem persists. Cheers, -- Gunnar Nick Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne
Re: [PERFORM] help with too slow query
Pedro Jiménez Pérez wrote:
Sent: Friday, November 02, 2012 1:14 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] help with too slow query
Hello,
I have this table definition:
CREATE TABLE ism_floatvalues
(
id_signal bigint NOT NULL, -- Indica la señal a la que pertenece este
valor. Clave foránea que
referencia al campo id_signal de la tabla ism_signal.
time_stamp timestamp without time zone NOT NULL, -- Marca de tiempo que
indica fecha y hora
correpondiente a este dato. Junto con id_signal forma la clave primaria de
esta tabla
var_value double precision, -- Almacena el valor concreto de la señal en la
marca de tiempo
espeficicada.
CONSTRAINT ism_floatvalues_id_signal_fkey FOREIGN KEY (id_signal)
REFERENCES ism_signal (id_signal) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
CREATE INDEX ism_floatvalues_index_idsignal_timestamp
ON ism_floatvalues
USING btree
(id_signal, time_stamp DESC);
*
Then I run this query
*
EXPLAIN analyze
select round(CAST(sum(var_value) AS numeric),2) as var_value,
date_trunc('month', time_stamp) as
time_stamp , date_part('month',date_trunc('month', time_stamp)) as month,
date_part('year',date_trunc('year', time_stamp)) as year from ism_floatvalues
where id_signal in
(
select id_signal from ism_signal where reference = 'EDCA' and id_source in
(
select id_source from ism_installation where id_installation in
(select id_installation from ism_groupxinstallation where id_group = 101)
)
)
and time_stamp date_trunc('month', current_date - interval '11 months')
group by date_trunc('month', time_stamp), month, year
order by time_stamp
**
And this is the result:
**
GroupAggregate (cost=4766541.62..4884678.62 rows=39483 width=16) (actual
time=1302542.073..1302713.154 rows=10 loops=1)
[...]
- Hash Join (cost=545.65..3203518.39 rows=9356201 width=16)
(actual time=458941.090..1302245.307 rows=9741 loops=1)
Hash Cond: (ism_floatvalues.id_signal = ism_signal.id_signal)
- Seq Scan on ism_floatvalues (cost=0.00..2965077.57
rows=28817098 width=24) (actual time=453907.600..1002381.652 rows=29114105
loops=1)
Filter: (time_stamp date_trunc('month'::text,
(('now'::text)::date - '11 mons'::interval)))
[...]
This query is very slow as you can see, it took about 20 minutos to
complete Can someone help me
to improve performance on this query??
Regards.
This sequential scan takes the lion share of the time.
Are the 29 million rows selected in that scan a significant percentage
of the total rows? If yes, then the sequential scan is the
most efficient way to get the result, and the only remedy is to get
faster I/O or to cache more of the table in RAM.
If the query needs to access a lot of rows to complete, it must
be slow.
Yours,
Laurenz Albe
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] help with too slow query
Title: Documento sin ttulo
Hello,
I have this table definition:
CREATE TABLE ism_floatvalues
(
id_signal bigint NOT NULL, -- Indica la seal a la que pertenece
este valor. Clave fornea que referencia al campo id_signal de la
tabla ism_signal.
time_stamp timestamp without time zone NOT NULL, -- Marca de
tiempo que indica fecha y hora correpondiente a este dato. Junto con
id_signal forma la clave primaria de esta tabla
var_value double precision, -- Almacena el valor concreto de la
seal en la marca de tiempo espeficicada.
CONSTRAINT ism_floatvalues_id_signal_fkey FOREIGN KEY (id_signal)
REFERENCES ism_signal (id_signal) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
CREATE INDEX ism_floatvalues_index_idsignal_timestamp
ON ism_floatvalues
USING btree
(id_signal, time_stamp DESC);
*
Then I run this query
*
EXPLAIN analyze
select round(CAST(sum(var_value) AS numeric),2) as var_value,
date_trunc('month', time_stamp) as time_stamp ,
date_part('month',date_trunc('month', time_stamp)) as month,
date_part('year',date_trunc('year', time_stamp)) as year from
ism_floatvalues where id_signal in
(
select id_signal from ism_signal where reference = 'EDCA' and
id_source in
(
select id_source from ism_installation where id_installation in
(select id_installation from ism_groupxinstallation where id_group =
101)
)
)
and time_stamp date_trunc('month', current_date - interval '11
months')
group by date_trunc('month', time_stamp), month, year
order by time_stamp
**
And this is the result:
**
"GroupAggregate (cost=4766541.62..4884678.62 rows=39483 width=16)
(actual time=1302542.073..1302713.154 rows=10 loops=1)"
" - Sort (cost=4766541.62..4789932.12 rows=9356201 width=16)
(actual time=1302444.324..1302531.447 rows=9741 loops=1)"
" Sort Key: (date_trunc('month'::text,
ism_floatvalues.time_stamp)), (date_part('month'::text,
date_trunc('month'::text, ism_floatvalues.time_stamp))),
(date_part('year'::text, date_trunc('year'::text,
ism_floatvalues.time_stamp)))"
" Sort Method: quicksort Memory: 941kB"
" - Hash Join (cost=545.65..3203518.39 rows=9356201
width=16) (actual time=458941.090..1302245.307 rows=9741 loops=1)"
" Hash Cond: (ism_floatvalues.id_signal =
ism_signal.id_signal)"
" - Seq Scan on ism_floatvalues
(cost=0.00..2965077.57 rows=28817098 width=24) (actual
time=453907.600..1002381.652 rows=29114105 loops=1)"
" Filter: (time_stamp
date_trunc('month'::text, (('now'::text)::date - '11
mons'::interval)))"
" - Hash (cost=544.19..544.19 rows=117 width=8)
(actual time=733.782..733.782 rows=40 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 2kB"
" - HashAggregate (cost=543.02..544.19
rows=117 width=8) (actual time=733.072..733.412 rows=40 loops=1)"
" - Hash Semi Join
(cost=27.61..542.73 rows=117 width=8) (actual time=638.175..687.934
rows=40 loops=1)"
" Hash Cond: (ism_signal.id_source =
ism_installation.id_source)"
" - Bitmap Heap Scan on
ism_signal (cost=18.84..530.42 rows=850 width=16) (actual
time=243.690..284.303 rows=850 loops=1)"
" Recheck Cond:
((reference)::text = 'EDCA'::text)"
" - Bitmap Index Scan on
ism_signal_idx_reference (cost=0.00..18.63 rows=850 width=0)
(actual time=243.429..243.429 rows=865 loops=1)"
" Index Cond:
((reference)::text = 'EDCA'::text)"
" - Hash (cost=8.27..8.27
rows=40 width=8) (actual time=394.393..394.393 rows=40 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 2kB"
" - Hash Semi Join
(cost=3.25..8.27 rows=40 width=8) (actual time=391.966..394.000
rows=40 loops=1)"
" Hash Cond:
(ism_installation.id_installation =
ism_groupxinstallation.id_installation)"
" - Seq Scan on
ism_installation (cost=0.00..4.17 rows=117 width=16) (actual
time=0.086..1.354 rows=117 loops=1)"
" - Hash
(cost=2.75..2.75 rows=40 width=8) (actual time=390.274..390.274
rows=40 loops=1)"
" Buckets: 1024
Batches: 1 Memory Usage: 2kB"
" - Seq Scan
on ism_groupxinstallation (cost=0.00..2.75 rows=40 width=8) (actual
time=389.536..389.903 rows=40 loops=1)"
" Filter:
(id_group = 101)"
"Total runtime: 1302731.013 ms"
This query is very slow as you can see, it took about 20 minutos to
complete Can someone help me to improve performance on this
query??
Regards.
--
Pedro Jimnez Prez
p.jime...@ismsolar.com
Re: [PERFORM] dbt2 performance regresses from 9.1.6 to 9.2.1
On Sun, Nov 4, 2012 at 7:23 PM, Dong Ye y...@vmware.com wrote: You are using prepared statements, this makes me think that this regression might be due to support for parameter specific plans for prepared statements. [1] Can you run the test on both versions without prepared statements and see if the regressions remains. Without prepare statement, we got 48837.33 avg notpm with 9.1.6 and 43264.54 avg notpm with 9.2.1. notps over time shows the slowdown of 9.2.1 is evident during the entire course of the run. Their profiles are posted on http://pgsql.privatepaste.com/b770f72967 (9.1.6) and http://pgsql.privatepaste.com/6fa8b7f174 (9.2.1). You know... it does look as if 9.2.1 is generating a lot more pressure into the memory allocator (AllocSetAlloc notably higher). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On Sat, Nov 3, 2012 at 10:09 AM, Gunnar Nick Bluth gunnar.bl...@pro-open.de wrote: Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-) In my experience when doing sorts in isolation, having more work_mem is a bad thing, unless it enables you to remove a layer of tape-merging. I always blamed it on the L1/L2 etc. levels of caching. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes jeff.ja...@gmail.com wrote: Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-) In my experience when doing sorts in isolation, having more work_mem is a bad thing, unless it enables you to remove a layer of tape-merging. I always blamed it on the L1/L2 etc. levels of caching. Blame it on quicksort, which is quite cache-unfriendly. Perhaps PG should consider using in-memory mergesort for the bigger chunks. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes jeff.ja...@gmail.com wrote: Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-) In my experience when doing sorts in isolation, having more work_mem is a bad thing, unless it enables you to remove a layer of tape-merging. I always blamed it on the L1/L2 etc. levels of caching. Blame it on quicksort, which is quite cache-unfriendly. The observation applies to heap sort. If you can't set work_mem large enough to do the sort in memory, then you want to set it just barely large enough to avoid two layers of tape sorting. Any larger than that reduces performance rather than increasing it. Of course that assumes you have the luxury of knowing ahead of time exactly how large your sort will be and can set work_mem accordingly on a case by case basis, which is unlikely in the real world. Perhaps PG should consider using in-memory mergesort for the bigger chunks. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [HACKERS] [PERFORM] out of memory
On Tue, Oct 30, 2012 at 6:08 AM, Tatsuo Ishii is...@postgresql.org wrote: i have sql file (it's size are 1GB ) when i execute it then the String is 987098801 bytr too long for encoding conversion error occured . pls give me solution about You hit the upper limit of internal memory allocation limit in PostgreSQL. IMO, there's no way to avoid the error except you use client encoding identical to backend. We recently had a customer who suffered a failed in pg_dump because the quadruple-allocation required by COPY OUT for an encoding conversion exceeded allocatable memory. I wonder whether it would be possible to rearrange things so that we can do a streaming encoding conversion. That is, if we have a large datum that we're trying to send back to the client, could we perhaps chop off the first 50MB or so, do the encoding on that amount of data, send the data to the client, lather, rinse, repeat? Your recent work to increase the maximum possible size of large objects (for which I thank you) seems like it could make these sorts of issues more common. As objects get larger, I don't think we can go on assuming that it's OK for peak memory utilization to keep hitting 5x or more. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On Mon, Nov 5, 2012 at 2:09 PM, Jeff Janes jeff.ja...@gmail.com wrote: In my experience when doing sorts in isolation, having more work_mem is a bad thing, unless it enables you to remove a layer of tape-merging. I always blamed it on the L1/L2 etc. levels of caching. Blame it on quicksort, which is quite cache-unfriendly. The observation applies to heap sort. Well, heapsort is worse, but quicksort is also quite bad. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
On Mon, Nov 5, 2012 at 2:40 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Nov 5, 2012 at 2:09 PM, Jeff Janes jeff.ja...@gmail.com wrote: In my experience when doing sorts in isolation, having more work_mem is a bad thing, unless it enables you to remove a layer of tape-merging. I always blamed it on the L1/L2 etc. levels of caching. Blame it on quicksort, which is quite cache-unfriendly. The observation applies to heap sort. Well, heapsort is worse, but quicksort is also quite bad. Here[0], an interesting analysis. I really believe quicksort in PG (due to its more complex datatypes) fares a lot worse. [0] http://www.google.com/url?sa=trct=jq=esrc=ssource=webcd=2ved=0CD0QFjABurl=http%3A%2F%2Fwww.cs.auckland.ac.nz%2F~mcw%2FTeaching%2Frefs%2Fsorting%2Fladner-lamarca-cach-sorting.pdfei=PPqXUMnEL9PaqQHntoDgDQusg=AFQjCNE3mDf6ydj1MHUzfQw13TccOa895A -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Constraint exclusion in views
On Sun, Nov 4, 2012 at 2:32 PM, Claudio Freire klaussfre...@gmail.com wrote: Well, what partition actually means is only bother to try constraint exclusion proofs on appendrel members. UNION ALL trees will get flattened into appendrels in some cases. In a quick look at the code, it seems like in recent releases the restrictions are basically that the UNION ALL arms have to (1) each be a plain SELECT from a single table with no WHERE restriction; (2) all produce the same column datatypes; and (3) not have any volatile functions in the SELECT lists. I might be missing something relevant to the OP's case, but it's hard to tell without a concrete example. I would think our view succeeds all those tests, but I'm not entirely sure about 2. It does use coalesce too, but I really doubt coalesce is volatile... right? I don't have access to the code during the weekend, but I'll check first thing tomorrow whether we have some datatype inconsistencies I didn't notice. Thanks for the hint. It was indeed a type mismatch, there was an int in one subquery that was a bigint in all the others. Thanks a lot. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to keep queries low latency as concurrency increases
On Mon, Nov 5, 2012 at 2:58 PM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 4, 2012 at 1:53 AM, Jeff Janes jeff.ja...@gmail.com wrote: On a 4 CPU machine, if I run pgbench -c10 -j10 with dummy queries (like select 1; or set timezone...) against 2 instances of pgbouncer, I get nearly twice the throughput as if I use only one instance. A rather odd workload, maybe, but it does seem to be similar to the one that started this thread. Every-connection-is-busy is pessimal workload for pgbouncer, as it has nothing useful to contribute to setup, just overhead. It still has something to contribute if connections are made and broken too often (pgbench -C type workload), as seems to be the case here. If he can get an application-side pooler (or perhaps just a change in configuration) such that the connections are not made and broken so often, then removing pgbouncer from the loop would probably be a win. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to keep queries low latency as concurrency increases
On Tue, Nov 6, 2012 at 1:31 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Nov 5, 2012 at 2:58 PM, Marko Kreen mark...@gmail.com wrote: On Sun, Nov 4, 2012 at 1:53 AM, Jeff Janes jeff.ja...@gmail.com wrote: On a 4 CPU machine, if I run pgbench -c10 -j10 with dummy queries (like select 1; or set timezone...) against 2 instances of pgbouncer, I get nearly twice the throughput as if I use only one instance. A rather odd workload, maybe, but it does seem to be similar to the one that started this thread. Every-connection-is-busy is pessimal workload for pgbouncer, as it has nothing useful to contribute to setup, just overhead. It still has something to contribute if connections are made and broken too often (pgbench -C type workload), as seems to be the case here. I did not notice -C in your message above. In such case, in a practical, non-pgbench workload, you should move pgbouncer to same machine as app, so any overhead is just CPU, spread over all app instances, and does not include network latency. If he can get an application-side pooler (or perhaps just a change in configuration) such that the connections are not made and broken so often, then removing pgbouncer from the loop would probably be a win. Yes, if app has good pooling, there is less use for pgbouncer. In any case, only long connections should go over network. -- marko -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
