[PERFORM] Query slow after analyse on postgresql 8.2
Hi, I am new to this list so please forgive me if it not fits the standards. I have the following query that I run agains postgresql 8.2: select distinct m.koid, m.name, m.farbe, m.aktennummer, m.durchgefuehrt_von, m.durchgefuehrt_bis, rf.bezeichnung as rf_bezeichnung, mt.bezeichnung as mt_bezeichnung, wl_farben.wert, v_adr.text_lkr, v_adr.text_gemeinde from (( boden.massnahmeobjekt m left join boden.massnahmengruppe mg on m.massnahmengruppe_koid=mg.koid) left join boden.th_referate rf on mg.angelegt_von_referat=rf.th_id) left join boden.th_massnahmentyp mt on m.massnahmentyp=mt.th_id) left join boden.wl_farben wl_farben on m.farbe=wl_farben.wl_id) left join boden_views.v_z_lc_flst v_flst on m.koid=v_flst.koid) left join boden_views.v_z_lc_adresse v_adr on m.koid=v_adr.koid) where m.aktennummer ~* 'M\\-2009\\-1' order by koid asc limit 100 - It takes a around 10 secs to complete with the following plan: Limit (cost=128494.42..128494.69 rows=9 width=1212) (actual time=12463.236..12464.675 rows=100 loops=1) - Unique (cost=128494.42..128494.69 rows=9 width=1212) (actual time=12463.206..12464.183 rows=100 loops=1) - Sort (cost=128494.42..128494.44 rows=9 width=1212) (actual time=12463.178..12463.490 rows=123 loops=1) Sort Key: m.koid, m.name, m.farbe, m.aktennummer, m.durchgefuehrt_von, m.durchgefuehrt_bis, rf.bezeichnung, mt.bezeichnung, wl_farben.wert, t2.bezeichnung, t3.bezeichnung - Hash Left Join (cost=119377.13..128494.28 rows=9 width=1212) (actual time=10475.870..12416.672 rows=3922 loops=1) Hash Cond: (m.koid = lc.koid) - Nested Loop Left Join (cost=26.59..5848.52 rows=3 width=1148) (actual time=1.697..1711.535 rows=3813 loops=1) - Nested Loop Left Join (cost=26.59..5847.53 rows=3 width=1156) (actual time=1.664..1632.871 rows=3813 loops=1) - Nested Loop Left Join (cost=26.59..5846.68 rows=3 width=1152) (actual time=1.617..1538.819 rows=3813 loops=1) - Nested Loop Left Join (cost=0.00..3283.05 rows=1 width=1148) (actual time=1.267..1352.254 rows=3694 loops=1) - Nested Loop Left Join (cost=0.00..3282.77 rows=1 width=1120) (actual time=1.230..1232.264 rows=3694 loops=1) - Nested Loop Left Join (cost=0.00..3274.48 rows=1 width=1124) (actual time=1.089..1143.501 rows=3694 loops=1) Join Filter: (m.massnahmentyp = mt.th_id) - Nested Loop Left Join (cost=0.00..3273.03 rows=1 width=1100) (actual time=0.999..671.405 rows=3694 loops=1) Join Filter: (m.farbe = wl_farben.wl_id) - Seq Scan on massnahmeobjekt m (cost=0.00..3271.88 rows=1 width=1068) (actual time=0.909..425.324 rows=3694 loops=1) Filter: ((aktennummer)::text ~* 'M\\-2009\\-1'::text) - Seq Scan on wl_farben (cost=0.00..1.07 rows=7 width=36) (actual time=0.005..0.024 rows=7 loops=3694) - Seq Scan on th_massnahmentyp mt (cost=0.00..1.20 rows=20 width=40) (actual time=0.003..0.060 rows=20 loops=3694) - Index Scan using idx_massnahmengruppe_koid on massnahmengruppe mg (cost=0.00..8.28 rows=1 width=12) (actual time=0.009..0.012 rows=1 loops=3694) -- But when I run analyse the same query runs for hours. (See eyplain output below) Limit (cost=111795.21..111795.24 rows=1 width=149) (actual time=10954094.322..10954095.612 rows=100 loops=1) - Unique (cost=111795.21..111795.24 rows=1 width=149) (actual time=10954094.316..10954095.165 rows=100 loops=1) - Sort (cost=111795.21..111795.22 rows=1 width=149) (actual time=10954094.310..10954094.600 rows=123 loops=1) Sort Key: m.koid, m.name, m.farbe, m.aktennummer, m.durchgefuehrt_von, m.durchgefuehrt_bis, rf.bezeichnung, mt.bezeichnung, wl_farben.wert, t2.bezeichnung, t3.bezeichnung - Nested Loop Left Join (cost=101312.40..111795.20 rows=1 width=149) (actual time=7983.197..10954019.963 rows=3922 loops=1) Join Filter: (m.koid = lc.koid) - Nested Loop Left Join (cost=0.00..3291.97 rows=1 width=119) (actual time=1.083..2115.512 rows=3813 loops=1) - Nested Loop Left Join (cost=0.00..3291.69 rows=1 width=115) (actual time=0.980..2018.008 rows=3813 loops=1) - Nested Loop Left Join (cost=0.00..3283.41 rows=1 width=119) (actual time=0.868..1874.309
Re: [PERFORM] Parallel queries for a web-application |performance testing
Balkrishna Sharma b...@hotmail.com writes: I will have a web application having postgres 8.4+ as backend. At any given time, there will be max of 1000 parallel web-users interacting with the database (read/write) I wish to do performance testing of 1000 simultaneous read/write to the database. See about tsung, and either benckmarck only the PostgreSQL side of things, or at the HTTP side of things directly : that will run your application code against PostgreSQL. http://tsung.erlang-projects.org/ And as Kevin said, consider using a connection pool, such as pgbouncer. Once you have setup the benchmark with Tsung, adding pgbouncer and comparing the results will be easy. Regards, -- dim -- 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] Parallel queries for a web-application |performance testing
On Wed, 16 Jun 2010, Balkrishna Sharma wrote: Hello,I will have a web application having postgres 8.4+ as backend. At any given time, there will be max of 1000 parallel web-users interacting with the database (read/write)I wish to do performance testing of 1000 simultaneous read/write to the database. When you set up a server that has high throughput requirements, the last thing you want to do is use it in a manner that cripples its throughput. Don't try and have 1000 parallel Postgres backends - it will process those queries slower than the optimal setup. You should aim to have approximately ((2 * cpu core count) + effective spindle count) number of backends, as that is the point at which throughput is the greatest. You can use pgbouncer to achieve this. I can do a simple unix script on the postgres server and have parallel updates fired for example with an ampersand at the end. Example: echo '\timing \\update DAPP.emp_data set f1 = 123where emp_id =0;' | psql test1 postgres|grep Time:|cut -d' ' -f2- /home/user/Documents/temp/logs/$NUM.txt pid1=$! echo '\timing \\update DAPP.emp_data set f1 = 123 where emp_id =2;' | psql test1 postgres|grep Time:|cut -d' ' -f2- /home/user/Documents/temp/logs/$NUM.txt pid2=$! echo '\timing \\update DAPP.emp_data set f1 = 123 where emp_id =4;' | psql test1 postgres|grep Time:|cut -d' ' -f2- /home/user/Documents/temp/logs/$NUM.txt pid3=$! . Don't do that. The overhead of starting up an echo, a psql, and a grep will limit the rate at which these queries can be fired at Postgres, and consume quite a lot of CPU. Use a proper benchmarking tool, possibly on a different server. Also, you should be using a different username to postgres - that one is kind of reserved for superuser operations. Matthew -- People who love sausages, respect the law, and work with IT standards shouldn't watch any of them being made. -- Peter Gutmann -- 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] Parallel queries for a web-application |performance testing
When you set up a server that has high throughput requirements, the last thing you want to do is use it in a manner that cripples its throughput. Don't try and have 1000 parallel Postgres backends - it will process those queries slower than the optimal setup. You should aim to have approximately ((2 * cpu core count) + effective spindle count) number of backends, as that is the point at which throughput is the greatest. You can use pgbouncer to achieve this. The same is true of a web server : 1000 active php interpreters (each eating several megabytes or more) are not ideal for performance ! For php, I like lighttpd with php-fastcgi : the webserver proxies requests to a small pool of php processes, which are only busy while generating the page. Once the page is generated the webserver handles all (slow) IO to the client. An interesting side effect is that the number of database connections is limited to the number of PHP processes in the pool, so you don't even need a postgres connection pooler (unless you have lots of php boxes)... -- 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] Parallel queries for a web-application |performance testing
Pierre C li...@peufeu.com writes: The same is true of a web server : 1000 active php interpreters (each eating several megabytes or more) are not ideal for performance ! For php, I like lighttpd with php-fastcgi : the webserver proxies requests to a small pool of php processes, which are only busy while generating the page. Once the page is generated the webserver handles all (slow) IO to the client. I use haproxy for that, it handles requests queues very effectively. -- dim -- 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] Query slow after analyse on postgresql 8.2
Kaufhold, Christian (LFD) christian.kaufh...@blfd.bayern.de writes: I have the following query that I run agains postgresql 8.2: ... But when I run analyse the same query runs for hours. Seems like the core of the problem is here: - Seq Scan on massnahmeobjekt m (cost=0.00..3271.88 rows=1 width=94) (actual time=0.387..577.771 rows=3694 loops=1) Filter: ((aktennummer)::text ~* 'M\\-2009\\-1'::text) If that rowcount estimate weren't off by three orders of magnitude you probably would be getting a more appropriate plan. The first thing you could try is increasing the statistics target for aktennummer. Also, if you're running in a non-C locale and this is 8.2.5 or older, try a more recent 8.2.x. Updating to 8.3 or 8.4 might help even more. regards, tom lane -- 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] Query slow after analyse on postgresql 8.2
Thanks Tom, alter table boden.massnahmeobjekt alter column aktennummer set statistics 1000; fixed it. Regards Christian -Ursprüngliche Nachricht- Von: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] Im Auftrag von Tom Lane Gesendet: Donnerstag, 17. Juni 2010 16:59 An: Kaufhold, Christian (LFD) Cc: pgsql-performance@postgresql.org Betreff: Re: [PERFORM] Query slow after analyse on postgresql 8.2 Kaufhold, Christian (LFD) christian.kaufh...@blfd.bayern.de writes: I have the following query that I run agains postgresql 8.2: ... But when I run analyse the same query runs for hours. Seems like the core of the problem is here: - Seq Scan on massnahmeobjekt m (cost=0.00..3271.88 rows=1 width=94) (actual time=0.387..577.771 rows=3694 loops=1) Filter: ((aktennummer)::text ~* 'M\\-2009\\-1'::text) If that rowcount estimate weren't off by three orders of magnitude you probably would be getting a more appropriate plan. The first thing you could try is increasing the statistics target for aktennummer. Also, if you're running in a non-C locale and this is 8.2.5 or older, try a more recent 8.2.x. Updating to 8.3 or 8.4 might help even more. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] PostgreSQL as a local in-memory cache
All, So, I've been discussing this because using PostgreSQL on the caching layer has become more common that I think most people realize. Jonathan is one of 4 companies I know of who are doing this, and with the growth of Hadoop and other large-scale data-processing technologies, I think demand will increase. Especially as, in repeated tests, PostgreSQL with persistence turned off is just as fast as the fastest nondurable NoSQL database. And it has a LOT more features. Now, while fsync=off and tmpfs for WAL more-or-less eliminate the IO for durability, they don't eliminate the CPU time. Which means that a caching version of PostgreSQL could be even faster. To do that, we'd need to: a) Eliminate WAL logging entirely b) Eliminate checkpointing c) Turn off the background writer d) Have PostgreSQL refuse to restart after a crash and instead call an exteral script (for reprovisioning) Of the three above, (a) is the most difficult codewise. (b)(c) and (d) should be relatively straightforwards, although I believe that we now have the bgwriter doing some other essential work besides syncing buffers. There's also a narrower use-case in eliminating (a), since a non-fsync'd server which was recording WAL could be used as part of a replication chain. This isn't on hackers because I'm not ready to start working on a patch, but I'd like some feedback on the complexities of doing (b) and (c) as well as how many people could use a non-persistant, in-memory postgres. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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] PostgreSQL as a local in-memory cache
Especially as, in repeated tests, PostgreSQL with persistence turned off is just as fast as the fastest nondurable NoSQL database. And it has a LOT more features. An option to completely disable WAL for such use cases would make it a lot faster, especially in the case of heavy concurrent writes. Now, while fsync=off and tmpfs for WAL more-or-less eliminate the IO for durability, they don't eliminate the CPU time. Actually the WAL overhead is some CPU and lots of locking. Which means that a caching version of PostgreSQL could be even faster. To do that, we'd need to: a) Eliminate WAL logging entirely b) Eliminate checkpointing c) Turn off the background writer d) Have PostgreSQL refuse to restart after a crash and instead call an exteral script (for reprovisioning) Of the three above, (a) is the most difficult codewise. Actually, it's pretty easy, look in xlog.c -- 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] PostgreSQL as a local in-memory cache
Hi, Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely b) Eliminate checkpointing c) Turn off the background writer d) Have PostgreSQL refuse to restart after a crash and instead call an exteral script (for reprovisioning) Well I guess I'd prefer a per-transaction setting, allowing to bypass WAL logging and checkpointing. Forcing the backend to care itself for writing the data I'm not sure is a good thing, but if you say so. Then you could have the GUC set for a whole cluster, only a database etc. We already have synchronous_commit to trade durability against performances, we could maybe support protect_data = off too. The d) point I'm not sure still applies if you have per transaction setting, which I think makes the most sense. The data you choose not to protect is missing at restart, just add some way to register a hook there. We already have one (shared_preload_libraries) but it requires coding in C. Calling a user function at the end of recovery and before accepting connection would be good I think. A user function (per database) is better than a script because if you want to run it before accepting connections and still cause changes in the database… Regards, -- dim -- 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] PostgreSQL as a local in-memory cache
Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely b) Eliminate checkpointing c) Turn off the background writer d) Have PostgreSQL refuse to restart after a crash and instead call an exteral script (for reprovisioning) Well I guess I'd prefer a per-transaction setting, allowing to bypass WAL logging and checkpointing. Not going to happen; this is all or nothing. Forcing the backend to care itself for writing the data I'm not sure is a good thing, but if you say so. Yeah, I think proposal (c) is likely to be a net loss. (a) and (d) are probably simple, if by reprovisioning you mean rm -rf $PGDATA; initdb. Point (b) will be a bit trickier because there are various housekeeping activities tied into checkpoints. I think you can't actually remove checkpoints altogether, just skip the flush-dirty-pages part. regards, tom lane -- 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] PostgreSQL as a local in-memory cache
Josh Berkus wrote: a) Eliminate WAL logging entirely c) Turn off the background writer Note that if you turn off full_page_writes and set bgwriter_lru_maxpages=0, you'd get a substantial move in both these directions without touching any code. Would help prove those as useful directions to move toward or not. The difference in WAL writes just after a checkpoint in particular, due to the full_page_writes behavior, is a significant portion of total WAL activity on most systems. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] PostgreSQL as a local in-memory cache
Well I guess I'd prefer a per-transaction setting, allowing to bypass WAL logging and checkpointing. Forcing the backend to care itself for writing the data I'm not sure is a good thing, but if you say so. Well if the transaction touches a system catalog it better be WAL-logged... A per-table (or per-index) setting makes more sense IMHO. For instance on recovery, truncate this table (this was mentioned before). Another option would be make the table data safe, but on recovery, destroy and rebuild this index : because on a not so large, often updated table, with often updated indexes, it may not take long to rebuild the indexes, but all those wal-logged index updates do add some overhead. -- 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] requested shared memory size overflows size_t
Tom Wilcox wrote: Any suggestions for good monitoring software for linux? By monitoring, do you mean for alerting purposes or for graphing purposes? Nagios is the only reasonable choice for the former, while doing at best a mediocre job at the latter. For the later, I've found that Munin does a good job of monitoring Linux and PostgreSQL in its out of the box configuration, in terms of providing useful activity graphs. And you can get it to play nice with Nagios. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Add slowdown after conversion to UTF8
Hello there, I've searched the web and can find very little on this issue, so I was hoping those on this list would be able to shed some light on it. Performance has dropped through the floor after converting my db from ASCI to UTF8. Is this normal behavior on 8.4.x? I'm mystified as to the problem. Thanks for any input you can provide. -- Brant Fitzsimmons Everything should be made as simple as possible, but not simpler. -- Albert Einstein
Re: [PERFORM] PostgreSQL as a local in-memory cache
Well I guess I'd prefer a per-transaction setting, allowing to bypass WAL logging and checkpointing. Not even conceiveable. For this to work, we're talking about the whole database installation. This is only a set of settings for a database *server* which is considered disposable and replaceable, where if it shuts down unexpectedly, you throw it away and replace it. Forcing the backend to care itself for writing the data I'm not sure is a good thing, but if you say so. Oh, yeah, I guess we'd only be turning off the LRU cache operations of the background writer. Same with checkpoints. Copying between shared_buffers and the LRU cache would still happen. Calling a user function at the end of recovery and before accepting connection would be good I think. A user function (per database) is better than a script because if you want to run it before accepting connections and still cause changes in the database… Hmmm, you're not quite following my idea. There is no recovery. If the database shuts down unexpectedly, it's toast and you replace it from another copy somewhere else. (a) and (d) are probably simple, if by reprovisioning you mean rm -rf $PGDATA; initdb. Exactly. Followed by scp database_image. Or heck, just replacing the whole VM. Point (b) will be a bit trickier because there are various housekeeping activities tied into checkpoints. I think you can't actually remove checkpoints altogether, just skip the flush-dirty-pages part. Yes, and we'd want to flush dirty pages on an actual shutdown command. We do want to be able to shut down the DB on purpose. Well if the transaction touches a system catalog it better be WAL-logged... Given the above, why? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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] Add slowdown after conversion to UTF8
Brant Fitzsimmons brant.fitzsimm...@gmail.com writes: I've searched the web and can find very little on this issue, so I was hoping those on this list would be able to shed some light on it. Performance has dropped through the floor after converting my db from ASCI to UTF8. Is this normal behavior on 8.4.x? Well, with no specifics on performance of *what*, it's hard to say. There are certain operations that could be quite a bit slower, yes. I haven't heard of too many people noticing a problem though. It's probably worth noting that locale could be at least as much of a factor as encoding ... but perhaps I'm jumping to conclusions about what your slow operations are. regards, tom lane -- 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] PostgreSQL as a local in-memory cache
Josh Berkus j...@agliodbs.com writes: (a) and (d) are probably simple, if by reprovisioning you mean rm -rf $PGDATA; initdb. Exactly. Followed by scp database_image. Or heck, just replacing the whole VM. Right, that would work. I don't think you really need to implement that inside Postgres. I would envision having the startup script do it, ie rm -rf $PGDATA cp -pr prepared-database-image $PGDATA # this loop exits when postmaster exits normally while ! postmaster ... do rm -rf $PGDATA cp -pr prepared-database-image $PGDATA done Then all you need is a tweak to make the postmaster exit(1) after a crash instead of trying to launch recovery. regards, tom lane -- 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] requested shared memory size overflows size_t
On 17/06/2010 22:41, Greg Smith wrote: Tom Wilcox wrote: Any suggestions for good monitoring software for linux? By monitoring, do you mean for alerting purposes or for graphing purposes? Nagios is the only reasonable choice for the former, while doing at best a mediocre job at the latter. For the later, I've found that Munin does a good job of monitoring Linux and PostgreSQL in its out of the box configuration, in terms of providing useful activity graphs. And you can get it to play nice with Nagios. Thanks Greg. Ill check Munin and Nagios out. It is very much for graphing purposes. I would like to be able to perform objective, platform-independent style performance comparisons. Cheers, Tom -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] wal_synch_method = open_sync safe on RHEL 5.5?
Some more on the RHEL 5.5 system I'm helping to setup. Some benchmarking using pgbench appeared to suggest that wal_sync_method=open_sync was a little faster than fdatasync [1]. Now I recall some discussion about this enabling direct io and the general flakiness of this on Linux, so is the option regarded as safe? [1] The workout: $ pgbench -i -s 1000 bench $ pgbench -c [1,2,4,8,32,64,128] -t 1 Performance peaked around 2500 tps @32 clients using open_sync and 2200 with fdatasync. However the disk arrays are on a SAN and I suspect that when testing with fdatasync later in the day there may have been workload 'leakage' from other hosts hitting the SAN.
Re: [PERFORM] wal_synch_method = open_sync safe on RHEL 5.5?
Mark Kirkwood wrote: Now I recall some discussion about this enabling direct io and the general flakiness of this on Linux, so is the option regarded as safe? No one has ever refuted the claims in http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php that it can be unsafe under a heavy enough level of mixed load on RHEL5. Given the performance benefits are marginal on ext3, I haven't ever considered it worth the risk. (I've seen much larger gains on Linux+Veritas VxFS). From what I've seen, recent Linux kernel work has reinforced that the old O_SYNC implementation was full of bugs now that more work is being done to improve that area. My suspicion (based on no particular data, just what I've seen it tested with) is that it only really worked before in the very specific way that Oracle does O_SYNC writes, which is different from what PostgreSQL does. P.S. Be wary of expecting pgbench to give you useful numbers on a single run. For the default write-heavy test, I recommend three runs of 10 minutes each (-T 600 on recent PostgreSQL versions) before I trust any results it gives. You can get useful data from the select-only test in only a few seconds, but not the one that writes a bunch. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] wal_synch_method = open_sync safe on RHEL 5.5?
The conclusion I read was that Linux O_SYNC behaves like O_DSYNC on other systems. For WAL, this seems satisfactory? Personally, I use fdatasync(). I wasn't able to measure a reliable difference for my far more smaller databases, and fdatasync() seems reliable and fast enough, that fighting with O_SYNC doesn't seem to be worth it. Also, technically speaking, fdatasync() appeals more to me, as it allows the system to buffer while it can, and the application to instruct it across what boundaries it should not buffer. O_SYNC / O_DSYNC seem to imply a requirement that it does a synch on every block. My gut tells me that fdatasync() gives the operating system more opportunities to optimize (whether it does or not is a different issue :-) ). Cheers, mark On 06/17/2010 11:29 PM, Greg Smith wrote: Mark Kirkwood wrote: Now I recall some discussion about this enabling direct io and the general flakiness of this on Linux, so is the option regarded as safe? No one has ever refuted the claims in http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php that it can be unsafe under a heavy enough level of mixed load on RHEL5. Given the performance benefits are marginal on ext3, I haven't ever considered it worth the risk. (I've seen much larger gains on Linux+Veritas VxFS). From what I've seen, recent Linux kernel work has reinforced that the old O_SYNC implementation was full of bugs now that more work is being done to improve that area. My suspicion (based on no particular data, just what I've seen it tested with) is that it only really worked before in the very specific way that Oracle does O_SYNC writes, which is different from what PostgreSQL does. P.S. Be wary of expecting pgbench to give you useful numbers on a single run. For the default write-heavy test, I recommend three runs of 10 minutes each (-T 600 on recent PostgreSQL versions) before I trust any results it gives. You can get useful data from the select-only test in only a few seconds, but not the one that writes a bunch. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.comwww.2ndQuadrant.us -- Mark Mielkem...@mielke.cc
Re: [PERFORM] Add slowdown after conversion to UTF8
On tor, 2010-06-17 at 18:28 -0400, Brant Fitzsimmons wrote: Performance has dropped through the floor after converting my db from ASCI to UTF8. Converting from ASCII to UTF8 is a noop. If you did some configuration changes, you need to tell us which. -- 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] wal_synch_method = open_sync safe on RHEL 5.5?
On 18/06/10 15:29, Greg Smith wrote: P.S. Be wary of expecting pgbench to give you useful numbers on a single run. For the default write-heavy test, I recommend three runs of 10 minutes each (-T 600 on recent PostgreSQL versions) before I trust any results it gives. You can get useful data from the select-only test in only a few seconds, but not the one that writes a bunch. Yeah, I did several runs of each, and a couple with -c 128 and -t 10 to give the setup a good workout (also 2000-2400 tps, nice to see a well behaved SAN). Cheers Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
