Re: [PERFORM] Optimize update query
Actually, what's the point in putting logs to ssd? SSDs are good for random access and logs are accessed sequentially. I'd put table spaces on ssd and leave logs on hdd 30 лист. 2012 04:33, Niels Kristian Schjødt nielskrist...@autouncle.com напис. Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not really working as it should., and maybe new relic is not monitoring as It should. If I do a sudo iostat -k 1 I get a lot of output like this: Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md21398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0 The storage thing is, that the sda and sdb is the SSD drives and the sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting utilized - and I should expect that since they are serving my pg_xlog right? - so maybe I did something wrong in the setup. Here is the path I followed: # 1) First setup the SSD drives in a software RAID1 setup: # http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1 # # 2) Then move the postgres pg_xlog dir # sudo /etc/init.d/postgresql-9.2 stop # sudo mkdir -p /ssd/pg_xlog # sudo chown -R postgres.postgres /ssd/pg_xlog # sudo chmod 700 /ssd/pg_xlog # sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog # sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog_old # sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog # sudo /etc/init.d/postgresql-9.2 start Can you spot something wrong? Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt nielskrist...@autouncle.com: Den 30/11/2012 kl. 02.24 skrev Kevin Grittner kgri...@mail.com: Niels Kristian Schjødt wrote: Okay, now I'm done the updating as described above. I did the postgres.conf changes. I did the kernel changes, i added two SSD's in a software RAID1 where the pg_xlog is now located - unfortunately the the picture is still the same :-( You said before that you were seeing high disk wait numbers. Now it is zero accourding to your disk utilization graph. That sounds like a change to me. When the database is under heavy load, there is almost no improvement to see in the performance compared to before the changes. In client-visible response time and throughput, I assume, not resource usage numbers? A lot of both read and writes takes more than a 1000 times as long as they usually do, under lighter overall load. As an odd coincidence, you showed your max_connections setting to be 1000. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -Kevin Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of I/O it was CPU I/O, it also states that in the chart in the link. However, as I'm not very familiar with these deep down database and server things, I had no idea wether a disk bottle neck could hide in this I/O, so i went along with Shauns great help, that unfortunately didn't solve my issues. Back to the issue: Could it be that it is the fact that I'm using ubuntus built in software raid to raid my disks, and that it is not at all capable of handling the throughput? -- 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] Optimize update query
Most modern SSD are much faster for fsync type operations than a spinning disk - similar performance to spinning disk + writeback raid controller + battery. However as you mention, they are great at random IO too, so Niels, it might be worth putting your postgres logs *and* data on the SSDs and retesting. Regards Mark On 30/11/12 21:37, Vitalii Tymchyshyn wrote: Actually, what's the point in putting logs to ssd? SSDs are good for random access and logs are accessed sequentially. I'd put table spaces on ssd and leave logs on hdd 30 лист. 2012 04:33, Niels Kristian Schjødt nielskrist...@autouncle.com mailto:nielskrist...@autouncle.com напис. Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not really working as it should., and maybe new relic is not monitoring as It should. If I do a sudo iostat -k 1 I get a lot of output like this: Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md21398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0 The storage thing is, that the sda and sdb is the SSD drives and the sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting utilized - and I should expect that since they are serving my pg_xlog right? - so maybe I did something wrong in the setup. Here is the path I followed: # 1) First setup the SSD drives in a software RAID1 setup: # http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1 # # 2) Then move the postgres pg_xlog dir # sudo /etc/init.d/postgresql-9.2 stop # sudo mkdir -p /ssd/pg_xlog # sudo chown -R postgres.postgres /ssd/pg_xlog # sudo chmod 700 /ssd/pg_xlog # sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog # sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog_old # sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog # sudo /etc/init.d/postgresql-9.2 start Can you spot something wrong? Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt nielskrist...@autouncle.com mailto:nielskrist...@autouncle.com: Den 30/11/2012 kl. 02.24 skrev Kevin Grittner kgri...@mail.com mailto:kgri...@mail.com: Niels Kristian Schjødt wrote: Okay, now I'm done the updating as described above. I did the postgres.conf changes. I did the kernel changes, i added two SSD's in a software RAID1 where the pg_xlog is now located - unfortunately the the picture is still the same :-( You said before that you were seeing high disk wait numbers. Now it is zero accourding to your disk utilization graph. That sounds like a change to me. When the database is under heavy load, there is almost no improvement to see in the performance compared to before the changes. In client-visible response time and throughput, I assume, not resource usage numbers? A lot of both read and writes takes more than a 1000 times as long as they usually do, under lighter overall load. As an odd coincidence, you showed your max_connections setting to be 1000. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -Kevin Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of I/O it was CPU I/O, it also states that in the chart in the link. However, as I'm not very familiar with these deep down database and server things, I had no idea wether a disk bottle neck could hide in this I/O, so i went along with Shauns great help, that unfortunately didn't solve my issues. Back to the issue: Could it be that it is the fact that I'm using ubuntus built in software raid to raid my disks, and that it is not at all capable of handling the throughput? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org mailto: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] Optimize update query
When I try your command sequence I end up with the contents of the new pg_xlog owned by root. Postgres will not start: PANIC: could not open file pg_xlog/000100060080 (log file 6, segment 128): Permission denied While this is fixable, I suspect you have managed to leave the xlogs directory that postgres is actually using on the HDD drives. When I do this I normally do: $ service postgresql stop $ sudo mkdir -p /ssd/pg_xlog $ sudo chown -R postgres.postgres /ssd/pg_xlog $ sudo chmod 700 /ssd/pg_xlog $ sudo su - postgres postgres $ mv /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog postgres $ rmdir /var/lib/postgresql/9.2/main/pg_xlog postgres $ ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog postgres $ service postgresql start regards Mark On 30/11/12 15:32, Niels Kristian Schjødt wrote: Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not really working as it should., and maybe new relic is not monitoring as It should. If I do a sudo iostat -k 1 I get a lot of output like this: Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md21398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0 The storage thing is, that the sda and sdb is the SSD drives and the sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting utilized - and I should expect that since they are serving my pg_xlog right? - so maybe I did something wrong in the setup. Here is the path I followed: # 1) First setup the SSD drives in a software RAID1 setup: # http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1 # # 2) Then move the postgres pg_xlog dir # sudo /etc/init.d/postgresql-9.2 stop # sudo mkdir -p /ssd/pg_xlog # sudo chown -R postgres.postgres /ssd/pg_xlog # sudo chmod 700 /ssd/pg_xlog # sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog # sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog_old # sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog # sudo /etc/init.d/postgresql-9.2 start Can you spot something wrong? Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt nielskrist...@autouncle.com: Den 30/11/2012 kl. 02.24 skrev Kevin Grittner kgri...@mail.com: Niels Kristian Schjødt wrote: Okay, now I'm done the updating as described above. I did the postgres.conf changes. I did the kernel changes, i added two SSD's in a software RAID1 where the pg_xlog is now located - unfortunately the the picture is still the same :-( You said before that you were seeing high disk wait numbers. Now it is zero accourding to your disk utilization graph. That sounds like a change to me. When the database is under heavy load, there is almost no improvement to see in the performance compared to before the changes. In client-visible response time and throughput, I assume, not resource usage numbers? A lot of both read and writes takes more than a 1000 times as long as they usually do, under lighter overall load. As an odd coincidence, you showed your max_connections setting to be 1000. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -Kevin Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of I/O it was CPU I/O, it also states that in the chart in the link. However, as I'm not very familiar with these deep down database and server things, I had no idea wether a disk bottle neck could hide in this I/O, so i went along with Shauns great help, that unfortunately didn't solve my issues. Back to the issue: Could it be that it is the fact that I'm using ubuntus built in software raid to raid my disks, and that it is not at all capable of handling the throughput? -- 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] Optimize update query
Oh, yes. I don't imagine DB server without RAID+BBU :) When there is no BBU, SSD can be handy. But you know, SSD is worse in linear read/write than HDD. Best regards, Vitalii Tymchyshyn 2012/11/30 Mark Kirkwood mark.kirkw...@catalyst.net.nz Most modern SSD are much faster for fsync type operations than a spinning disk - similar performance to spinning disk + writeback raid controller + battery. However as you mention, they are great at random IO too, so Niels, it might be worth putting your postgres logs *and* data on the SSDs and retesting. Regards Mark On 30/11/12 21:37, Vitalii Tymchyshyn wrote: Actually, what's the point in putting logs to ssd? SSDs are good for random access and logs are accessed sequentially. I'd put table spaces on ssd and leave logs on hdd 30 лист. 2012 04:33, Niels Kristian Schjødt nielskrist...@autouncle.com mailto:nielskristian@**autouncle.comnielskrist...@autouncle.com напис. Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not really working as it should., and maybe new relic is not monitoring as It should. If I do a sudo iostat -k 1 I get a lot of output like this: Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md21398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0 The storage thing is, that the sda and sdb is the SSD drives and the sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting utilized - and I should expect that since they are serving my pg_xlog right? - so maybe I did something wrong in the setup. Here is the path I followed: # 1) First setup the SSD drives in a software RAID1 setup: # http://askubuntu.com/**questions/223194/setup-of-two-** additional-ssd-drives-in-raid-**1http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1 # # 2) Then move the postgres pg_xlog dir # sudo /etc/init.d/postgresql-9.2 stop # sudo mkdir -p /ssd/pg_xlog # sudo chown -R postgres.postgres /ssd/pg_xlog # sudo chmod 700 /ssd/pg_xlog # sudo cp -rf /var/lib/postgresql/9.2/main/**pg_xlog/* /ssd/pg_xlog # sudo mv /var/lib/postgresql/9.2/main/**pg_xlog /var/lib/postgresql/9.2/main/**pg_xlog_old # sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/**pg_xlog # sudo /etc/init.d/postgresql-9.2 start Can you spot something wrong? Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt nielskrist...@autouncle.com mailto:nielskristian@**autouncle.comnielskrist...@autouncle.com : Den 30/11/2012 kl. 02.24 skrev Kevin Grittner kgri...@mail.com mailto:kgri...@mail.com: Niels Kristian Schjødt wrote: Okay, now I'm done the updating as described above. I did the postgres.conf changes. I did the kernel changes, i added two SSD's in a software RAID1 where the pg_xlog is now located - unfortunately the the picture is still the same :-( You said before that you were seeing high disk wait numbers. Now it is zero accourding to your disk utilization graph. That sounds like a change to me. When the database is under heavy load, there is almost no improvement to see in the performance compared to before the changes. In client-visible response time and throughput, I assume, not resource usage numbers? A lot of both read and writes takes more than a 1000 times as long as they usually do, under lighter overall load. As an odd coincidence, you showed your max_connections setting to be 1000. http://wiki.postgresql.org/**wiki/Number_Of_Database_** Connectionshttp://wiki.postgresql.org/wiki/Number_Of_Database_Connections -Kevin Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of I/O it was CPU I/O, it also states that in the chart in the link. However, as I'm not very familiar with these deep down database and server things, I had no idea wether a disk bottle neck could hide in this I/O, so i went along with Shauns great help, that unfortunately didn't solve my issues. Back to the issue: Could it be that it is the fact
Re: [PERFORM] Optimize update query
Actually, what's the point in putting logs to ssd? SSDs are good for random access and logs are accessed sequentially. I'd put table spaces on ssd and leave logs on hdd 30 лист. 2012 04:33, Niels Kristian Schjødt nielskrist...@autouncle.com напис. Because SSD's are considered faster. Then you have to put the most phyisical IO intensive operations on SSD. For the majority of databases, these are the logfiles. But you should investigate where the optimum is for your situation.
Re: [PERFORM] Optimize update query
SSDs are not faster for sequential IO as I know. That's why (with BBU or synchronious_commit=off) I prefer to have logs on regular HDDs. Best reag 2012/11/30 Willem Leenen willem_lee...@hotmail.com Actually, what's the point in putting logs to ssd? SSDs are good for random access and logs are accessed sequentially. I'd put table spaces on ssd and leave logs on hdd 30 лист. 2012 04:33, Niels Kristian Schjødt nielskrist...@autouncle.com напис. Because SSD's are considered faster. Then you have to put the most phyisical IO intensive operations on SSD. For the majority of databases, these are the logfiles. But you should investigate where the optimum is for your situation. -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Optimize update query
Niels Kristian Schjødt wrote: You said before that you were seeing high disk wait numbers. Now it is zero accourding to your disk utilization graph. That sounds like a change to me. Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of I/O it was CPU I/O A lot of both read and writes takes more than a 1000 times as long as they usually do, under lighter overall load. As an odd coincidence, you showed your max_connections setting to be 1000. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Back to the issue: Could it be that it is the fact that I'm using ubuntus built in software raid to raid my disks, and that it is not at all capable of handling the throughput? For high performance situations I would always use a high quality RAID controller with battery-backed RAM configured for write-back; however: The graphs you included suggest that your problem has nothing to do with your storage system. Now maybe you didn't capture the data for the graphs while the problem was occurring, in which case the graphs would be absolutely useless; but based on what slim data you have provided, you need a connection pool (like maybe pgbouncer configured in transaction mode) to limit the number of database connections used to something like twice the number of cores. If you still have problems, pick the query which is using the most time on your database server, and post it with the information suggested on this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions -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] Optimize update query
Okay, So to understand this better before I go with that solution: In theory what difference should it make to the performance, to have a pool in front of the database, that all my workers and web servers connect to instead of connecting directly? Where is the performance gain coming from in that situation? Den 30/11/2012 kl. 13.03 skrev Kevin Grittner kgri...@mail.com: Niels Kristian Schjødt wrote: You said before that you were seeing high disk wait numbers. Now it is zero accourding to your disk utilization graph. That sounds like a change to me. Hehe, I'm sorry if it somehow was misleading, I just wrote a lot of I/O it was CPU I/O A lot of both read and writes takes more than a 1000 times as long as they usually do, under lighter overall load. As an odd coincidence, you showed your max_connections setting to be 1000. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Back to the issue: Could it be that it is the fact that I'm using ubuntus built in software raid to raid my disks, and that it is not at all capable of handling the throughput? For high performance situations I would always use a high quality RAID controller with battery-backed RAM configured for write-back; however: The graphs you included suggest that your problem has nothing to do with your storage system. Now maybe you didn't capture the data for the graphs while the problem was occurring, in which case the graphs would be absolutely useless; but based on what slim data you have provided, you need a connection pool (like maybe pgbouncer configured in transaction mode) to limit the number of database connections used to something like twice the number of cores. If you still have problems, pick the query which is using the most time on your database server, and post it with the information suggested on this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions -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] Optimize update query
On 11/30/2012 07:31 AM, Niels Kristian Schjødt wrote: In theory what difference should it make to the performance, to have a pool in front of the database, that all my workers and web servers connect to instead of connecting directly? Where is the performance gain coming from in that situation? If you have several more connections than you have processors, the database does a *lot* more context switching, and among other things, that drastically reduces PG performance. On a testbed, I can get over 150k transactions per second on PG 9.1 with a 1-1 relationship between CPU and client. Increase that to a few hundred, and my TPS drops down to 30k. Simply having the clients there kills performance. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Optimize update query
On 11/29/2012 08:32 PM, Niels Kristian Schjødt wrote: If I do a sudo iostat -k 1 I get a lot of output like this: Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md21398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0 The storage thing is, that the sda and sdb is the SSD drives and the sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting utilized - and I should expect that since they are serving my pg_xlog right? No, that's right. They are, but it would appear that the majority of your traffic actually isn't due to transaction logs like I'd suspected. If you get a chance, could you monitor the contents of: /var/lib/postgresql/9.2/main/base/pgsql_tmp Your main drives are getting way, way more writes than they should. 13MB per second is ridiculous even under heavy write loads. Based on the TPS count, you're basically saturating the ability of those two 3TB drives. Those writes have to be coming from somewhere. # sudo mkdir -p /ssd/pg_xlog This is going to sound stupid, but are you *sure* the SSD is mounted at /ssd ? # sudo chown -R postgres.postgres /ssd/pg_xlog # sudo chmod 700 /ssd/pg_xlog # sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog # sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog_old # sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog # sudo /etc/init.d/postgresql-9.2 start The rest of this is fine, except that you probably should have added: sudo chown -R postgres:postgres /ssd/pg_xlog/* -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Optimize update query
On 11/30/2012 02:37 AM, Vitalii Tymchyshyn wrote: Actually, what's the point in putting logs to ssd? SSDs are good for random access and logs are accessed sequentially. While this is true, Niels' problem is that his regular HDs are getting saturated. In that case, moving any activity off of them is an improvement. Why not move the data to the SSDs, you ask? Because he bought two 3TB drives. The assumption here is that a 256GB SSD will not have enough space for the long-term lifespan of this database. Either way, based on the iostat activity he posted, clearly there's some other write stream happening we're not privy to. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] deadlock under load
Hello We am running a web application on ubuntu 10.10 using postgres 8.4.3. We are experiencing regular problems (each morning as the users come in) which seem to be caused by deadlocks in the postgres database. I am seeing messages like: 2012-11-30 10:24:36 GMT LOG: sending cancel to blocking autovacuum PID 16951 at character 62 2012-11-30 10:24:36 GMT DETAIL: Process 3368 waits for AccessShareLock on relation 36183 of database 33864. 2012-11-30 10:24:36 GMT STATEMENT: SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue WHERE indicatorid I N (41471, 46324, 41481, 41487) AND periodid IN (46422, 46423, 46424) AND organisationunitid IN (67) Almost all of the postgres processes seem to be stuck in the PARSE WAITING state and the application ceases to respond as it becomes starved of database connections. The only way to get things moving again seems to be to restart postgres. Trying to interpret this, does this mean that the autovacuum process is holding a lock which is required tn order to complete the select query? Is it possible that the autovacuum process is ignoring that 'cancel' request so everything stays blocked? Sorry if these seem like basic questions. I am not too sure where to look to start resolving this. Any suggestions would be appreciated. Bob
Re: [PERFORM] Optimize update query
Den 30/11/2012 kl. 15.02 skrev Shaun Thomas stho...@optionshouse.com: On 11/29/2012 08:32 PM, Niels Kristian Schjødt wrote: If I do a sudo iostat -k 1 I get a lot of output like this: Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md21398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0 The storage thing is, that the sda and sdb is the SSD drives and the sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid arrays on the HDD's and the md3 is the raid on the SSD's. Neither of the md3 or the SSD's are getting utilized - and I should expect that since they are serving my pg_xlog right? No, that's right. They are, but it would appear that the majority of your traffic actually isn't due to transaction logs like I'd suspected. If you get a chance, could you monitor the contents of: /var/lib/postgresql/9.2/main/base/pgsql_tmp Your main drives are getting way, way more writes than they should. 13MB per second is ridiculous even under heavy write loads. Based on the TPS count, you're basically saturating the ability of those two 3TB drives. Those writes have to be coming from somewhere. # sudo mkdir -p /ssd/pg_xlog This is going to sound stupid, but are you *sure* the SSD is mounted at /ssd ? # sudo chown -R postgres.postgres /ssd/pg_xlog # sudo chmod 700 /ssd/pg_xlog # sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog # sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog_old # sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog # sudo /etc/init.d/postgresql-9.2 start The rest of this is fine, except that you probably should have added: sudo chown -R postgres:postgres /ssd/pg_xlog/* -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email Oh my, Shaun once again you nailed it! That's what you get from working too late in the night - I forgot to run 'sudo mount -a' I feel so embarrassed now :-( - In other words no the drive was not mounted to the /ssd dir. So now it is, and this has gained me a performance increase of roughly around 20% - a little less than what I would have hoped for but still better - but anyways yes that's right. I still see a lot of CPU I/O when doing a lot of writes, so the question is, what's next. Should I try and go' for the connection pooling thing or monitor that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do you mean by monitor - size?) PS. comment on the Why not move the data to the SSDs you are exactly right. i don't think the SSD's will be big enough for the data within a not too long timeframe, so that is exactly why I want to keep my data on the big drives. PPS. I talked with New Relic and it turns out there is something wrong with the disk monitoring tool, so that's why there was nothing in the disk charts but iostat showed a lot of activity. -- 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] Optimize update query
On 11/30/2012 08:48 AM, Niels Kristian Schjødt wrote: I forgot to run 'sudo mount -a' I feel so embarrassed now :-( - In other words no the drive was not mounted to the /ssd dir. Yeah, that'll get ya. I still see a lot of CPU I/O when doing a lot of writes, so the question is, what's next. Should I try and go' for the connection pooling thing or monitor that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do you mean by monitor - size?) Well, like Keven said, if you have more than a couple dozen connections on your hardware, you're losing TPS. It's probably a good idea to install pgbouncer or pgpool and let your clients connect to those instead. You should see a good performance boost from that. But what concerns me is that your previous CPU charts showed a lot of iowait. Even with the SSD taking some of the load off your write stream, something else is going on, there. That's why you need to monitor the size in MB, or number of files, for the pgsql_tmp directory. That's where PG puts temp files when sorts are too big for your work_mem. If that's getting a ton of activity, that would explain some of your write overhead. PPS. I talked with New Relic and it turns out there is something wrong with the disk monitoring tool, so that's why there was nothing in the disk charts but iostat showed a lot of activity. Yeah. Next time you need to check IO, use iostat. It's not as pretty, but it tells everything. ;) Just to help out with that, use: iostat -dmx That will give you extended information, including the % utilization of your drives. TPS stats are nice, but I was just guessing your drives were stalling out based on experience. Getting an outright percentage is better. You should also use sar. Just a plain: sar 1 100 Will give you a lot of info on what the CPU is doing. You want that %iowait column to be as low as possible. Keep us updated. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Optimize update query
Hmm very very interesting. Currently I run at medium load compared to the very high loads in the night. This is what the CPU I/O on new relic show: https://rpm.newrelic.com/public/charts/8RnSOlWjfBy And this is what iostat shows: Linux 3.2.0-33-generic (master-db) 11/30/2012 _x86_64_(8 CPU) Device: rrqm/s wrqm/s r/s w/srMB/swMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 3.46 26.62 57.06 1.66 0.6857.41 0.040.430.770.28 0.09 0.73 sdb 0.0316.850.01 70.26 0.00 2.3568.36 0.060.810.210.81 0.10 0.73 sdc 1.9656.37 25.45 172.56 0.53 3.7243.98 30.83 155.70 25.15 174.96 1.74 34.46 sdd 1.8356.52 25.48 172.42 0.52 3.7243.90 30.50 154.11 25.66 173.09 1.74 34.37 md1 0.00 0.000.000.00 0.00 0.00 3.02 0.000.000.000.00 0.00 0.00 md0 0.00 0.000.570.59 0.00 0.00 8.00 0.000.000.000.00 0.00 0.00 md2 0.00 0.00 54.14 227.94 1.05 3.7234.61 0.000.000.000.00 0.00 0.00 md3 0.00 0.000.01 60.46 0.00 0.6823.12 0.000.000.000.00 0.00 0.00 A little reminder md3 is the raid array of the ssd drives sda and sdb and the md0-2 is the array of the regular hdd drives sdc and sdd The pgsql_tmp dir is not changing at all it's constantly empty (a size of 4.0K). So It doesn't seem like the ssd drives is at all utilized but the regular drives certainly is. but now i know for sure that the /ssd is mounted correctly: sudo df /ssd Filesystem 1K-blocksUsed Available Use% Mounted on /dev/md3 230619228 5483796 213420620 3% /ssd Den 30/11/2012 kl. 16.00 skrev Shaun Thomas stho...@optionshouse.com: On 11/30/2012 08:48 AM, Niels Kristian Schjødt wrote: I forgot to run 'sudo mount -a' I feel so embarrassed now :-( - In other words no the drive was not mounted to the /ssd dir. Yeah, that'll get ya. I still see a lot of CPU I/O when doing a lot of writes, so the question is, what's next. Should I try and go' for the connection pooling thing or monitor that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do you mean by monitor - size?) Well, like Keven said, if you have more than a couple dozen connections on your hardware, you're losing TPS. It's probably a good idea to install pgbouncer or pgpool and let your clients connect to those instead. You should see a good performance boost from that. But what concerns me is that your previous CPU charts showed a lot of iowait. Even with the SSD taking some of the load off your write stream, something else is going on, there. That's why you need to monitor the size in MB, or number of files, for the pgsql_tmp directory. That's where PG puts temp files when sorts are too big for your work_mem. If that's getting a ton of activity, that would explain some of your write overhead. PPS. I talked with New Relic and it turns out there is something wrong with the disk monitoring tool, so that's why there was nothing in the disk charts but iostat showed a lot of activity. Yeah. Next time you need to check IO, use iostat. It's not as pretty, but it tells everything. ;) Just to help out with that, use: iostat -dmx That will give you extended information, including the % utilization of your drives. TPS stats are nice, but I was just guessing your drives were stalling out based on experience. Getting an outright percentage is better. You should also use sar. Just a plain: sar 1 100 Will give you a lot of info on what the CPU is doing. You want that %iowait column to be as low as possible. Keep us updated. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
Re: [PERFORM] deadlock under load
Bob Jolliffe bobjolli...@gmail.com writes: We am running a web application on ubuntu 10.10 using postgres 8.4.3. Current release in that branch is 8.4.14. (By this time next week it'll be 8.4.15.) You are missing a lot of bug fixes: http://www.postgresql.org/docs/8.4/static/release.html Trying to interpret this, does this mean that the autovacuum process is holding a lock which is required tn order to complete the select query? Possibly. Looking into the pg_locks view would tell you 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] Optimize update query
On 11/30/2012 09:44 AM, Niels Kristian Schjødt wrote: Just a note on your iostat numbers. The first reading is actually just a summary. You want the subsequent readings. The pgsql_tmp dir is not changing at all it's constantly empty (a size of 4.0K). Good. Filesystem 1K-blocksUsed Available Use% Mounted on /dev/md3 230619228 5483796 213420620 3% /ssd Good. You could just be seeing lots of genuine activity. But going back on the thread, I remember seeing this in your postgresql.conf: shared_buffers = 7680MB Change this to: shared_buffers = 4GB I say that because you mentioned you're using Ubuntu 12.04, and we were having some problems with PG on that platform. With shared_buffers over 4GB, it starts doing really weird things to the memory subsystem. Whatever it does causes the kernel to purge cache rather aggressively. We saw a 60% reduction in read IO by reducing shared_buffers to 4GB. Without as many reads, your writes should be much less disruptive. You'll need to restart PG to adopt that change. But I encourage you to keep iostat running in a terminal window so you can watch it for a while. It's very revealing. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] deadlock under load
On 30 November 2012 15:57, Tom Lane t...@sss.pgh.pa.us wrote: Bob Jolliffe bobjolli...@gmail.com writes: We am running a web application on ubuntu 10.10 using postgres 8.4.3. Current release in that branch is 8.4.14. (By this time next week it'll be 8.4.15.) You are missing a lot of bug fixes: http://www.postgresql.org/docs/8.4/static/release.html Sorry I reported that incorrectly. 8.4.3 was initially installed but the package system has kept it up to date. Currently it is in fact 8.4.14. Trying to interpret this, does this mean that the autovacuum process is holding a lock which is required tn order to complete the select query? Possibly. Looking into the pg_locks view would tell you more. Ok. I guess I will have to wait for it to lock up again to do this. regards, tom lane
[PERFORM] shared_buffers on ubuntu precise
On Nov 30, 2012, at 8:06 AM, Shaun Thomas wrote: I say that because you mentioned you're using Ubuntu 12.04, and we were having some problems with PG on that platform. With shared_buffers over 4GB, it starts doing really weird things to the memory subsystem. Whatever it does causes the kernel to purge cache rather aggressively. We saw a 60% reduction in read IO by reducing shared_buffers to 4GB. Without as many reads, your writes should be much less disruptive. Hm, this sounds like something we should look into. Before we start digging do you have more to share, or did you leave it with the huh, that's weird; this seems to fix it solution?
Re: [PERFORM] Comparative tps question
On 29/11/2012 17:33, Merlin Moncure wrote: Since we have some idle cpu% here we can probably eliminate pgbench as a bottleneck by messing around with the -j switch. another thing we want to test is the -N switch -- this doesn't update the tellers and branches table which in high concurrency situations can bind you from locking perspective. Using -N gives around a 15% increase in tps with no major changes in load, etc. using more threads slightly drops the performance (as expected with only 32 cores). dropping it does give a slight increase (presumably because half the cores aren't real). one thing that immediately jumps out here is that your wal volume could be holding you up. so it's possible we may want to move wal to the ssd volume. if you can scrounge up a 9.2 pgbench, we can gather more evidence for that by running pgbench with the --unlogged-tables option, which creates the tables unlogged so that they are not wal logged (for the record, this causes tables to be truncated when not shut down in clean state). I did notice that using -S drives the tps up to near 30K tps, so it is possibly the wal volume, although saying that I did move the pg_xlog directory onto the ssd array before posting to the list and the difference wasn't significant. I'll try and repeat that when I get some more downtime (I'm having to run the current tests while the db is live, but under light load). I'll have a look at using the 9.2 pgbench and see what happens. yeah -- this will tell us raw seek performance of ssd volume which presumably will be stupendous. 2x is minimum btw 10x would be more appropriate. since you're building a beast, other settings to explore are numa (http://frosty-postgres.blogspot.com/2012/08/postgresql-numa-and-zone-reclaim-mode.html) and dell memory bios settings that are occasionally set from the factory badly (see here: http://bleything.net/articles/postgresql-benchmarking-memory.html). Cheers for the links, I'd already looked at the numa stuff and disabled zone reclaim. I was looking at using the patch previously posted that used shared mode for the master process and then local only for the workers - excuse the terminology - but time constraints prevented that. Made sure the box was in performance mode in the bios, unfortunately I spotted bens blog when I was setting the box up, but didn't have time to go through all the tests. At the time performance seemed ok (well better than the previous box :) - but having it live for a while made me think I or it could be doing better. Anyway, I still think it would be nice to post tps results for compative purposes, so if I get a minute or two I'll create a site and stick mine on there. John -- 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] shared_buffers on ubuntu precise
On 11/30/2012 01:57 PM, Ben Chobot wrote: Hm, this sounds like something we should look into. Before we start digging do you have more to share, or did you leave it with the huh, that's weird; this seems to fix it solution? We're still testing. We're still on the -31 kernel. We tried the -33 kernel which *might* fix it, but then this happened: https://bugs.launchpad.net/ubuntu/+source/linux/+bug/1084264 So now we're testing -34 which is currently proposed. Either way, it's pretty clear that Ubuntu's choice of patches to backport is rather eclectic and a little wonky, or that nailing down load calculations went awry since the NOHZ stuff started, or both. At this point, I wish we'd stayed on CentOS. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] shared_buffers on ubuntu precise
On Fri, Nov 30, 2012 at 02:01:45PM -0600, Shaun Thomas wrote: On 11/30/2012 01:57 PM, Ben Chobot wrote: Hm, this sounds like something we should look into. Before we start digging do you have more to share, or did you leave it with the huh, that's weird; this seems to fix it solution? We're still testing. We're still on the -31 kernel. We tried the -33 kernel which *might* fix it, but then this happened: https://bugs.launchpad.net/ubuntu/+source/linux/+bug/1084264 So now we're testing -34 which is currently proposed. Either way, it's pretty clear that Ubuntu's choice of patches to backport is rather eclectic and a little wonky, or that nailing down load calculations went awry since the NOHZ stuff started, or both. At this point, I wish we'd stayed on CentOS. Or Debian. Not sure what would justify use of Ubuntu as a server, except wanting to have the exact same OS as their personal computers. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] shared_buffers on ubuntu precise
On 11/30/2012 02:38 PM, Bruce Momjian wrote: Or Debian. Not sure what would justify use of Ubuntu as a server, except wanting to have the exact same OS as their personal computers. Honestly not sure why we went that direction. I'm not in the sysadmin group, though I do work with them pretty closely. I think it was because of the LTS label, and the fact that the packages are quite a bit more recent than Debian stable. I can say however, that I'm testing the 3.4 kernel right now, and it seems much better. I may be able to convince them to install that instead if their own tests prove beneficial. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] shared_buffers on ubuntu precise
On Fri, Nov 30, 2012 at 12:38 PM, Bruce Momjian br...@momjian.us wrote: Or Debian. Not sure what would justify use of Ubuntu as a server, except wanting to have the exact same OS as their personal computers. We have switched from Debian to Ubuntu: there is definitely non-zero value in the PPA hosting (although it's rather terrible in many ways), regular LTS releases (even if you choose not to use them right away, and know they are somewhat buggy at times), and working with AWS and Canonical as organizations (that, most importantly, can interact directly without my own organization) on certain issues. For example, this dog of a bug: https://bugs.launchpad.net/ubuntu/+source/linux-ec2/+bug/929941 I also frequently take advantage of Debian unstable for backporting of specific packages that are very important to me, so there's a lot of value to me in Ubuntu being quite similar to Debian. In fact, even though I say we 'switched', it's not as though we re-did some entrenched systems from Debian to Ubuntu -- rather, we employ both systems at the same time and I don't recall gnashing of teeth about that, because they are very similar. Yet, there is a clear Ubuntu preference for new systems made today and, to wit, I can't think of anyone with more than the most mild preference for Debian. Conversely, I'd say the preference for Ubuntu for the aforementioned reasons is clear but moderate at most. Also, there's the similarity to the lap/desktop environment. Often cited with some derision, yet it does add a lot of value, even if people run slightly newer Ubuntus on their non-production computer. -- fdr -- 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] shared_buffers on ubuntu precise
On 01/12/12 11:21, Daniel Farina wrote: On Fri, Nov 30, 2012 at 12:38 PM, Bruce Momjian br...@momjian.us wrote: Or Debian. Not sure what would justify use of Ubuntu as a server, except wanting to have the exact same OS as their personal computers. We have switched from Debian to Ubuntu: there is definitely non-zero value in the PPA hosting (although it's rather terrible in many ways), regular LTS releases (even if you choose not to use them right away, and know they are somewhat buggy at times), and working with AWS and Canonical as organizations (that, most importantly, can interact directly without my own organization) on certain issues. For example, this dog of a bug: https://bugs.launchpad.net/ubuntu/+source/linux-ec2/+bug/929941 I also frequently take advantage of Debian unstable for backporting of specific packages that are very important to me, so there's a lot of value to me in Ubuntu being quite similar to Debian. In fact, even though I say we 'switched', it's not as though we re-did some entrenched systems from Debian to Ubuntu -- rather, we employ both systems at the same time and I don't recall gnashing of teeth about that, because they are very similar. Yet, there is a clear Ubuntu preference for new systems made today and, to wit, I can't think of anyone with more than the most mild preference for Debian. Conversely, I'd say the preference for Ubuntu for the aforementioned reasons is clear but moderate at most. Also, there's the similarity to the lap/desktop environment. Often cited with some derision, yet it does add a lot of value, even if people run slightly newer Ubuntus on their non-production computer. +1 We have gone through pretty much the same process in the last couple of years. Most of our new systems run Ubuntu, some Debian. There is definitely value in running the same system on the desktop too - often makes bug replication ridiculously easy (no having to find the appropriate test environment, ask if I can hammer/punish/modify it etc etc, and no need even spin up a VM). 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
Re: [PERFORM] Optimize update query
Hmm - not strictly true as stated: 1 SSD will typically do 500MB/s sequential read/write. 1 HDD will be lucky to get a 1/3 that. We are looking at replacing 4 to 6 disk RAID10 arrays of HDD with a RAID1 pair of SSD, as they perform about the same for sequential work and vastly better at random. Plus they only use 2x 2.5 slots (or, ahem 2x PCIe sockets), so allow smaller form factor servers and save on power and cooling. Cheers Mark On 30/11/12 23:07, Vitalii Tymchyshyn wrote: Oh, yes. I don't imagine DB server without RAID+BBU :) When there is no BBU, SSD can be handy. But you know, SSD is worse in linear read/write than HDD. Best regards, Vitalii Tymchyshyn 2012/11/30 Mark Kirkwood mark.kirkw...@catalyst.net.nz mailto:mark.kirkw...@catalyst.net.nz Most modern SSD are much faster for fsync type operations than a spinning disk - similar performance to spinning disk + writeback raid controller + battery. However as you mention, they are great at random IO too, so Niels, it might be worth putting your postgres logs *and* data on the SSDs and retesting. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance