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 Database size is about 7 Gigs. Live lines in config: ---------------------------------------- max_connections = 500 shared_buffers = 21760 work_mem = 2048 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.
