Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++
On 18/08/11 17:35, Craig Ringer wrote: On 18/08/2011 11:48 AM, Ogden wrote: Isn't this very dangerous? I have the Dell PERC H700 card - I see that it has 512Mb Cache. Is this the same thing and good enough to switch to nobarrier? Just worried if a sudden power shut down, then data can be lost on this option. Yeah, I'm confused by that too. Shouldn't a write barrier flush data to persistent storage - in this case, the RAID card's battery backed cache? Why would it force a RAID controller cache flush to disk, too? If the card's cache has a battery, then the cache is preserved in the advent of crash/power loss etc - provided it has enough charge, so setting 'writeback' property on arrays is safe. The PERC/SERVERRAID cards I'm familiar (LSI Megaraid rebranded models) all switch to write-though mode if they detect the battery is dangerously discharged so this is not normally a problem (but commit/fsync performance will fall off a cliff when this happens)! 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] tunning strategy needed
On 18 Srpen 2011, 0:40, hyelluas wrote: Should I start with replacing the sql with procedures? Should I start with replacing the views with the procedures to save time on recreating an execution plan and parsing? Should I start with tuning server parameters ? Yes, you should start by tuning the server as a whole. Did you just install the DB and restored your database? Have you tuned the config? Tell us what are the basic performance-related parameters, i.e. shared_buffers effective_cache_size checkpoint_segments checkpoint_completion_target work_mem maintainance_work_mem seq_page_cost random_page_cost and more information about the hardware and setup too (RAM, database size). There's a quite nice guide regarding general tuning: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server Tomas -- 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] Raid 5 vs Raid 10 Benchmarks Using bonnie++
On Thu, Aug 18, 2011 at 1:35 AM, Craig Ringer ring...@ringerc.id.au wrote: On 18/08/2011 11:48 AM, Ogden wrote: Isn't this very dangerous? I have the Dell PERC H700 card - I see that it has 512Mb Cache. Is this the same thing and good enough to switch to nobarrier? Just worried if a sudden power shut down, then data can be lost on this option. Yeah, I'm confused by that too. Shouldn't a write barrier flush data to persistent storage - in this case, the RAID card's battery backed cache? Why would it force a RAID controller cache flush to disk, too? The barrier is the linux fs/block way of saying these writes need to be on persistent media before I can depend on them. On typical spinning media disks, that means out of the disk cache (which is not persistent) and on platters. The way it assures that the writes are on persistant media is with a flush cache type of command. The flush cache is a close approximation to make sure it's persistent. If your cache is battery backed, it is now persistent, and there is no need to flush cache, hence the nobarrier option if you believe your cache is persistent. Now, make sure that even though your raid cache is persistent, your disks have cache in write-through mode, cause it would suck for your raid cache to work, but believe the data is safely on disk and only find out that it was in the disks (small) cache, and you're raid is out of sync after an outage because of that... I believe most raid cards will handle that correctly for you automatically. a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- 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] Raid 5 vs Raid 10 Benchmarks Using bonnie++
On Aug 18, 2011, at 2:07 AM, Mark Kirkwood wrote: On 18/08/11 17:35, Craig Ringer wrote: On 18/08/2011 11:48 AM, Ogden wrote: Isn't this very dangerous? I have the Dell PERC H700 card - I see that it has 512Mb Cache. Is this the same thing and good enough to switch to nobarrier? Just worried if a sudden power shut down, then data can be lost on this option. Yeah, I'm confused by that too. Shouldn't a write barrier flush data to persistent storage - in this case, the RAID card's battery backed cache? Why would it force a RAID controller cache flush to disk, too? If the card's cache has a battery, then the cache is preserved in the advent of crash/power loss etc - provided it has enough charge, so setting 'writeback' property on arrays is safe. The PERC/SERVERRAID cards I'm familiar (LSI Megaraid rebranded models) all switch to write-though mode if they detect the battery is dangerously discharged so this is not normally a problem (but commit/fsync performance will fall off a cliff when this happens)! Cheers Mark So a setting such as this: Device Name : /dev/sdb Type: SAS Read Policy : No Read Ahead Write Policy: Write Back Cache Policy: Not Applicable Stripe Element Size : 64 KB Disk Cache Policy : Enabled Is sufficient to enable nobarrier then with these settings? Thank you Ogden -- 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] Raid 5 vs Raid 10 Benchmarks Using bonnie++
On Aug 17, 2011, at 4:17 PM, Greg Smith wrote: On 08/17/2011 02:26 PM, Ogden wrote: I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with the new one we have, which I have configured with RAID 10. The drives are the same (SAS 15K). I tried the new system with ext3 and then XFS but the results seem really outrageous as compared to the current system, or am I reading things wrong? The benchmark results are here: http://malekkoheavyindustry.com/benchmark.html Congratulations--you're now qualified to be a member of the RAID5 sucks club. You can find other members at http://www.miracleas.com/BAARF/BAARF2.html Reasonable read speeds and just terrible write ones are expected if that's on your old hardware. Your new results are what I would expect from the hardware you've described. The only thing that looks weird are your ext4 Sequential Output - Block results. They should be between the ext3 and the XFS results, not far lower than either. Normally this only comes from using a bad set of mount options. With a battery-backed write cache, you'd want to use nobarrier for example; if you didn't do that, that can crush output rates. I have mounted the ext4 system with the nobarrier option: /dev/sdb1 on /var/lib/pgsql type ext4 (rw,noatime,data=writeback,barrier=0,nobh,errors=remount-ro) Yet the results show absolutely a decrease in performance in the ext4 Sequential Output - Block results: http://malekkoheavyindustry.com/benchmark.html However, the Random seeks is better, even more so than XFS... Any thoughts as to why this is occurring? Ogden
Re: [PERFORM] Calculating statistic via function rather than with query is slowing my query
Hi Craig, Fair point. For now, I mean just fast - which is 5-15 seconds, but I'd like to get it down to the 1-2 second range. From the query I provided, I have approximately 30,000 unique keys (what I called primary_id) that I'm grouping by, and each key has a series of numerical values for each of the type_ids. I'm looking at averages, stddev and other statistics across a few hundred type_ids (where agg.type_id in ). The part of the query that varies is the user specified type_ids, which makes it impossible to precalculate my statistics. I'd like this to eventually scale to a million unique keys, and a thousand type_ids. For now Postgres been great for modeling the data, understanding where I hit performance bottle necks, and providing a fast enough user interface. But, I'm definitely starting to think about whether I can cache my data (with millions of keys and thousands of type_ids, the data might be too large), and whether to look into distributed databases (even thought I can't precompute the stats, my queries are easily distributable across multiple processors since each processor could take a batch of keys). I might even want to consider a column oriented database - since my keys don't change often, I could potentially add new columns when there are new type_ids. I've been thinking of looking into memcached or hbase. If you have any suggestions on which options I should explore, I'd greatly appreciate it. Sorry, for veering off topic a bit from postgres. thanks, Anish On Wed, Aug 17, 2011 at 10:32 PM, Craig Ringer ring...@ringerc.id.auwrote: On 18/08/2011 9:03 AM, Anish Kejariwal wrote: Thanks for the help Pavel and Craig. I really appreciate it. I'm going to try a couple of these different options (write a c function, use a sql function with case statements, and use plperl), so I can see which gives me the realtime performance that I need, and works best for clean code in my particular case. Do you really mean realtime? Or just fast? If you have strongly bounded latency requirements, any SQL-based, disk-based system is probably not for you. Especially not one that relies on a statics-based query planner, caching, and periodic checkpoints. I'd be looking into in-memory databases designed for realtime environments where latency is critical. Hard realtime: If this system fails to respond within x milliseconds, all the time, every time, then something will go smash or boom expensively and unrecoverably. Soft realtime: If this system responds late, the late response is expensive or less useful. Frequent late responses are unacceptable but the occasional one might be endurable. Just needs to be fast: If it responds late, the user gets irritated because they're sitting and waiting for a response. Regular long stalls are unacceptable, but otherwise the user can put up with it. You're more concerned with average latency than maximum latency. -- Craig Ringer
[PERFORM] settings input for upgrade
I'm in the process of upgrading from postgres 7.4.8 to 9.0.4 and wanted to run my decisions past some folks who can give me some input on whether my decisions make sense or not. It's basically a LAPP configuration and on a busy day we probably get in the neighborhood of a million hits. Server Info: - 4 dual-core AMD Opteron 2212 processors, 2010.485 MHz - 64GB RAM - 16 67GB RAID 1 drives and 1 464GB RAID 10 drive (all ext3) - Linux 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux There are 3 separate databases: DB1 is 10GB and consists of multiple tables that I've spread out so that the 3 most used have their data and indexes on 6 separate RAID1 drives, the 3 next busiest have data index on 3 drives, and the remaining tables and indexes are on the RAID10 drive. The WAL for all is on a separate RAID1 drive. The others are very write-heavy, started as one table within the original DB, and were split out on an odd/even id # in an effort to get better performance: DB2 is 25GB with data, index, and WAL all on separate RAID1 drives. DB3 is 15GB with data, index, and WAL on separate RAID1 drives. Here are the changes I made to postgres.conf. The only differences between the conf file for DB1 and those for DB2 3 are the port and effective_cache_size (which I made slightly smaller -- 8 GB instead of 10 -- for the 2 write-heavy DBs). The 600 max connections are often idle and don't get explicitly closed in the application. I'm looking at connection pooling as well. autovacuum = on autovacuum_analyze_threshold = 250 autovacuum_freeze_max_age = 2 autovacuum_max_workers = 3 autovacuum_naptime = 10min autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = -1 autovacuum_vacuum_threshold = 250 checkpoint_completion_target = 0.7 checkpoint_segments = 64 checkpoint_timeout = 5min checkpoint_warning = 30s deadlock_timeout = 3s effective_cache_size = 10GB log_autovacuum_min_duration = 1s maintenance_work_mem = 256MB max_connections = 600 max_locks_per_transaction = 64 max_stack_depth = 8MB shared_buffers = 4GB vacuum_cost_delay = 10ms wal_buffers = 32MB wal_level = minimal work_mem = 128MB ANY comments or suggestions would be greatly appreciated. Thank you, Midge
Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++
On 19/08/11 02:09, Ogden wrote: On Aug 18, 2011, at 2:07 AM, Mark Kirkwood wrote: On 18/08/11 17:35, Craig Ringer wrote: On 18/08/2011 11:48 AM, Ogden wrote: Isn't this very dangerous? I have the Dell PERC H700 card - I see that it has 512Mb Cache. Is this the same thing and good enough to switch to nobarrier? Just worried if a sudden power shut down, then data can be lost on this option. Yeah, I'm confused by that too. Shouldn't a write barrier flush data to persistent storage - in this case, the RAID card's battery backed cache? Why would it force a RAID controller cache flush to disk, too? If the card's cache has a battery, then the cache is preserved in the advent of crash/power loss etc - provided it has enough charge, so setting 'writeback' property on arrays is safe. The PERC/SERVERRAID cards I'm familiar (LSI Megaraid rebranded models) all switch to write-though mode if they detect the battery is dangerously discharged so this is not normally a problem (but commit/fsync performance will fall off a cliff when this happens)! Cheers Mark So a setting such as this: Device Name : /dev/sdb Type: SAS Read Policy : No Read Ahead Write Policy: Write Back Cache Policy: Not Applicable Stripe Element Size : 64 KB Disk Cache Policy : Enabled Is sufficient to enable nobarrier then with these settings? Hmm - that output looks different from the cards I'm familiar with. I'd want to see the manual entries for Cache Policy=Not Applicable and Disk Cache Policy=Enabled to understand what the settings actually mean. Assuming Disk Cache Policy=Enabled means what I think it does (i.e writes are cached in the physical drives cache), this setting seems wrong if your card has on board cache + battery, you would want to only cache 'em in the *card's* cache (too many caches to keep straight in one's head, lol). 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] Raid 5 vs Raid 10 Benchmarks Using bonnie++
On 19/08/11 12:52, Mark Kirkwood wrote: On 19/08/11 02:09, Ogden wrote: On Aug 18, 2011, at 2:07 AM, Mark Kirkwood wrote: On 18/08/11 17:35, Craig Ringer wrote: On 18/08/2011 11:48 AM, Ogden wrote: Isn't this very dangerous? I have the Dell PERC H700 card - I see that it has 512Mb Cache. Is this the same thing and good enough to switch to nobarrier? Just worried if a sudden power shut down, then data can be lost on this option. Yeah, I'm confused by that too. Shouldn't a write barrier flush data to persistent storage - in this case, the RAID card's battery backed cache? Why would it force a RAID controller cache flush to disk, too? If the card's cache has a battery, then the cache is preserved in the advent of crash/power loss etc - provided it has enough charge, so setting 'writeback' property on arrays is safe. The PERC/SERVERRAID cards I'm familiar (LSI Megaraid rebranded models) all switch to write-though mode if they detect the battery is dangerously discharged so this is not normally a problem (but commit/fsync performance will fall off a cliff when this happens)! Cheers Mark So a setting such as this: Device Name : /dev/sdb Type: SAS Read Policy : No Read Ahead Write Policy: Write Back Cache Policy: Not Applicable Stripe Element Size : 64 KB Disk Cache Policy : Enabled Is sufficient to enable nobarrier then with these settings? Hmm - that output looks different from the cards I'm familiar with. I'd want to see the manual entries for Cache Policy=Not Applicable and Disk Cache Policy=Enabled to understand what the settings actually mean. Assuming Disk Cache Policy=Enabled means what I think it does (i.e writes are cached in the physical drives cache), this setting seems wrong if your card has on board cache + battery, you would want to only cache 'em in the *card's* cache (too many caches to keep straight in one's head, lol). FWIW - here's what our ServerRaid (M5015) output looks like for a RAID 1 array configured with writeback, reads not cached on the card's memory, physical disk caches disabled: $ MegaCli64 -LDInfo -L0 -a0 Adapter 0 -- Virtual Drive Information: Virtual Drive: 0 (Target Id: 0) Name: RAID Level : Primary-1, Secondary-0, RAID Level Qualifier-0 Size: 67.054 GB State : Optimal Strip Size : 64 KB Number Of Drives: 2 Span Depth : 1 Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU Access Policy : Read/Write Disk Cache Policy : Disabled Encryption Type : None -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
