Re: [PERFORM] Architecting a database
On 26/06/2010 3:36 AM, t...@exquisiteimages.com wrote: I am in the process of moving a system that has been built around FoxPro tables for the last 18 years into a PostgreSQL based system. Over time I came up with decent strategies for making the FoxPro tables work well with the workload that was placed on them, but we are getting to the point that the locking mechanisms are causing problems when some of the more used tables are being written to. With the FoxPro tables I had one directory that contained the tables that had global data that was common to all clients. Things like documents that had been received and logged, checks that had been cut, etc. Then each client had his own directory which housed tables that had information relating to that specific client. I am wondering how I should architect this in PostgreSQL. Should I follow a similar strategy and have a separate database for each client and one database that contains the global data? No - use separate schema within a single database. You can't do inter-database queries in PostgreSQL, and most things you're used to using different "databases" for are best done with separate schema (namespaces) within one database. A schema is almost a logical directory, really. With the dBase and ISAM tables I have a good idea of how to handle them since I have been working with them since dBASE originally came out. With the PostgreSQL type tables I am not so certain how the data is arranged within the one file. Does having the data all in one database allow PostgreSQL to better utilize indexes and caches or does having a number of smaller databases provide performance increases? It doesn't really make much difference, and for easier management a single database for a single app is very much the way to go. In case it is important, there are 2000 clients involved, so that would be 2000 databases if I followed my current FoxPro related structure. Nonono! Definitely use different schema if you need to separate things this way. -- Craig Ringer -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear Greg/Kevin/List , Many thanks for the comments regarding the params, I am however able to change an experiment on production in a certain time window , when that arrives i shall post my observations. Rajesh Kumar Mallah. Tradeindia.com - India's Largest B2B eMarketPlace.
Re: [PERFORM] Architecting a database
Interesting point you made about the read to write ratio of 1 to 15. How frequently will you be adding new entities or in the case of storing the customers in one database table, how frequently will you be adding new objects of a certain entity type. How many entity types do you foresee existing? i.e. "Customer?" Will Customer have subtypes or is a Customer the single entity in the database? How frequent and for how long are write operations and are they heavily transaction based? Will you need to support complex reporting in the future? What is the max number of customers? And how much data (approximate) will a single customer record consume in bytes? At what rate does it grow? (in bytes) Will your system need to support any type of complex reporting in the future (despite it being write intensive)? I'd take a look at memcached, plproxy, pgpool, and some of the other cool stuff in the postgresql community. At a minimum, it might help you architect the system in such a manner that you don't box yourself in. Last, KV stores for heavy write intensive operations in distributed environments are certainly interesting - a hybrid solution could work. Sounds like a fun project! Bryan On Fri, Jun 25, 2010 at 7:02 PM, Greg Smith wrote: > Kevin Grittner wrote: > >> A schema is a logical separation within a database. Table >> client1.account is a different table from client2.account. While a >> user can be limited to tables within a single schema, a user with >> rights to all the tables can join between them as needed. You could >> put common reference data in a public schema which all users could >> access in addition to their private schemas >> > > My guess would be that this app will end up being best split by schema. I > wonder whether it *also* needs to be split by database, too. 2000 clusters > is clearly a nightmare, and putting all the client data into one big table > has both performance and security issues; that leaves database and schema as > possible splits. However, having 2000 databases in a cluster is probably > too many; having 2000 schemas in a database might also be too many. There > are downsides to expanding either of those to such a high quantity. > > In order to keep both those in the domain where they perform well and are > managable, it may be that what's needed is, say, 50 databases with 40 > schemas each, rather than 2000 of either. Hard to say the ideal ratio. > However, I think that at the application design level, it would be wise to > consider each client as having a database+schema pair unique to them, and > with the assumption some shared data may need to be replicated to all the > databases in the cluster. Then it's possible to shift the trade-off around > as needed once the app is built. Building that level of flexibility in > shouldn't be too hard if it's in the design from day one, but it would be > painful bit of refactoring to do later. Once there's a prototype, then some > benchmark work running that app could be done to figure out the correct > ratio between the two. It might even make sense to consider full > scalability from day one and make the unique client connection info > host:port:database:schema. > > P.S. Very refreshing to get asked about this before rather than after a > giant app that doesn't perform well is deployed. > > -- > 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] WAL+Os on a single disk
On Thu, Jun 24, 2010 at 10:55 AM, Anj Adu wrote: > What would you recommend to do a quick test for this? (i.e WAL on > internal disk vs WALon the 12 disk raid array )? Maybe just pgbench? http://archives.postgresql.org/pgsql-performance/2010-06/msg00223.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Architecting a database
Kevin Grittner wrote: A schema is a logical separation within a database. Table client1.account is a different table from client2.account. While a user can be limited to tables within a single schema, a user with rights to all the tables can join between them as needed. You could put common reference data in a public schema which all users could access in addition to their private schemas My guess would be that this app will end up being best split by schema. I wonder whether it *also* needs to be split by database, too. 2000 clusters is clearly a nightmare, and putting all the client data into one big table has both performance and security issues; that leaves database and schema as possible splits. However, having 2000 databases in a cluster is probably too many; having 2000 schemas in a database might also be too many. There are downsides to expanding either of those to such a high quantity. In order to keep both those in the domain where they perform well and are managable, it may be that what's needed is, say, 50 databases with 40 schemas each, rather than 2000 of either. Hard to say the ideal ratio. However, I think that at the application design level, it would be wise to consider each client as having a database+schema pair unique to them, and with the assumption some shared data may need to be replicated to all the databases in the cluster. Then it's possible to shift the trade-off around as needed once the app is built. Building that level of flexibility in shouldn't be too hard if it's in the design from day one, but it would be painful bit of refactoring to do later. Once there's a prototype, then some benchmark work running that app could be done to figure out the correct ratio between the two. It might even make sense to consider full scalability from day one and make the unique client connection info host:port:database:schema. P.S. Very refreshing to get asked about this before rather than after a giant app that doesn't perform well is deployed. -- 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] Architecting a database
On 6/25/10 3:28 PM, Kevin Grittner wrote: wrote: With the PostgreSQL type tables I am not so certain how the data is arranged within the one file. Does having the data all in one database allow PostgreSQL to better utilize indexes and caches or does having a number of smaller databases provide performance increases? In case it is important, there are 2000 clients involved, so that would be 2000 databases if I followed my current FoxPro related structure. The implications of putting multiple clients in a table, with a client's rows identified by a client_id column, are probably fairly obvious. If many of those 2,000 clients have tables with millions of rows, performance could suffer without very careful indexing, managing tables with billions of rows can become challenging, and there could be concerns about how to ensure that data from one client isn't accidentally shown to another. You should also ask whether there are social (that is, nontechncal) reasons to avoid multiple clients per table. When a customer asks about security and you tell them, "You get your own database, nobody else can log in," they tend to like that. If you tell them that their data is mixed with everyone else's, but "we've done a really good job with our app software and we're pretty sure there are no bugs that would let anyone see your data," that may not fly. People will trust Postgres security (assuming you actually do it right) because it's an open source, trusted product used by some really big companies. But your own app? Do you even trust it? Even if your application IS secure, it may not matter. It's what the customer believes or worries about that can sell your product. We've also found another really good reason for separate databases. It lets you experiment without any impact on anything else. We have scripts that can create a database in just a few minutes, load it up, and have it ready to demo in just a few minutes. If we don't end up using it, we just blow it off and its gone. No other database is impacted at all. Craig -- 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] Architecting a database
wrote: > With the dBase and ISAM tables I have a good idea of how to handle > them since I have been working with them since dBASE originally > came out. Ah, someone with whom I can reminisce about CP/M and WordStar? :-) > With the PostgreSQL type tables I am not so certain how the data > is arranged within the one file. Does having the data all in one > database allow PostgreSQL to better utilize indexes and caches or > does having a number of smaller databases provide performance > increases? In case it is important, there are 2000 clients > involved, so that would be 2000 databases if I followed my current > FoxPro related structure. Well, there are many options here. You could have: - one PostgreSQL cluster for each client, - one database for each client (all in one cluster), - one schema for each client (all in one database), or - a client_id column in each table to segregate data. The first would probably be a maintenance nightmare; it's just listed for completeness. The cluster is the level at which you start and stop the database engine, do real-time backups through the database transaction logging, etc. You probably don't want to do that individually for each of 2,000 clients, I'm assuming. Besides that, each cluster has its own memory cache, which would probably be a problem for you. (The caching issues go away for all the following options.) The database is the level at which you can get a connection. You can see some cluster-level resources within all databases, like the list of databases and the list of users, but for the most part, each database is independent, even though they're running in the same executable engine. It would be relatively easy to keep the whole cluster (all databases) backed up (especially after 9.0 is release this summer), and you could have a cluster on another machine for standby, if desired. You are able to do dumps of individual databases, but only as snapshots of a moment in time or through external tools. It's hard to efficiently join data from a table in one database to a table in another. A schema is a logical separation within a database. Table client1.account is a different table from client2.account. While a user can be limited to tables within a single schema, a user with rights to all the tables can join between them as needed. You could put common reference data in a public schema which all users could access in addition to their private schemas. The implications of putting multiple clients in a table, with a client's rows identified by a client_id column, are probably fairly obvious. If many of those 2,000 clients have tables with millions of rows, performance could suffer without very careful indexing, managing tables with billions of rows can become challenging, and there could be concerns about how to ensure that data from one client isn't accidentally shown to another. Hopefully that's enough to allow you to make a good choice. If any of that wasn't clear, please ask. -Kevin -- 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: sudden spurt in swap utilization (was:cpu bound postgresql setup.)
On 25/06/10 20:00, Rajesh Kumar Mallah wrote: Dear tom, we have autocommit off in dbi. Any commit or rollback from the persistent modperl process immediately issues begin work; if the modperl process is waiting for request the database backend remains in idle in transaction state. Unless we modify data in a http request we neighter issue a commit nor rollback. The backend shouldn't go to 'idle in transaction' state until there is some activity within the transaction. I've attached an example script to demonstrate this - note that even SELECT queries will leave the handle as 'IDLE in transaction' unless you've changed the transaction isolation level from the default. Any queries that are idle in transaction will block connection pooling and cause old versions of table rows to hang around, as described in other replies. Note that this is nothing to do with mod_perl, it's purely due to the way transactions are handled - a one-off script would also have this issue, but on exit issues an implicit rollback and disconnects. Typically your database wrapper would handle this (I think DBIx::Class should take care of this automatically, although I haven't used it myself). Tom dbiPgConnectionHandling.pl Description: Perl program -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Architecting a database
I am in the process of moving a system that has been built around FoxPro tables for the last 18 years into a PostgreSQL based system. Over time I came up with decent strategies for making the FoxPro tables work well with the workload that was placed on them, but we are getting to the point that the locking mechanisms are causing problems when some of the more used tables are being written to. With the FoxPro tables I had one directory that contained the tables that had global data that was common to all clients. Things like documents that had been received and logged, checks that had been cut, etc. Then each client had his own directory which housed tables that had information relating to that specific client. Setting things up like this kept me from having any tables that were too terribly large so record addition and index creation were not very time consuming. I am wondering how I should architect this in PostgreSQL. Should I follow a similar strategy and have a separate database for each client and one database that contains the global data? With the dBase and ISAM tables I have a good idea of how to handle them since I have been working with them since dBASE originally came out. With the PostgreSQL type tables I am not so certain how the data is arranged within the one file. Does having the data all in one database allow PostgreSQL to better utilize indexes and caches or does having a number of smaller databases provide performance increases? In case it is important, there are 2000 clients involved, so that would be 2000 databases if I followed my current FoxPro related structure. Of course, I suppose it is always possible to combine a number of groups into a database if the number of databases is an issue. Tables within the client specific databases are generally name and address information as well as tables for 10 different types of accounts which require different structures and those tables hold anywhere from 10,000 transactions a piece for some smaller groups and 1 million for larger groups. I believe we have read to write ratio of about 1 to 15. Thanks for any input. -- 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] pgbench results on a new server
Craig James wrote: I've got a new server and want to make sure it's running well. Any changes to the postgresql.conf file? Generally you need at least a moderate shared_buffers (1GB or so at a minimum) and checkpoint_segments (32 or higher) in order for the standard pgbench test to give good results. pgbench -c20 -t 5000 -U test tps = 5789 pgbench -c30 -t -U test tps = 6961 pgbench -c40 -t 2500 -U test tps = 2945 General numbers are OK, the major drop going from 30 to 40 clients is larger than it should be. I'd suggest running the 40 client count one again to see if that's consistent. If it is, that may just be pgbench itself running into a problem. It doesn't handle high client counts very well unless you use the 9.0 version that supports multiple pgbench workers with the "-j" option. -- 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] pgbench results on a new server
On Fri, Jun 25, 2010 at 2:53 PM, Craig James wrote: > I've got a new server and want to make sure it's running well. Are these > pretty decent numbers? > > 8 cores (2x4 Intel Nehalem 2 GHz) > 12 GB memory > 12 x 7200 SATA 500 GB disks > 3WARE 9650SE-12ML RAID controller with BBU > WAL on ext2, 2 disks: RAID1 500GB, blocksize=4096 > Database on ext4, 8 disks: RAID10 2TB, stripe size 64K, blocksize=4096 > Ubuntu 10.04 LTS (Lucid) > Postgres 8.4.4 > > pgbench -i -s 100 -U test > pgbench -c 5 -t 2 -U test > tps = 4903 > pgbench -c 10 -t 1 -U test > tps = 4070 > pgbench -c20 -t 5000 -U test > tps = 5789 > pgbench -c30 -t -U test > tps = 6961 > pgbench -c40 -t 2500 -U test > tps = 2945 Numbers are okay, but you likely need much longer tests to see how they average out with the bgwriter / checkpoints happening, and keep track of your IO numbers to see where your dips are. I usually run pgbench runs, once they seem to get decent numbers, for several hours non-stop. Sometimes days during burn in. Note that running pgbench on a machine other than the actual db is often a good idea so you're not measuring how fast pgbench can run in contention with your own database. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear tom, we have autocommit off in dbi. Any commit or rollback from the persistent modperl process immediately issues begin work; if the modperl process is waiting for request the database backend remains in idle in transaction state. Unless we modify data in a http request we neighter issue a commit nor rollback. On 6/25/10, Tom Molesworth wrote: > On 25/06/10 16:59, Rajesh Kumar Mallah wrote: >> when i reduce max_connections i start getting errors, i will see again >> concurrent connections >> during business hours. lot of our connections are in > transaction state> during business >> this peculiar behavior of mod_perl servers have been discussed in >> past i think. dont' remember >> if there was any resolution. > > If connections spend any significant amount of time in transaction> state, that might indicate you're not committing/rolling > back after running queries - can you show an example of the code you're > using? > > e.g. something like my $dbh = DBI->connect(...); my $sth = > $dbh->prepare(q{select ... }); $sth->fetchall_arrayref; $sth->rollback; > > Tom > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent from Gmail for mobile | mobile.google.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] pgbench results on a new server
I've got a new server and want to make sure it's running well. Are these pretty decent numbers? 8 cores (2x4 Intel Nehalem 2 GHz) 12 GB memory 12 x 7200 SATA 500 GB disks 3WARE 9650SE-12ML RAID controller with BBU WAL on ext2, 2 disks: RAID1 500GB, blocksize=4096 Database on ext4, 8 disks: RAID10 2TB, stripe size 64K, blocksize=4096 Ubuntu 10.04 LTS (Lucid) Postgres 8.4.4 pgbench -i -s 100 -U test pgbench -c 5 -t 2 -U test tps = 4903 pgbench -c 10 -t 1 -U test tps = 4070 pgbench -c20 -t 5000 -U test tps = 5789 pgbench -c30 -t -U test tps = 6961 pgbench -c40 -t 2500 -U test tps = 2945 Thanks, Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Any recent AMD purchases?
I'm trying to find someone who has a system with an AMD "Magny Cours" 6100 series processor in it, like the Opteron 6174 or 6176 SE, who'd be willing to run a short test for me during an idle period to collect some performance data about it. Can't be running Windows, probably easiest to compile the test programs under Linux. If you have one of those processors and would be willing to help me out, please drop me an off-list note and I'll tell you what I'm looking for. Will need permission to publish the results to the community. -- 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] Write performance
On Jun 24, 2010, at 6:16 AM, Janning wrote: > On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote: >> On Thu, 24 Jun 2010, Janning wrote: >>> We have a 12 GB RAM machine with intel i7-975 and using >>> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)" >> >> Those discs are 1.5TB, not 1.5GB. > > sorry, my fault. > >>> One disk for the system and WAL etc. and one SW RAID-0 with two disks for >>> postgresql data. Our database is about 24GB. >> >> Beware of RAID-0 - make sure you can recover the data when (not if) a disc >> fails. > > oh sorry again, its a raid-1 of course. shame on me. If your WAL is not on RAID but your data is, you will lose data if the WAL log drive dies. You will then have a difficult time recovering data from the data drives even though they are RAID protected. Most likely indexes and some data will be corrupted since the last checkpoint. I have lost a WAL before, and the result was a lot of corrupted system indexes that had to be rebuilt in single user mode, and one system table (stats related) that had to be purged and regenerated from scratch. This was not fun. Most of the data was fine, but the cleanup is messy if you lose WAL, and there is no guarantee that your data is safe if you don't have the WAL available. -- 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] Occasional giant spikes in CPU load
On 6/25/10 9:41 AM, Kevin Grittner wrote: Craig James wrote: I always just assumed that lots of backends that would be harmless if each one was doing very little. Even if each is doing very little, if a large number of them happen to make a request at the same time, you can have problems. This is exactly where a connection pool can massively improve both throughput and response time. If you can arrange it, you want a connection pool which will put a limit on active database transactions and queue requests to start a new transaction until one of the pending ones finishes. No, that's doesn't seem to be the case. There is no external activity that triggers this huge spike in usage. It even happens to our backup server when only one of us is using it to do a single query. This problem seems to be triggered by Postgres itself, not by anything external. Per Tom's suggestion, I think upgrading to 8.4.4 is the answer. I'll learn more when our new hardware comes into use with a shiny new 8.4.4 installation. Craig -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Rajesh Kumar Mallah wrote: default_statistics_target = 50 # pgtune wizard 2010-06-25 (current 100 via default) (*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 (18GB , specified) checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified) You probably want to keep your existing values for all of these. Your effective_cache_size setting may be a little low, but I wouldn't worry about changing that right now--you have bigger problems right now. (*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 (16MB via default) (*) wal_buffers = 8MB # pgtune wizard 2010-06-25 ( 64kb , via default) checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 (0.5 via default) shared_buffers = 7680MB # pgtune wizard 2010-06-25 ( 4096 MB , specified) These are all potentially better for your system, but you'll use more RAM if you make these changes. For example, if you're having swap trouble, you definitely don't want to increase maintenance_work_mem. I suspect that 8GB of shared_buffers is probably the most you want to use. Most systems stop gaining any more benefit from that somewhere between 8GB and 10GB, and instead performance gets worse; it's better to be on the low side of that drop. You can probably support 8GB just fine if you sort out the work_mem issues. (*) max_connections = 80 # pgtune wizard 2010-06-25 ( 300 , ;-) specified ) work_mem = 192MB # pgtune wizard 2010-06-25 (256MB , specified) pgtune makes a guess at how many connections you'll have based on specified workload. If you know you have more connections than that, you should specify that on the command line: pgtune -c 300 ... It will then re-compute the work_mem figure more accurately using that higher connection count. Right now, it's guessing 192MB based on 80 connections, which is on the high side of reasonable. 192MB with *300* connections is way oversized. My rough computation says that if you tell it the number of connections correctly, pgtune will suggest to you around 50MB for work_mem. -- 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] Occasional giant spikes in CPU load
Craig James writes: > On 6/25/10 7:47 AM, Tom Lane wrote: >> Any chance of going to 8.4? If this is what I suspect, you really need >> this 8.4 fix: >> http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php >> which eliminated the thundering-herd behavior that previous releases >> exhibit when the sinval queue overflows. > Yes, there is a chance of upgrading to 8.4.4. I just bought a new server and > it has 8.4.4 on it, but it won't be online for a while so I can't compare > yet. This may motivate me to upgrade the current servers to 8.4.4 too. I > was pleased to see that 8.4 has a new upgrade-in-place feature that means we > don't have to dump/restore. That really helps a lot. I wouldn't put a lot of faith in pg_migrator for an 8.3 to 8.4 conversion ... it might work, but test it on a copy of your DB first. Possibly it'll actually be recommendable in 9.0. > A question about 8.4.4: I've been having problems with bloat. I thought I'd > adjusted the FSM parameters correctly based on advice I got here, but > apparently not. 8.4.4 has removed the configurable FSM parameters > completely, which is very cool. But ... if I upgrade a bloated database > using the upgrade-in-place feature, will 8.4.4 recover the bloat and return > it to the OS, or do I still have to recover the space manually (like > vacuum-full/reindex, or cluster, or copy/drop a table)? No, an in-place upgrade to 8.4 isn't magically going to fix that. This might actually be sufficient reason to stick with the tried&true dump and reload method, since you're going to have to do something fairly expensive anyway to clean out the bloat. 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] Occasional giant spikes in CPU load
Craig James wrote: if I upgrade a bloated database using the upgrade-in-place feature, will 8.4.4 recover the bloat and return it to the OS, or do I still have to recover the space manually (like vacuum-full/reindex, or cluster, or copy/drop a table)? There's no way for an upgrade in place to do anything about bloat. The changes in 8.4 reduce the potential sources for new bloat (like running out of a FSM pages), and the overhead of running VACUUM drops some due to things like the "Partial VACUUM" changes. But existing bloated tables and indexes are moved forward to the new version without any change. -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Rajesh Kumar Mallah wrote: > pgtune suggests the following: > (current value are in braces via reason) , (*) indicates > significant difference from current value. Different people have come to different conclusions on some of these settings. I believe that's probably because differences in hardware and workloads actually make the best choice different in different environments, and it's not always clear how to characterize that to make the best choice. If yo get conflicting advice on particular settings, I would strongly recommend testing to establish what works best for your actual workload on your hardware and OS. That said, my experience suggests... > default_statistics_target = 50 # pgtune wizard 2010-06-25 > (current 100 via default) Higher values add a little bit to the planning time of complex queries, but reduce the risk of choosing a bad plan. I would recommend leaving this at 100 unless you notice problems with long plan times. > (*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 > (16MB via default) Yeah, I'd boost this to 1GB. > checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 > (0.5 via default) I'd change this one by itself, and probably after some of the other tuning is done, so you can get a good sense of "before" and "after". I'm guessing that 0.9 would be better, but I would test it. > (*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 > (18GB , specified) Unless you're running other processes on the box which consume a lot of RAM, 18GB is probably lower than ideal, although this setting isn't too critical -- it doesn't affect actual RAM allocation; it just gives the optimizer a hint about how much might get cached. A higher setting encourages index use; a lower setting encourages table scans. > work_mem = 192MB # pgtune wizard 2010-06-25 > (256MB , specified) With 300 connections, I think that either of these could lead you to experience intermittent bursts of extreme swapping. I'd drop it to somewhere in the 16MB to 32MB range until I had a connection pool configured such that it was actually keeping the number of active connections much lower. > (*) wal_buffers = 8MB # pgtune wizard 2010-06-25 > (64kb , via default) Sure, I'd boost this. > checkpoint_segments = 16 # pgtune wizard 2010-06-25 > (30 , specified) If you have the disk space for the 30 segments, I wouldn't reduce it. > shared_buffers = 7680MB # pgtune wizard 2010-06-25 > (4096 MB , specified) This one is perhaps the most sensitive to workload. Anywhere between 1GB and 8GB might be best for you. Greg Smith has some great advice on how to tune this for your workload. > (*) max_connections = 80 # pgtune wizard 2010-06-25 > (300 , ;-) specified) > > when i reduce max_connections i start getting errors, i will see > again concurrent connections during business hours. That's probably a good number to get to, but you have to reduce the number of actual connections before you set the limit that low. > lot of our connections are in in transaction state If any of these stay in that state for more than a minute or two, you need to address that if you want to get your connection count under control. If any of them persist for hours or days, you need to fix it to avoid bloat which can kill performance. -Kevin -- 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] Occasional giant spikes in CPU load
Craig James wrote: > I always just assumed that lots of backends that would be harmless > if each one was doing very little. Even if each is doing very little, if a large number of them happen to make a request at the same time, you can have problems. This is exactly where a connection pool can massively improve both throughput and response time. If you can arrange it, you want a connection pool which will put a limit on active database transactions and queue requests to start a new transaction until one of the pending ones finishes. -Kevin -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
On 25/06/10 16:59, Rajesh Kumar Mallah wrote: when i reduce max_connections i start getting errors, i will see again concurrent connections during business hours. lot of our connections are in transaction state> during business this peculiar behavior of mod_perl servers have been discussed in past i think. dont' remember if there was any resolution. If connections spend any significant amount of time in transaction> state, that might indicate you're not committing/rolling back after running queries - can you show an example of the code you're using? e.g. something like my $dbh = DBI->connect(...); my $sth = $dbh->prepare(q{select ... }); $sth->fetchall_arrayref; $sth->rollback; Tom -- 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] Occasional giant spikes in CPU load
On 6/25/10 7:47 AM, Tom Lane wrote: Craig James writes: On 6/24/10 9:04 PM, Tom Lane wrote: sinval queue overflow comes to mind ... although that really shouldn't happen if there's "no real load" on the server. What PG version is this? 8.3.10. Upgraded based on your advice when I first asked this question. Any chance of going to 8.4? If this is what I suspect, you really need this 8.4 fix: http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php which eliminated the thundering-herd behavior that previous releases exhibit when the sinval queue overflows. Yes, there is a chance of upgrading to 8.4.4. I just bought a new server and it has 8.4.4 on it, but it won't be online for a while so I can't compare yet. This may motivate me to upgrade the current servers to 8.4.4 too. I was pleased to see that 8.4 has a new upgrade-in-place feature that means we don't have to dump/restore. That really helps a lot. A question about 8.4.4: I've been having problems with bloat. I thought I'd adjusted the FSM parameters correctly based on advice I got here, but apparently not. 8.4.4 has removed the configurable FSM parameters completely, which is very cool. But ... if I upgrade a bloated database using the upgrade-in-place feature, will 8.4.4 recover the bloat and return it to the OS, or do I still have to recover the space manually (like vacuum-full/reindex, or cluster, or copy/drop a table)? Or you could look at using connection pooling so you don't have quite so many backends ... I always just assumed that lots of backends that would be harmless if each one was doing very little. If I understand your explanation, it sounds like that's not entirely true in pre-8.4.4 releases due to the sinval queue problems. Thanks, Craig -- 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] Occasional giant spikes in CPU load
Dear Criag, also check for the possibility of installing sysstat in our system. it goes a long way in collecting the system stats. you may consider increasing the frequency of data collection by changing the interval of cron job manually in /etc/cron.d/ normally its */10 , you may make it */2 for time being. the software automatically maintains historical records of data for 1 month.
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear List, pgtune suggests the following: (current value are in braces via reason) , (*) indicates significant difference from current value. default_statistics_target = 50 # pgtune wizard 2010-06-25 (current 100 via default) (*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 (16MB via default) checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 (0.5 via default) (*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 (18GB , specified) work_mem = 192MB # pgtune wizard 2010-06-25 (256MB , specified) (*) wal_buffers = 8MB # pgtune wizard 2010-06-25 ( 64kb , via default) checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified) shared_buffers = 7680MB # pgtune wizard 2010-06-25 ( 4096 MB , specified) (*) max_connections = 80 # pgtune wizard 2010-06-25 ( 300 , ;-) specified ) when i reduce max_connections i start getting errors, i will see again concurrent connections during business hours. lot of our connections are in during business this peculiar behavior of mod_perl servers have been discussed in past i think. dont' remember if there was any resolution.
Re: [PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
I changed shared_buffers from 10G to 4G , swap usage has almost become nil. # free total used free sharedbuffers cached Mem: 32871276 245758248295452 0 11064 22167324 -/+ buffers/cache:2397436 30473840 Swap: 41929123524192560 I also observed that there was a huge IO wait and load spike initially which gradually reduced to normal levels. Now things seems to be fine. but real test shall be during business hours. vmstat output: http://pastebin.com/ygu8gUhS the iowait now is very respectable < 10% and CPU is idling most of the time. # vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 2 1352 8482444 11336 2229910010 450 20800 50 6 39 5 0 4 0352 8393840 11352 2230448400 480 163 9260 12717 32 4 62 3 0 5 1352 8474788 11360 2230898000 304 445 8295 12358 28 4 67 2 0 3 0352 8370672 11376 2231667600 648 158 8760 13214 38 4 55 3 0 11 0352 8193824 11392 2232357200 621 577 8800 13163 37 4 56 3 0 2 0352 8229012 11408 2232666400 169 405 9588 13696 34 4 61 1 0 6 1352 8319176 11424 2233314400 559 170 8830 12929 32 4 61 3 0 I shall also try pgtune in a while. -- 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] Occasional giant spikes in CPU load
Craig James writes: > On 6/24/10 9:04 PM, Tom Lane wrote: >> sinval queue overflow comes to mind ... although that really shouldn't >> happen if there's "no real load" on the server. What PG version is >> this? > 8.3.10. Upgraded based on your advice when I first asked this question. Any chance of going to 8.4? If this is what I suspect, you really need this 8.4 fix: http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php which eliminated the thundering-herd behavior that previous releases exhibit when the sinval queue overflows. If you're stuck on 8.3 then you are going to have to modify your application's behavior to eliminate sinval overflows. If the overall system load isn't high then I would have to guess that the problem is some individual sessions sitting "idle in transaction" for long periods, long enough that a number of DDL operations happen elsewhere. You could also consider throwing memory at the problem by raising the sinval queue size. That'd require a custom build since it's not exposed as a configurable parameter, but it'd be a one-line patch I think. Or you could look at using connection pooling so you don't have quite so many backends ... 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Rajesh Kumar Mallah wrote: > its now non business hours and > SELECT procpid,current_query from pg_stat_activity where > current_query not ilike '%idle%' ; > is just 5-10, i am yet to measure it during business hours. Be careful about ' in transaction' status. Those are a problem if the transaction remains active for very long, because vacuum (autovacuum or otherwise) can't free space for dead rows which could still be visible to the ' in transaction' connection. It's normal to see this status briefly between statements in a transaction, but it's a problem if a connection just sits there in this status. -Kevin -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Dear List, Hmmm , lemme test efficacy of pg_tune. I would reduce shared buffers also. regarding concurrent queries: its now non business hours and SELECT procpid,current_query from pg_stat_activity where current_query not ilike '%idle%' ; is just 5-10, i am yet to measure it during business hours. Warm Regds Rajesh Kumar Mallah. On Fri, Jun 25, 2010 at 4:58 PM, Yeb Havinga wrote: > Rajesh Kumar Mallah wrote: >> >> A scary phenomenon is being exhibited by the server , which is the server >> is slurping all the swap suddenly >> 8 1 4192912 906164 6100 2787364000 2277 858 13440 16235 >> 63 8 19 10 0 >> >> I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that >> this >> abnormal consumption of swap was NOT there even when work_mem was 4GB. >> eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9 >> the question is whats making postgres slurp the swap? i am posting my >> current postgresql.conf >> once again. >> >> # cat postgresql.conf | grep -v "^\s*#" | grep -v "^\s*$" >> listen_addresses = '*' # what IP address(es) to listen on; >> port = 5432 # (change requires restart) >> max_connections = 300 # (change requires restart) >> > > Hello Rajesh, > > In constrast with e.g. shared_buffers and effective_cache_size, work_mem is > amount of memory per 'thing' (e.g. order/group by) that wants some working > memory, so even a single backend can use several pieces of work_mem memory. > > Looking at your postgresql.conf, other memory values seem a bit too high as > well for a 32GB ram server. It is probably a good idea to use pgtune (on > pgfoundry) to get some reasonable ball park settings for your hardware. > > regards, > Yeb Havinga > > -- 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] Occasional giant spikes in CPU load
On 6/24/10 9:04 PM, Tom Lane wrote: Craig James writes: So what is it that will cause every single Postgres backend to come to life at the same moment, when there's no real load on the server? Maybe if a backend crashes? Some other problem? sinval queue overflow comes to mind ... although that really shouldn't happen if there's "no real load" on the server. What PG version is this? 8.3.10. Upgraded based on your advice when I first asked this question. Also, the pg_stat_activity view contents when this happens would probably be more useful to look at than "top" output. I'll try. It's hard to discover anything because the whole machine is overwhelmed when this happens. The only way I got the top(1) output was by running it high priority as root using nice(1). I can't do that with a Postgres backend, but I'll see what I can do. Craig -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
Rajesh Kumar Mallah wrote: A scary phenomenon is being exhibited by the server , which is the server is slurping all the swap suddenly 8 1 4192912 906164 6100 2787364000 2277 858 13440 16235 63 8 19 10 0 I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that this abnormal consumption of swap was NOT there even when work_mem was 4GB. eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9 the question is whats making postgres slurp the swap? i am posting my current postgresql.conf once again. # cat postgresql.conf | grep -v "^\s*#" | grep -v "^\s*$" listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 300 # (change requires restart) Hello Rajesh, In constrast with e.g. shared_buffers and effective_cache_size, work_mem is amount of memory per 'thing' (e.g. order/group by) that wants some working memory, so even a single backend can use several pieces of work_mem memory. Looking at your postgresql.conf, other memory values seem a bit too high as well for a 32GB ram server. It is probably a good idea to use pgtune (on pgfoundry) to get some reasonable ball park settings for your hardware. regards, Yeb Havinga -- 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] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
On Fri, 2010-06-25 at 15:25 +0530, Rajesh Kumar Mallah wrote: > shared_buffers = 10GB # min 128kB > work_mem = 512MB# min 64kB These are still pretty high IMHO. How many *concurrent* connections do you have? -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[PERFORM] sudden spurt in swap utilization (was:cpu bound postgresql setup.)
A scary phenomenon is being exhibited by the server , which is the server is slurping all the swap suddenly , some of the relevant sar -r output are: 10:30:01 AM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad 10:40:01 AM979068 31892208 97.02 10588 28194876 1781568314872 15.02 66500 10:50:01 AM 1791536 31079740 94.55 10480 27426512 1782848313592 14.96 43880 11:00:01 AM 4678768 28192508 85.77 9692 27213312 1784888311552 14.86 33296 11:10:01 AM179208 32692068 99.45 3180 27569008 1725136371304 17.71 65444 11:20:01 AM225604 32645672 99.31 2604 29817192 1693672402768 19.21 78312 <--- 11:30:01 AM520224 32351052 98.42 1780 26863576 0 2096440100.00 1585772 <-- within 10mins 11:40:02 AM483532 32387744 98.53 2672 27220404 0 2096440100.00 43876 11:50:01 AM162700 32708576 99.51 3316 27792540 0 2096440100.00 43708 12:00:01 PM420176 32451100 98.72 3772 28181316 0 2096440100.00 43708 12:10:01 PM331624 32539652 98.99 3236 27857760 0 2096440100.00 0 12:20:01 PM 1023428 31847848 96.89 4632 27450504 0 2096440100.00 0 12:30:01 PM763296 32107980 97.68 4988 28270704 0 2096440100.00 0 12:40:01 PM770280 32100996 97.66 5260 28423292 0 2096440100.00 0 Then i added more swap made it 4GB from 2GB 02:10:05 PM 8734144 24137132 73.43 5532 21219972 2096788 2096124 49.9952 02:12:01 PM 5989044 26882232 81.78 6108 23606680 2096788 2096124 49.9952 02:14:01 PM 1517724 31353552 95.38 6320 26988280 2096788 2096124 49.9952 02:16:01 PM316692 32554584 99.04 6516 28840264 1844856 2348056 56.00251984 02:18:01 PM450672 32420604 98.63 7748 27238712 0 4192912100.00 2096840 < all swap gone. 02:20:01 PM164388 32706888 99.50 7556 27118104 0 4192912100.00 2096840 02:22:01 PM848544 32022732 97.42 6212 26718712 0 4192912100.00 2096840 02:24:01 PM231332 32639944 99.30 6136 27276720 0 4192912100.00 2096840 02:26:01 PM639560 32231716 98.05 5608 27029372 0 4192912100.00 2096840 02:28:01 PM868824 32002452 97.36 4648 26253996 0 4192912100.00 2096840 ... 03:04:01 PM854408 32016868 97.40 4976 27182140 0 4192912100.00 0 03:06:01 PM 1571904 31299372 95.22 5184 27513232 0 4192912100.00 0 03:08:02 PM304600 32566676 99.07 5420 27850780 0 4192912100.00 0 03:10:01 PM915352 31955924 97.22 5632 28076320 0 4192912100.00 0 03:12:01 PM705132 32166144 97.85 5680 28057444 0 4192912100.00 0 03:14:01 PM369516 32501760 98.88 6136 27684364 0 4192912100.00 0 in vmstat the system does not seems to be swapping vmstat 5 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 24 2 4192912 947796 6036 2778532410 451 20800 50 6 39 5 0 22 3 4192912 1028956 6044 2779572800 1730 555 13445 14736 67 12 17 4 0 24 0 4192912 877508 6052 2780617200 1595 2292 13334 15666 67 9 19 5 0 14 8 4192912 820432 6068 2781975600 2331 1351 13208 16192 66 9 14 11 0 23 1 4192912 925960 6076 2783164400 1932 1584 13144 16291 71 9 14 5 0 2 3 4192912 895288 6084 2784643200 2496 991 13450 16303 70 9 13 8 0 17 0 4192912 936252 6092 2785986800 2122 826 13438 16233 69 9 17 5 0 8 1 4192912 906164 6100 2787364000 2277 858 13440 16235 63 8 19 10 0 I reduced work_mem from 4GB to 2GB to 512MB (now). I clearly remember that this abnormal consumption of swap was NOT there even when work_mem was 4GB. eg during happier times swap utilisation was: http://pastebin.com/bnE1pFZ9 the question is whats making postgres slurp the swap? i am posting my current postgresql.conf once again. # cat postgresql.conf | grep -v "^\s*#" | grep -v "^\s*$" listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 300 # (change requires restart) shared_buffers = 10GB # min 128kB work_mem = 512MB# min 64kB fsync = on # turns forced synchronization on or off synchronous_commit = on # immediate fsync at commit checkpoint_segments = 30# in logfile segments, min 1, 16MB each archive_mode = on