#10 Query modification for base_cache.inc.php

BASE
closed-rejected
Nerveup
None
5
2006-01-27
2006-01-17
osborne
No

A few modifications for the postgres "insert into"
queries found in base_cache.inc.php I need a second
pair of eyes to insure that my modifications work
exactly like the originals, just faster.

Discussion

  • Nerveup
    Nerveup
    2006-01-17

    Logged In: YES
    user_id=1429350

    I could take a look.
    What this patch does?

    p.s. unified diff would be nicer

     
  • Kevin Johnson
    Kevin Johnson
    2006-01-17

    • labels: 708744 -->
    • milestone: 467930 --> BASE
     
  • Nerveup
    Nerveup
    2006-01-20

    • assigned_to: nobody --> nerveup
     
  • osborne
    osborne
    2006-01-20

    A patch with modified queries to speed up caching.

     
  • osborne
    osborne
    2006-01-20

    Logged In: YES
    user_id=1426836

    The original queries made my system running postgres never
    complete the query.

    The changes are to the joins on the sub-queries. They sped
    up the queries considerably on my system.

    I think I got a unified diff posted now (if you haven't
    already done it yourself).

     
  • Nerveup
    Nerveup
    2006-01-21

    Logged In: YES
    user_id=1429350

    Your modifications work exactly like originals(I mean -
    doesn't break anything),
    I made some benchmarks using psql \timing on tcp events to
    see speed changes.

    On pentium3 700mhz with bunch of memory:
    6000 Alerts: original query: ~1030ms, your query: ~990ms
    3000 Alerts: original query: ~530ms, your query: ~510ms
    1400 Alerts: original query: ~250ms, your query: ~245ms

    On soekris4801 223mhz with few mb of memory:
    150 Alerts: original query: ~400ms, your query: ~550ms
    400 Alerts: original query: ~700ms, your query: ~850ms
    2500 Alerts: original query: ~4320, your query: ~3560ms

    It seems that on faster systems, which has many alerts you
    could feel difference.
    How much alerts are on your system?

     
  • Nerveup
    Nerveup
    2006-01-21

    Logged In: YES
    user_id=1429350

    On soekris4801 223mhz with few mb of memory:
    err:
    2500 Alerts: original query: ~3560ms, your query: ~4320ms
    plus:
    7000 Alerts: original query: ~9150ms, your query: ~9250ms

     
  • osborne
    osborne
    2006-01-23

    Logged In: YES
    user_id=1426836

    With the rates you are showing me, I must have something set
    up wrong. On my 2 ghz, 64 bit AMD, with 1 gig of memory,
    i'm getting about 4000 tcp alerts in 9156ms. Not even close
    to your 700mhz machine.

    Now I have to go figure out what I messed up.

    Thanks for the input.

     
  • Nerveup
    Nerveup
    2006-01-23

    Logged In: YES
    user_id=1429350

    Default postgres.conf config is written to run on slowest pc
    with minumum of ram.
    On your machine postgres should fly using basic performance
    configuration tips:
    http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

     
  • Nerveup
    Nerveup
    2006-01-27

    • status: open --> closed-rejected