-----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-----
_______________________________________________
Maps-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/maps-l
