Re: Performance issues

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

 



The synchronous_commit off increased the TPS, but not the speed of the below query.

Oleg:
This is a query i am working on now. It creates an intersection of two geometries. One is a grid of 0.5 x 0.5 decimal degree sized cells, while the other is the country geometries of all countries in the world for a certain year.

priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode, ST_Intersection(pri
ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land, cshapeswdate WHERE ST_In
tersects(priogrid_land.cell, cshapeswdate.geom);
                                                                    QUERY PLAN

--------------------------------------------------------------------------------
------------------------------------------------------------------
 Nested Loop  (cost=0.00..12644.85 rows=43351 width=87704) (actual time=1.815..7
074973.711 rows=130331 loops=1)
   Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)
   ->  Seq Scan on cshapeswdate  (cost=0.00..14.42 rows=242 width=87248) (actual
 time=0.007..0.570 rows=242 loops=1)
   ->  Index Scan using idx_priogrid_land_cell on priogrid_land  (cost=0.00..7.1
5 rows=1 width=456) (actual time=0.069..5.604 rows=978 loops=242)
         Index Cond: (priogrid_land.cell && cshapeswdate.geom)
 Total runtime: 7075188.549 ms
(6 rows)

2011/3/7 Oleg Bartunov <oleg@xxxxxxxxxx>
On Mon, 7 Mar 2011, Andreas For? Tollefsen wrote:

Ok. Cheers. I will do some more testing on my heavy PostGIS queries which
often takes hours to complete.

I'd like to see hours long queries :) EXPLAIN ANALYZE



Thanks.
Andreas

2011/3/7 Kenneth Marshall <ktm@xxxxxxxx>

On Mon, Mar 07, 2011 at 03:17:05PM +0100, Andreas For? Tollefsen wrote:
Thanks, Ken.

It seems like the tip to turn off synchronous_commit did the trick:

/usr/lib/postgresql/8.4/bin/pgbench -T 60 test1
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
duration: 60 s
number of transactions actually processed: 86048
tps = 1434.123199 (including connections establishing)
tps = 1434.183362 (excluding connections establishing)

Is this acceptable compared to others when considering my setup?

Cheers,
Andreas



These are typical results for synchronous_commit off. The caveat
is you must be able to handle loosing transactions if you have a
database crash, but your database is still intact. This differs
from turning fsync off in which a crash means you would need to
restore from a backup.

Cheers,
Ken



       Regards,
               Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83


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

  Powered by Linux