Re: [PERFORM] PostgreSQL 9.2.4 very slow on laptop with windows 8

[Imre Samu]

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 gsubbar...@hotmail.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.1
 autovacuum_analyze_threshold50autovacuum_freeze_max_age 2
 autovacuum_max_workers3autovacuum_naptime 1minautovacuum_vacuum_cost_delay
 20msautovacuum_vacuum_cost_limit -1autovacuum_vacuum_scale_factor0.2
 autovacuum_vacuum_threshold 50backslash_quotesafe_encodingbgwriter_delay200ms
 bgwriter_lru_maxpages100bgwriter_lru_multiplier 2block_size8192bonjouroff
 bonjour_namebytea_outputescapecheck_function_bodies on
 checkpoint_completion_target0.5checkpoint_segments 3checkpoint_timeout5min
 checkpoint_warning30s client_encodingUTF8client_min_messagesnotice
 commit_delay0commit_siblings5 config_file
 C:/~2/POSTGR~1/data/postgresql.confconstraint_exclusionpartition
 cpu_index_tuple_cost0.005cpu_operator_cost0.0025 cpu_tuple_cost0.01
 cursor_tuple_fraction0.1data_directory C:/xxx~2/POSTGR~1/dataDateStyleISO,
 MDYdb_user_namespace offdeadlock_timeout1sdebug_assertionsoff
 debug_pretty_printondebug_print_parseoff debug_print_planoff
 debug_print_rewrittenoff default_statistics_target100default_tablespace
 default_text_search_config pg_catalog.english
 default_transaction_deferrableoffdefault_transaction_isolation read
 committeddefault_transaction_read_onlyoffdefault_with_oids off
 dynamic_library_path$libdireffective_cache_size 128MB
 effective_io_concurrency0enable_bitmapscan onenable_hashaggon
 enable_hashjoinon enable_indexonlyscanonenable_indexscanon enable_material
 onenable_mergejoinonenable_nestloop onenable_seqscanonenable_sorton
 enable_tidscanonescape_string_warningonevent_source PostgreSQL
 exit_on_erroroffexternal_pid_file extra_float_digits0from_collapse_limit8
 fsynconfull_page_writeson geqoongeqo_effort5geqo_generations 0
 geqo_pool_size0geqo_seed0 geqo_selection_bias2geqo_threshold12
 gin_fuzzy_search_limit 0hba_fileC:/~2/POSTGR~1/data/pg_hba.conf
 hot_standby offhot_standby_feedbackoffident_file
 C:/~2/POSTGR~1/data/pg_ident.conf ignore_system_indexesoff
 integer_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_verbositydefault
 log_executor_statsoff log_file_mode0600log_filename
 postgresql-%Y-%m-%d_%H%M%S.log log_hostnameofflog_line_prefix
 

Re: [PERFORM] postgres 9.3 vs. 9.4

[Imre Samu]

Hi Tigran,

my debugging tips:

Some technical details:
Host: rhel 6.5 2.6.32-431.23.3.el6.x86_64
256 GB RAM, 40 cores, Intel(R) Xeon(R) CPU E5-2660 v2 @ 2.20GHz
2x160GB  PCIe SSD DELL_P320h-MTFDGAL175SAH

As I know PCIe SSD DELL_P320h = Micron P320h
(
http://www.micron.com/products/solid-state-storage/enterprise-pcie-ssd/p320h-25-inch-pcie-ssd
)

so my suggestions:

#1.   Check/Upgrade to the latest Micron kernel driver
**
https://www.google.com/search?q=p420m_p320h_hhhl_installation_guide%2520_.pdf
*** RHEL version 6.1–6.5: kmod-mtip32xx-version.el6.x86_64_rhel6ux.rpm

#2. And check Technical Note P320h/P420m SSD Performance Optimization and
Testing
**
https://www.google.com/search?q=tnfd15_micron_pciessd_performance_testing.pdf

strange :
 Table 1: PCIe SSD Hardware and Software Requirements 
* Processors with clock speeds greater than 3 GHz (recommended for
best performance) * ( you have now:  2.20GHz )*
* *Up to 8 CPU cores *(logical + physical) with hyperthreading
(recommended)* ( you have 20! )*

so  check performance with* Turning off Hyper-Threading *

Imre

2014-09-24 15:03 GMT+02:00 Mkrtchyan, Tigran tigran.mkrtch...@desy.de:


 With pg_test_timing I can see, that overhead is 48 nsec on my server and
 32 nsec on the laptop.
 what makes this difference and have it any influence on the overall
 performance?

 Tigran.

 - Original Message -
  From: Mark Kirkwood mark.kirkw...@catalyst.net.nz
  To: Tigran Mkrtchyan tigran.mkrtch...@desy.de, Merlin Moncure 
 mmonc...@gmail.com
  Cc: postgres performance list pgsql-performance@postgresql.org
  Sent: Wednesday, September 24, 2014 12:04:12 PM
  Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
 
  On 24/09/14 21:23, Mkrtchyan, Tigran wrote:
   Hi Merlin et al.
  
   after building postgres 9.4 myself from sources I get the same
 performance
   as
   with 9.3. The difference was in the value of debug_assertions setting.
  
   Now the next step. Why my 3 years old laptop gets x1.8 times more tps
 than
   my one month old server?
   And Mark Kirkwood's desktop gets x2 times more tps as well? Is there
 some
   special optimization
   for i7 which does not work with Intel(R) Xeon(R) CPU E5-2660?
  
  
 
  Yes - firstly, nicely done re finding the assertions (my 9.4 beta2 was
  built from src - never thought to mention sorry)!
 
  I'd guess that you are seeing some bios setting re the p320 SSD - it
  *should* be seriously fast...but does not seem to be. You could try
  running some pure IO benchmarks to confirm this (e.g fio). Also see if
  the manual for however it is attached to the system allows for some
  optimized-for-ssd settings that tend to work better (altho these usually
  imply the drive is plugged into an adapter card of some kind - mind you
  your p320 *does* used a custom connector that does 2.5 SATA to PCIe
  style interconnect so I'd look to debug that first).
 
  Cheers
 
  Mark
 
 


 --
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] can't explain commit performance win7 vs linux : 8000/s vs 419/s

[Imre Samu]

my guess:
- maybe the NTFS compression is enabled?  [  "Compress this drive to save
disk space” ? ]  [  your test data is ideal for compression:  VALUES
('AA.. 250 times') ]
- or Windows Samsung Magician extreme settings?  or RAPID mode cache
enabled?

"RAPID mode is a RAM caching feature. Samsung’s RAPID white paper states
that RAPID works by analyzing “system traffic and leverages spare system
resources (DRAM and CPU) to deliver read acceleration through intelligent
caching of hot data and write optimization through tight coordination with
the SSD.”
http://www.thessdreview.com/software-2/samsung-magician-4-5-rapid-mode-2-1-testing/



on Ubuntu16.04  (+ Samsung SSD 840 PRO )I use "Samsung SSD Magician DC
"  trim optimization   [ "sudo ./magician -d 0 -T " ]
http://jcutrer.com/howto/linux/samsung-magician-command-line-linux

$ sudo ./magician

Samsung(R) SSD Magician DC Version 1.0
Copyright (c) 2014 Samsung Corporation

Usage:  ./magician  [operation] ..

Allowed Operations:
-L[ --list]  Shows a disk(s) attached to the system.
-F[ --firmware-update]   Updates firmware to specified disk.
-E[ --erase] Securely Erases all data from specified disk.
-O[ --over-provision]Performs one of the Over-Provisioning related
 operations on specified disk.
*-T[ --trim]  Optimizes specified disk.  *

-S[ --smart] Shows S.M.A.R.T values of specified disk.
-M[ --setmax]Performs SetMax related operations on specified
disk.
-W[ --writecache]Enables/Disables Write Cache on specified disk.
-X[ --sctcachestate] Gets the SCT write cache state for specified disk.
-C[ --command-history]   Shows history of the previously executed commands.
-I[ --info]  Displays the disk details to the user.
-license Shows the End User License Agreement.
-H[ --help]  Shows detailed Help.


regards,
 Imre


2016-06-25 18:19 GMT+02:00 t.dalpo...@gmail.com :

> Hi,
> I've postgres 9.5.3 installed on win7 64 bit, and ubuntu 16.04tls 64 bit,
> same SSD (evo 850 pro) , two different partitions. Laptop is 3.8Ghz.
> I've in each partition a simple database with one table called data256
> with one column of 256 char.
> I wrote a program using libpq which:
> 1 connects to 127.0.0.1 to the server
> 2 drops and recreates the table;
> 3 executes 2000 times the exec() function with the command  "INSERT INTO
> data256 VALUES ('AA.. 250 times')"
> I want to commit after every insert of course.
> The program is the same both in win and linux; in ansi c, so it's portable.
>
> Performance:
> Win7: 8000 write/sec
> Linux: 419 write/sec
>
> I don't figure out why such a difference. Also what should I expect? Which
> one is reasonable?
>
> I compared the two postgresql.conf, they're identical (except obvious
> things), they're the default ones, I didn't touch them. I just tried to
> disable ssl in one because it was set but nothing changes.
> I didn't go into deeper analysis because the source C file used for test
> is the same and the two postgresql.conf are identical.
>
> Then, in order to test write / flush without postgres, I made another C
> program, to open a file in writing, and for 1000 times : write 256 bytes
> and flush them (using fsync in linux and FlushFileBuffers in win).
> Win7: 200 write/sec
> Linux: 100 write/sec
>
>
>
>
> Thanks
> Pupillo
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Poor disk (virtio) Performance Inside KVM virt-machine vs host machine

[Imre Samu]

>I've noticed that there is a huge (more than ~3x slower) performance
difference between KVM guest and host machine.

I don't know that this is relevant or not , but there is an IBM research
paper (Published in 2014)
"IBM Research Report - An Updated Performance Comparison of Virtual
Machines and Linux Containers"
http://domino.research.ibm.com/library/cyberdig.nsf/papers/0929052195DD819C85257D2300681E7B/
->  " As we would expect, Docker introduces no overhead compared to Linux,
but KVM delivers only half as many IOPS because each I/O operation must go
through QEMU. While the VM’s absolute performance is still quite high, it
uses more CPU cycles per I/O operation, leaving less CPU available for
application work. Figure 7 shows that KVM increases read latency by 2-3x, a
crucial metric for some real workloads."

Imre




2016-04-26 16:03 GMT+02:00 Artem Tomyuk :

> Hi All.
>
> I've noticed that there is a huge (more than ~3x slower) performance
> difference between KVM guest and host machine.
> Host machine:
> dell r720xd
> RAID10 with 12 SAS 15 k drives and RAID0  with 2*128 GB INTEL SSD drives
> in Dell CacheCade mode.
>
> *On the KVM guest:*
>
>  /usr/pgsql-9.4/bin/pg_test_fsync -f test.sync
>
> 5 seconds per test
>
> O_DIRECT supported on this platform for open_datasync and open_sync.
>
>
> Compare file sync methods using one 8kB write:
>
> (in wal_sync_method preference order, except fdatasync
>
> is Linux's default)
>
> open_datasync  5190.279 ops/sec 193
> usecs/op
>
> fdatasync  4022.553 ops/sec 249
> usecs/op
>
> fsync  3069.069 ops/sec 326
> usecs/op
>
> fsync_writethrough  n/a
>
> open_sync  4892.348 ops/sec 204
> usecs/op
>
>
> Compare file sync methods using two 8kB writes:
>
> (in wal_sync_method preference order, except fdatasync
>
> is Linux's default)
>
> open_datasync  2406.577 ops/sec 416
> usecs/op
>
> fdatasync  4309.413 ops/sec 232
> usecs/op
>
> fsync  3518.844 ops/sec 284
> usecs/op
>
> fsync_writethrough  n/a
>
> open_sync  1159.604 ops/sec 862
> usecs/op
>
>
> Compare open_sync with different write sizes:
>
> (This is designed to compare the cost of writing 16kB
>
> in different write open_sync sizes.)
>
>  1 * 16kB open_sync write  3700.689 ops/sec 270
> usecs/op
>
>  2 *  8kB open_sync writes 2581.405 ops/sec 387
> usecs/op
>
>  4 *  4kB open_sync writes 1318.871 ops/sec 758
> usecs/op
>
>  8 *  2kB open_sync writes  698.640 ops/sec1431
> usecs/op
>
> 16 *  1kB open_sync writes  262.506 ops/sec3809
> usecs/op
>
>
> Test if fsync on non-write file descriptor is honored:
>
> (If the times are similar, fsync() can sync data written
>
> on a different descriptor.)
>
> write, fsync, close3071.141 ops/sec 326
> usecs/op
>
> write, close, fsync3303.946 ops/sec 303
> usecs/op
>
>
> Non-Sync'ed 8kB writes:
>
> write251321.188 ops/sec   4
> usecs/op
>
>
> *On the host machine:*
>
> /usr/pgsql-9.4/bin/pg_test_fsync -f test.sync
>
> 5 seconds per test
>
> O_DIRECT supported on this platform for open_datasync and open_sync.
>
>
> Compare file sync methods using one 8kB write:
>
> (in wal_sync_method preference order, except fdatasync
>
> is Linux's default)
>
> open_datasync 11364.136 ops/sec  88
> usecs/op
>
> fdatasync 12352.160 ops/sec  81
> usecs/op
>
> fsync  9833.745 ops/sec 102
> usecs/op
>
> fsync_writethrough  n/a
>
> open_sync 14938.531 ops/sec  67
> usecs/op
>
>
> Compare file sync methods using two 8kB writes:
>
> (in wal_sync_method preference order, except fdatasync
>
> is Linux's default)
>
> open_datasync  7703.471 ops/sec 130
> usecs/op
>
> fdatasync 11494.492 ops/sec  87
> usecs/op
>
> fsync  9029.837 ops/sec 111
> usecs/op
>
> fsync_writethrough  n/a
>
> open_sync  6504.138 ops/sec 154
> usecs/op
>
>
> Compare open_sync with different write sizes:
>
> (This is designed to compare the cost of writing 16kB
>
> in different write open_sync sizes.)
>
>  1 * 16kB open_sync write 14113.912 ops/sec  71
> usecs/op
>
>  2 *  8kB open_sync writes 7843.234 ops/sec 127
> usecs/op
>
>  4 *  4kB open_sync writes 

Re: [PERFORM] pgtune or similar to assist in initial settings

[Imre Samu]

>  So looking for a good start as I start bringing up systems in Amazon,
 AWS for performance.

Maybe useful information:

- "Amazon Web Services – RDBMS in the Cloud: PostgreSQL on AWS"  (2013)
  https://aws.amazon.com/whitepapers/postgresql-in-the-cloud/  Download
Whitepaper 

-" Amazon EBS Volume Performance on Linux Instances"
  http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSPerformance.html


Imre




2016-07-12 2:34 GMT+02:00 Tory M Blue :

> I've found that pgtune doesn't quite provide the benefit that I would
> like. It still uses large work mem and maintenance work mem numbers,
> even though, up until now Postgres has an issue with large numbers of
> tuples, so seems that smaller settings are better, in the 64MB type
> range. (based on feedback from this list in the past and testing of
> larger numbers on dedicated systems).
>
> Also I've found no benefit to larger Effective cache numbers in boxen
> dedicated to postgres.
>
>  So looking for a good start as I start bringing up systems in Amazon,
> AWS for performance.
>
> pgtune is a great idea, but it's numbers seem to be based on what
> should be, vs what is..
>
> I'm currently on CentOS6 and 9.4.5
>
> Hardware specs of the AWS systems are 8 cpu/60 GB, I may bump that to
> a 16/122gb, but trying to control costs and I know going from 8 to 32
> yielded almost 0, my biggest gain was memory but even then I don't
> think I've got settings correct.
>
> Thanks
> Tory
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] How to tune Postgres to take advantage of 256GB RAM hardware

[Imre Samu]

> I am trying to configure Postgres (version 9.5)

This is the latest PG 9.5 ?  (  =  9.5.5
 ? [
Release Date : 2016-10-27 ] )

2016-11-24 9:17 GMT+01:00 Carmen Mardiros :

> Hi Andreas,
>
> Thanks for the reply. fsync is off because I don't care if the data gets
> corrupted in this environment if it means a performance gain, as it's only
> a minor inconvenience to restore from backups.
>
>
> Here is the query itself that pushes memory usage to 100%:
> http://pastebin.com/VzCAerwd . What's interesting is that if I execute
> this stand-alone, memory usage never exceeds 1.3% and query completes in 7
> minutes. But if I paste 2 other queries in the query window, followed by
> this one and execute them all at the same time, by the time postgres
> reaches the 3rd (this query), memory usage goes up to 100%. I don't
> understand why this is.
>
> And the EXPLAIN output: https://explain.depesz.com/s/hwH5 (have to admit
> I don't know how to read this!). Any help is greatly appreciated.
>
> On Thu, 24 Nov 2016 at 04:19 Andreas Kretschmer 
> wrote:
>
>>
>>
>> Am 23. November 2016 23:15:25 MEZ, schrieb Carmen Mardiros <
>> bluec...@gmail.com>:
>> >
>> >various combinations)? How can I investigate what's limiting postgres
>> >from
>> >doing so?
>>
>> Why fsync=off?
>>
>> Please run the queries with EXPLAIN ANALYSE and show us the output.
>>
>> --
>> Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail
>> gesendet.
>>
>