-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Kai Krueger:
As an example the query "explain analyze select
count(*) from
planet_osm_roads where route='ferry';" took 55 seconds on yevaud
compare to 150 seconds on ptolemy. In the cache hot case, i.e. running
the statement immediately again, the time on yevaud went down to 8
seconds, whereas for ptolemy it still stayed at 105 seconds.
Okay, I found the cause of this: the /sql filesystem is mounted with
'cio' enabled. This massively increases performance for MySQL, so we
normally use it everywhere, but it seems that it disables the OS page
cache, which obviously kills PostgreSQL performance. I remounted the
/sql filesystem without it, and performance seems much better:
osm_mapnik=# explain analyze select count(*) from planet_osm_roads where
route='ferry';
QUERY PLAN
-
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=259824.01..259824.02 rows=1 width=0) (actual time=31596.203..31596.203
rows=1 loops=1)
-> Seq Scan on planet_osm_roads (cost=0.00..259824.00 rows=1 width=0) (actual
time=1143.908..31596.075 rows=24 loops=1)
Filter: (route = 'ferry'::text)
Total runtime: 31597.265 ms
(4 rows)
osm_mapnik=# explain analyze select count(*) from planet_osm_roads where
route='ferry';
QUERY PLAN
-
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=259825.23..259825.24 rows=1 width=0) (actual time=1989.096..1989.097
rows=1 loops=1)
-> Seq Scan on planet_osm_roads (cost=0.00..259825.23 rows=1 width=0) (actual
time=65.551..1988.990 rows=24 loops=1)
Filter: (route = 'ferry'::text)
Total runtime: 1989.513 ms
(4 rows)
(First is cold, second is hot.)
I kept cio on /sql/pg_xlog because the page cache shouldn't be needed
there.
- river.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (HP-UX)
iEYEARECAAYFAkuDvZ0ACgkQIXd7fCuc5vKgvgCfR4tl+VyKIrYpQKFaaCYa/JX8
J0IAoIEbRx1Z/0nkrooREdiiAFecVh51
=gQnx
-----END PGP SIGNATURE-----