Re: [PERFORM] How to get explain plan to prefer Hash Join
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of atxcanadian Sent: Wednesday, March 11, 2015 1:01 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] How to get explain plan to prefer Hash Join Currently seeing massive increase in performance when optimizer chooses Hash Join over Nested Loops. I achieve this by temporarily setting nested loops off. I'd like to setup some database variables where the optimizer prefers hash joins. Any suggestions? Try making small adjustments to either random_page_cost or cpu_tuple_cost. They can influence the planners choice here. I have solved similar issues in the past by adjusting one or the other. Be aware thought that those changes can have negative effects in other places, so be sure to test. Brad. -- 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] One huge db vs many small dbs
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql- performance-ow...@postgresql.org] On Behalf Of Max Sent: Thursday, December 05, 2013 5:42 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] One huge db vs many small dbs Hello, We are starting a new project to deploy a solution in cloud with the possibility to be used for 2.000+ clients. Each of this clients will use several tables to store their information (our model has about 500+ tables but there's less than 100 core table with heavy use). Also the projected ammout of information per client could be from small (few hundreds tuples/MB) to huge (few millions tuples/GB). One of the many questions we have is about performance of the db if we work with only one (using a ClientID to separete de clients info) or thousands of separate dbs. The management of the dbs is not a huge concert as we have an automated tool. If you are planning on using persisted connections, the large number of DB approach is going to have a significant disadvantage. You cannot pool connections between databases. So if you have 2000 databases, you are going to need a minimum of 2000 connections to service those database (assuming you want to keep at least one active connection open per client at a time). Brad. -- 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] 9.2.3 upgrade reduced pgbench performance by 60%
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql- performance-ow...@postgresql.org] On Behalf Of Josh Berkus Sent: Monday, March 25, 2013 4:34 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] 9.2.3 upgrade reduced pgbench performance by 60% The Linux server is on a cloud and has 4GB RAM and 2 CPUs and the same server is running both master and slave (these are separate in production). If you'd like any more details please ask. Here are the pgbench results: Presumably you created a new cloud server for 9.2, yes? I'd guess that the difference is between the two cloud servers. Try testing, for example, bonnie++ on the two servers. I saw some similar results comparing 9.0 and 9.2 pgbench tests. My tests were on a VM, but on a dedicate host/hardware with no other VM's running on it to minimize variables. I didn't have a lot of time to dig into it, but I do recall seeing more lock contention on updates on the 9.2 instance though. Brad. -- 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] pg_upgrade
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Bruce Momjian Sent: Saturday, December 03, 2011 6:42 PM To: Tory M Blue Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] pg_upgrade Tory M Blue wrote: On Sat, Dec 3, 2011 at 6:04 AM, Bruce Momjian br...@momjian.us wrote: Well, I am not totally clear how you are moving things around, but I do know pg_upgrade isn't happy to have the old and new cluster be very different. What I think is happening is that you didn't properly move the tablespace in the old cluster. ?We don't give you a very easy way to do that. ?You need to not only move the directory, but you need to update the symlinks in data/pg_tblspc/, and update the pg_tablespace system table. ?Did you do all of that? ?Does the 8.4 server see the tablespace properly after the move, but before pg_upgrade? Simple answer is umm no.. The no is an answer to which question? http://www.postgresql.org/docs/current/static/pgupgrade.html; is obviously lacking than :) S I can take what you have told me and see if I can't attempt to make those things happen and try again. Makes sense, but boy that's a large piece of info missing in the document! You mean moving tablespaces? That isn't something pg_upgrade deals with. If we need docs to move tablespaces, it is a missing piece of our main docs, not something pg_upgrade would ever mention. If I'm reading the issue correctly, and pg_upgrade gets part way through an upgrade then fails if it hits a tablespace - it seems to me like the pg_upgrade should check for such a condition at the initial validation stage not proceed if found. Brad. -- 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] pg_upgrade
-Original Message- From: Bruce Momjian [mailto:br...@momjian.us] Sent: Monday, December 05, 2011 10:24 AM To: Nicholson, Brad (Toronto, ON, CA) Cc: Tory M Blue; pgsql-performance@postgresql.org; Magnus Hagander Subject: Re: [PERFORM] pg_upgrade Nicholson, Brad (Toronto, ON, CA) wrote: You mean moving tablespaces? That isn't something pg_upgrade deals with. If we need docs to move tablespaces, it is a missing piece of our main docs, not something pg_upgrade would ever mention. If I'm reading the issue correctly, and pg_upgrade gets part way through an upgrade then fails if it hits a tablespace - it seems to me like the pg_upgrade should check for such a condition at the initial validation stage not proceed if found. Checking for all such cases would make pg_upgrade huge and unusable. If you messed up your configuration, pg_upgrade can't check for every such case. There are thosands of ways people can mess up their configuration. Based on the OP this does not seem like a messed up configuration. It sounds like the OP used a fully supported core feature of Postgres (tablespaces) and pg_upgrade failed as a result. I think having our upgrade utility fail under such circumstances is a bad thing. Brad. -- 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] backups blocking everything
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Samuel Gendler Sent: Thursday, October 27, 2011 12:47 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] backups blocking everything I've got a large mixed-used database, with the data warehouse side of things consisting of several tables at hundreds of millions of rows, plus a number of tables with tens of millions. There is partitioning, but as the volume of data has risen, individual partitions have gotten quite large. Hardware is 2x4 core 2.0Ghz Xeon processors, 176GB of RAM, 4 drives in raid 10 for WAL logs and 16 or 20 spindles for data, also in RAID 10. Total database size is currently 399GB - via pg_database_size(). It's also worth noting that we switched from 8.4 to 9.0.4 only about a month ago, and we were not seeing this problem on 8.4.x. The database is growing, but not at some kind of exponential rate. full backup, compressed, on the old hardware was 6.3GB and took about 1:45:00 to be written. Recent backups are 8.3GB and taking 3 or 4 hours. We were not seeing al queries stall out during the backups on 8.4, so far as I am aware. The time it takes for pg_dump to run has grown from 1 hour to 3 and even 4 hours over the last 6 months, with more than half of that increase occurring since we upgrade to 9.0.x. In the last several weeks (possibly since the upgrade to 9.0.4), we are seeing all connections getting used up (our main apps use connection pools, but monitoring and some utilities are making direct connections for each query, and some of them don't check for the prior query to complete before sending another, which slowly eats up available connections). Even the connection pool apps cease functioning during the backup, however, as all of the connections wind up in parse waiting state. I also see lots of sockets in close wait state for what seems to be an indefinite period while the backup is running and all connections are used up. I assume all of this is the result of pg_dump starting a transaction or otherwise blocking other access. I can get everything using a pool, that's not a huge problem to solve, but that won't fix the fundamental problem of no queries being able to finish while the backup is happening. What is the I/O utilization like during the dump? I've seen this situation in the past and it was caused be excessively bloated tables causing I/O starvation while they are getting dumped. Brad. -- 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] Need to tune for Heavy Write
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Thursday, August 04, 2011 4:46 AM To: Willy-Bas Loos Cc: Adarsh Sharma; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Need to tune for Heavy Write Moving the pg_xlog to a different directory only helps when that directory is on a different harddisk (or whatever I/O device). Not entirely true. By simply being on a different mounted file system this moves the fsync calls on the pg_xlog directories off of the same file system as the main data store. Previous testing has shown improvements in performance from just using a different file system. Is this still the case for xfs or ext4 where fsync is properly flushing only the correct blocks to disk, or was this referring to the good old ext3 flush everything on fysnc issue? Brad. -- 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] Suspected Postgres Datacorruption
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Thursday, August 04, 2011 5:22 PM To: Sumeet Jauhar Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Suspected Postgres Datacorruption I ideally want to push to a higher version of Postgres . If I can prove that there will be significant performance benefits and that crashes won't occur then I will be able to present a strong case . Hehe. It would be hard to NOT get significant performance improvements moving from 7.4 to 9.0. Heck our load on our production servers went from 12 to 3 or so when we went from 8.1 to 8.3. Saved us a ton on what we would have had to spend to keep 8.1 happy. Install a test version of 9.0 on a laptop, point your test servers at it, and watch it outrun your production database for 90% of everything you do. At a previous engagement, when we moved from 7.4 to 8.1 we saw a huge drop in transaction times. I don't remember the numbers but it was substantial. We also suffered very badly from checkpoint problems with 7.4, and we were able to tune them out in 8.1. When we went from 8.1 to 8.3, there wasn't an improvement in response times but we were able to deliver the same level of performance using a fraction of the I/O (due to HOT, autovacuum improvements the checkpoint smoothing stuff). We also ran 7.4 for quite a while (on reliable hardware), and never had any corruption problems except for some index corruption issues - but that bug was pretty obscure and was fixed in 7.4 Brad. -- 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] Suspected Postgres Datacorruption
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Nicholson, Brad (Toronto, ON, CA) Sent: Thursday, August 04, 2011 5:47 PM To: Scott Marlowe; Sumeet Jauhar Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Suspected Postgres Datacorruption We also ran 7.4 for quite a while (on reliable hardware), and never had any corruption problems except for some index corruption issues - but that bug was pretty obscure and was fixed in 7.4 By the way - to the original person asking about 7.4 do not view this as an endorsement. I would not trust my data to 7.4 any longer. Brad. -- 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 random io test with 2 SSD Kingston V+100 500GB in (software) Raid1
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Laurent Laborde Sent: Tuesday, April 19, 2011 8:37 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] postgresql random io test with 2 SSD Kingston V+100 500GB in (software) Raid1 If we use them (unlikely), recovery in case of power outage isn't a problem, as we will use it on slave database (using Slony-I) that can be created/destroyed at will. And, anyway, our slave have fsync=off so the battery won't change anything in case of power outage :) Are these on the same UPS? If so, you have a failure case that could cause you to lose everything. Brad. -- 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] buffercache/bgwriter
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of t...@fuzzy.cz Sent: Wednesday, March 23, 2011 10:42 AM To: Uwe Bartels Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] buffercache/bgwriter Hi, I have very bad bgwriter statistics on a server which runs since many weeks and it is still the same after a recent restart. There are roughly 50% of buffers written by the backend processes and the rest by checkpoints. The statistics below are from a server with 140GB RAM, 32GB shared_buffers and a runtime of one hour. As you can see in the pg_buffercache view that there are most buffers without usagecount - so they are as free or even virgen as they can be. At the same time I have 53% percent of the dirty buffers written by the backend process. There are some nice old threads dealing with this - see for example http://postgresql.1045698.n5.nabble.com/Bgwriter-and-pg-stat-bgwriter- buffers-clean-aspects-td2071472.html http://postgresql.1045698.n5.nabble.com/tuning-bgwriter-in-8-4-2- td1926854.html and there even some nice external links to more detailed explanation http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm The interesting question here is - with 3 million unallocated buffers, why is the DB evicting buffers (buffers_backend column) instead of allocating the unallocated buffers? Brad. -- 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] Help: massive parallel update to the same table
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Red Maple Sent: Friday, March 18, 2011 9:05 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Help: massive parallel update to the same table Hi all, Our system has a postgres database that has a table for statistic which is updated every hour by about 10K clients. Each client only make update to its own row in the table. So far I am only seeing one core out of eight cores on my server being active which tells me that the update is being done serial instead of being parallel. Do you know if there is a way for me to make these independent updates happen in parallel? Thank you, your help is very much appreciated! If they are all happening on one core, you are probably using one DB connection to do the updates. To split them across multiple cores, you need to use multiple DB connections. Be careful if/when you restructure things to filter these requests into a reasonable number of backend DB connections - turning a huge number of clients loose against a DB is not going end well. Brad. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
