Re: PostgreSQL 9.2.4 very slow on laptop with windows 8

Hi ,
Thanks for the inputs and direction.With the help of the support table i narrowed down to windows 2008 and 2003 servers.32 bit postgres 9.2.4 was very slow on windows 2008 (64 bit ), on running postgres 9.2.4 64 bit the response time was similar to what i used to get on windows 7. I did not have to do any performance tuning , the defaults worked.This works good for me.With this i was able to resolve postgres issues.
ThanksGirish Subbaramu

From: pella(dot)samu(at)gmail(dot)com
Date: Thu, 22 Aug 2013 17:25:51 +0200
Subject: Re: [PERFORM] PostgreSQL 9.2.4 very slow on laptop with windows 8
To: gsubbaramu(at)hotmail(dot)com
CC: pgsql-performance(at)postgresql(dot)org

As I see only Windows7 supported ( with EnterpriseDB version of PostgreSQL 9.2 Windows installer )http://www.enterprisedb.com/products-services-training/products-overview/postgresql-overview/supported-platforms-and-release-lif

Have you been tested with PostgreSQL 9.3 rc1 ? same speed ?http://www.enterprisedb.com/products-services-training/pgdevdownload

and some testing tips:
- modify laptop power settings
- compare disk speeds (laptop vs. desktop )
- ...

Imre

2013/8/22 girish subbaramu <gsubbaramu(at)hotmail(dot)com>

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

Settingallow_system_table_modsoffapplication_name

archive_command(disabled)archive_modeoff

archive_timeout0array_nullson

authentication_timeout1minautovacuumonautovacuum_analyze_scale_factor

0.1autovacuum_analyze_threshold50autovacuum_freeze_max_age

200000000autovacuum_max_workers3autovacuum_naptime

1minautovacuum_vacuum_cost_delay20msautovacuum_vacuum_cost_limit

-1autovacuum_vacuum_scale_factor0.2autovacuum_vacuum_threshold

50backslash_quotesafe_encodingbgwriter_delay

200msbgwriter_lru_maxpages100bgwriter_lru_multiplier

2block_size8192bonjouroff

bonjour_namebytea_outputescapecheck_function_bodies

oncheckpoint_completion_target0.5checkpoint_segments

3checkpoint_timeout5mincheckpoint_warning30s

client_encodingUTF8client_min_messagesnotice

commit_delay0commit_siblings5

config_fileC:/xxxx~2/POSTGR~1/data/postgresql.confconstraint_exclusionpartition

cpu_index_tuple_cost0.005cpu_operator_cost0.0025

cpu_tuple_cost0.01cursor_tuple_fraction0.1
data_directory
C:/xxx~2/POSTGR~1/dataDateStyleISO, MDYdb_user_namespace

offdeadlock_timeout1sdebug_assertionsoff

debug_pretty_printondebug_print_parseoff

debug_print_planoffdebug_print_rewrittenoff

default_statistics_target100default_tablespace
default_text_search_config
pg_catalog.englishdefault_transaction_deferrableoff
default_transaction_isolation
read committeddefault_transaction_read_onlyoffdefault_with_oids

offdynamic_library_path$libdireffective_cache_size

128MBeffective_io_concurrency0enable_bitmapscan

onenable_hashaggonenable_hashjoinon

enable_indexonlyscanonenable_indexscanon

enable_materialonenable_mergejoinonenable_nestloop

onenable_seqscanonenable_sorton

enable_tidscanonescape_string_warningonevent_source

PostgreSQLexit_on_erroroffexternal_pid_file

extra_float_digits0from_collapse_limit8

fsynconfull_page_writeson

geqoongeqo_effort5geqo_generations

0geqo_pool_size0geqo_seed0

geqo_selection_bias2geqo_threshold12gin_fuzzy_search_limit

0hba_fileC:/xxxx~2/POSTGR~1/data/pg_hba.confhot_standby

offhot_standby_feedbackoffident_fileC:/xxxx~2/POSTGR~1/data/pg_ident.conf

ignore_system_indexesoffinteger_datetimeson

IntervalStylepostgresjoin_collapse_limit8

krb_caseins_usersoffkrb_server_keyfilekrb_srvname

postgreslc_collateEnglish_United States.1252lc_ctype

English_United States.1252lc_messagesEnglish_United States.1252

lc_monetaryEnglish_United States.1252lc_numericEnglish_United States.1252

lc_timeEnglish_United States.1252listen_addresses127.0.0.1

lo_compat_privilegesofflocal_preload_libraries

log_autovacuum_min_duration-1log_checkpointsoff

log_connectionsofflog_destinationstderrlog_directory

pg_loglog_disconnectionsofflog_durationoff

log_error_verbositydefaultlog_executor_statsoff

log_file_mode0600log_filenamepostgresql-%Y-%m-%d_%H%M%S.log

log_hostnameofflog_line_prefix

log_lock_waitsofflog_min_duration_statement-1

log_min_error_statementerrorlog_min_messageswarning

log_parser_statsofflog_planner_statsofflog_rotation_age

1dlog_rotation_size10MBlog_statementnone

log_statement_statsofflog_temp_files-1

log_timezoneAsia/Calcuttalog_truncate_on_rotationoff

logging_collectoronmaintenance_work_mem16MB

max_connections100max_files_per_process1000max_function_args

100max_identifier_length63max_index_keys32

max_locks_per_transaction64max_pred_locks_per_transaction

64max_prepared_transactions0max_stack_depth

2MBmax_standby_archive_delay30smax_standby_streaming_delay

30smax_wal_senders0password_encryptionon

port5432post_auth_delay0

pre_auth_delay0quote_all_identifiersoffrandom_page_cost

4replication_timeout1minrestart_after_crash

onsearch_path"$user",viplsegment_size

1GBseq_page_cost1server_encodingUTF8

server_version9.2.4server_version_num90204
session_replication_role
originshared_buffers1GBshared_preload_libraries

sql_inheritanceonssloff

ssl_ca_filessl_cert_fileserver.crtssl_ciphers

ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTHssl_crl_filessl_key_file

server.keyssl_renegotiation_limit512MBstandard_conforming_strings

onstatement_timeout0stats_temp_directorypg_stat_tmp

superuser_reserved_connections3synchronize_seqscans
on
synchronous_commitonsynchronous_standby_names

syslog_facilitynonesyslog_identpostgres

tcp_keepalives_count0tcp_keepalives_idle-1
tcp_keepalives_interval
-1temp_buffers16MBtemp_file_limit-1

temp_tablespacesTimeZoneAsia/Calcutta

timezone_abbreviationsDefaulttrace_notifyoff

trace_recovery_messageslogtrace_sortofftrack_activities

ontrack_activity_query_size1024track_counts

ontrack_functionsnonetrack_io_timingoff

transaction_deferrableofftransaction_isolationread committed

transaction_read_onlyofftransform_null_equalsoff

unix_socket_directoryunix_socket_group

unix_socket_permissions0777update_process_titleon

vacuum_cost_delay0vacuum_cost_limit200vacuum_cost_page_dirty

20vacuum_cost_page_hit1vacuum_cost_page_miss

10vacuum_defer_cleanup_age0vacuum_freeze_min_age

50000000vacuum_freeze_table_age150000000wal_block_size

8192wal_buffers16MBwal_keep_segments0

wal_levelminimalwal_receiver_status_interval10s

wal_segment_size16MBwal_sync_methodopen_datasync

wal_writer_delay200mswork_mem512MB

xmlbinarybase64xmloptioncontentzero_damaged_pages

off

ThanksGirish Subbaramu.