At 05:15 AM 8/17/2005, Ulrich Wisser wrote:

thanks for all your suggestions.

I can see that the Linux system is 90% waiting for disc io.

A clear indication that you need to improve your HD IO subsystem if possible.

At that time all my queries are *very* slow.

To be more precise, your server performance at that point is essentially equal to your HD IO subsystem performance.

 My scsi raid controller and disc are already the fastest available.

Oh, REALLY?  This is the description of the system you gave us:

"We have a box with
Linux Fedora Core 3, Postgres 7.4.2
Intel(R) Pentium(R) 4 CPU 2.40GHz
2 scsi 76GB disks (15.000RPM, 2ms)"

The is far, Far, FAR from the "the fastest available" in terms of SW, OS, CPU host, _or_ HD subsystem.

The "fastest available" means
1= you should be running PostgreSQL 8.0.3
2= you should be running the latest stable 2.6 based kernel
3= you should be running an Opteron based server
4= Fibre Channel HDs are slightly higher performance than SCSI ones.

Your description of you workload was:
"one of our services is click counting for on line advertising. We do this by importing Apache log files every five minutes. This results in a lot of insert and delete statements. At the same time our customers shall be able to do on line reporting."

There are two issues here:
1= your primary usage is OLTP-like, but you are also expecting to do reports against the same schema that is supporting your OLTP-like usage. Bad Idea. Schemas that are optimized for reporting and other data mining like operation are pessimal for OLTP-like applications and vice versa. You need two schemas: one optimized for lots of inserts and deletes (OLTP-like), and one optimized for reporting (data-mining like).

2= 2 spindles, even 15K rpm spindles, is minuscule. Real enterprise class RAID subsystems have at least 10-20x that many spindles, usually split into 6-12 sets dedicated to different groups of tables in the DB. Putting xlog on its own dedicated spindles is just the first step.

The absolute "top of the line" for RAID controllers is something based on Fibre Channel from Xyratex (who make the RAID engines for EMC and NetApps), Engino (the enterprise division of LSI Logic who sell mostly to IBM. Apple has a server based on an Engino card), or dot-hill (who bought Chaparral among others). I suspect you can't afford them even if they would do business with you. The ante for a FC-based RAID subsystem in this class is in the ~$32K to ~$128K range, even if you buy direct from the actual RAID HW manufacturer rather than an OEM like EMC, IBM, or NetApp who will 2x or 4x the price. OTOH, these subsystems will provide OLTP or OLTP-like DB apps with performance that is head-and-shoulders better than anything else to be found. Numbers like 50K-200K IOPS. You get what you pay for.

In the retail commodity market where you are more realistically going to be buying, the current best RAID controllers are probably the Areca cards ( ). They come darn close to saturating the Real World Peak Bandwidth of a 64b 133MHz PCI-X bus and have better IOPS numbers than their commodity brethren. However, _none_ of the commodity RAID cards have IOPS numbers anywhere near as high as those mentioned above.

To avoid aggregating to many rows, I already made some aggregation tables which will be updated after the import from the Apache logfiles. That did help, but only to a certain level.

I believe the biggest problem is disc io. Reports for very recent data are quite fast, these are used very often and therefor already in the cache. But reports can contain (and regulary do) very old data. In that case the whole system slows down. To me this sounds like the recent data is flushed out of the cache and now all data for all queries has to be fetched from disc.

I completely agree. Hopefully my above suggestions make sense and are of use to you.

My machine has 2GB memory,

...and while we are at it, OLTP like apps benefit less from RAM than data mining ones, but still 2GB of RAM is just not that much for a real DB server...

Ron Peacetree

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


Reply via email to