Re: 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