In response to Brian Maguire <[EMAIL PROTECTED]>:
> Hi,
>
> We're trying to figure out why we're getting poor query performance on a
> particular database running on a 64 bit Solaris box. The info for the poor
> database is:
>
> Red Hat Enterprise Linux AS release 4 (Nahant Update 2) Linux vl-sfv40z-001
> 2.6.9-22.0.2.ELsmp #1 SMP Thu Jan 5 17:11:56 EST 2006 x86_64 x86_64 x86_64
> GNU/Linux
>
> 16Gb ram.
>
> Postgres version 8.1.2
I don't know how you can expect older technology to run faster than
newer, more optimized technology.
>
> Database size is about 7 Gigs.
>
> Live lines in config:
> ----------------------------------------
> max_connections = 500
> shared_buffers = 21760
^^^^^
With 16G of RAM, this is so small as to be laughable.
> work_mem = 2048
This as well.
There's a lot of detail missing, but I wouldn't even try to diagnose any
more until you've tuned those numbers closer to sanity.
Also, did you vacuum analyze prior to running the speed test?
> max_fsm_pages = 50000
> checkpoint_segments = 125
> effective_cache_size = 262144 # =2GB typically 8KB each
> redirect_stderr = on # Enable capturing of stderr into log
> log_directory = '/var/log/pglogs'
> log_truncate_on_rotation = on # If on, any existing log file of the
> same
> log_rotation_size = 10240
> log_min_duration_statement = 4000
> stats_command_string = on
> lc_messages = 'en_US.UTF-8' # locale for system error
> message
> lc_monetary = 'en_US.UTF-8' # locale for monetary
> formatting
> lc_numeric = 'en_US.UTF-8' # locale for number formatting
> lc_time = 'en_US.UTF-8' # locale for time formatting
>
> We've already ruled out an I/O issue. The disk is running FAST.
>
> We know it's running poorly because when we put a copy of the database on a
> lesser hardware 32 bit server, it runs TEN TIMES faster.
>
> Here are the relevant issues with the FAST server:
>
> Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Linux vl-filesrv-001
> 2.6.9-42.0.8.ELsmp #1 SMP Tue Jan 23 13:01:26 EST 2007 i686 i686 i386
> GNU/Linux
>
> Dell dual CPU
> 4GB ram.
>
>
> Postgres version: 8.2.3
>
> live config lines:
>
> max_connections = 100 # (change requires restart)
> shared_buffers = 24MB # min 128kB or max_connections*16kB
> max_fsm_pages = 153600 # min max_fsm_relations*16, 6 bytes each
> redirect_stderr = on # Enable capturing of stderr into log
> log_directory = 'pg_log' # Directory where log files are
> writtenlog_truncate_on_rotation = on # If on, any existing log file
> of the same
> log_rotation_age = 1d # Automatic rotation of logfiles will
> log_rotation_size = 0 # Automatic rotation of logfiles will
> datestyle = 'iso, mdy'
> lc_messages = 'en_US.UTF-8' # locale for system error
> message
> lc_monetary = 'en_US.UTF-8' # locale for monetary
> formatting
> lc_numeric = 'en_US.UTF-8' # locale for number formatting
> lc_time = 'en_US.UTF-8' # locale for time formatting
> --- --- --- ---
> I explain the characterization of fast and slow like this: Slow is taking
> about ten times longer than fast to execute the same query.
>
> If there's any gotcha here that we're not seeing, please point it out. I'm
> flummoxed.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
--
Bill Moran
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
