Hi,
I am running PostgreSQL 9.2.4 on windows 8 , 64 bit operating system , 4GB RAM.
A laptop with i3 - 3110M , 2.4 GHZ .
The database came bundled with wapp stack 5.4.17-0. We have an php application that serves data from PostgreSQL 9.2.4.
The configuration runs with very good performance (3 sec response php + db ) on windows 7 32, 64 bit OS , 4GB RAM (desktops with i3-2100 3.10 GHZ ) .
But take around 25 seconds to render on windows 8 , the laptop.
I was able to eliminate php , as the performance was as expected. (without DB calls)
On the other part the database calls take more than 100 ms for simple queries (Example a table with just 10 row sometimes takes around 126 ms). This information i was able to collect from the pg_log.
The php pages have multiple queries in them, a single query works as expected, but running multiple queries in the page causes the db performance to go down. Please note this setup is working fine (3 sec overall including php ) on all windows 7 32, 64 bit OS , desktops.
Appreciate help in giving me an direction on how to get to the issue.
The db size is 11mb only. Most of the tables have less than 100 rows with appropriate indexes. Some tables have more than 1000 rows , are not queried in the php pages . The super user login is used from php . (Changing super user reserved connections did not help, tried changing shared _buffers and other setting , none of the setting seem to have any effect on the db performance )
Following are the variable settings that works fine on on all windows 7 32, 64 bit OS , desktops.
| Name | Setting |
|---|
| allow_system_table_mods | off |
| application_name | |
| archive_command | (disabled) |
| archive_mode | off |
| archive_timeout | 0 |
| array_nulls | on |
| authentication_timeout | 1min |
| autovacuum | on |
| autovacuum_analyze_scale_factor | 0.1 |
| autovacuum_analyze_threshold | 50 |
| autovacuum_freeze_max_age | 200000000 |
| autovacuum_max_workers | 3 |
| autovacuum_naptime | 1min |
| autovacuum_vacuum_cost_delay | 20ms |
| autovacuum_vacuum_cost_limit | -1 |
| autovacuum_vacuum_scale_factor | 0.2 |
| autovacuum_vacuum_threshold | 50 |
| backslash_quote | safe_encoding |
| bgwriter_delay | 200ms |
| bgwriter_lru_maxpages | 100 |
| bgwriter_lru_multiplier | 2 |
| block_size | 8192 |
| bonjour | off |
| bonjour_name | |
| bytea_output | escape |
| check_function_bodies | on |
| checkpoint_completion_target | 0.5 |
| checkpoint_segments | 3 |
| checkpoint_timeout | 5min |
| checkpoint_warning | 30s |
| client_encoding | UTF8 |
| client_min_messages | notice |
| commit_delay | 0 |
| commit_siblings | 5 |
| config_file | C:/xxxx~2/POSTGR~1/data/postgresql.conf |
| constraint_exclusion | partition |
| cpu_index_tuple_cost | 0.005 |
| cpu_operator_cost | 0.0025 |
| cpu_tuple_cost | 0.01 |
| cursor_tuple_fraction | 0.1 |
| data_directory | C:/xxx~2/POSTGR~1/data |
| DateStyle | ISO, MDY |
| db_user_namespace | off |
| deadlock_timeout | 1s |
| debug_assertions | off |
| debug_pretty_print | on |
| debug_print_parse | off |
| debug_print_plan | off |
| debug_print_rewritten | off |
| default_statistics_target | 100 |
| default_tablespace | |
| default_text_search_config | pg_catalog.english |
| default_transaction_deferrable | off |
| default_transaction_isolation | read committed |
| default_transaction_read_only | off |
| default_with_oids | off |
| dynamic_library_path | $libdir |
| effective_cache_size | 128MB |
| effective_io_concurrency | 0 |
| enable_bitmapscan | on |
| enable_hashagg | on |
| enable_hashjoin | on |
| enable_indexonlyscan | on |
| enable_indexscan | on |
| enable_material | on |
| enable_mergejoin | on |
| enable_nestloop | on |
| enable_seqscan | on |
| enable_sort | on |
| enable_tidscan | on |
| escape_string_warning | on |
| event_source | PostgreSQL |
| exit_on_error | off |
| external_pid_file | |
| extra_float_digits | 0 |
| from_collapse_limit | 8 |
| fsync | on |
| full_page_writes | on |
| geqo | on |
| geqo_effort | 5 |
| geqo_generations | 0 |
| geqo_pool_size | 0 |
| geqo_seed | 0 |
| geqo_selection_bias | 2 |
| geqo_threshold | 12 |
| gin_fuzzy_search_limit | 0 |
| hba_file | C:/xxxx~2/POSTGR~1/data/pg_hba.conf |
| hot_standby | off |
| hot_standby_feedback | off |
| ident_file | C:/xxxx~2/POSTGR~1/data/pg_ident.conf |
| ignore_system_indexes | off |
| integer_datetimes | on |
| IntervalStyle | postgres |
| join_collapse_limit | 8 |
| krb_caseins_users | off |
| krb_server_keyfile | |
| krb_srvname | postgres |
| lc_collate | English_United States.1252 |
| lc_ctype | English_United States.1252 |
| lc_messages | English_United States.1252 |
| lc_monetary | English_United States.1252 |
| lc_numeric | English_United States.1252 |
| lc_time | English_United States.1252 |
| listen_addresses | 127.0.0.1 |
| lo_compat_privileges | off |
| local_preload_libraries | |
| log_autovacuum_min_duration | -1 |
| log_checkpoints | off |
| log_connections | off |
| log_destination | stderr |
| log_directory | pg_log |
| log_disconnections | off |
| log_duration | off |
| log_error_verbosity | default |
| log_executor_stats | off |
| log_file_mode | 0600 |
| log_filename | postgresql-%Y-%m-%d_%H%M%S.log |
| log_hostname | off |
| log_line_prefix | |
| log_lock_waits | off |
| log_min_duration_statement | -1 |
| log_min_error_statement | error |
| log_min_messages | warning |
| log_parser_stats | off |
| log_planner_stats | off |
| log_rotation_age | 1d |
| log_rotation_size | 10MB |
| log_statement | none |
| log_statement_stats | off |
| log_temp_files | -1 |
| log_timezone | Asia/Calcutta |
| log_truncate_on_rotation | off |
| logging_collector | on |
| maintenance_work_mem | 16MB |
| max_connections | 100 |
| max_files_per_process | 1000 |
| max_function_args | 100 |
| max_identifier_length | 63 |
| max_index_keys | 32 |
| max_locks_per_transaction | 64 |
| max_pred_locks_per_transaction | 64 |
| max_prepared_transactions | 0 |
| max_stack_depth | 2MB |
| max_standby_archive_delay | 30s |
| max_standby_streaming_delay | 30s |
| max_wal_senders | 0 |
| password_encryption | on |
| port | 5432 |
| post_auth_delay | 0 |
| pre_auth_delay | 0 |
| quote_all_identifiers | off |
| random_page_cost | 4 |
| replication_timeout | 1min |
| restart_after_crash | on |
| search_path | "$user",vipl |
| segment_size | 1GB |
| seq_page_cost | 1 |
| server_encoding | UTF8 |
| server_version | 9.2.4 |
| server_version_num | 90204 |
| session_replication_role | origin |
| shared_buffers | 1GB |
| shared_preload_libraries | |
| sql_inheritance | on |
| ssl | off |
| ssl_ca_file | |
| ssl_cert_file | server.crt |
| ssl_ciphers | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH |
| ssl_crl_file | |
| ssl_key_file | server.key |
| ssl_renegotiation_limit | 512MB |
| standard_conforming_strings | on |
| statement_timeout | 0 |
| stats_temp_directory | pg_stat_tmp |
| superuser_reserved_connections | 3 |
| synchronize_seqscans | on |
| synchronous_commit | on |
| synchronous_standby_names | |
| syslog_facility | none |
| syslog_ident | postgres |
| tcp_keepalives_count | 0 |
| tcp_keepalives_idle | -1 |
| tcp_keepalives_interval | -1 |
| temp_buffers | 16MB |
| temp_file_limit | -1 |
| temp_tablespaces | |
| TimeZone | Asia/Calcutta |
| timezone_abbreviations | Default |
| trace_notify | off |
| trace_recovery_messages | log |
| trace_sort | off |
| track_activities | on |
| track_activity_query_size | 1024 |
| track_counts | on |
| track_functions | none |
| track_io_timing | off |
| transaction_deferrable | off |
| transaction_isolation | read committed |
| transaction_read_only | off |
| transform_null_equals | off |
| unix_socket_directory | |
| unix_socket_group | |
| unix_socket_permissions | 0777 |
| update_process_title | on |
| vacuum_cost_delay | 0 |
| vacuum_cost_limit | 200 |
| vacuum_cost_page_dirty | 20 |
| vacuum_cost_page_hit | 1 |
| vacuum_cost_page_miss | 10 |
| vacuum_defer_cleanup_age | 0 |
| vacuum_freeze_min_age | 50000000 |
| vacuum_freeze_table_age | 150000000 |
| wal_block_size | 8192 |
| wal_buffers | 16MB |
| wal_keep_segments | 0 |
| wal_level | minimal |
| wal_receiver_status_interval | 10s |
| wal_segment_size | 16MB |
| wal_sync_method | open_datasync |
| wal_writer_delay | 200ms |
| work_mem | 512MB |
| xmlbinary | base64 |
| xmloption | content |
| zero_damaged_pages | off
|
Thanks
Girish Subbaramu.
