Re: [GENERAL] Tuning read ahead continued...
On May 16, 2013, at 5:56 PM, Ramsey Gurley wrote:Hi All,I tried bumping my read ahead up to 4096. Instead of having faster reads, it seems it actually slowed things down. In fact, most of the tuning suggestions I've tried have made little to no difference in the results I get from bonnie++.I've run more tests with bonnie++. I'm beginning to wonder if there's something wrong with my system or my setup. In every test I have run, Seq Reads is faster with read ahead set to 256. If I increase read ahead to 4096 as suggested in Postgresql 9.0 High Performance, I get slower reads and slower writes.Other settings I've made as suggested by the book, /dev/sdb1 / ext3 noatime,errors=remount-ro 0 1 vm.swappiness=0 vm.overcommit_memory=2echo 2 /proc/sys/vm/dirty_ratioecho 1 /proc/sys/vm/dirty_background_ratioHere is 4096 read aheadVersion 1.03e--Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP498088-db1.s 96280M 130123 24 103634 15 277467 14 652.4 1498088-db1.smarthealth.com,96280M,,,130123,24,103634,15,,,277467,14,652.4,1,And here is the default 256 read aheadVersion 1.03e--Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP498088-db1.s 96280M 160881 28 104868 17 286109 17 591.9 0498088-db1.smarthealth.com,96280M,,,160881,28,104868,17,,,286109,17,591.9,0,I also made some zcav plots. They are very flat on 256, which seems to indicate some limiting factor, but they also appear to be consistently *higher* than the 4096 values after about 70GB. Does this look familiar to anyone?
Re: [GENERAL] Tuning read ahead
On May 16, 2013, at 6:01 AM, Shaun Thomas wrote:
On 05/15/2013 08:04 PM, Ramsey Gurley wrote:
My question: Is that advice just for the database drive, or should I
increase read ahead on the OS/WAL disk as well?
Definitely the database drive, but it doesn't hurt to do both. It doesn't
mention it in the book, but if you have a Debian or Ubuntu system, you can
set it up to retain these settings through reboots very easily. The udev
system can be set with rules that can target whole ranges of devices. Here's
one we use:
* In a file named /etc/udev/rules.d/20-pg.rules
ACTION==add|change, KERNEL==sd[a-z],ATTR{queue/read_ahead_kb}=4096
Our systems are also NVRAM based, so we also throw in a NOOP access scheduler:
ACTION==add|change, KERNEL==sd[a-z], ATTR{queue/scheduler}=noop
There's really no reason to do it any other way if you have udev installed.
You *could* put blockdev calls in /etc/rc.local I suppose, but udev applies
rules at device detection, which can be beneficial.
Interesting point. I had not considered whether the setting would be maintained
through reboots. I'll have to google for the appropriate settings on Red Hat.
I assume both. I should ask the same for noatime advice while I'm at
it.
You can probably get away with relatime, which is the default for most modern
systems these days.
I will probably go with noatime on the data drive then. I see where that would
require lots of reads and should not be writing to the drive. In my mind, WAL
should be read much less frequently. Maybe I am wrong about that :-)
Thank you,
Ramsey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tuning read ahead continued...
Hi All, I tried bumping my read ahead up to 4096. Instead of having faster reads, it seems it actually slowed things down. In fact, most of the tuning suggestions I've tried have made little to no difference in the results I get from bonnie++. I'll include a table of values in html. I'm wondering if these are normal values in my case; 4 disk RAID10 Linux ext3 146GB SAS 15K RPM Drive. Title: Benchmarks 4 disk RAID 10 ext3 Red Hat Config Block Out Rewrite Out Block In Random Seeks Read Ahead noatime swappiness overcommit dirty ratio dirty bg ratio Chunk size K/sec %cpu K/sec %cpu K/sec %cpu /sec %cpu 192 noatime 0 2 2 1 96280M 163189 29 102625 17 288505 17 583.4 1 256 default default default 40 10 96280M 160848 28 105445 17 288802 17 588.2 1 256 noatime default default 40 10 96280M 157318 26 106947 17 289369 17 603.7 1 256 noatime 0 2 2 1 96280M 162137 29 104220 17 292700 17 609.3 1 1024 noatime 0 2 2 1 96280M 162908 29 98724 15 267720 13 665.0 0 4096 default default default 40 10 96280M 160507 28 105678 15 277123 13 666.5 1 4096 noatime default default 40 10 96280M 159806 27 106396 15 276359 13 525.1 1 8192 noatime default default 40 10 96280M 122381 21 103858 15 270377 13 658.0 1 Thank you, Ramsey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tuning read ahead
Hi all, I've just gotten into my new database server yesterday and I've started doing database setup and tuning. I'm on a Rackspace Linux server with two raid arrays. Both are ext3. One is a two disk RAID1 I plan on using for WAL and OS, the other is a four disk RAID10 I will use for the data. I read in Postgres 9.0 High Performance that one of the most important parameters I should tune is the device read-ahead. My question: Is that advice just for the database drive, or should I increase read ahead on the OS/WAL disk as well? I assume both. I should ask the same for noatime advice while I'm at it. Is it important to disable atime on the WAL as well as the data? Thanks, Ramsey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC driver versions
Hi all, I'm migrating from postgres 8.3 to 9.2. Included in my application is an old jar for the JDBC driver for the 8.3 database. Do I need a separate build with an up to date JDBC jar for 9.2 while I am testing out a new database or is it safe to use the latest JDBC jar with a very old version of postgres? Thanks, Ramsey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC driver versions
Okay, I saw that, but I just wanted to double check. It seemed odd that there were builds going all the way back to 8.2 if the 9.2 version should work for everything. Thanks for the reassurance :-) Ramsey On Apr 16, 2013, at 1:36 PM, Adrian Klaver wrote: On 04/16/2013 01:13 PM, Ramsey Gurley wrote: Hi all, I'm migrating from postgres 8.3 to 9.2. Included in my application is an old jar for the JDBC driver for the 8.3 database. Do I need a separate build with an up to date JDBC jar for 9.2 while I am testing out a new database or is it safe to use the latest JDBC jar with a very old version of postgres? From here: http://jdbc.postgresql.org/download.html#current Current Version This is the current version of the driver. Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using. It supports Postgresql 7.2 or newer and requires a 1.5 or newer JVM. It contains support for SSL and the javax.sql package. It comes in two flavours, JDBC3 and JDBC4. If you are using the 1.6 or 1.7 JVM, then you should use the JDBC4 version. Thanks, Ramsey -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inconsistent query performance
On Apr 8, 2013, at 7:09 PM, Kevin Grittner wrote: Ramsey Gurley rgur...@smarthealth.com wrote: I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. expression took 20147 ms: SELECT t0.appointment_id, t0.customer_id, t0.event_date, t0.patient_id, t0.transaction_id FROM customer.customer_transactions_detail t0 WHERE (t0.patient_id = 7441 AND t0.customer_id = 2965) Index Scan using customer_id_patient_id_idx on customer_transactions_detail t0 (cost=0.00..10.22 rows=1 width=24) (actual time=35.952..99.487 rows=14 loops=1) Index Cond: ((customer_id = 2965) AND (patient_id = 7441)) Total runtime: 99.537 ms So it took 20 seconds at 12:18pm today, but now it takes ~100ms. Well, often when you get a faster run time when running a query again it is due to caching, but there are other things which can come into play. There could be blocking. There could be a glut of disk writes at checkpoint time which holds up all other disk I/O. Is there a way to make checkpoints happen more frequently so that large ones don't cause two minute delays? You could have had a badly bloated index when the query was run the first time, and a VACUUM command or autovacuum cleaned things up before your explain analyze. I did run a vacuum on the entire database the day before. I don't know if I have auto-vacuuming set up. I'm currently using postgres 8.3.x That was much more vulnerable to the write glut problem than versions which are still in support. I'm in the process of upgrading to 9.2.x. I'm also moving the database to a dedicated machine with more RAM available to the database. So maybe a less crufty setup in another week or two. It's hard to even suggest what steps to take next without knowing the OS, your hardware, or your configuration. Please read these two pages: http://www.postgresql.org/support/versioning/ http://wiki.postgresql.org/wiki/SlowQueryQuestions Thanks for the suggestions Kevin :) -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inconsistent query performance
On Apr 8, 2013, at 8:46 PM, Jeff Janes wrote: On Monday, April 8, 2013, Ramsey Gurley wrote: Hi all, I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. Here is an example: sqlLogging _valuesForSQL ran 116509ms with sql string: I think that your IO subsystem is getting congested on occasion, and that you have a selection bias. You are seeing in your log the occasions on which it is running slow, but not seeing the occasions it is running fast. When you run it manually, you see a result similar to the (invisible, but frequent) times that it did not run slow. I think you're right about this. It seems to run fast most of the time. It's those times it takes 2 minutes to respond to a customer which I'd like to minimize or eliminate. But I think caching has a bit to do with it too. I can go back to the logs from last week and run a slow query and it will frequently be equally slow again. Not always, but enough that I think something else is wrong with my setup. You should monitor with sar, or iostat, or vmstat, or similar. I'll look into that. Thanks Jeff :) Cheers, Jeff
[GENERAL] Inconsistent query performance
Hi all, I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. Here is an example: sqlLogging _valuesForSQL ran 116509ms with sql string: select t1.transaction_id, t1.campaign_definition_id, t1.campaign_detail_number, t2.name as campaign_name, t1.communication_type, t1.delivery_error, t1.call_result into temporary activity_transactions_temp from customer.customer_transactions t1, campaign.campaign_definitions t2 where t1.customer_id = 2577 and t1.timestamp = '04/08/2013' and t1.timestamp '04/09/2013' and t1.campaign_definition_id = 23 and t1.campaign_definition_id = t2.campaign_definition_id order by campaign_name, communication_type; But if I run an explain analyze on that I get Sort (cost=18.08..18.08 rows=1 width=89) (actual time=767.051..767.061 rows=17 loops=1) Sort Key: t2.name, t1.communication_type Sort Method: quicksort Memory: 26kB - Nested Loop (cost=0.00..18.07 rows=1 width=89) (actual time=758.421..766.881 rows=17 loops=1) - Index Scan using timestamp_only_ndx on customer_transactions t1 (cost=0.00..9.79 rows=1 width=69) (actual time=79.771..88.119 rows=17 loops=1) Index Cond: ((timestamp = '2013-04-08 00:00:00'::timestamp without time zone) AND (timestamp '2013-04-09 00:00:00'::timestamp without time zone)) Filter: ((customer_id = 2577) AND (campaign_definition_id = 23)) - Index Scan using campaign_definitions_campaign_definition_id_key on campaign_definitions t2 (cost=0.00..8.27 rows=1 width=24) (actual time=39.922..39.923 rows=1 loops=17) Index Cond: (t2.campaign_definition_id = 23) Total runtime: 770.830 ms Is there anything that can tell me why this query took 117 seconds, and what, if anything, can be done to fix it? Here's another much more simple one: expression took 20147 ms: SELECT t0.appointment_id, t0.customer_id, t0.event_date, t0.patient_id, t0.transaction_id FROM customer.customer_transactions_detail t0 WHERE (t0.patient_id = 7441 AND t0.customer_id = 2965) Index Scan using customer_id_patient_id_idx on customer_transactions_detail t0 (cost=0.00..10.22 rows=1 width=24) (actual time=35.952..99.487 rows=14 loops=1) Index Cond: ((customer_id = 2965) AND (patient_id = 7441)) Total runtime: 99.537 ms So it took 20 seconds at 12:18pm today, but now it takes ~100ms. Thanks, Ramsey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inconsistent query performance
I forgot to mention. I'm currently using postgres 8.3.x On Apr 8, 2013, at 3:08 PM, Ramsey Gurley wrote: Hi all, I'm having issues with slow queries using postgres, and I'm finding some of the issues difficult to reproduce. My application logs slow queries for me, but often, when I go back to run explain analyze on the query it is very fast. I assume this is due to some sort of caching. Here is an example: sqlLogging _valuesForSQL ran 116509ms with sql string: select t1.transaction_id, t1.campaign_definition_id, t1.campaign_detail_number, t2.name as campaign_name, t1.communication_type, t1.delivery_error, t1.call_result into temporary activity_transactions_temp from customer.customer_transactions t1, campaign.campaign_definitions t2 where t1.customer_id = 2577 and t1.timestamp = '04/08/2013' and t1.timestamp '04/09/2013' and t1.campaign_definition_id = 23 and t1.campaign_definition_id = t2.campaign_definition_id order by campaign_name, communication_type; But if I run an explain analyze on that I get Sort (cost=18.08..18.08 rows=1 width=89) (actual time=767.051..767.061 rows=17 loops=1) Sort Key: t2.name, t1.communication_type Sort Method: quicksort Memory: 26kB - Nested Loop (cost=0.00..18.07 rows=1 width=89) (actual time=758.421..766.881 rows=17 loops=1) - Index Scan using timestamp_only_ndx on customer_transactions t1 (cost=0.00..9.79 rows=1 width=69) (actual time=79.771..88.119 rows=17 loops=1) Index Cond: ((timestamp = '2013-04-08 00:00:00'::timestamp without time zone) AND (timestamp '2013-04-09 00:00:00'::timestamp without time zone)) Filter: ((customer_id = 2577) AND (campaign_definition_id = 23)) - Index Scan using campaign_definitions_campaign_definition_id_key on campaign_definitions t2 (cost=0.00..8.27 rows=1 width=24) (actual time=39.922..39.923 rows=1 loops=17) Index Cond: (t2.campaign_definition_id = 23) Total runtime: 770.830 ms Is there anything that can tell me why this query took 117 seconds, and what, if anything, can be done to fix it? Here's another much more simple one: expression took 20147 ms: SELECT t0.appointment_id, t0.customer_id, t0.event_date, t0.patient_id, t0.transaction_id FROM customer.customer_transactions_detail t0 WHERE (t0.patient_id = 7441 AND t0.customer_id = 2965) Index Scan using customer_id_patient_id_idx on customer_transactions_detail t0 (cost=0.00..10.22 rows=1 width=24) (actual time=35.952..99.487 rows=14 loops=1) Index Cond: ((customer_id = 2965) AND (patient_id = 7441)) Total runtime: 99.537 ms So it took 20 seconds at 12:18pm today, but now it takes ~100ms. Thanks, Ramsey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
