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.
NameSettingallow_system_table_modsoffapplication_namearchive_command(disabled)archive_modeoffarchive_timeout0array_nullsonauthentication_timeout1minautovacuumonautovacuum_analyze_scale_factor0.1autovacuum_analyze_threshold50autovacuum_freeze_max_age200000000autovacuum_max_workers3autovacuum_naptime1minautovacuum_vacuum_cost_delay20msautovacuum_vacuum_cost_limit-1autovacuum_vacuum_scale_factor0.2autovacuum_vacuum_threshold50backslash_quotesafe_encodingbgwriter_delay200msbgwriter_lru_maxpages100bgwriter_lru_multiplier2block_size8192bonjouroffbonjour_namebytea_outputescapecheck_function_bodiesoncheckpoint_completion_target0.5checkpoint_segments3checkpoint_timeout5mincheckpoint_warning30sclient_encodingUTF8client_min_messagesnoticecommit_delay0commit_siblings5config_fileC:/xxxx~2/POSTGR~1/data/postgresql.confconstraint_exclusionpartitioncpu_index_tuple_cost0.005cpu_operator_cost0.0025cpu_tuple_cost0.01cursor_tuple_fraction0.1data_directoryC:/xxx~2/POSTGR~1/dataDateStyleISO,
MDYdb_user_namespaceoffdeadlock_timeout1sdebug_assertionsoffdebug_pretty_printondebug_print_parseoffdebug_print_planoffdebug_print_rewrittenoffdefault_statistics_target100default_tablespacedefault_text_search_configpg_catalog.englishdefault_transaction_deferrableoffdefault_transaction_isolationread
committeddefault_transaction_read_onlyoffdefault_with_oidsoffdynamic_library_path$libdireffective_cache_size128MBeffective_io_concurrency0enable_bitmapscanonenable_hashaggonenable_hashjoinonenable_indexonlyscanonenable_indexscanonenable_materialonenable_mergejoinonenable_nestlooponenable_seqscanonenable_sortonenable_tidscanonescape_string_warningonevent_sourcePostgreSQLexit_on_erroroffexternal_pid_fileextra_float_digits0from_collapse_limit8fsynconfull_page_writesongeqoongeqo_effort5geqo_generations0geqo_pool_size0geqo_seed0geqo_selection_bias2geqo_threshold12gin_fuzzy_search_limit0hba_fileC:/xxxx~2/POSTGR~1/data/pg_hba.confhot_standbyoffhot_standby_feedbackoffident_fileC:/xxxx~2/POSTGR~1/data/pg_ident.confignore_system_indexesoffinteger_datetimesonIntervalStylepostgresjoin_collapse_limit8krb_caseins_usersoffkrb_server_keyfilekrb_srvnamepostgreslc_collateEnglish_United
States.1252lc_ctypeEnglish_United States.1252lc_messagesEnglish_United
States.1252lc_monetaryEnglish_United States.1252lc_numericEnglish_United
States.1252lc_timeEnglish_United
States.1252listen_addresses127.0.0.1lo_compat_privilegesofflocal_preload_librarieslog_autovacuum_min_duration-1log_checkpointsofflog_connectionsofflog_destinationstderrlog_directorypg_loglog_disconnectionsofflog_durationofflog_error_verbositydefaultlog_executor_statsofflog_file_mode0600log_filenamepostgresql-%Y-%m-%d_%H%M%S.loglog_hostnameofflog_line_prefixlog_lock_waitsofflog_min_duration_statement-1log_min_error_statementerrorlog_min_messageswarninglog_parser_statsofflog_planner_statsofflog_rotation_age1dlog_rotation_size10MBlog_statementnonelog_statement_statsofflog_temp_files-1log_timezoneAsia/Calcuttalog_truncate_on_rotationofflogging_collectoronmaintenance_work_mem16MBmax_connections100max_files_per_process1000max_function_args100max_identifier_length63max_index_keys32max_locks_per_transaction64max_pred_locks_per_transaction64max_prepared_transactions0max_stack_depth2MBmax_standby_archive_delay30smax_standby_streaming_delay30smax_wal_senders0password_encryptiononport5432post_auth_delay0pre_auth_delay0quote_all_identifiersoffrandom_page_cost4replication_timeout1minrestart_after_crashonsearch_path"$user",viplsegment_size1GBseq_page_cost1server_encodingUTF8server_version9.2.4server_version_num90204session_replication_roleoriginshared_buffers1GBshared_preload_librariessql_inheritanceonssloffssl_ca_filessl_cert_fileserver.crtssl_ciphersALL:!ADH:!LOW:!EXP:!MD5:@STRENGTHssl_crl_filessl_key_fileserver.keyssl_renegotiation_limit512MBstandard_conforming_stringsonstatement_timeout0stats_temp_directorypg_stat_tmpsuperuser_reserved_connections3synchronize_seqscansonsynchronous_commitonsynchronous_standby_namessyslog_facilitynonesyslog_identpostgrestcp_keepalives_count0tcp_keepalives_idle-1tcp_keepalives_interval-1temp_buffers16MBtemp_file_limit-1temp_tablespacesTimeZoneAsia/Calcuttatimezone_abbreviationsDefaulttrace_notifyofftrace_recovery_messageslogtrace_sortofftrack_activitiesontrack_activity_query_size1024track_countsontrack_functionsnonetrack_io_timingofftransaction_deferrableofftransaction_isolationread
committedtransaction_read_onlyofftransform_null_equalsoffunix_socket_directoryunix_socket_groupunix_socket_permissions0777update_process_titleonvacuum_cost_delay0vacuum_cost_limit200vacuum_cost_page_dirty20vacuum_cost_page_hit1vacuum_cost_page_miss10vacuum_defer_cleanup_age0vacuum_freeze_min_age50000000vacuum_freeze_table_age150000000wal_block_size8192wal_buffers16MBwal_keep_segments0wal_levelminimalwal_receiver_status_interval10swal_segment_size16MBwal_sync_methodopen_datasyncwal_writer_delay200mswork_mem512MBxmlbinarybase64xmloptioncontentzero_damaged_pagesoff
ThanksGirish Subbaramu.
