Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004

    Unhappy Unanswered: How to Speed up my DB ?!?

    is there a good way to Speed up my DB (postgreSQL with postGis includes geos and proj4) except Gist (is allready in use ;P ) ?
    My DB contains a lot of geographical coordinates ...

    can anybody help me ?
    [/.. Höre alles, glaube wenig, sage nichts ..\]

  2. #2
    Join Date
    Jun 2004
    Database tuning is a fairly complex issue and we would need to know more in order to help speed things up.

    For example:

    On Hardware:
    * What kind of machine are you running on (Intel P4, Mac G5, Sun Sparc, etc.)? How many CPUs? How fast are the CPUs?
    * How much RAM do you have?
    * How many and what kind of disks do you have? IDE? SCSI? Fibre Channel? Software RAID? Hardware RAID? Hardware RAID with cache?
    * Have you separated out the WAL logs onto their own disks?

    On Software:
    * What operating system is this running under? Windows? AIX? MacOS? What version?
    * What version of Postgresql are you running?
    * What filesystem type is it running on? UFS? NTFS? Ext2? Ext3? ReiserFS?

    On Workloads:
    * Is the box being used for other purposes, or just dedicated to serving Pg?
    * If other apps are running, how are they impacting performance?
    * Is your transaction mix mostly read? Mostly write? Random or sequential reads? Random or sequential writes?
    * What is the bottleneck? Is the CPU(s) pegged at 100%? Are the drives at 100%? Is all of the memory being consumed? Is the network saturated?

    On the Application:
    * How is the data laid out? What do the tables look like?
    * How much data is in each table?
    * How stable is the data? Does it change often? Rarely? Never?
    * What fields of which tables are indexed how? Indexes radically slow down INSERT/UPDATE/DELETE and can (but not always) radically speed up SELECT.
    * What do your queries look like? How often are each run? Once per month? Daily? 100 times per second?
    * Have you analyzed the execution path of each query to compare the expected execution cost to actual execution costs? Are there ways to speed up each query?
    * For queries run often (multiple times per second), are you reusing database connections or reconnecting each time? Are you reusing cached queries?

    On Expectations:
    * What portions of the application are too slow?
    * How much faster do you need these to be?
    * Are you trying to reduce latency (response time) of small queries?
    * Are you trying to increase batch throughput of long running queries?
    * Are you trying to increase concurrency, i.e. reduce the slowdown as more users run the application in parallel?

    Often, latency, throughput and concurrency are mutually exclusive, e.g. dropping the latency of a single fast query will reduce the batch throughput and lower concurrency.

    The approach you would take to speed up Pg is dependent on the answers to these questions. Let us know the answers and we will try to help.

  3. #3
    Join Date
    Oct 2004


    thank you for answering , but i do not need to speed up anymore
    [/.. Höre alles, glaube wenig, sage nichts ..\]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts