Thread: postgres performance

On Friday, December 06, 2013 11:06:58 PM chidamparam muthusamy wrote:
> hi,
> Registered with PostgreSQL Help Forum to identify and resolve the Postgres
> DB performance issue, received suggestions but could not improve the
> speed/response time. Please help.
>
> Details:
> Postgres Version 9.3.1
> Server configuration:
> Processor: 2 x Intel Quad core E5620 @ 2.40GHz
> RAM: 16 GB
>
> Postgres configuration:
> Effective cache size = 10 GB
> shared Buffer = 1250 MB
> random page cost = 4
>
> Table size = 60 GB
> Number of records = 44 million
> Carried out Vacuum Analyze after inserting new records and also after
> creating Index,
> 6 months data, every month around 10 GB will get added. Expecting good
> performance with 3 years data.
> DB Will be used for Reporting/Read, will not be used for transaction. Daily
> records will be inserted through bulk insertion every day.

Suggestions:

Partition by month.

Add many more disks, in RAID-10.
 or move to SSD.

Add a lot more RAM.

On 06/12/13 17:36, chidamparam muthusamy wrote:

I rather think Alan is right - you either want a lot more RAM or faster
disks. Have a look at your first query...

> Query:
> EXPLAIN (analyze, buffers) SELECT text(client) as client, text(gateway)
> as gateway,count(*)::bigint as total_calls, (avg(duration)/1000.0)
> ::numeric(10,2) as acd, (avg(pdd)) ::numeric(10,2) as pdd,
> sum(call_duration_recv)/1000.0 as duration_recv,
> sum(call_duration_pay)/1000.0 as duration_pay, sum(call_amount_recv) as
> call_amount_recv, sum(call_amount_pay) as call_amount_
> pay FROM detailed_report WHERE end_time>='2013-05-01 00:00' and
> end_time<'2013-07-01 00:00' and group_id='admin' and client ='CHOICE'
> GROUP by client, gateway ORDER BY call_amount_recv DESC;

> QUERY PLAN
> ------------------------------------------------------
> Sort (cost=3422863.06..3422868.69 rows=2254 width=44) (actual
> time=137852.474..137852.474 rows=5 loops=1)
> Sort Key: (sum(call_amount_recv))
> Sort Method: quicksort Memory: 25kB
> Buffers: shared read=2491664

> -> HashAggregate (cost=3422664.28..3422737.53 rows=2254 width=44)
> (actual time=137852.402..137852.454 rows=5 loops=1)
> Buffers: shared read=2491664

> -> Bitmap Heap Scan on detailed_report (cost=644828.11..3399506.87
> rows=1029218 width=44) (actual time=4499.558..125443.122 rows=5248227
> loops=1)
> Recheck Cond: ((end_time >= '2013-05-01 00:00:00+00'::timestamp with
> time zone) AND (end_time < '2013-07-01 00:00:00+00'::timestamp with time
> zone) AND ((group_id)::text = 'adm
> in'::text) AND ((client)::text = 'CHOICE'::text))
> Buffers: shared read=2491664

> -> Bitmap Index Scan on endtime_groupid_client_tsidx_detail_report
> (cost=0.00..644570.81 rows=1029218 width=0) (actual
> time=3418.754..3418.754 rows=5248227 loops=1)
> Index Cond: ((end_time >= '2013-05-01 00:00:00+00'::timestamp with time
> zone) AND (end_time < '2013-07-01 00:00:00+00'::timestamp with time
> zone) AND ((group_id)::text =
> 'admin'::text) AND ((client)::text = 'CHOICE'::text))
> Buffers: shared read=95055

> Total runtime: *137868.946 ms*
> (13 rows)

The index is being used, but most of your time is going on the "Bitmap
Heap Scan". You're processing 5.2 million rows in about 120 seconds -
that's about 43 rows per millisecond - not too bad. It's not getting any
cache hits though, it's having to read all the blocks. Looking at the
number of blocks, that's ~2.5 million at 8KB each or about 20GB. You
just don't have the RAM to cache that.

If you have lots of similar reporting queries to run, you might get away
with dropping the index and letting them run in parallel. Each
individual query would be slow but they should be smart enough to share
each other's sequential scans - the disks would basically be looping
through you data continuously.

--
   Richard Huxton
   Archonet Ltd

On 6.12.2013 18:36, chidamparam muthusamy wrote:
> hi,
> Registered with PostgreSQL Help Forum to identify and resolve the
> Postgres DB performance issue, received suggestions but could not
> improve the speed/response time. Please help.
>
> Details:
> Postgres Version 9.3.1
> Server configuration:
> Processor: 2 x Intel Quad core E5620 @ 2.40GHz
> RAM: 16 GB
>
> Postgres configuration:
> Effective cache size = 10 GB
> shared Buffer = 1250 MB
> random page cost = 4
>
> Table size = 60 GB
> Number of records = 44 million
> Carried out Vacuum Analyze after inserting new records and also after
> creating Index,
> 6 months data, every month around 10 GB will get added. Expecting good
> performance with 3 years data.

So, what is good performance? What times do you need to achieve for the
queries you've posted?

It's difficult to read the explain plans wrapped in the message, so I've
pasted some of them into explain.depesz.com:

  http://explain.depesz.com/s/9SH
  http://explain.depesz.com/s/hFp

The estimates are very accurate, and as Richard Huxton pointed out, the
dominating part is the bitmap heap scan. I assume this is because or
reading the data from disk. Can you check iostat/vmstat while the
queries are running? Are you CPU or I/O bound? I'd guess the latter.

In that case, adding more RAM / more powerful I/O is probably the
easiest way to improve the performance. And a partitioning (but that
depends on the queries, as it may improve some and hurt others).

regards
Tomas