Re: PostgreSQL 9.2.4 very slow on laptop with windows 8

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

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. 

Thanks
Girish Subbaramu


From: pella.samu@xxxxxxxxx
Date: Thu, 22 Aug 2013 17:25:51 +0200
Subject: Re: PostgreSQL 9.2.4 very slow on laptop with windows 8
To: gsubbaramu@xxxxxxxxxxx
CC: pgsql-performance@xxxxxxxxxxxxxx

As I see only Windows7 supported  ( with EnterpriseDB version of PostgreSQL 9.2 Windows installer )

Have you been tested with PostgreSQL 9.3 rc1 ?     same speed ?

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

Imre


2013/8/22 girish subbaramu <gsubbaramu@xxxxxxxxxxx>

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_modsoff
application_name
archive_command(disabled)
archive_modeoff
archive_timeout0
array_nullson
authentication_timeout1min
autovacuumon
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers3
autovacuum_naptime 1min
autovacuum_vacuum_cost_delay20ms
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor0.2
autovacuum_vacuum_threshold 50
backslash_quotesafe_encoding
bgwriter_delay 200ms
bgwriter_lru_maxpages100
bgwriter_lru_multiplier 2
block_size8192
bonjouroff
bonjour_name
bytea_outputescape
check_function_bodies on
checkpoint_completion_target0.5
checkpoint_segments 3
checkpoint_timeout5min
checkpoint_warning30s
client_encodingUTF8
client_min_messagesnotice
commit_delay0
commit_siblings5
config_fileC:/xxxx~2/POSTGR~1/data/postgresql.conf
constraint_exclusionpartition
cpu_index_tuple_cost0.005
cpu_operator_cost0.0025
cpu_tuple_cost0.01
cursor_tuple_fraction0.1
data_directory C:/xxx~2/POSTGR~1/data
DateStyleISO, MDY
db_user_namespace off
deadlock_timeout1s
debug_assertionsoff
debug_pretty_printon
debug_print_parseoff
debug_print_planoff
debug_print_rewrittenoff
default_statistics_target100
default_tablespace
default_text_search_config pg_catalog.english
default_transaction_deferrableoff
default_transaction_isolation read committed
default_transaction_read_onlyoff
default_with_oids off
dynamic_library_path$libdir
effective_cache_size 128MB
effective_io_concurrency0
enable_bitmapscan on
enable_hashaggon
enable_hashjoinon
enable_indexonlyscanon
enable_indexscanon
enable_materialon
enable_mergejoinon
enable_nestloop on
enable_seqscanon
enable_sorton
enable_tidscanon
escape_string_warningon
event_source PostgreSQL
exit_on_erroroff
external_pid_file
extra_float_digits0
from_collapse_limit8
fsyncon
full_page_writeson
geqoon
geqo_effort5
geqo_generations 0
geqo_pool_size0
geqo_seed0
geqo_selection_bias2
geqo_threshold12
gin_fuzzy_search_limit 0
hba_fileC:/xxxx~2/POSTGR~1/data/pg_hba.conf
hot_standby off
hot_standby_feedbackoff
ident_fileC:/xxxx~2/POSTGR~1/data/pg_ident.conf
ignore_system_indexesoff
integer_datetimeson
IntervalStylepostgres
join_collapse_limit8
krb_caseins_usersoff
krb_server_keyfile
krb_srvname postgres
lc_collateEnglish_United States.1252
lc_ctype English_United States.1252
lc_messagesEnglish_United States.1252
lc_monetaryEnglish_United States.1252
lc_numericEnglish_United States.1252
lc_timeEnglish_United States.1252
listen_addresses127.0.0.1
lo_compat_privilegesoff
local_preload_libraries
log_autovacuum_min_duration-1
log_checkpointsoff
log_connectionsoff
log_destinationstderr
log_directory pg_log
log_disconnectionsoff
log_durationoff
log_error_verbositydefault
log_executor_statsoff
log_file_mode0600
log_filenamepostgresql-%Y-%m-%d_%H%M%S.log
log_hostnameoff
log_line_prefix
log_lock_waitsoff
log_min_duration_statement-1
log_min_error_statementerror
log_min_messageswarning
log_parser_statsoff
log_planner_statsoff
log_rotation_age 1d
log_rotation_size10MB
log_statementnone
log_statement_statsoff
log_temp_files-1
log_timezoneAsia/Calcutta
log_truncate_on_rotationoff
logging_collectoron
maintenance_work_mem16MB
max_connections100
max_files_per_process1000
max_function_args 100
max_identifier_length63
max_index_keys32
max_locks_per_transaction64
max_pred_locks_per_transaction 64
max_prepared_transactions0
max_stack_depth 2MB
max_standby_archive_delay30s
max_standby_streaming_delay 30s
max_wal_senders0
password_encryptionon
port5432
post_auth_delay0
pre_auth_delay0
quote_all_identifiersoff
random_page_cost 4
replication_timeout1min
restart_after_crash on
search_path"$user",vipl
segment_size 1GB
seq_page_cost1
server_encodingUTF8
server_version9.2.4
server_version_num90204
session_replication_role origin
shared_buffers1GB
shared_preload_libraries
sql_inheritanceon
ssloff
ssl_ca_file
ssl_cert_fileserver.crt
ssl_ciphers ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH
ssl_crl_file
ssl_key_file server.key
ssl_renegotiation_limit512MB
standard_conforming_strings on
statement_timeout0
stats_temp_directorypg_stat_tmp
superuser_reserved_connections3
synchronize_seqscans on
synchronous_commiton
synchronous_standby_names
syslog_facilitynone
syslog_identpostgres
tcp_keepalives_count0
tcp_keepalives_idle-1
tcp_keepalives_interval -1
temp_buffers16MB
temp_file_limit-1
temp_tablespaces
TimeZoneAsia/Calcutta
timezone_abbreviationsDefault
trace_notifyoff
trace_recovery_messageslog
trace_sortoff
track_activities on
track_activity_query_size1024
track_counts on
track_functionsnone
track_io_timingoff
transaction_deferrableoff
transaction_isolationread committed
transaction_read_onlyoff
transform_null_equalsoff
unix_socket_directory
unix_socket_group
unix_socket_permissions0777
update_process_titleon
vacuum_cost_delay0
vacuum_cost_limit200
vacuum_cost_page_dirty 20
vacuum_cost_page_hit1
vacuum_cost_page_miss 10
vacuum_defer_cleanup_age0
vacuum_freeze_min_age 50000000
vacuum_freeze_table_age150000000
wal_block_size 8192
wal_buffers16MB
wal_keep_segments0
wal_levelminimal
wal_receiver_status_interval10s
wal_segment_size16MB
wal_sync_methodopen_datasync
wal_writer_delay200ms
work_mem512MB
xmlbinarybase64
xmloptioncontent
zero_damaged_pages off




Thanks
Girish Subbaramu.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux