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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
