Thread: PLEASE GOD HELP US!
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a developer) for a small web development company. We recently switched over to PostgreSQL after going over some of the powerful features that it holds over MySQL. However, after we launched one site, we slapped ourselves in the head. We have run a message board for a few years now, and have always used MySQL for the backend, but recently switched to PostgreSQL. It has always been lightening fast with MySQL, but has slowed to nearly a halt in terms of online access time. I can't seem to do anything about it!! PLEASE HELP US!! Now, I've read as much as I could about optimizing PostgreSQL for performance, and nothing I do seems to help anything. Just so I don't get 20 links to the same sites I've read... I'll post what I've already used for reference: http://postgis.refractions.net/pipermail/postgis-users/2004-January/003757.html http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.phpbuilder.com/columns/smith20010821.php3?page=2 http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php http://www.lyris.com/lm_help/6.0/tuning_postgresql.html Nothing makes much of a difference. I even attempted to use persistant connections to the database to get around the connection overhead... and THAT just seemed to eat up all the system's memory while giving little or no gain in performance. All of my optimizations seem to help at first, but everything soon gets just as slow as it was before... and I mean SLOW. Sooo.... I'll give you as much info as I can... it may be too much, but I suppose that's better than not enough.... # HARDWARE # Pentium 4 2.5ghz 1.5 gb of DDR 400 Unlimited bandwidth # MEMORY USAGE (top output sorted on memory) of persistant PgSQL connections: 27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46 0 postmaster 27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25 0 postmaster 27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20 0 postmaster 27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36 0 postmaster 27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32 0 postmaster 27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40 0 postmaster 27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24 0 postmaster 27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25 0 postmaster 27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22 0 postmaster 27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18 0 postmaster 27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26 0 postmaster 27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15 0 postmaster 27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26 0 postmaster 27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17 0 postmaster 27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30 0 postmaster 27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03 0 postmaster 27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00 0 postmaster 27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00 0 postmaster 27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00 0 postmaster 27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02 0 postmaster 27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02 0 postmaster 27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02 0 postmaster 27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00 0 postmaster 27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00 0 postmaster 27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00 0 postmaster 27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00 0 postmaster 27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00 0 postmaster 27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00 0 postmaster 27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00 0 postmaster 27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00 0 postmaster 1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0 mdmpd 27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00 0 postmaster 27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00 0 postmaster # WEBSITE # 30-60 users online at any given time 15,000 rows in the 'threads' table joined on 300,000 rows in the 'posts' table Total size of database on disk is 1.1 Gigabytes # SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES (this happened to be not so bad.... which may say a lot since it took 10 seconds...) 8 Queries Totaling 10.7413 Seconds SQL: SELECT count(*) AS count FROM thread_listing Num Rows: 1 Affected Rows: 0 Exec Time: 0.75249910354614 SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC,t.lastreply desc LIMIT 25 OFFSET 0 Num Rows: 25 Affected Rows: 0 Exec Time: 9.1602659225464 SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND threadcategoryid<>0 ORDER BY orderid ASC Num Rows: 4 Affected Rows: 0 Exec Time: 0.81906294822693 SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND promoted=true ORDER BY orderid ASC Num Rows: 9 Affected Rows: 0 Exec Time: 0.0021350383758545 SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND promoted=true ORDER BY orderid ASC Num Rows: 5 Affected Rows: 0 Exec Time: 0.0019958019256592 SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND promoted=true ORDER BY orderid ASC Num Rows: 4 Affected Rows: 0 Exec Time: 0.0019819736480713 SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND promoted=true ORDER BY orderid ASC Num Rows: 5 Affected Rows: 0 Exec Time: 0.0021347999572754 SQL: SELECT userid,username FROM users WHERE userid IN (select * from buddynetwork(0,2)) ORDER BY username ASC Num Rows: 1 Exec Time: 0.0011849403381348 # PGSQL Version 7.4.2 # ----------------------------- # PostgreSQL configuration file # ----------------------------- #--------------------------------------------------------------------------- # CONNECTIONS AND AUTHENTICATION #--------------------------------------------------------------------------- # - Connection Settings - tcpip_socket = true max_connections = 50 #superuser_reserved_connections = 2 port = 5432 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 #virtual_host = '' #rendezvous_name = '' # - Security & Authentication - #authentication_timeout = 60 ssl = true password_encryption = true #krb_server_keyfile = '' #db_user_namespace = false #--------------------------------------------------------------------------- # RESOURCE USAGE (except WAL) #--------------------------------------------------------------------------- # - Memory - shared_buffers = 8192 sort_mem = 8192 vacuum_mem = 127072 # - Free Space Map - max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - max_files_per_process = 3052 # min 25 #preload_libraries = '' #--------------------------------------------------------------------------- # WRITE AHEAD LOG #--------------------------------------------------------------------------- # - Settings - fsync = true # turns forced synchronization on or off #wal_sync_method = fsync wal_buffers = 8192 # min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3 # 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 #--------------------------------------------------------------------------- # QUERY TUNING #--------------------------------------------------------------------------- # - Planner Method Enabling - #enable_hashagg = true #enable_hashjoin = true #enable_indexscan = true #enable_mergejoin = true #enable_nestloop = true enable_seqscan = false #enable_sort = true #enable_tidscan = true # - Planner Cost Constants - effective_cache_size = 131072 # typically 8KB each random_page_cost = 4 # units are one sequential page fetch cost cpu_tuple_cost = .01 # (same) default .01 cpu_index_tuple_cost = .001 # (same) default .001 cpu_operator_cost = 0.0025 # (same) default .0025 # - Genetic Query Optimizer - geqo = true geqo_threshold = 20 #geqo_effort = 1 #geqo_generations = 0 #geqo_pool_size = 0 # default based on tables in statement, #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target = 10 # range 1-1000 #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs #--------------------------------------------------------------------------- # ERROR REPORTING AND LOGGING #--------------------------------------------------------------------------- # - Syslog - #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # - When to Log - client_min_messages = error log_min_messages = error log_error_verbosity = default log_min_error_statement = panic log_min_duration_statement = -1 #silent_mode = false # DO NOT USE without Syslog! # - What to Log - debug_print_parse = false debug_print_rewritten = false debug_print_plan = false debug_pretty_print = false log_connections = false log_duration = false log_pid = false log_statement = false log_timestamp = false log_hostname = false log_source_port = false #--------------------------------------------------------------------------- # RUNTIME STATISTICS #--------------------------------------------------------------------------- # - Statistics Monitoring - log_parser_stats = false log_planner_stats = false log_executor_stats = false log_statement_stats = false # - Query/Index Statistics Collector - stats_start_collector = false stats_command_string = false stats_block_level = false stats_row_level = false stats_reset_on_server_start = false #--------------------------------------------------------------------------- # CLIENT CONNECTION DEFAULTS #--------------------------------------------------------------------------- # - Statement Behavior - #search_path = '$user,public' # schema names #check_function_bodies = true #default_transaction_isolation = 'read committed' #default_transaction_read_only = false #statement_timeout = 0 # 0 is disabled, in milliseconds # - Locale and Formatting - #datestyle = 'iso, mdy' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii # actually, defaults to database encoding # These settings are initialized by initdb -- they may be changed lc_messages = 'en_US.UTF-8' # locale for system error message strings 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 # - Other Defaults - explain_pretty_print = true #dynamic_library_path = '$libdir' #max_expr_depth = 10000 # min 10 #--------------------------------------------------------------------------- # LOCK MANAGEMENT #--------------------------------------------------------------------------- #deadlock_timeout = 1000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each #--------------------------------------------------------------------------- # VERSION/PLATFORM COMPATIBILITY #--------------------------------------------------------------------------- # - Previous Postgres Versions - #add_missing_from = true regex_flavor = advanced # advanced, extended, or basic #sql_inheritance = true # - Other Platforms & Clients - #transform_null_equals = false PLEASE HELP ME FIND THE BOTTLE NECK!! Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds, and that is absolutely unacceptable in terms of online use. If I can't fix this I'm going to have to spend the month or two switching back to MySQL... and I really don't want to do that, so anything you could do to help us would be amazing!! Thanks in advance!! - Shane
while you weren't looking, Shane | SkinnyCorp wrote: > Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a > developer) for a small web development company. We recently switched over > to PostgreSQL after going over some of the powerful features that it holds > over MySQL. What kind of query load are you experiencing? (That is, hits/views/queries per second/minute/whatever.) What's your disk subsystem? What indices do you have defined on which tables? Run EXPLAIN ANALYZE on your longer-running queries and check to see if your indices are being used or not. How often are you vacuuming? /rls -- :wq
> SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > Num Rows: 25 > Affected Rows: 0 > Exec Time: 9.1602659225464 Is t.status a big or small int? You might need to cast it... This also goes for the other queries below. What are the explains for these queries? explain SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC,t.lastreply desc LIMIT 25 OFFSET 0 When was the last time you ran Vacuum, Analyze? Sincerely, Joshua D. Drake > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND > threadcategoryid<>0 ORDER BY orderid ASC > Num Rows: 4 > Affected Rows: 0 > Exec Time: 0.81906294822693 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND > promoted=true ORDER BY orderid ASC > Num Rows: 9 > Affected Rows: 0 > Exec Time: 0.0021350383758545 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND > promoted=true ORDER BY orderid ASC > Num Rows: 5 > Affected Rows: 0 > Exec Time: 0.0019958019256592 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND > promoted=true ORDER BY orderid ASC > Num Rows: 4 > Affected Rows: 0 > Exec Time: 0.0019819736480713 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND > promoted=true ORDER BY orderid ASC > Num Rows: 5 > Affected Rows: 0 > Exec Time: 0.0021347999572754 > > SQL: SELECT userid,username FROM users WHERE userid IN (select * > from buddynetwork(0,2)) ORDER BY username ASC > Num Rows: 1 > Exec Time: 0.0011849403381348 > > > > # PGSQL Version 7.4.2 > # ----------------------------- > # PostgreSQL configuration file > # ----------------------------- > > #--------------------------------------------------------------------------- > # CONNECTIONS AND AUTHENTICATION > > #--------------------------------------------------------------------------- > # - Connection Settings - > tcpip_socket = true > max_connections = 50 > #superuser_reserved_connections = 2 > port = 5432 > #unix_socket_directory = '' > #unix_socket_group = '' > #unix_socket_permissions = 0777 > #virtual_host = '' > #rendezvous_name = '' > # - Security & Authentication - > #authentication_timeout = 60 > ssl = true > password_encryption = true > #krb_server_keyfile = '' > #db_user_namespace = false > > > #--------------------------------------------------------------------------- > # RESOURCE USAGE (except WAL) > > #--------------------------------------------------------------------------- > # - Memory - > shared_buffers = 8192 > sort_mem = 8192 > vacuum_mem = 127072 > > # - Free Space Map - > max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 1000 # min 100, ~50 bytes each > # - Kernel Resource Usage - > max_files_per_process = 3052 # min 25 > #preload_libraries = '' > > > #--------------------------------------------------------------------------- > # WRITE AHEAD LOG > > #--------------------------------------------------------------------------- > # - Settings - > fsync = true # turns forced synchronization on or off > #wal_sync_method = fsync > wal_buffers = 8192 # min 4, 8KB each > > # - Checkpoints - > #checkpoint_segments = 3 # 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 > > > #--------------------------------------------------------------------------- > # QUERY TUNING > > #--------------------------------------------------------------------------- > > # - Planner Method Enabling - > #enable_hashagg = true > #enable_hashjoin = true > #enable_indexscan = true > #enable_mergejoin = true > #enable_nestloop = true > enable_seqscan = false > #enable_sort = true > #enable_tidscan = true > # - Planner Cost Constants - > effective_cache_size = 131072 # typically 8KB each > random_page_cost = 4 # units are one sequential page fetch cost > cpu_tuple_cost = .01 # (same) default .01 > cpu_index_tuple_cost = .001 # (same) default .001 > cpu_operator_cost = 0.0025 # (same) default .0025 > # - Genetic Query Optimizer - > geqo = true > geqo_threshold = 20 > #geqo_effort = 1 > #geqo_generations = 0 > #geqo_pool_size = 0 # default based on tables in statement, > #geqo_selection_bias = 2.0 # range 1.5-2.0 > # - Other Planner Options - > #default_statistics_target = 10 # range 1-1000 > #from_collapse_limit = 8 > #join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs > > > #--------------------------------------------------------------------------- > # ERROR REPORTING AND LOGGING > > #--------------------------------------------------------------------------- > # - Syslog - > #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog > #syslog_facility = 'LOCAL0' > #syslog_ident = 'postgres' > # - When to Log - > client_min_messages = error > log_min_messages = error > log_error_verbosity = default > log_min_error_statement = panic > log_min_duration_statement = -1 > > #silent_mode = false # DO NOT USE without Syslog! > > # - What to Log - > > debug_print_parse = false > debug_print_rewritten = false > debug_print_plan = false > debug_pretty_print = false > log_connections = false > log_duration = false > log_pid = false > log_statement = false > log_timestamp = false > log_hostname = false > log_source_port = false > > > > #--------------------------------------------------------------------------- > # RUNTIME STATISTICS > > #--------------------------------------------------------------------------- > > # - Statistics Monitoring - > > log_parser_stats = false > log_planner_stats = false > log_executor_stats = false > log_statement_stats = false > > # - Query/Index Statistics Collector - > > stats_start_collector = false > stats_command_string = false > stats_block_level = false > stats_row_level = false > stats_reset_on_server_start = false > > > > #--------------------------------------------------------------------------- > # CLIENT CONNECTION DEFAULTS > > #--------------------------------------------------------------------------- > > # - Statement Behavior - > > #search_path = '$user,public' # schema names > #check_function_bodies = true > #default_transaction_isolation = 'read committed' > #default_transaction_read_only = false > #statement_timeout = 0 # 0 is disabled, in milliseconds > > # - Locale and Formatting - > > #datestyle = 'iso, mdy' > #timezone = unknown # actually, defaults to TZ environment > setting > #australian_timezones = false > #extra_float_digits = 0 # min -15, max 2 > #client_encoding = sql_ascii # actually, defaults to database encoding > > # These settings are initialized by initdb -- they may be changed > lc_messages = 'en_US.UTF-8' # locale for system error message > strings > 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 > > # - Other Defaults - > > explain_pretty_print = true > #dynamic_library_path = '$libdir' > #max_expr_depth = 10000 # min 10 > > > > #--------------------------------------------------------------------------- > # LOCK MANAGEMENT > > #--------------------------------------------------------------------------- > > #deadlock_timeout = 1000 # in milliseconds > #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each > > > > #--------------------------------------------------------------------------- > # VERSION/PLATFORM COMPATIBILITY > > #--------------------------------------------------------------------------- > > # - Previous Postgres Versions - > > #add_missing_from = true > regex_flavor = advanced # advanced, extended, or basic > #sql_inheritance = true > > # - Other Platforms & Clients - > > #transform_null_equals = false > > > > > > > PLEASE HELP ME FIND THE BOTTLE NECK!! > > Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds, > and that is absolutely unacceptable in terms of online use. If I can't fix > this I'm going to have to spend the month or two switching back to MySQL... > and I really don't want to do that, so anything you could do to help us > would be amazing!! > > Thanks in advance!! > > - Shane > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
-----Original Message----- > From: pgsql-admin-owner@postgresql.org > On Behalf Of Shane | SkinnyCorp > Sent: Friday, October 01, 2004 10:05 AM > To: PgSQL ADMIN > Subject: [ADMIN] PLEASE GOD HELP US! > > ..<snip>.. You can use pgmonitor to visually see general load and check if there is any contention. Your sample queries were all SELECTs but the data has to be getting in there somehow. What's the relationship between the process that issues the queries and the database machine? Could there be any latency between the two? Individual queries could run quickly but if the requester takes a while to retrieve the request results before submitting a subsequent query, that can result in problems? Without index, table size and sample distribution information (or explain verbose output), it is difficult to say if any one query could be made to execute faster. Marc Mitchell Enterprise Information Solutions, Inc. Downers Grove, IL 60515 marcm@eisolution.com
I don't think ORDER BY X=Y will use an index even if casted. I may be wrong. We're still using 7.3.5. Joshua D. Drake wrote: >> SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 >> DESC,t.lastreply desc LIMIT 25 OFFSET 0 >> Num Rows: 25 >> Affected Rows: 0 >> Exec Time: 9.1602659225464 > > > Is t.status a big or small int? You might need to cast it... This also > goes for the other queries below. What are the explains for these queries? > > explain SELECT * FROM thread_listing AS t ORDER BY t.status=5 > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > > When was the last time you ran Vacuum, Analyze? > > Sincerely, > > Joshua D. Drake > > >> >> SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 >> AND >> threadcategoryid<>0 ORDER BY orderid ASC >> Num Rows: 4 >> Affected Rows: 0 >> Exec Time: 0.81906294822693 >> >> SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 >> AND >> promoted=true ORDER BY orderid ASC >> Num Rows: 9 >> Affected Rows: 0 >> Exec Time: 0.0021350383758545 >> >> SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 >> AND >> promoted=true ORDER BY orderid ASC >> Num Rows: 5 >> Affected Rows: 0 >> Exec Time: 0.0019958019256592 >> >> SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 >> AND >> promoted=true ORDER BY orderid ASC >> Num Rows: 4 >> Affected Rows: 0 >> Exec Time: 0.0019819736480713 >> >> SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 >> AND >> promoted=true ORDER BY orderid ASC >> Num Rows: 5 >> Affected Rows: 0 >> Exec Time: 0.0021347999572754 >> >> SQL: SELECT userid,username FROM users WHERE userid IN (select * >> from buddynetwork(0,2)) ORDER BY username ASC >> Num Rows: 1 >> Exec Time: 0.0011849403381348 >> >> >> >> # PGSQL Version 7.4.2 >> # ----------------------------- >> # PostgreSQL configuration file >> # ----------------------------- >> >> #--------------------------------------------------------------------------- >> >> # CONNECTIONS AND AUTHENTICATION >> >> #--------------------------------------------------------------------------- >> >> # - Connection Settings - >> tcpip_socket = true >> max_connections = 50 >> #superuser_reserved_connections = 2 >> port = 5432 >> #unix_socket_directory = '' >> #unix_socket_group = '' >> #unix_socket_permissions = 0777 >> #virtual_host = '' >> #rendezvous_name = '' >> # - Security & Authentication - >> #authentication_timeout = 60 >> ssl = true >> password_encryption = true >> #krb_server_keyfile = '' >> #db_user_namespace = false >> >> >> #--------------------------------------------------------------------------- >> >> # RESOURCE USAGE (except WAL) >> >> #--------------------------------------------------------------------------- >> >> # - Memory - >> shared_buffers = 8192 >> sort_mem = 8192 >> vacuum_mem = 127072 >> >> # - Free Space Map - >> max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each >> max_fsm_relations = 1000 # min 100, ~50 bytes each >> # - Kernel Resource Usage - >> max_files_per_process = 3052 # min 25 >> #preload_libraries = '' >> >> >> #--------------------------------------------------------------------------- >> >> # WRITE AHEAD LOG >> >> #--------------------------------------------------------------------------- >> >> # - Settings - >> fsync = true # turns forced synchronization on or off >> #wal_sync_method = fsync >> wal_buffers = 8192 # min 4, 8KB each >> >> # - Checkpoints - >> #checkpoint_segments = 3 # 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 >> >> >> #--------------------------------------------------------------------------- >> >> # QUERY TUNING >> >> #--------------------------------------------------------------------------- >> >> >> # - Planner Method Enabling - >> #enable_hashagg = true >> #enable_hashjoin = true >> #enable_indexscan = true >> #enable_mergejoin = true >> #enable_nestloop = true >> enable_seqscan = false >> #enable_sort = true >> #enable_tidscan = true >> # - Planner Cost Constants - >> effective_cache_size = 131072 # typically 8KB each >> random_page_cost = 4 # units are one sequential page fetch >> cost >> cpu_tuple_cost = .01 # (same) default .01 >> cpu_index_tuple_cost = .001 # (same) default .001 >> cpu_operator_cost = 0.0025 # (same) default .0025 >> # - Genetic Query Optimizer - >> geqo = true >> geqo_threshold = 20 >> #geqo_effort = 1 >> #geqo_generations = 0 >> #geqo_pool_size = 0 # default based on tables in statement, >> #geqo_selection_bias = 2.0 # range 1.5-2.0 >> # - Other Planner Options - >> #default_statistics_target = 10 # range 1-1000 >> #from_collapse_limit = 8 >> #join_collapse_limit = 8 # 1 disables collapsing of explicit >> JOINs >> >> >> #--------------------------------------------------------------------------- >> >> # ERROR REPORTING AND LOGGING >> >> #--------------------------------------------------------------------------- >> >> # - Syslog - >> #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog >> #syslog_facility = 'LOCAL0' >> #syslog_ident = 'postgres' >> # - When to Log - >> client_min_messages = error >> log_min_messages = error >> log_error_verbosity = default >> log_min_error_statement = panic >> log_min_duration_statement = -1 >> >> #silent_mode = false # DO NOT USE without Syslog! >> >> # - What to Log - >> >> debug_print_parse = false >> debug_print_rewritten = false >> debug_print_plan = false >> debug_pretty_print = false >> log_connections = false >> log_duration = false >> log_pid = false >> log_statement = false >> log_timestamp = false >> log_hostname = false >> log_source_port = false >> >> >> >> #--------------------------------------------------------------------------- >> >> # RUNTIME STATISTICS >> >> #--------------------------------------------------------------------------- >> >> >> # - Statistics Monitoring - >> >> log_parser_stats = false >> log_planner_stats = false >> log_executor_stats = false >> log_statement_stats = false >> >> # - Query/Index Statistics Collector - >> >> stats_start_collector = false >> stats_command_string = false >> stats_block_level = false >> stats_row_level = false >> stats_reset_on_server_start = false >> >> >> >> #--------------------------------------------------------------------------- >> >> # CLIENT CONNECTION DEFAULTS >> >> #--------------------------------------------------------------------------- >> >> >> # - Statement Behavior - >> >> #search_path = '$user,public' # schema names >> #check_function_bodies = true >> #default_transaction_isolation = 'read committed' >> #default_transaction_read_only = false >> #statement_timeout = 0 # 0 is disabled, in milliseconds >> >> # - Locale and Formatting - >> >> #datestyle = 'iso, mdy' >> #timezone = unknown # actually, defaults to TZ environment >> setting >> #australian_timezones = false >> #extra_float_digits = 0 # min -15, max 2 >> #client_encoding = sql_ascii # actually, defaults to database >> encoding >> >> # These settings are initialized by initdb -- they may be changed >> lc_messages = 'en_US.UTF-8' # locale for system error >> message >> strings >> 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 >> >> # - Other Defaults - >> >> explain_pretty_print = true >> #dynamic_library_path = '$libdir' >> #max_expr_depth = 10000 # min 10 >> >> >> >> #--------------------------------------------------------------------------- >> >> # LOCK MANAGEMENT >> >> #--------------------------------------------------------------------------- >> >> >> #deadlock_timeout = 1000 # in milliseconds >> #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes >> each >> >> >> >> #--------------------------------------------------------------------------- >> >> # VERSION/PLATFORM COMPATIBILITY >> >> #--------------------------------------------------------------------------- >> >> >> # - Previous Postgres Versions - >> >> #add_missing_from = true >> regex_flavor = advanced # advanced, extended, or basic >> #sql_inheritance = true >> >> # - Other Platforms & Clients - >> >> #transform_null_equals = false >> >> >> >> >> >> >> PLEASE HELP ME FIND THE BOTTLE NECK!! >> >> Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds, >> and that is absolutely unacceptable in terms of online use. If I >> can't fix >> this I'm going to have to spend the month or two switching back to >> MySQL... >> and I really don't want to do that, so anything you could do to help us >> would be amazing!! >> >> Thanks in advance!! >> >> - Shane >> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org > > > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Greg Spiegelberg Product Development Manager Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: gspiegelberg@cranel.com Technology. Integrity. Focus.
Shane,
Two things: 1) when was the last time you vacuumed the
database? From my experience that has a large effect on the database
performance. 2) Why do you have so many postmaster processes? I've got
an active database but only one postmaster.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Shane | SkinnyCorp [mailto:shanew@skinnycorp.com]
Sent: Friday, October 01, 2004 12:05 PM
To: PgSQL ADMIN
Subject: [ADMIN] PLEASE GOD HELP US!
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly
a
developer) for a small web development company. We recently switched
over to PostgreSQL after going over some of the powerful features that
it holds over MySQL.
However, after we launched one site, we slapped ourselves in the head.
We have run a message board for a few years now, and have always used
MySQL for the backend, but recently switched to PostgreSQL. It has
always been lightening fast with MySQL, but has slowed to nearly a halt
in terms of online access time. I can't seem to do anything about it!!
PLEASE HELP US!!
Now, I've read as much as I could about optimizing PostgreSQL for
performance, and nothing I do seems to help anything. Just so I don't
get 20 links to the same sites I've read... I'll post what I've already
used for
reference:
http://postgis.refractions.net/pipermail/postgis-users/2004-January/0037
57.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.phpbuilder.com/columns/smith20010821.php3?page=2
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php
http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
Nothing makes much of a difference. I even attempted to use persistant
connections to the database to get around the connection overhead... and
THAT just seemed to eat up all the system's memory while giving little
or no gain in performance. All of my optimizations seem to help at
first, but everything soon gets just as slow as it was before... and I
mean SLOW.
Sooo.... I'll give you as much info as I can... it may be too much, but
I suppose that's better than not enough....
# HARDWARE #
Pentium 4 2.5ghz
1.5 gb of DDR 400
Unlimited bandwidth
# MEMORY USAGE (top output sorted on memory) of persistant PgSQL
connections:
27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46
0
postmaster
27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25
0
postmaster
27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20
0
postmaster
27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36
0
postmaster
27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32
0
postmaster
27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40
0
postmaster
27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24
0
postmaster
27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25
0
postmaster
27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22
0
postmaster
27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18
0
postmaster
27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26
0
postmaster
27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15
0
postmaster
27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26
0
postmaster
27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17
0
postmaster
27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30
0
postmaster
27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03
0
postmaster
27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00
0
postmaster
27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00
0
postmaster
27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00
0
postmaster
27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02
0
postmaster
27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02
0
postmaster
27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02
0
postmaster
27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00
0
postmaster
27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00
0
postmaster
27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00
0
postmaster
27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00
0
postmaster
27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00
0
postmaster
27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00
0
postmaster
27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00
0
postmaster
27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00
0
postmaster
1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0
mdmpd
27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00
0
postmaster
27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00
0
postmaster
# WEBSITE #
30-60 users online at any given time
15,000 rows in the 'threads' table
joined on
300,000 rows in the 'posts' table
Total size of database on disk is 1.1 Gigabytes
# SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
(this happened to be not so bad.... which may say a lot since it
took 10 seconds...)
8 Queries Totaling 10.7413 Seconds
SQL: SELECT count(*) AS count FROM thread_listing
Num Rows: 1
Affected Rows: 0
Exec Time: 0.75249910354614
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0
AND threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1
AND promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2
AND promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3
AND promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4
AND promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754
SQL: SELECT userid,username FROM users WHERE userid IN (select
* from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348
# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#-----------------------------------------------------------------------
----
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------------------------
----
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#-----------------------------------------------------------------------
----
# RESOURCE USAGE (except WAL)
#-----------------------------------------------------------------------
----
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes
each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''
#-----------------------------------------------------------------------
----
# WRITE AHEAD LOG
#-----------------------------------------------------------------------
----
# - Settings -
fsync = true # turns forced synchronization on or
off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # 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
#-----------------------------------------------------------------------
----
# QUERY TUNING
#-----------------------------------------------------------------------
----
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch
cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000 #from_collapse_limit =
8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
JOINs
#-----------------------------------------------------------------------
----
# ERROR REPORTING AND LOGGING
#-----------------------------------------------------------------------
----
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false
#-----------------------------------------------------------------------
----
# RUNTIME STATISTICS
#-----------------------------------------------------------------------
----
# - Statistics Monitoring -
log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false
#-----------------------------------------------------------------------
----
# CLIENT CONNECTION DEFAULTS
#-----------------------------------------------------------------------
----
# - Statement Behavior -
#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error
message
strings
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
# - Other Defaults -
explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#-----------------------------------------------------------------------
----
# LOCK MANAGEMENT
#-----------------------------------------------------------------------
----
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes
each
#-----------------------------------------------------------------------
----
# VERSION/PLATFORM COMPATIBILITY
#-----------------------------------------------------------------------
----
# - Previous Postgres Versions -
#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
PLEASE HELP ME FIND THE BOTTLE NECK!!
Sometimes just ONE page load (approx. 13 queries) takes up to 30
seconds, and that is absolutely unacceptable in terms of online use. If
I can't fix this I'm going to have to spend the month or two switching
back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!
Thanks in advance!!
- Shane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Friday 01 October 2004 09:04 am, you wrote: > SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > Num Rows: 25 > Affected Rows: 0 > Exec Time: 9.1602659225464 EXPLAIN is your friend here. Well, the above query is nice for MySQL - being a fast reader. I bet it doesn't use an index at all. You're selecting ALL rows from the table, then sort ALL rows and only retrieve 25. This means if you have 1 million rows in the table, postgres has to sort 1 million rows just to return the latest 25. Maybe you want to rethink the approach to this. Databases are designed to pull out specific records from a large set of records and that's where most databases are good at. Design the query in a way that allows to isolate the records you want in a "where clause". In the above query you could already put an index on thread_listing.status and write: select * from thread_listing where status=5 order by lastreply desc limit 25 offset 0 Although not yet good, it will already only take the rows with status=5 into consideration. If you only have 20% of records with status=5 it will not sort 1 million records anymore, but only 200000. Your problem is not the speed of the DB, it's bad query design (and possibly bad database design not using keys and indexes properly). Using indexes makes write operations slower, but in your case that doesn't matter because nobody will post 1000 threads in a second, however many may want to read them, which is where the index will increase speed exponentially Example: I have a data-warehouse on postgres. One particular operation would store 32 records to one table and afterwards read the records just stored. Without indexes the read took about 20 seconds, because a lot of joins to other tables were involved. After I properly indexed the keys used to join to the other tables the execution time went down to about a second - and the database isn't even optimized for my application, it's a vanilla standard out of the box configuration in postgresql.conf (except for increased shared memory to allow more simultaneous connections). I bet I could get the execution-time to less than 1/2 second if I'd care about this. UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBXYp0jqGXBvRToM4RAgJkAJ4tu7tPyzieAjQ1+pwFr+6sa1CffgCgxCYb K6szMn9iVelBmL5tqrE0YS4= =tYYW -----END PGP SIGNATURE-----
On Friday 01 October 2004 9:04 am, Shane | SkinnyCorp wrote: > Hey, my name is Shane Witschen and I'm the Systems Administrator > (mainly a developer) for a small web development company. We > recently switched over to PostgreSQL after going over some of the > powerful features that it holds over MySQL. > > However, after we launched one site, we slapped ourselves in the > head. We have run a message board for a few years now, and have > always used MySQL for the backend, but recently switched to > PostgreSQL. It has always been lightening fast with MySQL, but has > slowed to nearly a halt in terms of online access time. I can't > seem to do anything about it!! PLEASE HELP US!! Others have mentioned issues with indexes, schema and such. Let me explain vacuum. If you haven't vacuumed regularly then you may have very large disk files relative to your database size - expecially if you have much update activity. PG, unlike mysql, has full multi-version concurrency control. This is just one piece of the power that sets it apart from mysql and others. With MVCC in PG, every record that is updated within a transaction is duplicated on-disk. The updated tuples are not visible to other transactions until the updating transaction is complete. The old versions of the updated tuples are no longer visible when all running transactions no longer need the old tuples. Vacuuming marks the unused space as reusable. If you did a lot of development without regular vacuums then your disk size may be huge compared to what is necessary. You can physically shrink the file by running a "vacuum full" but this will lock the table for the duration of the vacuum (probably not too much of a problem if you are already seeing 10+ second page loads). Note: getting your disk files to a size that allows them to be better cached by the OS can make a huge difference in query speed - especially if you are doing full table scans either intentionally or due to bad design. The "analyze" command updates the statistics that PG needs in order for the query planner to make appropriate use of indexes and such. After running your "vacuum full", be sure to run "vacuum analyze" regularly. Check out the autovacuum daemon to help automate this process. I have a similar machine (but more RAM) and can easily run a count(*) of a 4+ million row table in well under 4 seconds which makes me suspicious of your vacuuming. Cheers, Steve
Hi... I take vast offense to the suggestion that my query / database design is at fault here. I highly doubt it, although I AM willing to admit fault where the fault is indeed my own. However, on the topic of sorting all 15,000 rows of the thread select query and then limiting the output to 25, that is absolutely unavoidable, and I don't see how query / table design is at any fault of my own. How else do you suggest I grab the 25 most recent threads posted?!? Exactly. As for vacuum... good lord. I run vacuum full on the database every single night through a cron job, simply because pg_autovacuum or whatever needs the stats collector running, and that creates even MORE overhead, so I choose to do it myself. I must admit that I expected much more from this list than I've recieved so far, as I've gotten about 20 ppl suggesting that I use vacuum (which honestly should've been implied, but maybe it was my bad for not mentioning), and one guy telling me that my queries are at fault... I mean, wtf? I do admit that there could be areas in which my queries could be better written for speed, but nothing in that gentleman's post had any significance or relevance to my issue whatsoever. I would like to also add that I've been a sysadmin for quite some time, and I've been designing databases for quite some time as well. I'm no idiot, I just can't find the bottleneck here (if one does in fact exist). So in light of this, please send me some suggestions I can work with. Also of note is that much of my design and indexing was in attempt to speed up an already lagging database... as in, adding triggers to update totals and insert them into the threads table instead of doing a count on posts every query... Anyhow, as for an explain analyze on some queries, I can provide that... but it's ALWAYS using index scans (i made sure of this), and things like a simple update on a 2000 row table can take up to 10 seconds... which is ludicrous. One last thing... even with ALL of the data intact (and yes, we DID do testing... we just didn't have enough ppl to test the production server load), if we shut out ALL of the users, and run these queries... they are LIGHTNING fast... but with the user load... it's horrid. The only information I can give at the moment about the number of queries per second is this: there is an average of 60 users online at any given time, and the average number of queries per page load is 12, and they are refreshing and clicking etc quite a bit... so I'd say about 120 queries per second or so... (VERY rough estimate).... Got any suggestions now?!? I was sort of looking for more information / insight on my postgresql.conf file... but it seems we had to get the "IS HE A MORON" question answered :P Anyhow, again thank you for any help you can lend... - Shane Witschen ----- Original Message ----- From: "Goulet, Dick" <DGoulet@vicr.com> To: "Shane | SkinnyCorp" <shanew@skinnycorp.com>; "PgSQL ADMIN" <pgsql-admin@postgresql.org> Sent: Friday, October 01, 2004 11:46 AM Subject: Re: [ADMIN] PLEASE GOD HELP US! > Shane, > > Two things: 1) when was the last time you vacuumed the > database? From my experience that has a large effect on the database > performance. 2) Why do you have so many postmaster processes? I've got > an active database but only one postmaster. > > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > -----Original Message----- > From: Shane | SkinnyCorp [mailto:shanew@skinnycorp.com] > Sent: Friday, October 01, 2004 12:05 PM > To: PgSQL ADMIN > Subject: [ADMIN] PLEASE GOD HELP US! > > Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly > a > developer) for a small web development company. We recently switched > over to PostgreSQL after going over some of the powerful features that > it holds over MySQL. > > However, after we launched one site, we slapped ourselves in the head. > We have run a message board for a few years now, and have always used > MySQL for the backend, but recently switched to PostgreSQL. It has > always been lightening fast with MySQL, but has slowed to nearly a halt > in terms of online access time. I can't seem to do anything about it!! > PLEASE HELP US!! > > Now, I've read as much as I could about optimizing PostgreSQL for > performance, and nothing I do seems to help anything. Just so I don't > get 20 links to the same sites I've read... I'll post what I've already > used for > reference: > > http://postgis.refractions.net/pipermail/postgis-users/2004-January/0037 > 57.html > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > http://www.phpbuilder.com/columns/smith20010821.php3?page=2 > http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php > http://www.lyris.com/lm_help/6.0/tuning_postgresql.html > > Nothing makes much of a difference. I even attempted to use persistant > connections to the database to get around the connection overhead... and > THAT just seemed to eat up all the system's memory while giving little > or no gain in performance. All of my optimizations seem to help at > first, but everything soon gets just as slow as it was before... and I > mean SLOW. > > > Sooo.... I'll give you as much info as I can... it may be too much, but > I suppose that's better than not enough.... > > # HARDWARE # > Pentium 4 2.5ghz > 1.5 gb of DDR 400 > Unlimited bandwidth > # MEMORY USAGE (top output sorted on memory) of persistant PgSQL > connections: > 27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46 > 0 > postmaster > 27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25 > 0 > postmaster > 27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20 > 0 > postmaster > 27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36 > 0 > postmaster > 27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32 > 0 > postmaster > 27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40 > 0 > postmaster > 27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24 > 0 > postmaster > 27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25 > 0 > postmaster > 27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22 > 0 > postmaster > 27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18 > 0 > postmaster > 27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26 > 0 > postmaster > 27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15 > 0 > postmaster > 27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26 > 0 > postmaster > 27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17 > 0 > postmaster > 27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30 > 0 > postmaster > 27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03 > 0 > postmaster > 27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00 > 0 > postmaster > 27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00 > 0 > postmaster > 27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00 > 0 > postmaster > 27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02 > 0 > postmaster > 27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02 > 0 > postmaster > 27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02 > 0 > postmaster > 27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00 > 0 > postmaster > 27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00 > 0 > postmaster > 27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00 > 0 > postmaster > 27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00 > 0 > postmaster > 27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00 > 0 > postmaster > 27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00 > 0 > postmaster > 27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00 > 0 > postmaster > 27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00 > 0 > postmaster > 1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0 > mdmpd > 27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00 > 0 > postmaster > 27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00 > 0 > postmaster > > > > # WEBSITE # > 30-60 users online at any given time > 15,000 rows in the 'threads' table > joined on > 300,000 rows in the 'posts' table > Total size of database on disk is 1.1 Gigabytes > > # SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES > (this happened to be not so bad.... which may say a lot since it > took 10 seconds...) > > 8 Queries Totaling 10.7413 Seconds > > SQL: SELECT count(*) AS count FROM thread_listing > Num Rows: 1 > Affected Rows: 0 > Exec Time: 0.75249910354614 > > SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > Num Rows: 25 > Affected Rows: 0 > Exec Time: 9.1602659225464 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 > AND threadcategoryid<>0 ORDER BY orderid ASC > Num Rows: 4 > Affected Rows: 0 > Exec Time: 0.81906294822693 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 > AND promoted=true ORDER BY orderid ASC > Num Rows: 9 > Affected Rows: 0 > Exec Time: 0.0021350383758545 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 > AND promoted=true ORDER BY orderid ASC > Num Rows: 5 > Affected Rows: 0 > Exec Time: 0.0019958019256592 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 > AND promoted=true ORDER BY orderid ASC > Num Rows: 4 > Affected Rows: 0 > Exec Time: 0.0019819736480713 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 > AND promoted=true ORDER BY orderid ASC > Num Rows: 5 > Affected Rows: 0 > Exec Time: 0.0021347999572754 > > SQL: SELECT userid,username FROM users WHERE userid IN (select > * from buddynetwork(0,2)) ORDER BY username ASC > Num Rows: 1 > Exec Time: 0.0011849403381348 > > > > # PGSQL Version 7.4.2 > # ----------------------------- > # PostgreSQL configuration file > # ----------------------------- > > #----------------------------------------------------------------------- > ---- > # CONNECTIONS AND AUTHENTICATION > > #----------------------------------------------------------------------- > ---- > # - Connection Settings - > tcpip_socket = true > max_connections = 50 > #superuser_reserved_connections = 2 > port = 5432 > #unix_socket_directory = '' > #unix_socket_group = '' > #unix_socket_permissions = 0777 > #virtual_host = '' > #rendezvous_name = '' > # - Security & Authentication - > #authentication_timeout = 60 > ssl = true > password_encryption = true > #krb_server_keyfile = '' > #db_user_namespace = false > > > #----------------------------------------------------------------------- > ---- > # RESOURCE USAGE (except WAL) > > #----------------------------------------------------------------------- > ---- > # - Memory - > shared_buffers = 8192 > sort_mem = 8192 > vacuum_mem = 127072 > > # - Free Space Map - > max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes > each > max_fsm_relations = 1000 # min 100, ~50 bytes each > # - Kernel Resource Usage - > max_files_per_process = 3052 # min 25 > #preload_libraries = '' > > > #----------------------------------------------------------------------- > ---- > # WRITE AHEAD LOG > > #----------------------------------------------------------------------- > ---- > # - Settings - > fsync = true # turns forced synchronization on or > off > #wal_sync_method = fsync > wal_buffers = 8192 # min 4, 8KB each > > # - Checkpoints - > #checkpoint_segments = 3 # 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 > > > #----------------------------------------------------------------------- > ---- > # QUERY TUNING > > #----------------------------------------------------------------------- > ---- > > # - Planner Method Enabling - > #enable_hashagg = true > #enable_hashjoin = true > #enable_indexscan = true > #enable_mergejoin = true > #enable_nestloop = true > enable_seqscan = false > #enable_sort = true > #enable_tidscan = true > # - Planner Cost Constants - > effective_cache_size = 131072 # typically 8KB each > random_page_cost = 4 # units are one sequential page fetch > cost > cpu_tuple_cost = .01 # (same) default .01 > cpu_index_tuple_cost = .001 # (same) default .001 > cpu_operator_cost = 0.0025 # (same) default .0025 > # - Genetic Query Optimizer - > geqo = true > geqo_threshold = 20 > #geqo_effort = 1 > #geqo_generations = 0 > #geqo_pool_size = 0 # default based on tables in statement, > #geqo_selection_bias = 2.0 # range 1.5-2.0 > # - Other Planner Options - > #default_statistics_target = 10 # range 1-1000 #from_collapse_limit = > 8 > #join_collapse_limit = 8 # 1 disables collapsing of explicit > JOINs > > > #----------------------------------------------------------------------- > ---- > # ERROR REPORTING AND LOGGING > > #----------------------------------------------------------------------- > ---- > # - Syslog - > #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog > #syslog_facility = 'LOCAL0' > #syslog_ident = 'postgres' > # - When to Log - > client_min_messages = error > log_min_messages = error > log_error_verbosity = default > log_min_error_statement = panic > log_min_duration_statement = -1 > > #silent_mode = false # DO NOT USE without Syslog! > > # - What to Log - > > debug_print_parse = false > debug_print_rewritten = false > debug_print_plan = false > debug_pretty_print = false > log_connections = false > log_duration = false > log_pid = false > log_statement = false > log_timestamp = false > log_hostname = false > log_source_port = false > > > > #----------------------------------------------------------------------- > ---- > # RUNTIME STATISTICS > > #----------------------------------------------------------------------- > ---- > > # - Statistics Monitoring - > > log_parser_stats = false > log_planner_stats = false > log_executor_stats = false > log_statement_stats = false > > # - Query/Index Statistics Collector - > > stats_start_collector = false > stats_command_string = false > stats_block_level = false > stats_row_level = false > stats_reset_on_server_start = false > > > > #----------------------------------------------------------------------- > ---- > # CLIENT CONNECTION DEFAULTS > > #----------------------------------------------------------------------- > ---- > > # - Statement Behavior - > > #search_path = '$user,public' # schema names > #check_function_bodies = true > #default_transaction_isolation = 'read committed' > #default_transaction_read_only = false > #statement_timeout = 0 # 0 is disabled, in milliseconds > > # - Locale and Formatting - > > #datestyle = 'iso, mdy' > #timezone = unknown # actually, defaults to TZ environment > setting > #australian_timezones = false > #extra_float_digits = 0 # min -15, max 2 > #client_encoding = sql_ascii # actually, defaults to database > encoding > > # These settings are initialized by initdb -- they may be changed > lc_messages = 'en_US.UTF-8' # locale for system error > message > strings > 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 > > # - Other Defaults - > > explain_pretty_print = true > #dynamic_library_path = '$libdir' > #max_expr_depth = 10000 # min 10 > > > > #----------------------------------------------------------------------- > ---- > # LOCK MANAGEMENT > > #----------------------------------------------------------------------- > ---- > > #deadlock_timeout = 1000 # in milliseconds > #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes > each > > > > #----------------------------------------------------------------------- > ---- > # VERSION/PLATFORM COMPATIBILITY > > #----------------------------------------------------------------------- > ---- > > # - Previous Postgres Versions - > > #add_missing_from = true > regex_flavor = advanced # advanced, extended, or basic > #sql_inheritance = true > > # - Other Platforms & Clients - > > #transform_null_equals = false > > > > > > > PLEASE HELP ME FIND THE BOTTLE NECK!! > > Sometimes just ONE page load (approx. 13 queries) takes up to 30 > seconds, and that is absolutely unacceptable in terms of online use. If > I can't fix this I'm going to have to spend the month or two switching > back to MySQL... > and I really don't want to do that, so anything you could do to help us > would be amazing!! > > Thanks in advance!! > > - Shane > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Hi Shane, As many others have alluded to - performance like this is almost always attributable to your queries not using an index. Be it on Oracle, Mysql, or postgres, i have seen this problem popup often. Also, could you tell us what language you are using, and if you are using a DB abstraction layer? On to the particulars: > # WEBSITE # > > # SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES > > 8 Queries Totaling 10.7413 Seconds Since one query is taking 90% of the time, it clearly is the first cuplrit: > SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > Num Rows: 25 > Affected Rows: 0 > Exec Time: 9.1602659225464 Your SQL here seems what I would consider not typical. I would write it as: SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply desc LIMIT 25 OFFSET 0; Run that from a psql shell, and see if that speed things up. If not, run: db=> EXPLAIN ANALYSE SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply desc LIMIT 25 OFFSET 0; and db=> \d thread_listing And send it to the list. You are in good shape I think, and porting won't be necessary. I've used many db's and postgres is my favorite by far. I'd say you've made a good choice ;-) [ \ / [ >X< spider@steelsun.com | http://www.steelsun.com/ [ / \
Shane | SkinnyCorp wrote:> # PGSQL Version 7.4.2 Upgrade to 7.4.5 > #---------------------------------------------------------------------------> # RESOURCE USAGE (except WAL)>> #---------------------------------------------------------------------------> # - Memory -> shared_buffers = 8192> sort_mem= 8192> vacuum_mem = 127072 Here vacuum_mem is too high, memory wasted, if you do regular vacuum or you use the autovacuum daemon then usualy 32MB are enough > # - Free Space Map -> max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each> max_fsm_relations =1000 # min 100, ~50 bytes each you didn't show us the result of vacuum full verbose so I can not tell you if they are enough or not. > enable_seqscan = false enable_seqscan = true Do you think that an index scan is always worst then a seqscan ? > random_page_cost = 4 # units are one sequential page fetch cost decrease this value to 2.5 > # - Query/Index Statistics Collector ->> stats_start_collector = false> stats_command_string = false> stats_block_level= false> stats_row_level = false> stats_reset_on_server_start = false I suggest you to start the collectors stats_start_collector = true stats_command_string = true stats_block_level = true stats_row_level = true stats_reset_on_server_start = false Regards Gaetano Mendola
> I would like to also add that I've been a sysadmin for quite some time, and > I've been designing databases for quite some time as well. I'm no idiot, I > just can't find the bottleneck here (if one does in fact exist). So in light > of this, please send me some suggestions I can work with. Here are three concise suggestions: 1. You say your issue is only under load, then I can probably guarantee your issue is available connections: max_connections = 50 That number is way too small for a site with decent traffic. make it like 500. how mindful are you about opening connections per page view? 2. You have a query taking 9 seconds. run that query by hand on the shell to find out why. Rework the query, add or recreate indices as necessary. Using conditionals in ORDER BY is a bad idea. Do it in the WHERE clause per my previous email. 3. Leave your attitude at the door, or just leave. Making comments like: > I take vast offense to the suggestion that my query / database design is > at fault here.... I must admit that I expected much more from this list > than I've recieved so far. make it clear that you have a lot more room for growth as a developer. If you stop biting the hands that help you, you will learn a lot more. > One last thing... even with ALL of the data intact (and yes, we DID do > testing... we just didn't have enough ppl to test the production server > load) Another mistake showing your lack of experience. Use apache bench ( ab command ) to simulate load. > > The only information I can give at the moment about the number of queries > per second is this: there is an average of 60 users online at any given > time, and the average number of queries per page load is 12, and they are > refreshing and clicking etc quite a bit... so I'd say about 120 queries per > second or so... (VERY rough estimate).... And you only have 50 max_connections for postgres? What are you thinking? Of course every apache process is waiting for a connection. Also, do you even have enough apache workers? Your attitude sucks. Your problems are right under your nose, and you are too cocky to learn your tools. I imagine you are getting hammered by your co-workers to get things fixed. I will tell you empathy will always get you much farther than animosity. [ \ / [ >X< spider@steelsun.com | http://www.steelsun.com/ [ / \
while you weren't looking, Shane | SkinnyCorp wrote:
> How else do you suggest I grab the 25 most recent
> threads posted?!?
select *
from thread_listing t
where t.status = 5
order by lastreply desc
limit 25
offset 0
The WHERE clause is there to limit the number of tuples you're looking
at. Without a WHERE clause, every tuple in the relation is pulled.
Compare the following queries and query plans, run against our
production database. The columns and types are different, but the
logic is identical. Note from the row counts in the first query that
this is a 5+m row table. Such queries generally return in fractions
of seconds for us.
tci=> explain select * from account.cust order by prodid = 153::bigint
desc, createddt desc limit 25 offset 0;
QUERY PLAN
-----------------------------------------------------------------------------
Limit (cost=1785296.85..1785296.91 rows=25 width=272)
-> Sort (cost=1785296.85..1797950.28 rows=5061375 width=272)
Sort Key: (prodid = 153::bigint), createddt
-> Seq Scan on cust (cost=0.00..207355.19 rows=5061375 width=272)
(4 rows)
tci=> explain select * from account.cust where prodid = 153::bigint
order by createddt desc limit 25 offset 0;
QUERY PLAN
---------------------------------------------------------------------------
Limit (cost=208299.77..208299.83 rows=25 width=272)
-> Sort (cost=208299.77..208334.13 rows=13743 width=272)
Sort Key: createddt
-> Seq Scan on cust (cost=0.00..207355.19 rows=13743 width=272)
Filter: (prodid = 153::bigint)
(5 rows)
The query with a WHERE clause has to look at and sort 13,743 rows; the
query without has to look at and sort all 5+m.
Which would you expect to be faster?
> Exactly.
/rls
--
:wq
Okay, just so no one posts about this again... the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads with a status of '5' to the top of the list... it is NOT meant to only grab threads where the status = 5. Oh and believe me, when I take this out of the query, it CERTAINLY doesn't add any more than possible 1/4 of a millesecond to the speed of the SELECT statement. :/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Friday 01 October 2004 01:26 pm, Shane | SkinnyCorp wrote: > Okay, just so no one posts about this again... > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > with a status of '5' to the top of the list... it is NOT meant to only grab > threads where the status = 5. Oh and believe me, when I take this out of > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > millesecond to the speed of the SELECT statement. > One quick idea that comes to my head without knowing your database structure (which would be neccessary to do this properly) is: use a serial field as row key (int4 with a sequence as default) in your query grab the current value from the sequence, subtract 25 and select all records with a key larger than that number. Et voila: you only select 25 records with a where clause that will use an index properly. I bet that runs in a couple of milliseconds. BTW: You're asking and complaining about a "lagging database". You think nothing you did is at fault. Why then ask in the first place if you don't want to hear the answers? I've been working with databases long before postgres was born (over 23 years). I still ask questions on this list and usually get good responses. UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBXcKpjqGXBvRToM4RAv4YAJ9cj/P+vCbbJXNf1G5Dh3dWnZzkDQCgqdfa lxicqf7qRS6hA7Zzv0v94/U= =e94/ -----END PGP SIGNATURE-----
Shane | SkinnyCorp wrote: > Okay, just so no one posts about this again... > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > with a status of '5' to the top of the list... it is NOT meant to only > grab > threads where the status = 5. Oh and believe me, when I take this out of > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > millesecond to the speed of the SELECT statement. > > :/ Perhaps I missed it, but you did not yet send the output of the explain of this query, did you? Could you please do: EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC, t.lastreply DESC LIMIT 25 OFFSET 0; EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply DESC LIMIT 25 OFFSET 0; EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25 OFFSET 0; (and post the results here) The first one will certainly do a sequential scan, the last one will use an index if available. For the second you will need a partial index on lastreply with a where clause WHERE status=5, I believe. So a solution would perhaps be to use two queries, one with WHERE t.status=5, another without. Make both use an index. Then combine them in your application. Of course this can only work if you do not need an offset other then 0. Also, did you check that your system does not swap (using vmstat)? Best Regards, Michael Paesold
Shane | SkinnyCorp wrote: > I would like to also add that I've been a sysadmin for quite some time, and > I've been designing databases for quite some time as well. I'm no idiot, I > just can't find the bottleneck here (if one does in fact exist). So in light > of this, please send me some suggestions I can work with. My 2 cents. When I see a message titled "PLEASE GOD HELP US", my first thought is not "experienced db designer". Less bombastic statements, more logs (vacuum verbose, explain analyze), db stats, cpu stats, i/o stats -- would have (1) cut down on the obvious advice which you say you already know and (2) let the experts hone in on the problem. It's not like we can guess what your system is doing. Here's a few things that may or may not mean anything. Just random guesses off the top of my head due to the lack of detailed info. > Nothing makes much of a difference. I even attempted to use > persistant connections to the database to get around the connection > overhead... and THAT just seemed to eat up all the system's memory > while giving little or no gain in performance. At buffers of 8192, that's just 64MB used up for shared memory + a few MB per connection. At the max 60 users clicking on submit at exactly the same time, that's about 300MB of memory compared to your total of 1.5GB. If something is "eating up all the system memory", it isn't Postgres and it certainly wouldn't be a connection pooling mechanism. > max_connections = 50 A bit low in db connections for a production website. Fine for development but I personally max my DBs out at the full 1024. No harm in setting it really high if you can allocate the shared memory. Also could be causing delays if you don't have enough connections for the number of users. Dunno > enable_seqscan = false There are cases where seqscan will be faster than indexscans. For example, your query to retrieve the latest 25 threads -- always faster using seqscan. If it was using indexscan, that would explain the 9 seconds to run because the HD heads would have to jump back & forth from index to table for every record. (And as an experienced sysadmin, you should know that HD latency is way more expensive than HD throughput.) On my dev system (P3/750, 1GB ram, 1 IDE HD), I get the following results on a 36K table: SELECT * FROM 16K_table ORDER BY un_indexed_fld DESC LIMIT 25 --> 5s -- 1st run --> .013s -- 2nd run Doing the selects on an indexed field -- my results start at < 100 msec (1st run) and drops to < 1 msec for cached I/O.
On Fri, 1 Oct 2004, Michael Paesold wrote: > Shane | SkinnyCorp wrote: > > > Okay, just so no one posts about this again... > > > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > > with a status of '5' to the top of the list... it is NOT meant to only > > grab > > threads where the status = 5. Oh and believe me, when I take this out of > > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > > millesecond to the speed of the SELECT statement. > > > > :/ > > Perhaps I missed it, but you did not yet send the output of the explain of > this query, did you? > > Could you please do: > > EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC, > t.lastreply DESC LIMIT 25 OFFSET 0; > > EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY > t.lastreply DESC LIMIT 25 OFFSET 0; > > EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25 > OFFSET 0; > > (and post the results here) > > The first one will certainly do a sequential scan, the last one will use an > index if available. For the second you will need a partial index on > lastreply with a where clause WHERE status=5, I believe. So a solution would Actually, I think he'd want an expression index on ((status=5),lastreply). In simple tests (admittedly on 8.0b3) it looks like such an index can be used rather than a separate sort step.
On Friday 01 October 2004 12:26 pm, Shane | SkinnyCorp wrote: > Funny... > > I vacuum full EVERY night @ midnight... > > And yes, that's great about your similar machine with more RAM... > only... does YOUR table have 60+ users @ 120-some queries per > second at any given moment? > > ... > > I didn't need a friggin' tutorial on vacuum man... I need some > advice. I don't need a class in database design either, and I know > quite well how postgresql works, but aside from this I opted to > drop whatever I *think* I know about pgsql, and ask some of the > 'experts' on this list... > > And all I'm getting is shit straight out of the damn manual. Which > I've read. Many times... > > Got any other suggestions? > > - Shane Um, a new attitude would help - if you knew all the answers you wouldn't be asking the questions. Before biting off the heads of those trying to help you, try reading http://linuxmafia.com/faq/Essays/smart-questions.html by Eric S. Raymond and Rick Moen. I assume from your reading list you also read: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html Some of your postgresql.conf settings look _way_ out of the normal range. A couple that jump out: ... > wal_buffers = 8192 # min 4, 8KB each 64 MB for wal_buffers? What analysis led you to set this to over 2000 times higher than the default? Mine is set to 64 buffers which seems fine but of course ymmv. At least it is shared rather than per-process memory so it's not completely killing you. > vacuum_mem = 127072 Also looks way too high. This is settable per connection. Bump it up when running your vacuum fulls if you need to. You only showed pg processes from top. What is the swapping activity on your machine? Do you have memory left over for caching? Cheers, Steve
>Got any suggestions now?!? I was sort of looking for more information / >insight on my postgresql.conf file... but it seems we had to get the "IS HE >A MORON" question answered :P > >Anyhow, again thank you for any help you can lend... > > > Well, try not to SHOUT is a good suggestion. Also, how about posting the output of explain analyze for the queries that go into a making typical page (taken while the DB is under load preferably).
William Yu wrote: > There are cases where seqscan will be faster than indexscans. For > example, your query to retrieve the latest 25 threads -- always faster > using seqscan. If it was using indexscan, that would explain the 9 > seconds to run because the HD heads would have to jump back & forth from Oops, I didn't write down my thoughts clearly. I meant to say -- DEPENDING ON THE DISTRIBUTION OF THE VALUES -- the query might be faster using seqscan.
On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote: > Okay, just so no one posts about this again... > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > with a status of '5' to the top of the list... it is NOT meant to only grab > threads where the status = 5. Oh and believe me, when I take this out of > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > millesecond to the speed of the SELECT statement. Wouldn't this work just as well? SELECT * FROM thread_listing AS t ORDER BY t.status DESC,t.lastreply desc LIMIT 25 OFFSET 0 Assuming 5 is the highest number. If not, a where clause entry might make sense. SELECT * FROM thread_listing AS t WHERE t.status <= 5 ORDER BY t.status DESC,t.lastreply desc LIMIT 25 OFFSET 0 Or is the data set put together some weird way that makes that impossible?
On Fri, 1 Oct 2004, Scott Marlowe wrote: > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote: > > Okay, just so no one posts about this again... > > > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > > with a status of '5' to the top of the list... it is NOT meant to only grab > > threads where the status = 5. Oh and believe me, when I take this out of > > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > > millesecond to the speed of the SELECT statement. > > > Wouldn't this work just as well? > > SELECT * FROM thread_listing AS t ORDER BY t.status > DESC,t.lastreply desc LIMIT 25 OFFSET 0 Probably not, because I don't think he wants the other statuses to have special ranking over the others, so a status=4 and status=1 row should be sorted by lastreply only effectively. This is the problem of combining separate status flags into a single field if you want to be doing these sorts of queries.
On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote: > On Fri, 1 Oct 2004, Scott Marlowe wrote: > > > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote: > > > Okay, just so no one posts about this again... > > > > > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > > > with a status of '5' to the top of the list... it is NOT meant to only grab > > > threads where the status = 5. Oh and believe me, when I take this out of > > > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > > > millesecond to the speed of the SELECT statement. > > > > > > Wouldn't this work just as well? > > > > SELECT * FROM thread_listing AS t ORDER BY t.status > > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > > Probably not, because I don't think he wants the other statuses to have > special ranking over the others, so a status=4 and status=1 row should be > sorted by lastreply only effectively. This is the problem of combining > separate status flags into a single field if you want to be doing these > sorts of queries. > So would a union give good performance? Just union the first 25 or less with status=5 with the rest, using a 1 and 0 in each union to order by first? Hopefully the indexes would then be used.
On Sat, 2 Oct 2004, Scott Marlowe wrote: > On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote: > > On Fri, 1 Oct 2004, Scott Marlowe wrote: > > > > > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote: > > > > Okay, just so no one posts about this again... > > > > > > > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > > > > with a status of '5' to the top of the list... it is NOT meant to only grab > > > > threads where the status = 5. Oh and believe me, when I take this out of > > > > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > > > > millesecond to the speed of the SELECT statement. > > > > > > > > > Wouldn't this work just as well? > > > > > > SELECT * FROM thread_listing AS t ORDER BY t.status > > > DESC,t.lastreply desc LIMIT 25 OFFSET 0 > > > > Probably not, because I don't think he wants the other statuses to have > > special ranking over the others, so a status=4 and status=1 row should be > > sorted by lastreply only effectively. This is the problem of combining > > separate status flags into a single field if you want to be doing these > > sorts of queries. > > > > So would a union give good performance? Just union the first 25 or less > with status=5 with the rest, using a 1 and 0 in each union to order by > first? Hopefully the indexes would then be used. You'd want to use union all I think and you may have to put limits on the unioned arms to get good behavior. I think the expression index would probably work without changing the query (at least for relatively low offsets) at the cost of having an extra index to maintain.
On Sat, 2004-10-02 at 15:42, Stephan Szabo wrote:
> On Sat, 2 Oct 2004, Scott Marlowe wrote:
> > So would a union give good performance? Just union the first 25 or less
> > with status=5 with the rest, using a 1 and 0 in each union to order by
> > first? Hopefully the indexes would then be used.
>
> You'd want to use union all I think and you may have to put limits on the
> unioned arms to get good behavior. I think the expression index would
> probably work without changing the query (at least for relatively low
> offsets) at the cost of having an extra index to maintain.
>
I wonder if this would offer any improvement:
select * from (
(SELECT 2 as a,* FROM thread_listing AS t
where t.status=5
ORDER BY t.lastreply desc limit 25)
union
(SELECT 1 as a,* FROM thread_listing AS t
where t.status<>5
order by t.lastreply desc limit 25)
) as p
order by p.a desc, p.lastreply desc limit 25;
Or is there a moving offset we have to deal with here?
Scott Marlowe wrote: > On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote: > >>On Fri, 1 Oct 2004, Scott Marlowe wrote: >> >> >>>On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote: >>> >>>>Okay, just so no one posts about this again... >>>> >>>>the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads >>>>with a status of '5' to the top of the list... it is NOT meant to only grab >>>>threads where the status = 5. Oh and believe me, when I take this out of >>>>the query, it CERTAINLY doesn't add any more than possible 1/4 of a >>>>millesecond to the speed of the SELECT statement. >>> >>> >>>Wouldn't this work just as well? >>> >>>SELECT * FROM thread_listing AS t ORDER BY t.status >>>DESC,t.lastreply desc LIMIT 25 OFFSET 0 >> >>Probably not, because I don't think he wants the other statuses to have >>special ranking over the others, so a status=4 and status=1 row should be >>sorted by lastreply only effectively. This is the problem of combining >>separate status flags into a single field if you want to be doing these >>sorts of queries. >> > > > So would a union give good performance? Just union the first 25 or less > with status=5 with the rest, using a 1 and 0 in each union to order by > first? Hopefully the indexes would then be used. anyone seen that the OP is running the server with sequential scan disabled ? Reagards Gaetano Mendola
William Yu wrote:
> Shane | SkinnyCorp wrote:
>
>> I would like to also add that I've been a sysadmin for quite some
>> time, and
>> I've been designing databases for quite some time as well. I'm no
>> idiot, I
>> just can't find the bottleneck here (if one does in fact exist). So in
>> light
>> of this, please send me some suggestions I can work with.
>
>
> My 2 cents. When I see a message titled "PLEASE GOD HELP US", my first
> thought is not "experienced db designer".
Not to drag this off topic too far, but I never caught the post that
began this thread, because the subject line caused an immediate delete
as I just new it was a variation of a scam..
Useful subject lines would be appreciated in the future.
--
Until later, Geoffrey Registered Linux User #108567
AT&T Certified UNIX System Programmer - 1995
Sounds like all you need to do is to check what keys your app needs on the tables and then ensure that you have these set up. There is no way that MySQL will be faster than PG... seems like your MySQL DB was optimised and your PG wasn't. Should be very easy and quick to sort out. Brad Shane | SkinnyCorp wrote: >Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a >developer) for a small web development company. We recently switched over >to PostgreSQL after going over some of the powerful features that it holds >over MySQL. > >However, after we launched one site, we slapped ourselves in the head. We >have run a message board for a few years now, and have always used MySQL for >the backend, but recently switched to PostgreSQL. It has always been >lightening fast with MySQL, but has slowed to nearly a halt in terms of >online access time. I can't seem to do anything about it!! PLEASE HELP >US!! > >Now, I've read as much as I could about optimizing PostgreSQL for >performance, and nothing I do seems to help anything. Just so I don't get >20 links to the same sites I've read... I'll post what I've already used for >reference: > >http://postgis.refractions.net/pipermail/postgis-users/2004-January/003757.html >http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html >http://www.phpbuilder.com/columns/smith20010821.php3?page=2 >http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php >http://www.lyris.com/lm_help/6.0/tuning_postgresql.html > >Nothing makes much of a difference. I even attempted to use persistant >connections to the database to get around the connection overhead... and >THAT just seemed to eat up all the system's memory while giving little or no >gain in performance. All of my optimizations seem to help at first, but >everything soon gets just as slow as it was before... and I mean SLOW. > > >Sooo.... I'll give you as much info as I can... it may be too much, but I >suppose that's better than not enough.... > ># HARDWARE # > Pentium 4 2.5ghz > 1.5 gb of DDR 400 > Unlimited bandwidth > # MEMORY USAGE (top output sorted on memory) of persistant PgSQL >connections: > 27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46 0 >postmaster > 27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25 0 >postmaster > 27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20 0 >postmaster > 27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36 0 >postmaster > 27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32 0 >postmaster > 27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40 0 >postmaster > 27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24 0 >postmaster > 27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25 0 >postmaster > 27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22 0 >postmaster > 27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18 0 >postmaster > 27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26 0 >postmaster > 27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15 0 >postmaster > 27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26 0 >postmaster > 27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17 0 >postmaster > 27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30 0 >postmaster > 27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03 0 >postmaster > 27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00 0 >postmaster > 27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00 0 >postmaster > 27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00 0 >postmaster > 27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02 0 >postmaster > 27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02 0 >postmaster > 27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02 0 >postmaster > 27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00 0 >postmaster > 27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00 0 >postmaster > 27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00 0 >postmaster > 27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00 0 >postmaster > 27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00 0 >postmaster > 27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00 0 >postmaster > 27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00 0 >postmaster > 27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00 0 >postmaster > 1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0 >mdmpd > 27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00 0 >postmaster > 27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00 0 >postmaster > > > ># WEBSITE # > 30-60 users online at any given time > 15,000 rows in the 'threads' table > joined on > 300,000 rows in the 'posts' table > Total size of database on disk is 1.1 Gigabytes > > # SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES > (this happened to be not so bad.... which may say a lot since it >took 10 seconds...) > > 8 Queries Totaling 10.7413 Seconds > > SQL: SELECT count(*) AS count FROM thread_listing > Num Rows: 1 > Affected Rows: 0 > Exec Time: 0.75249910354614 > > SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5 >DESC,t.lastreply desc LIMIT 25 OFFSET 0 > Num Rows: 25 > Affected Rows: 0 > Exec Time: 9.1602659225464 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND >threadcategoryid<>0 ORDER BY orderid ASC > Num Rows: 4 > Affected Rows: 0 > Exec Time: 0.81906294822693 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND >promoted=true ORDER BY orderid ASC > Num Rows: 9 > Affected Rows: 0 > Exec Time: 0.0021350383758545 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND >promoted=true ORDER BY orderid ASC > Num Rows: 5 > Affected Rows: 0 > Exec Time: 0.0019958019256592 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND >promoted=true ORDER BY orderid ASC > Num Rows: 4 > Affected Rows: 0 > Exec Time: 0.0019819736480713 > > SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND >promoted=true ORDER BY orderid ASC > Num Rows: 5 > Affected Rows: 0 > Exec Time: 0.0021347999572754 > > SQL: SELECT userid,username FROM users WHERE userid IN (select * >from buddynetwork(0,2)) ORDER BY username ASC > Num Rows: 1 > Exec Time: 0.0011849403381348 > > > > # PGSQL Version 7.4.2 > # ----------------------------- > # PostgreSQL configuration file > # ----------------------------- > >#--------------------------------------------------------------------------- > # CONNECTIONS AND AUTHENTICATION > >#--------------------------------------------------------------------------- > # - Connection Settings - > tcpip_socket = true > max_connections = 50 > #superuser_reserved_connections = 2 > port = 5432 > #unix_socket_directory = '' > #unix_socket_group = '' > #unix_socket_permissions = 0777 > #virtual_host = '' > #rendezvous_name = '' > # - Security & Authentication - > #authentication_timeout = 60 > ssl = true > password_encryption = true > #krb_server_keyfile = '' > #db_user_namespace = false > > >#--------------------------------------------------------------------------- > # RESOURCE USAGE (except WAL) > >#--------------------------------------------------------------------------- > # - Memory - > shared_buffers = 8192 > sort_mem = 8192 > vacuum_mem = 127072 > > # - Free Space Map - > max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 1000 # min 100, ~50 bytes each > # - Kernel Resource Usage - > max_files_per_process = 3052 # min 25 > #preload_libraries = '' > > >#--------------------------------------------------------------------------- > # WRITE AHEAD LOG > >#--------------------------------------------------------------------------- > # - Settings - > fsync = true # turns forced synchronization on or off > #wal_sync_method = fsync > wal_buffers = 8192 # min 4, 8KB each > > # - Checkpoints - > #checkpoint_segments = 3 # 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 > > >#--------------------------------------------------------------------------- > # QUERY TUNING > >#--------------------------------------------------------------------------- > > # - Planner Method Enabling - > #enable_hashagg = true > #enable_hashjoin = true > #enable_indexscan = true > #enable_mergejoin = true > #enable_nestloop = true > enable_seqscan = false > #enable_sort = true > #enable_tidscan = true > # - Planner Cost Constants - > effective_cache_size = 131072 # typically 8KB each > random_page_cost = 4 # units are one sequential page fetch cost > cpu_tuple_cost = .01 # (same) default .01 > cpu_index_tuple_cost = .001 # (same) default .001 > cpu_operator_cost = 0.0025 # (same) default .0025 > # - Genetic Query Optimizer - > geqo = true > geqo_threshold = 20 > #geqo_effort = 1 > #geqo_generations = 0 > #geqo_pool_size = 0 # default based on tables in statement, > #geqo_selection_bias = 2.0 # range 1.5-2.0 > # - Other Planner Options - > #default_statistics_target = 10 # range 1-1000 > #from_collapse_limit = 8 > #join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs > > >#--------------------------------------------------------------------------- > # ERROR REPORTING AND LOGGING > >#--------------------------------------------------------------------------- > # - Syslog - > #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog > #syslog_facility = 'LOCAL0' > #syslog_ident = 'postgres' > # - When to Log - > client_min_messages = error > log_min_messages = error > log_error_verbosity = default > log_min_error_statement = panic > log_min_duration_statement = -1 > > #silent_mode = false # DO NOT USE without Syslog! > > # - What to Log - > > debug_print_parse = false > debug_print_rewritten = false > debug_print_plan = false > debug_pretty_print = false > log_connections = false > log_duration = false > log_pid = false > log_statement = false > log_timestamp = false > log_hostname = false > log_source_port = false > > > >#--------------------------------------------------------------------------- > # RUNTIME STATISTICS > >#--------------------------------------------------------------------------- > > # - Statistics Monitoring - > > log_parser_stats = false > log_planner_stats = false > log_executor_stats = false > log_statement_stats = false > > # - Query/Index Statistics Collector - > > stats_start_collector = false > stats_command_string = false > stats_block_level = false > stats_row_level = false > stats_reset_on_server_start = false > > > >#--------------------------------------------------------------------------- > # CLIENT CONNECTION DEFAULTS > >#--------------------------------------------------------------------------- > > # - Statement Behavior - > > #search_path = '$user,public' # schema names > #check_function_bodies = true > #default_transaction_isolation = 'read committed' > #default_transaction_read_only = false > #statement_timeout = 0 # 0 is disabled, in milliseconds > > # - Locale and Formatting - > > #datestyle = 'iso, mdy' > #timezone = unknown # actually, defaults to TZ environment >setting > #australian_timezones = false > #extra_float_digits = 0 # min -15, max 2 > #client_encoding = sql_ascii # actually, defaults to database encoding > > # These settings are initialized by initdb -- they may be changed > lc_messages = 'en_US.UTF-8' # locale for system error message >strings > 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 > > # - Other Defaults - > > explain_pretty_print = true > #dynamic_library_path = '$libdir' > #max_expr_depth = 10000 # min 10 > > > >#--------------------------------------------------------------------------- > # LOCK MANAGEMENT > >#--------------------------------------------------------------------------- > > #deadlock_timeout = 1000 # in milliseconds > #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each > > > >#--------------------------------------------------------------------------- > # VERSION/PLATFORM COMPATIBILITY > >#--------------------------------------------------------------------------- > > # - Previous Postgres Versions - > > #add_missing_from = true > regex_flavor = advanced # advanced, extended, or basic > #sql_inheritance = true > > # - Other Platforms & Clients - > > #transform_null_equals = false > > > > > > >PLEASE HELP ME FIND THE BOTTLE NECK!! > >Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds, >and that is absolutely unacceptable in terms of online use. If I can't fix >this I'm going to have to spend the month or two switching back to MySQL... >and I really don't want to do that, so anything you could do to help us >would be amazing!! > >Thanks in advance!! > >- Shane > > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > >
On Sun, 3 Oct 2004, Gaetano Mendola wrote: > Scott Marlowe wrote: > > On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote: > > > >>On Fri, 1 Oct 2004, Scott Marlowe wrote: > >> > >> > >>>On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote: > >>> > >>>>Okay, just so no one posts about this again... > >>>> > >>>>the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > >>>>with a status of '5' to the top of the list... it is NOT meant to only grab > >>>>threads where the status = 5. Oh and believe me, when I take this out of > >>>>the query, it CERTAINLY doesn't add any more than possible 1/4 of a > >>>>millesecond to the speed of the SELECT statement. > >>> > >>> > >>>Wouldn't this work just as well? > >>> > >>>SELECT * FROM thread_listing AS t ORDER BY t.status > >>>DESC,t.lastreply desc LIMIT 25 OFFSET 0 > >> > >>Probably not, because I don't think he wants the other statuses to have > >>special ranking over the others, so a status=4 and status=1 row should be > >>sorted by lastreply only effectively. This is the problem of combining > >>separate status flags into a single field if you want to be doing these > >>sorts of queries. > >> > > > > > > So would a union give good performance? Just union the first 25 or less > > with status=5 with the rest, using a 1 and 0 in each union to order by > > first? Hopefully the indexes would then be used. > > anyone seen that the OP is running the server with sequential scan disabled ? Yep, but he's doing some queries for which the indexes weren't useful unless he had some very specific ones, so it'd still be choosing sequential scans for those AFAICT (for example I believe the 9s get the first 25 threads query). I think someone'd already pointed out that running with sequential scan disabled is generally a bad idea, so I didn't think it was worth mentioning again.
Are there any updates/deleteed/adding to the db?What did help you ?Are you using transactions? > gain in performance. All of my optimizations seem to help at first, but > everything soon gets just as slow as it was before... and I mean SLOW. > -- Evgeny.
it sometimes happens that mysql is faster .obviosly you have a case when you have to try force the query to use indexes . here are some not nice and trivial solutions: [1] add another column that marks the rows that are in the upper 25 now, set up trigger to update the field .that would be VERY fast (probably much more faster then mySql or any design in any db). [2] add a column that inserts a serial number of every row ,set a trigger to update it on insert now use the max() to find the latest post and < max()-25 to find the others. I can suggest more ,but you got the idea. I understand that this complicates things and the design is less simple/nice ,but it should speed it up. Hope it helps. sorry for english . -- Evgeny.
I have a question: You say this happens under load and that it slows down. I would think that a problem with max connections would cause other problems but not the slowness. Someone can correct me though. What sort of performance tuning have you done? I suspect we can help you narrow down the problems. Historically, the default settings are designed around systems with limited memory so that it will *start* on anything. To get decent performance under load you have to change some settings. Also how much RAM do you have? Which version of PostgreSQL are you running? Best Wishes, Chris Travers Metatron Technology Consulting Christian Fowler wrote: > > >> I would like to also add that I've been a sysadmin for quite some >> time, and >> I've been designing databases for quite some time as well. I'm no >> idiot, I >> just can't find the bottleneck here (if one does in fact exist). So >> in light >> of this, please send me some suggestions I can work with. > > > Here are three concise suggestions: > > 1. You say your issue is only under load, then I can probably > guarantee your issue is available connections: > > max_connections = 50 > > That number is way too small for a site with decent traffic. make it > like 500. how mindful are you about opening connections per page view? > > 2. You have a query taking 9 seconds. run that query by hand on the > shell to find out why. Rework the query, add or recreate indices as > necessary. > Using conditionals in ORDER BY is a bad idea. Do it in the WHERE > clause per my previous email. > > 3. Leave your attitude at the door, or just leave. Making comments like: > >> I take vast offense to the suggestion that my query / database design >> is at fault here.... I must admit that I expected much more from this >> list than I've recieved so far. > > > make it clear that you have a lot more room for growth as a developer. > If you stop biting the hands that help you, you will learn a lot more. > >> One last thing... even with ALL of the data intact (and yes, we DID do >> testing... we just didn't have enough ppl to test the production server >> load) > > > Another mistake showing your lack of experience. Use apache bench ( ab > command ) to simulate load. > >> >> The only information I can give at the moment about the number of >> queries >> per second is this: there is an average of 60 users online at any given >> time, and the average number of queries per page load is 12, and they >> are >> refreshing and clicking etc quite a bit... so I'd say about 120 >> queries per >> second or so... (VERY rough estimate).... > > > And you only have 50 max_connections for postgres? What are you > thinking? Of course every apache process is waiting for a connection. > Also, do you even have enough apache workers? > > Your attitude sucks. Your problems are right under your nose, and you > are too cocky to learn your tools. I imagine you are getting hammered > by your co-workers to get things fixed. I will tell you empathy will > always get you much farther than animosity. > > > [ \ / > [ >X< spider@steelsun.com | http://www.steelsun.com/ > [ / \ > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 > >