1

simple question - what is the best way to improve a database's performance using hardware only?

in this situation a few very very large tables are being queried very regularly by 1-4 processes at a time.

we are executing thousands of queries, many of which take +10s to return, and all of which return only a small quantity of data. this suggests to me that HDD seek time is the bottleneck.

as part of this process, we also need to create summary tables from our raw data tables. one of these queries may take hours to run.

please assume that all software/database optimisation has been done already.

Assume this because we have spent some time doing code/db optimisation and are ready to spend some of the budget on hardware. i understand that more software/db optimisation is possible but that is not the current focus.

we do not run out of ram at the moment, but could possibly allocate more to the DB.

the current platform is windows, this may change depending on the hardware solution.

the database is postres 8.4.

thanks.

6
  • 1
    What database software? What platform? How large is "very very large"? What optimizations have been done (I'm sure you haven't thought of all of them.)
    – Joe
    Dec 12, 2009 at 5:03
  • 6
    "please assume that all software/database optimisation has been done already." what a silly assumption to make.
    – Sneakyness
    Dec 12, 2009 at 5:11
  • 2
    That's an odd question. When you did "all software/database optimisation", what did you optimize for? A database server is a system, so pick hardware to work well with the "software/database optimisation" you did.
    – Ken
    Dec 12, 2009 at 5:22
  • 1
    If the database itself is assumed to be optimal, then the answer is always "more spindles" or "more ram" Dec 12, 2009 at 13:33
  • 4
    "we are executing thousands of queries, many of which take +10s to return, and all of which return only a small quantity of data" - to me that points to poor indexing, not hardware Dec 14, 2009 at 23:25

5 Answers 5

9

What's the performance bottleneck? The usual culprits are:

  • If the system is I/O bound, adding more CPUs won't help. Adding more memory might increase the portion of the database that can be cached in memory, but the best way to improve performance will be to increase the I/O bandwidth.
  • If the system is CPU bound, then adding more cores will help if the DBMS software can make good use of them. Otherwise, increasing the speed of the CPUs will help.
  • If the system is memory bound - the bottleneck is getting the data from RAM to the CPU - then improving the memory bandwidth will improve the performance.

Note that when you manage to relieve one performance bottleneck, one of the others becomes the new performance bottleneck.

On most systems running database with seriously large data volumes being scanned, the system is I/O bound. Adding more disk controllers and spreading the data across them increases the parallelism available for disk I/O, usually improving performance.

Don't forget that the most dramatic improvements may be algorithmic - if you can answer the question two ways, one scanning 1 TB of data and one scanning just 1 KB, the second is going to outperform the first, regardless of what you do with the hardware.


Elaborating on disk controllers - as requested.

A given disk controller can only transfer so much data from disk to memory in a second, typically using DMA. To increase the bandwidth, you can spread your data over multiple disks controlled by separate controllers. Then, if the system architecture allows it, you can get more data per second from disks into the main memory - thus speeding up operations. Note that if all the data in the database is on a single disk, then extra controllers do not help. If the DBMS cannot trigger parallel reading from the separate disks, then extra controllers do not help. So, whether the extra controllers will help depends on your hardware and your software and the organization of the data in the database.

2
  • your comment re disk controllers sounds about right .. care to elaborate on that - i've edited the question to add more info. thx.
    – pstanton
    Dec 13, 2009 at 21:19
  • 1
    Also keep in mind as you improve one area of performance in isolation it can place pressure on another area that wasn't previously a bottleneck. For example if your CPUs were a constraint and you added more cores then your I/O system might become a bottleneck as you are able to drive it harder.
    – Sim
    Dec 14, 2009 at 22:58
3
  1. RAM
  2. Faster CPU
  3. Store the database on a RAID0 (or RAID10 if redundancy is needed) with as many drives as you can.
  4. Multicore processors.
  5. Increased sizes of L0 and L1 caches.
5
  • please elaborate on 5
    – pstanton
    Dec 13, 2009 at 21:22
  • 1
    RAID0 should never be used - if any drive fails, your data is toast
    – warren
    Sep 13, 2011 at 17:44
  • @warren: Never say never. For some applications, the tradeoffs may be acceptable.
    – wallyk
    Sep 13, 2011 at 19:41
  • @wallyk - I suppose there's some flaky, footnote-to-an-appendix-situation where it could be "ok" ... but since it's only striping with no data integrity, ANY disk issues will be catastrophic. Use RAID5 or 6 (or 15 or 16)
    – warren
    Sep 13, 2011 at 22:10
  • 1
    It is quite common. For example when you run a shardf of 5+ replicated servers a raid 0 may be good enough. A server fails? good, rebuild it. naturally keep the master server not on raid 0.
    – TomTom
    Dec 2, 2011 at 19:30
2

I would recommend collecting some basic performance metrics and finding the bottlenecks in your current system as a first step. That may give you a good idea where your money is best spend--I/O, memory, or processing power.

That said--from my experience, the most common bottleneck for really large tables is Disk I/O. If "all software/database optimisation has been done already" did not include utilizing proper partitioning strategies, do some research on that topic. If your disk subsystem is old, consider upgrading it.

2
  • yes, i believe the disk IO is the bottleneck. i've added more info to the question. the disk is not old but is a stock standard SATA in a desktop PC.
    – pstanton
    Dec 13, 2009 at 21:20
  • THE DISc isa problem Database server run on many discs. 10, 20+. I have seen 200 disc systems.
    – TomTom
    Dec 2, 2011 at 19:31
1

As jadew suggested, the more informaiton that is stored in the RAM the faster it is. Also, you may want to take a look at using SSDs, that would speed up things, however you do need to do a study on the lifecycle of those as a db storage medium. They go dead after so many writes.

0

more RAM should do it, of course you'll have to tell the db engine to use that additional RAM :)

1
  • 3
    Maybe. Maybe not. Depends on the OS and memory management limitations of said OS, and similarly of those capabilities of the SQL engine.
    – Joe
    Dec 12, 2009 at 5:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .