and your /etc/sysctl.conf is? Cheers Bèrto On 24 February 2013 14:08, Andre <pg@xxxxxxxx> wrote: > Hi, > Since our upgrade of hardware, OS and Postgres we experience server stalls > under certain conditions, during that time (up to 2 minutes) all CPUs show > 100% system time. All Postgres processes show BIND in top. > Usually the server only has a load of < 0.5 (12 cores) with up to 30 > connections, 200-400 tps > > Here is top -H during the stall: > Threads: 279 total, 25 running, 254 sleeping, 0 stopped, 0 zombie > %Cpu(s): 0.2 us, 99.8 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 > st > > This is under normal circumstances: > Threads: 274 total, 1 running, 273 sleeping, 0 stopped, 0 zombie > %Cpu(s): 0.2 us, 0.2 sy, 0.0 ni, 99.6 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 > st > > iostat shows under 0.3% load on the drives. > > The stalls are mostly reproducible when there is the normal load on the > server and then 20-40 new processes start executing SQLs. > Deactivating HT seemed to have reduced the frequency and length of the > stalls. > > The log shows entries for slow BINDs (8 seconds): > ... LOG: duration: 8452.654 ms bind pdo_stmt_00000001: SELECT [20 columns > selected] FROM users WHERE users.USERID=$1 LIMIT 1 > > I have tried to create a testcase, but even starting 200 client processes > that execute prepared statements does not reproduce this behaviour on a > nearly idle server, only under normal workload does it stall. > > Hardware details: > 2x Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz > 64 GB RAM > > Postgres version: 9.2.2 and 9.2.3 > > Linux: OpenSUSE 12.2 with Kernel 3.4.6 > > Postgres config: > max_connections = 200 > effective_io_concurrency = 3 > max_wal_senders = 2 > wal_keep_segments = 2048 > max_locks_per_transaction = 500 > default_statistics_target = 100 > checkpoint_completion_target = 0.9 > maintenance_work_mem = 1GB > effective_cache_size = 60GB > work_mem = 384MB > wal_buffers = 8MB > checkpoint_segments = 64 > shared_buffers = 15GB > > > This might be related to this topic: > http://www.postgresql.org/message-id/CANQNgOquOGH7AkqW6ObPafrgxv=J3WsiZg-NgVvbki-qYpoY7Q@xxxxxxxxxxxxxx > (Poor performance after update from SLES11 SP1 to SP2) > I believe the old server was OpenSUSE 11.x. > > > Thanks for any hint on how to fix this or diagnose the problem. > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- ============================== If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
