Re: [PERFORM] Six PostgreSQL questions from a pokerplayer
On 7/5/09 11:13 PM, Mark Kirkwood mar...@paradise.net.nz wrote: Craig Ringer wrote: On Sat, 2009-07-04 at 11:51 -0700, Patvs wrote: With 4 regular harddisks in RAID0 you get great read/write speeds, but the SSDs excel in IO/s and a 0.1ms access time. ... but are often really, really, really, really slow at writing. The fancier ones are fast at writing but generally slow down over time. Also, (probably pointing out the obvious here) to be on the safe side you should avoid RAID0 for any data that is important to you - as it's pretty easy to get one bad disk straight from new! With respect to SSD's one option for a small sized database is 2xSSD in RAID1 - provided they are the *right* SSD that is, which at this point in time seems to be the Intel X25E. Note that I have not benchmarked this configuration, so no guarantees that it (or the Intel SSDs themselves) are as good as the various on-the-web tests indicate! There is no reason to go RAID 1 with SSD's if this is an end-user box and the data is recoverable. Unlike a hard drive, a decent SSD isn't expected to go bad. I have deployed over 150 Intel X25-M's and they all work flawlessly. Some had the 'slowdown' problem due to how they were written to, but the recent firmware fixed that. At this point, I consider a single high quality SSD as more fault tolerant than software raid-1. Unless there are lots of writes going on (I'm guessing its mostly read, given the description) a single X25-M will make the DB go very fast regardless of random or sequential access. If the system is CPU bound, then getting a SSD like that won't help as much. But I'd be willing to bet that in a normal PC or workstation I/O is the limiting factor. Some tuning of work_mem and shared_buffers might help some too. Use some monitoring tools (PerfMon 'Physical Disk' stats on windows) to see if normal use is causing a lot of disk access. If so, and especially if its mostly reads, an Intel X-25M will make a huge difference. If there is lots of writes, an X-25E will do but its 40% the space for the same price. regards Mark -- Sent via pgsql-performance mailing list (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] Six PostgreSQL questions from a pokerplayer
On 7/6/09 1:43 AM, Scott Carey sc...@richrelevance.com wrote: On 7/5/09 11:13 PM, Mark Kirkwood mar...@paradise.net.nz wrote: Craig Ringer wrote: On Sat, 2009-07-04 at 11:51 -0700, Patvs wrote: There is no reason to go RAID 1 with SSD's if this is an end-user box and the data is recoverable. Unlike a hard drive, a decent SSD isn't expected to go bad. Clarification -- normal hard drives are expected to have a chance of dying within the first few months, or days. SSD's are expected to wear down slowly and die eventually -- but better ones will do so by entering a read-only state. -- 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] Six PostgreSQL questions from a pokerplayer
On Sat, Jul 4, 2009 at 7:51 PM, Patvspa...@chello.nl wrote: -4 One a scale from 1 to 10, how significant are the following on performance increase: -[ ] Getting a faster harddisk (RAID or a SSD) -[ ] Getting a faster CPU -[ ] Upgrading PostgreSQL (8.2 and 8.3) to 8.4 -[ ] Tweaking PostgreSQL (increasing # shared_buffers, wal_buffers, effective_cache_size, etc.) -[10!] Something else? It sounds like you have specific performance problems you're trying to address. Given the use case it seems surprising that you're looking at such heavy-duty hardware. It seems more likely that PokerTracker/Holdem Manager is missing some indexes in its schema or that some queries could be tweaked to run more efficiently. Perhaps if you set log_statement_duration and send any slow queries here we would find a problem that could be fixed. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Six PostgreSQL questions from a pokerplayer
* Craig Ringer (cr...@postnewspapers.com.au) wrote: What that does mean, though, is that if you don't have significantly more RAM than a 32-bit machine can address (say, 6 to 8 GB), you should stick with 32-bit binaries. I'm not sure this is always true since on the amd64/em64t platforms you'll get more registers and whatnot in 64-bit mode which can offset the pointer size increases. Thanks, Stephen signature.asc Description: Digital signature
Re: [PERFORM] Six PostgreSQL questions from a pokerplayer
On Mon, Jul 6, 2009 at 2:26 PM, Greg Smithgsm...@gregsmith.com wrote: 6) Normally to change the locale you have to shutdown the database, delete its data directory, and then run the initdb command with appropriate options to use an alternate locale. I thought the one-click installer handled that though--the screen shots at http://www.enterprisedb.com/learning/pginst_guide.do show the Advanced Options page allowing one to set the locale. This is really the wrong list for that questions--if you still have trouble there, try sending something with *just* that one to the pgsql-general list instead. From the replies you've gotten here you can see everyone is fixed on the performance questions, and this one is buried at the bottom of your long message. On Windows, the installer will always use utf-8, as it's the only encoding we know should work with any locale on that platform (and there's no easy way of figuring out other combinations without trying them). We intentionally don't make SQL_ASCII available, as we consider that to be an 'expert' choice which regularly gets misused. To get round that if you really need to, either manually init a new cluster using initdb, or do something like: CREATE DATABASE foo WITH ENCODING 'SQL_ASCII' TEMPLATE template0; to get a single database in SQL_ASCII. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Six PostgreSQL questions from a pokerplayer
On 07/06/2009 06:23 AM, Stephen Frost wrote: * Craig Ringer (cr...@postnewspapers.com.au) wrote: What that does mean, though, is that if you don't have significantly more RAM than a 32-bit machine can address (say, 6 to 8 GB), you should stick with 32-bit binaries. I'm not sure this is always true since on the amd64/em64t platforms you'll get more registers and whatnot in 64-bit mode which can offset the pointer size increases. Which leads to other things like faster calling conventions... Even if you only have 4 GB of RAM, the 32-bit kernel needs to fight with low memory vs high memory, whereas 64-bit has a clean address space. All things being equal, I recommend 64-bit. Cheers, mark -- Mark Mielkem...@mielke.cc
Re: [PERFORM] Six PostgreSQL questions from a pokerplayer
On Mon, 2009-07-06 at 15:27 -0400, Mark Mielke wrote: Even if you only have 4 GB of RAM, the 32-bit kernel needs to fight with low memory vs high memory, whereas 64-bit has a clean address space. That's a good point. The cutoff is probably closer to 2G or at most 3G. Certainly it's madness to use hacks like PAE to gain access to the RAM behind the PCI address space rather than just going 64-bit ... unless you have a really pressing reason, at least. It's also nice that on a 64 bit machine, there's no 2G/2G or 3G/1G userspace/kernelspace address mapping split to limit your app's memory use. I seem to recall that Windows uses 2G/2G which can be painfully limiting for memory-hungry applications. Personally, I'd probably go 64-bit on any reasonably modern machine that could be expected to have more than 2 or 3 GB of RAM. Then again, I can't imagine willingly building a production database server for any non-trivial (ie a couple of gigs) database with less than 8GB of RAM with RAM prices so absurdly low. Skip-lunch-to-afford-more-RAM low. -- 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] Six PostgreSQL questions from a pokerplayer
On Mon, Jul 6, 2009 at 10:51 PM, Craig Ringercr...@postnewspapers.com.au wrote: Personally, I'd probably go 64-bit on any reasonably modern machine that could be expected to have more than 2 or 3 GB of RAM. Then again, I can't imagine willingly building a production database server for any non-trivial (ie a couple of gigs) database with less than 8GB of RAM with RAM prices so absurdly low. Skip-lunch-to-afford-more-RAM low. Exactly, I was pricing out a new db server at work, and the difference in cost on a $7000 or so machine was something like $250 or so to go from 16G to 32G of RAM. I also can't imagine running a large pgsql server on windows, even 64 bit windows. -- 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] Six PostgreSQL questions from a pokerplayer
On Sat, 2009-07-04 at 11:51 -0700, Patvs wrote: I can see two databases in my pgAdmin: postgres and HoldemManager. All the poker data (about 30 GB of data) is in the HoldemManager database. Does the quote above (if true?) means, having a 2 Ghz single core or a Xeon 2x quadcore (8x 2 Ghz cores) will make no real difference for my performance? What matters isn't the number of databases, but the number of connections. Any given connection can use at most one full core. If you have only one actively working connection you will still gain a bit of performance from having a second core that can do other misc work for the OS, I/O management and general housekeeping so that the first core can be fully dedicated to the active pg backend. More than that probably won't gain you anything. If you want to improve performance, first learn about where your code is bottlenecked. Is it even CPU-limited? Often databases are really limited by disk I/O performance rather than CPU time. If it is CPU-limited, you might gain from having fewer faster cores, and/or significantly faster RAM. If it's not CPU-limited, you'd be wasting time effort and money upgrading those parts. -2 In the recent 8.3 vs 8.4 benchmarks, 8.4. was much faster than 8.3 running on a 16 and 32 core server (with 64GB RAM). With 8 cores, they were about the same speed. Does this mean on a normal single core computer, there will be NO NOTICABLE performance increase in 8.3 vs 8.4 and even 8.2? Benchmark it and see. It'll be rather workload-dependent. I have no idea what the maximum amount of RAM is, my database uses. But what exactly will take twice as much space? Does this mean a simple database uses double the amount of RAM on a 64 bit system? Absolutely not. Certain data structures take up more room because of alignment/padding concerns, pointer size increases, etc. That does mean that you can fit fewer of them into a given amount of memory, but it's not a simple doubling by any stretch. What that does mean, though, is that if you don't have significantly more RAM than a 32-bit machine can address (say, 6 to 8 GB), you should stick with 32-bit binaries. -4 One a scale from 1 to 10, how significant are the following on performance increase: -[ ] Getting a faster harddisk (RAID or a SSD) -[ ] Getting a faster CPU -[ ] Upgrading PostgreSQL (8.2 and 8.3) to 8.4 -[ ] Tweaking PostgreSQL (increasing # shared_buffers, wal_buffers, effective_cache_size, etc.) -[10!] Something else? Very workload dependent. Analyse what parts of your system are busiest and which are largely idle while Pg is working hard, then consider upgrading the busy bits. Tweaking Pg again depends a lot on workload. Sometimes you won't gain much, sometimes you'll see incredible gains (say, if you increase sort/working memory\ so a sort that used to spill to disk can instead be done in RAM). If you have very few connections and they do really complex queries, you might benefit from dramatically increasing work mem etc. -[ ] Does NOT effect me, but I was wondering what a switch from Windows to LINUX/Solaris does for professional server users in terms of performance. Not a bad plan, honestly. Pg is just more mature on UNIX/Linux at this point. -5 The IO operations/s performance of your harddisk vs read/write speeds vs access time? What is more important? Depends on workload. If you're doing lots of sequential scans, you want really fast sequential reads. If you're doing lots of index scans etc, you will benefit from both sequential read speed and access time. If you have particular queries you note are slow, consider running them with EXPLAIN ANALYZE to see what their query plans are. What disk access patterns are the queries resulting in? Do they have sorts spilling to disk? etc. With 4 regular harddisks in RAID0 you get great read/write speeds, but the SSDs excel in IO/s and a 0.1ms access time. ... but are often really, really, really, really slow at writing. The fancier ones are fast at writing but generally slow down over time. What is the most usefull for which situations? Depends on your workload, see above. -- 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
