Thanks for the useful info Paul - that makes sense and I understand now.
I will run autovacuum's on each of the slaves from now on, and try
increasing RAM as much as I can (the db is ~30Gb and growing).
Cheers,
Graham
On 29/05/2014 20:00, [email protected] wrote:
Send postgis-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of postgis-users digest..."
Today's Topics:
1. Re: Indexes (Paul Ramsey)
2. ERROR: rt_raster_from_two_rasters: (Jason Mathis)
----------------------------------------------------------------------
Message: 1
Date: Thu, 29 May 2014 09:59:34 -0700
From: Paul Ramsey <[email protected]>
To: PostGIS Users Discussion <[email protected]>
Subject: Re: [postgis-users] Indexes
Message-ID:
<CACowWR3D7v83YoZC8iWSkSv=gz7os4ycwqatkee9a-gx4s_...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Running queries does not build up an index, you CREATE INDEX, and
that's it, it's built.
The time differences you are seeing are almost certainly the result of
data being transferred up into the operating system's virtual file
system (VFS) from the disk. Reading from disk is slow; reading from
memory is fast; to try and take advantage of RAM, the operating system
caches frequently read disk pages in RAM. So, your first slow query is
going all the way to disk, the second (and subsequent) query is able
to take advantage of the cached data.
I'm pretty sure that a VACUUM command doesn't get transferred to the
slave replicas, but I could be wrong. In any event the slaves should
have their own autovacuum settings in their own configs and be
gathering stats and updating dead pages on their own.
VACUUM and ANALYZE are only going to make differences performance-wise
for data that is changing enough to (a) create a lot of dead tuples
and (b) to change the distribution of values of data in the tables.
All the forgoing to say: you're just seeing the difference between a
"cold" (uncached) and "hot" (cached) query and then making a lot of
(incorrect) inferences about indexes from that observation. If your
data is large enough that it doesn't fit all in RAM, you'll find that
as you bounce around the data set with queries some queries will be
"hot" (they'll have been cached by a previous query) and some will be
"cold", but that each query will cause different bits of data to be
swapped in and out of memory as the operating system does its best to
keep the "most useful" bits of data in RAM.
P.
On Wed, May 28, 2014 at 10:29 AM, Graham Glanfield
<[email protected]> wrote:
Hi folks,
I have streaming replication running between one master and a few slaves
(Fedorax_86_64 - PostgreSQL 9.2.6 - POSTGIS 2.1.2).
My application only ever reads from a slave, and I have a couple of
questions with regards to indexes and vacuuming.
It is my understanding (probably incorrect) that any vacuuming executed on
the master gets propagated to the slaves as part of the streaming
replication process.
My main question is if I run a spatial query against a slave the first time,
it may take 3 seconds, then the 2nd time it is near instant (if I query the
same slave), so I'm assuming that the slave index/stats must be being
updated? Vacuum is set to run once per night on the master, so does this
mean that if I ran the query on that slave the next morning, it would take 3
seconds again?
On a similar point, I have a background process that creates dynamic spatial
queries that I hope will help build up the GIST index, and increase the
speed of users spatial queries, but I don't know whether to run these
queries against the master or on each of the slaves?
Best regards,
Graham
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
------------------------------
Message: 2
Date: Thu, 29 May 2014 12:45:02 -0600
From: Jason Mathis <[email protected]>
To: [email protected]
Subject: [postgis-users] ERROR: rt_raster_from_two_rasters:
Message-ID: <etPan.5387802f.625558ec.92f3@palos>
Content-Type: text/plain; charset="utf-8"
I loaded a bunch raster data into separate tables. One for each us western
state. I thought it maybe handy to query a view instead of the individual
tables. Anyways everything seemed ok but I started to get errors on a few ids I
was testing. I knew what table it should be pulling from so I queried the table
and no issues. So i must assume the error is because of the ?union all? in the
view. Can anyone confirm this and why? I am on postgres 9.2.8 and postgis
2.1.2.?
thanks,
jason
query>>>
SELECT
? ? ? ? report_id, incident_id,?
? ? ? ? ST_SummaryStats(
? ? ? ? ? ST_UNION(
? ? ? ? ? ? ST_Clip(c.rast, 1,?
? ? ? ? ? ? ST_Transform(w.geom, 5070), true)
? ? ? ? ? ),
? ? ? ? ? 1
? ? ? ? )?as hdesc
FROM rasters c
JOIN w_reports w?
? ? ? ? ON ST_Intersects(c.rast, ST_Transform(w.geom, 5070)) ?
where w.incident_id = 5443?
group by incident_id, report_id
error>>>
ERROR:? rt_raster_from_two_rasters: The two rasters provided do not
have the same alignment
********** Error **********
ERROR: rt_raster_from_two_rasters: The two rasters provided do not have
the same alignment
SQL state: XX000
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
