On Fri, 4 Feb 2011, ??????? ???????? wrote:
2011/2/3 <david@xxxxxxx>
If the table is not large enough to fit in ram, then it will compete for
I/O, and the user will have to wait.
what I'm proposing is that as the records are created, the process doing
the creation makes copies of the records (either all of them, or some of
them if not all are needed for the analysis, possibly via shareing memory
with the analysis process), this would be synchronous with the load, not
asynchronous.
this would take zero I/O bandwidth, it would take up some ram, memory
bandwidth, and cpu time, but a load of a large table like this is I/O
contrained.
it would not make sense for this to be the default, but as an option it
should save a significant amount of time.
I am making the assumption that an Analyze run only has to go over the data
once (a seqential scan of the table if it's >> ram for example) and gathers
stats as it goes.
with the current code, this is a completely separate process that knows
nothing about the load, so if you kick it off when you start the load, it
makes a pass over the table (competing for I/O), finishes, you continue to
update the table, so it makes another pass, etc. As you say, this is a bad
thing to do. I am saying to have an option that ties the two togeather,
essentially making the data feed into the Analyze run be a fork of the data
comeing out of the insert run going to disk. So the Analyze run doesn't do
any I/O and isn't going to complete until the insert is complete. At which
time it will have seen one copy of the entire table.
Actually that are two different problems. The one is to make analyze more
automatic to make select right after insert more clever by providing
statistics to it.
Another is to make it take less IO resources.
I dont like for it to be embedded into insert (unless the threshold can be
determined before inserts starts). Simply because it is more CPU/memory that
will slow down each insert. And if you will add knob, that is disabled by
default, this will be no more good than manual analyze.
if it can happen during the copy instead of being a step after the copy it
will speed things up. things like the existing parallel restore could use
this instead ofneeding a separate pass. so I don't think that having to
turn it on manually makes it useless, any more than the fact that you have
to explicity disable fsync makes that disabling feature useless (and the
two features would be likely to be used togeather)
when a copy command is issued, I assume that there is some indication of
how much data is going to follow. I know that it's not just 'insert
everything until the TCP connection terminates' because that would give
you no way of knowing if the copy got everything in or was interrupted
part way through. think about what happens with ftp if the connection
drops, you get a partial file 'successfully' as there is no size provided,
but with HTTP you get a known-bad transfer that you can abort or resume.
David Lang
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
