[PERFORM] Six PostgreSQL questions from a pokerplayer
I use poker software (HoldemManager) to keep track of the statistics (and show nice graphs) of millions of poker hand histories. This software (also PokerTracker 3) imports all the poker hands in PostgreSQL. The software runs on Windows) only. All of its users have NORMAL PCs. From single-core laptops, to a quadcore desktop at best. Questions: -1 [quote] POSTGRESQL uses a multi-process model. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs.[/quote] 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? And the real performance increase is only for professional servers running multiple databases? Will I greatly benefit from having quad instead of a single-core system? -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? -3 [quote] With PostgreSQL, you could easily have more than 1GB per backend (if necessary) without running out of memory, which significantly pushes away the point when you need to go to 64-bit. In some cases it may actually be better to run a 32-bit build of PostgreSQL to reduce memory usage. In a 64-bit server, every pointer and every integer will take twice as much space as in a 32bit server. That overhead can be significant, and is most likely unnecessary. [/quote] 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? And it's probably better for my 30 GB database to run a 32-bit build of PostgreSQL to reduce memory usage? -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? -[ ] 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. -5 The IO operations/s performance of your harddisk vs read/write speeds vs access time? What is more important? 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. What is the most usefull for which situations? -6 The 8.4.0-1 one-click installer automatically set the encoding to UTF8. With the other installers, I was able to change the encoding to SQL_ASCII during the installation process. How do I solve this after I've installed 8.4.0-1? (I was unable to delete the postgres database, so I couldn't create a new one with the right encoding in 8.4.0-1) -- View this message in context: http://www.nabble.com/Six-PostgreSQL-questions-from-a-pokerplayer-tp24337072p24337072.html Sent from the PostgreSQL - performance mailing list archive at Nabble.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 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
[PERFORM] Bundling postgreSQL with my Java application
Hi, We are bundling PostgreSQL 8.3.7 with our Java based application. We observe that in some systems the Database access becomes very slow after running it for couple of days. We understand that postgresql.conf needs to be adjusted as per the system specification where postgreSQL is running. Is there a utility that we can use that can check the system specification and change the required parameters in postgresql.conf accordingly? Thanks, Saurabh
Re: [PERFORM] Bundling postgreSQL with my Java application
On 07/06/2009 01:48 AM, Saurabh Dave wrote: We are bundling PostgreSQL 8.3.7 with our Java based application. We observe that in some systems the Database access becomes very slow after running it for couple of days. We understand that postgresql.conf needs to be adjusted as per the system specification where postgreSQL is running. Is there a utility that we can use that can check the system specification and change the required parameters in postgresql.conf accordingly? Hi Saurabh: No offense intended - but have you looked at the documentation for postgresql.conf? If you are going to include PostgreSQL in your application, I'd highly recommend you understand what you are including. :-) PostgreSQL 8.4 comes with significantly improved out of the box configuration. I think that is what you are looking for. Specifically, you are probably looking for autovacuum to be enabled. Cheers, mark -- Mark Mielkem...@mielke.cc -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
