PostgreSQL Performance - Need for speed

Author

Need for speed

Ulrich Wisser

2005-08-16, 1:27 pm

Hello,

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.

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)

I did put pg_xlog on another file system on other discs.

Still when several users are on line the reporting gets very slow.
Queries can take more then 2 min.

I need some ideas how to improve performance in some orders of
magnitude. I already thought of a box with the whole database on a ram
disc. So really any idea is welcome.

Ulrich

--
Ulrich Wisser / System Developer

RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
____________________
____________________
____________________
____
http://www.relevanttraffic.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Richard Huxton

2005-08-16, 1:27 pm

John A Meinel

2005-08-16, 1:27 pm

Ulrich Wisser wrote:
> Hello,
>
> 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.

What are you deleting? I can see having a lot of updates and inserts,
but I'm trying to figure out what the deletes would be.

Is it just that you completely refill the table based on the apache log,
rather than doing only appending?
Or are you deleting old rows?

>
> 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)
>
> I did put pg_xlog on another file system on other discs.
>
> Still when several users are on line the reporting gets very slow.
> Queries can take more then 2 min.

If it only gets slow when you have multiple clients it sounds like your
select speed is the issue, more than conflicting with your insert/deletes.

>
> I need some ideas how to improve performance in some orders of
> magnitude. I already thought of a box with the whole database on a ram
> disc. So really any idea is welcome.

How much ram do you have in the system? It sounds like you only have 1
CPU, so there is a lot you can do to make the box scale.

A dual Opteron (possibly a dual motherboard with dual core (but only
fill one for now)), with 16GB of ram, and an 8-drive RAID10 system would
perform quite a bit faster.

How big is your database on disk? Obviously it isn't very large if you
are thinking to hold everything in RAM (and only have 76GB of disk
storage to put it in anyway).

If your machine only has 512M, an easy solution would be to put in a
bunch more memory.

In general, your hardware is pretty low in overall specs. So if you are
willing to throw money at the problem, there is a lot you can do.

Alternatively, turn on statement logging, and then post the queries that
are slow. This mailing list is pretty good at fixing poor queries.

One thing you are probably hitting is a lot of sequential scans on the
main table.

If you are doing mostly inserting, make sure you are in a transaction,
and think about doing a COPY.

There is a lot more that can be said, we just need to have more
information about what you want.

John
=:->

>
> Ulrich
>
>
>

Jeffrey W. Baker

2005-08-16, 1:27 pm

On Tue, 2005-08-16 at 17:39 +0200, Ulrich Wisser wrote:
> Hello,
>
> 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.
>
> We have a box with
> Linux Fedora Core 3, Postgres 7.4.2
> Intel(R) Pentium(R) 4 CPU 2.40GHz

This is not a good CPU for this workload. Try an Opteron or Xeon. Also
of major importance is the amount of memory. If possible, you would
like to have memory larger than the size of your database.

> 2 scsi 76GB disks (15.000RPM, 2ms)

If you decide your application is I/O bound, here's an obvious place for
improvement. More disks == faster.

> I did put pg_xlog on another file system on other discs.

Did that have a beneficial effect?

> Still when several users are on line the reporting gets very slow.
> Queries can take more then 2 min.

Is this all the time or only during the insert?

> I need some ideas how to improve performance in some orders of
> magnitude. I already thought of a box with the whole database on a ram
> disc. So really any idea is welcome.

You don't need a RAM disk, just a lot of RAM. Your operating system
will cache disk contents in memory if possible. You have a very small
configuration, so more CPU, more memory, and especially more disks will
probably all yield improvements.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Alex Turner

2005-08-16, 1:27 pm

Are you calculating aggregates, and if so, how are you doing it (I ask
the question from experience of a similar application where I found
that my aggregating PGPLSQL triggers were bogging the system down, and
changed them so scheduled jobs instead).

Alex Turner
NetEconomist

On 8/16/05, Ulrich Wisser <ulrich. wisser@relevanttraff
ic.se> wrote:
> Hello,
>
> 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.
>
> 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)
>
> I did put pg_xlog on another file system on other discs.
>
> Still when several users are on line the reporting gets very slow.
> Queries can take more then 2 min.
>
> I need some ideas how to improve performance in some orders of
> magnitude. I already thought of a box with the whole database on a ram
> disc. So really any idea is welcome.
>
> Ulrich
>
>
>
> --
> Ulrich Wisser / System Developer
>
> RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
> Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
> ____________________
____________________
____________________
____
> http://www.relevanttraffic.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dennis Bjorklund

2005-08-16, 8:24 pm

On Tue, 16 Aug 2005, Ulrich Wisser wrote:

> Still when several users are on line the reporting gets very slow.
> Queries can take more then 2 min.

Could you show an exampleof such a query and the output of EXPLAIN ANALYZE
on that query (preferably done when the database is slow).

It's hard to say what is wrong without more information.

--
/Dennis Björklund

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ulrich Wisser

2005-08-17, 9:31 am

Hello,

thanks for all your suggestions.

I can see that the Linux system is 90% waiting for disc io. At that time
all my queries are *very* slow. My scsi raid controller and disc are
already the fastest available. The query plan uses indexes and "vacuum
analyze" is run once a day.

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.

My machine has 2GB memory, please find postgresql.conf below.

Ulrich

#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 20000 # min 16, at least max_connections*2,
sort_mem = 4096 # min 64, size in KB
vacuum_mem = 8192 # min 1024, size in KB

# - Free Space Map -

max_fsm_pages = 50000 # min max_fsm_relations*16
, 6 bytes each
max_fsm_relations = 3000 # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_proce
ss = 1000 # min 25
#preload_libraries = ''

#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
wal_buffers = 128 # min 4, 8KB each

# - Checkpoints -

checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-08-17, 1:24 pm

Ulrich Wisser <ulrich. wisser@relevanttraff
ic.se> writes:
> My machine has 2GB memory, please find postgresql.conf below.

> max_fsm_pages = 50000 # min max_fsm_relations*16
, 6 bytes each

FWIW, that index I've been groveling through in connection with your
other problem contains an astonishingly large amount of dead space ---
almost 50%. I suspect that you need a much larger max_fsm_pages
setting, and possibly more-frequent vacuuming, in order to keep a lid
on the amount of wasted space.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jeffrey W. Baker

2005-08-17, 1:24 pm

On Wed, 2005-08-17 at 11:15 +0200, Ulrich Wisser wrote:
> Hello,
>
> thanks for all your suggestions.
>
> I can see that the Linux system is 90% waiting for disc io. At that time
> all my queries are *very* slow. My scsi raid controller and disc are
> already the fastest available.

What RAID controller? Initially you said you have only 2 disks, and
since you have your xlog on a separate spindle, I assume you have 1 disk
for the xlog and 1 for the data. Even so, if you have a RAID, I'm going
to further assume you are using RAID 1, since no sane person would use
RAID 0. In those cases you are getting the performance of a single
disk, which is never going to be very impressive. You need a RAID.

Please be more precise when describing your system to this list.

-jwb

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Josh Berkus

2005-08-17, 1:24 pm

Ulrich,

> 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.

How large is the database on disk?

> My machine has 2GB memory, please find postgresql.conf below.

hmmmm ...
effective_cache_size
?
random_page_cost?
cpu_tuple_cost?
etc.

--
Josh Berkus
Aglio Database Solutions
San Francisco

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

http://www.postgresql.org/docs/faq

Ron

2005-08-17, 8:25 pm

Ron

2005-08-17, 8:25 pm

At 05:15 AM 8/17/2005, Ulrich Wisser wrote:
>Hello,
>
>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.
5= (and this is the big one) YOU NEED MORE SPINDLES AND A HIGHER END
RAID CONTROLLER.

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 ( www.areca.us ). 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?

http://www.postgresql.org/docs/faq

Matthew Nuzum

2005-08-17, 8:25 pm

On 8/17/05, Ron <rjpeace@earthlink.net> wrote:
> At 05:15 AM 8/17/2005, Ulrich Wisser wrote:
....[color=darkred]
> 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).

Ulrich,

If you meant that your disc/scsi system is already the fastest
available *with your current budget* then following Ron's advise I
quoted above will be a good step.

I have some systems very similar to yours. What I do is import in
batches and then immediately pre-process the batch data into tables
optimized for quick queries. For example, if your reports frequenly
need to find the total number of views per hour for each customer,
create a table whose data contains just the totals for each customer
for each hour of the day. This will make it a tiny fraction of the
size, allowing it to fit largely in RAM for the query and making the
indexes more efficient.

This is a tricky job, but if you do it right, your company will be a
big success and buy you more hardware to work with. Of course, they'll
also ask you to create dozens of new reports, but that's par for the
course.

Even if you have the budget for more hardware, I feel that creating an
effective db structure is a much more elegant solution than to throw
more hardware. (I admit, sometimes its cheaper to throw more hardware)

If you have particular queries that are too slow, posting the explain
analyze for each on the list should garner some help.

--
Matthew Nuzum
www.bearfruit.org

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

http://www.postgresql.org/docs/faq

Roger Hand

2005-08-19, 3:26 am

> Ulrich Wisser wrote:
....[color=darkred]
> If you are doing mostly inserting, make sure you are in a transaction,

Well, yes, but you may need to make sure that a single transaction doesn't have too many inserts in it.
I was having a performance problem when doing transactions with a huge number of inserts
(tens of thousands), and I solved the problem by putting a simple counter in the loop (in the Java import code,
that is) and doing a commit every 100 or so inserts.

-Roger
[color=darkred]
> John
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Christopher Browne

2005-08-19, 9:27 am

>> Ulrich Wisser wrote:
> ...
>
> Well, yes, but you may need to make sure that a single transaction
> doesn't have too many inserts in it. I was having a performance
> problem when doing transactions with a huge number of inserts (tens
> of thousands), and I solved the problem by putting a simple counter
> in the loop (in the Java import code, that is) and doing a commit
> every 100 or so inserts.

Are you sure that was an issue with PostgreSQL?

I have certainly observed that issue with Oracle, but NOT with
PostgreSQL.

I have commonly done data loads where they loaded 50K rows at a time,
the reason for COMMITting at that point being "programming paranoia"
at the possibility that some data might fail to load and need to be
retried, and I'd rather have less fail...

It would seem more likely that the issue would be on the Java side; it
might well be that the data being loaded might bloat JVM memory usage,
and that the actions taken at COMMIT time might keep the size of the
Java-side memory footprint down.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.

Jim C. Nasby

2005-08-22, 8:25 pm

RRS (http://rrs.decibel.org) might be of use in this case.

On Tue, Aug 16, 2005 at 01:59:53PM -0400, Alex Turner wrote:
> Are you calculating aggregates, and if so, how are you doing it (I ask
> the question from experience of a similar application where I found
> that my aggregating PGPLSQL triggers were bogging the system down, and
> changed them so scheduled jobs instead).
>
> Alex Turner
> NetEconomist
>
> On 8/16/05, Ulrich Wisser <ulrich. wisser@relevanttraff
ic.se> wrote:
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links