Johann Spies | 26 Nov 14:34 2014
Picon

Re: pgtune + configurations with 9.3

Hello Greame,

It's probably helpful if everyone sharing this information can post their measurement process / settings and the results as completely as possible, for comparison and reference.

Apologies.  I have only changed one parameter in postgresql.conf for the tests and that was shared_buffers:

 shared_buffers = 32GB                   # min 128k
shared_preload_libraries = 'auto_explain'               # (change requires restart)
vacuum_cost_delay = 5                   # 0-100 milliseconds
wal_sync_method = open_sync             # the default is the first option
        wal_buffers = -1                        # min 32kB, -1 sets based on shared_buffers
        checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0
        checkpoint_warning = 30s                # 0 disables
        default_statistics_target = 100 # range 1-10000
        log_line_prefix = '%t '                 # special values:
        log_statement = 'all'                   # none, ddl, mod, all
        log_timezone = 'localtime'
        autovacuum_vacuum_scale_factor = 0.1    # fraction of table size before vacuum
        autovacuum_vacuum_cost_delay = 5ms      # default vacuum cost delay for
        datestyle = 'iso, dmy'
        timezone = 'localtime'
        lc_messages = 'en_ZA.UTF-8'                     # locale for system error message
        lc_monetary = 'en_ZA.UTF-8'                     # locale for monetary formatting
        lc_numeric = 'en_ZA.UTF-8'                      # locale for number formatting
        lc_time = 'en_ZA.UTF-8'                         # locale for time formatting
        default_text_search_config = 'pg_catalog.english'
        auto_explain.log_min_duration = '6s' # Gregory Smith page 180
        effective_cache_size = 512GB # pgtune wizard 2014-09-25
        work_mem = 4608MB # pgtune wizard 2014-09-25
        checkpoint_segments = 16 # pgtune wizard 2014-09-25
        max_connections = 80 # pgtune wizard 2014-09-25

And pgbench-tools - the default configuration:

BASEDIR=`pwd`
PGBENCHBIN=`which pgbench`
TESTDIR="tests"
SKIPINIT=0
TABBED=0
OSDATA=1
TESTHOST=localhost
TESTUSER=`whoami`
TESTPORT=5432
TESTDB=pgbench
RESULTHOST="$TESTHOST"
RESULTUSER="$TESTUSER"
RESULTPORT="$TESTPORT"
RESULTDB=results
MAX_WORKERS=""
SCRIPT="select.sql"
SCALES="1 10 100 1000"
SETCLIENTS="1 2 4 8 16 32"
SETTIMES=3
RUNTIME=60
TOTTRANS=""
SETRATES=""


The server:

# See Gregory Smith: High Performans Postgresql 9.0 pages 81,82 for the next lines
vm.swappiness=0
vm.overcommit_memory=2
vm.dirty_ratio = 2
vm.dirty_background_ratio=1
# Maximum shared segment size in bytes
kernel.shmmax = 406622322688
# Maximum number of shared memory segments in pages
kernel.shmall = 99273028

$ free
             total       used       free     shared    buffers     cached
Mem:     794184164  792406416    1777748          0     123676  788079892
-/+ buffers/cache:    4202848  789981316
Swap:      7906300          0    7906300

I have attached the resulting graphs.

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
M Tarkeshwar Rao | 26 Nov 11:16 2014
Picon

issue in postgresql 9.1.3 in using arrow key in Solaris platform

Hi all,

 

We are facing following issue in postgresql 9.1.3 in using arrow key in Solaris platform.

Can you please help us to resolve it or any new release has fix for this or any workaround for this?

 

issue: psql client generates a core when up arrow is used twice.

============

Platfrom: Solaris X86

 

Steps to  reproduce:

=====================

1. Login to any postgres database

2. execute any quer say  "\list"

3. press up arrow twice.

4. segmentation fault occurs and core is generated. Also session is terminated.

 

PLease find example below

 

# ./psql -U super -d mgrdb

Password for user super:

psql (9.1.3)

Type "help" for help.

 

mgrdb=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg

es

-----------+----------+----------+-------------+-------------+------------------

-----

mgrdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres

    +

           |          |          |             |             | postgres=CTc/post

gres

template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres

    +

           |          |          |             |             | postgres=CTc/post

gres

(4 rows)

 

mgrdb=#

mgrdb=# select count(1) from operator_msm;Segmentation Fault (core dumped)

 

Regards

Tarkeshwar

Scott Marlowe | 25 Nov 21:36 2014
Picon

Small performance regression in 9.2 has a big impact

OK so there's a simple set of tree functions we use at work. They're
quite fast in 8.4 and they've gotten about 40% slower in 9.2. They're
a simple mix of sql and plpgsql functions which are at
http://pastebin.com/SXTnNhd5 and which I've attached.

Here's a test query:

select tree_ancestor_keys('000000000000000100000001');

According to explain analyze on both 8.4 and 9.2 they have the same
plan. However, on the same machine the query is about 40% slower on
9.2. Note we're not hitting the disks, or even buffers here. It's pure
in memory plpsql and sql that we're running.

explain analyze select tree_ancestor_keys('000000000000000100000001')
from generate_series(1,1000);

On 8.4 runs in about 280 to 300 ms. (you can run it once and get the
same diff, it's just easier to see with the generate series forcing it
to run 1000 times to kind of even out the noise.)

On 9.2, same machine, clean fresh dbs etc, it runs in ~400 ms. And
that difference seems to be there on all plpgsql and sql functions.

In our application, these tree functions get called millions and
millions of times a day, and a 40% performance penalty is a pretty big
deal.

We're already using the trick of telling the query planner that this
function will return 1 row with alter function rows 1 etc. That helps
a lot but it doesn't fix this underlying performance issue.

Server versions are 8.4.22 (last I think) and 9.2.9.

If anyone has any suggestions I'd love to hear them.
-- 
To understand recursion, one must first understand recursion.
Attachment (z.sql): application/sql, 9 KiB

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Vlad Arkhipov | 24 Nov 12:02 2014
Picon

Why don't use index on x when ORDER BY x, y?

Hello,

I wonder why Postgres does not use index in the query below? It is a 
quite common use-case when you  want to sort records by an arbitrary set 
of columns but do not want to create a lot of compound indexes for all 
possible combinations of them. It seems that if, for instance, your 
query's ORDER BY is x, y, z then any of these indexes could be used to 
improve the performance: (x); (x, y); (x, y, z).

create temp table t as
select s as x, s % 10 as y, s % 100 as z
from generate_series(1, 1000000) s;

analyze t;
create index idx1 on t (y);

select *
from t
order by y desc, x
limit 10;

--

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

Stuart Bishop | 21 Nov 06:07 2014
Picon

A pessimistic planner

Another day, another timing out query rewritten to force a more stable
query plan.

While I know that the planner almost always chooses a good plan, I
tend to think it is trying too hard. While 99% of the queries might be
10% faster, 1% might be timing out which makes my users cross and my
life difficult. I'd much rather have systems that are less efficient
overall, but stable with a very low rate of timeouts.

I was wondering if the planner should be much more pessimistic,
trusting in Murphy's Law and assuming the worst case is the likely
case? Would this give me a much more consistent system? Would it
consistently grind to a halt doing full table scans? Do we actually
know the worst cases, and would it be a relatively easy task to update
the planner so we can optionally enable this behavior per transaction
or across a system? Boolean choice between pessimistic or optimistic,
or is pessimism a dial?

-- 
Stuart Bishop <stuart <at> stuartbishop.net>
http://www.stuartbishop.net/

--

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

Yuri Kunde Schlesner | 16 Nov 00:16 2014
Picon

Plan uses wrong index, preferring to scan pkey index instead

Hi all,
 
Excuse me if I made any mistakes, as this is my first time posting to a mailing list.
 
I'm a user of Quassel, a IRC client that uses postgres a backing store for IRC logs and am running into heavy intermittent performance problems. I've tracked it down to a query that takes a very long time (around 4 minutes) to complete when its data isn't cached.
 
This is the layout of the table being queried and EXPLAIN ANALYZE result for the problematic query:
 
quassel=> \d backlog
                                        Table "public.backlog"
  Column   |            Type             |                          Modifiers
-----------+-----------------------------+-------------------------------------------------------------
 messageid | integer                     | not null default nextval('backlog_messageid_seq'::regclass)
 time      | timestamp without time zone | not null
 bufferid  | integer                     | not null
 type      | integer                     | not null
 flags     | integer                     | not null
 senderid  | integer                     | not null
 message   | text                        |
Indexes:
    "backlog_pkey" PRIMARY KEY, btree (messageid)
    "backlog_bufferid_idx" btree (bufferid, messageid DESC)
Foreign-key constraints:
    "backlog_bufferid_fkey" FOREIGN KEY (bufferid) REFERENCES buffer(bufferid) ON DELETE CASCADE
    "backlog_senderid_fkey" FOREIGN KEY (senderid) REFERENCES sender(senderid) ON DELETE SET NULL
 
quassel=> explain (analyze, buffers) SELECT messageid, time,  type, flags, sender, message
FROM backlog
LEFT JOIN sender ON backlog.senderid = sender.senderid
WHERE bufferid = 39
ORDER BY messageid DESC LIMIT 10;
                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.72..37.78 rows=10 width=102) (actual time=154410.353..154410.424 rows=10 loops=1)
   Buffers: shared hit=13952 read=19244
   ->  Nested Loop Left Join  (cost=0.72..145800.61 rows=39345 width=102) (actual time=154410.350..154410.414 rows=10 loops=1)
         Buffers: shared hit=13952 read=19244
         ->  Index Scan Backward using backlog_pkey on backlog  (cost=0.43..63830.21 rows=39345 width=62) (actual time=154410.327..154410.341 rows=10 loops=1)
               Filter: (bufferid = 39)
               Rows Removed by Filter: 1248320
               Buffers: shared hit=13921 read=19244
         ->  Index Scan using sender_pkey on sender  (cost=0.29..2.07 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=10)
               Index Cond: (backlog.senderid = senderid)
               Buffers: shared hit=31
 Total runtime: 154410.477 ms
(12 rows)
 
This plan is consistently chosen, even after ANALYZEing and REINDEXing the table. It looks like Postgres is opting to do a sequential scan of the backlog_pkey index, filtering rows by bufferid, instead of directly using the backlog_bufferid_idx index that directly maps to the operation being made by the query. I was advised on IRC to try dropping the backlog_pkey index to force Postgres to use the correct one, and that uses a better plan:
 
quassel=> begin;
BEGIN
quassel=> alter table backlog drop constraint backlog_pkey;
ALTER TABLE
quassel=> explain analyze SELECT messageid, time,  type, flags, sender, message
FROM backlog
LEFT JOIN sender ON backlog.senderid = sender.senderid
WHERE bufferid = 39
ORDER BY messageid DESC LIMIT 10;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.72..40.50 rows=10 width=102) (actual time=63.826..162.134 rows=10 loops=1)
   ->  Nested Loop Left Join  (cost=0.72..156518.91 rows=39345 width=102) (actual time=63.823..162.126 rows=10 loops=1)
         ->  Index Scan using backlog_bufferid_idx on backlog  (cost=0.43..74548.51 rows=39345 width=62) (actual time=63.798..63.814 rows=10 loops=1)
               Index Cond: (bufferid = 39)
         ->  Index Scan using sender_pkey on sender  (cost=0.29..2.07 rows=1 width=48) (actual time=8.532..9.825 rows=1 loops=10)
               Index Cond: (backlog.senderid = senderid)
 Total runtime: 162.377 ms
(7 rows)
 
quassel=> rollback;
ROLLBACK
 
(This query was also run with empty caches.) bufferid=39 in particular has this issue because it hasn't had any messages posted to for a long time, so scanning backlog upwards will take a long time to gather 10 messages from it. In contrast, most other bufferid's have their messages interleaved on the last entries of backlog. I believe this might be throwing Postgres' estimates off.
 
Does anyone know if there's any tweaking I can do in Postgres so that it uses the appropriate plan?
 
Info about my setup:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.9.1, 64-bit
Arch Linux, PostgreSQL installed from the official repositories, running inside a Xen HVM VPS.
Connecting to PostgreSQL using psql via UNIX socket.
Changed options: (locale-related ones omitted)
  listen_addresses = 
  max_stack_depth = 2MB
  shared_buffers = 256MB (Issue is also present with default value)
Total RAM: 1GB
 
 
Thanks,
--yuriks
CS DBA | 14 Nov 00:09 2014

Increased shared_buffer setting = lower hit ratio ?

All;

We have a large db server with 128GB of ram running complex functions.

with the server set to have the following we were seeing a somewhat low 
hit ratio and lots of temp buffers

shared_buffers = 18GB
work_mem = 75MB
effective_cache_size = 105GB
checkpoint_segments = 128

when we increased the values to these not only did the hit ratio drop 
but query times are now longer as well:

shared_buffers = 28GB
work_mem = 150MB
effective_cache_size = 105GB
checkpoint_segments = 256

This does not seem to make sense to me, anyone have any thoughts on why 
more memory resources would cause worse performance?

Thanks in advance

--

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

Filip RembiaƂkowski | 13 Nov 09:10 2014
Picon

9.0 performance degradation with kernel 3.11

Hi

After upgrading our 9.0 database server

from:
openSUSE 11.4, kernel 2.6.37.6-24-default, Pg 9.0.13

to:
openSUSE 13.1, kernel v 3.11.10-21-default, Pg 9.0.15

... and  overall server load is +1 after that.

We did not add any new services/daemons.

It's hard to track down to individual queries - when I tested most
individual query times are same as before the migration.

Any - ANY - hints will be much appreciated.

Thanks
Filip

--

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

Josh Berkus | 12 Nov 18:04 2014

Re: Lock pileup causes server to stall

On 11/12/2014 05:51 AM, Alvaro Herrera wrote:
> Anyway, the fklocks patch was stupidly complex (and still got much stuff
> wrong).  I didn't want to add more ground to objections by additionally
> breaking the abstraction between heapam and the concept of "columns
> referenced by a foreign key constraint".

Oh, come on.  We had hardly any problems with that patch!

;-)

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

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

Josh Berkus | 11 Nov 18:11 2014

Re: Lock pileup causes server to stall

On 11/10/2014 01:40 PM, Alvaro Herrera wrote:
> Josh Berkus wrote:
>> All,
>>
>> pg version: 9.3.5
>> RHEL 6.5
>> 128GB/32 cores
>> Configured with shared_buffers=16GB
>> Java/Tomcat/JDBC application
>>
>> Server has an issue that whenever we get lock waits (transaction lock
>> waits, usually on an FK dependancy) lasting over a minute or more than
>> 10 at once, *all* queries on the server slow to a crawl, taking 100X to
>> 400X normal execution times.
> 
> Current FK checking makes you wait if the referenced tuple is modified
> on any indexed column, not just those that are actually used in
> foreign keys.  Maybe this case would be sped up if we optimized that.
> 
>> * This applies even to queries which are against other databases, so
>> it's not purely a lock blocking issue.
> 
> Oh.

Yeah, I think this is more likely a problem with the general lock table
and shared_buffers than anything to do with actual lock-blocks.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

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

avpro avpro | 11 Nov 07:38 2014
Picon

trigger Before or After

i haven't seen anything referring to: how is affected the data inserted in the new table by a trigger Before Insert compared with a trigger After Insert? and anything related to performance

for example:

tables: actuals (summarize the total running hours), log (the functional hours are inserted in LOG as time)
 function: sum
view: timeview (where running hours are calculated as a difference)
-- Function: sum() -- DROP FUNCTION sum(); CREATE OR REPLACE FUNCTION sum() RETURNS trigger AS $BODY$begin update actuals set hours = hours + (select time from time_view where idlog = (select max(idlog) from timeview))
where actuals.idmac =
(SELECT idmac FROM selectedmac) ; return new; end$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION sum() OWNER TO user;



--trigger
CREATE TRIGGER update_actuals_tg01
  AFTER INSERT
  ON log
  FOR EACH ROW
  EXECUTE PROCEDURE sum();


I read somewhere (I don't find the link anymore) that if the trigger is After Insert, the data available in the table LOG might not be available anymore to run the trigger. is that correct? or I might understood wrong?

what's the difference related to performance concerning a trigger Before Insert compared with a trigger After Insert?

thank you
have a sunny day


Gmane