Thread: Re: [SQL] Performance
"Brett W. McCoy" <bmccoy@lan2wan.com> writes:
>> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius
>> detail files as a matter of fact. Apart from COPY taking forever to load
>> that (probably due to my several indexes), it seems the select is VERY
>> slow. Any tips?
> I found that if you create an index before doing a bulk COPY, yes, it does
> take forever to load, and the select is slow. What I did was drop the
> indices built from the COPY and rebuild them. Speeded the selects up
> significantly. So now I don't build any indices until after I load my
> huge databases in.
Not building the indexes until you've done the bulk load is good advice;
it does seem a lot faster to build an index on an already-loaded table
than to construct it piecemeal during the COPY. However, either way
should result in the same index, so I don't see why it'd affect the
speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE
both times? The system is likely to ignore the index until you have
vacuumed the table.
In short, best bulk load procedure is
CREATE TABLE ...
COPY ...
CREATE INDEX(es) on table
Repeat as needed for all tables being bulk-loaded
VACUUM ANALYZE
BTW, if you use pg_dump to dump and reload a big database, pg_dump
knows about the create-indexes-last trick. But it doesn't do a VACUUM
for you; you have to do that by hand after running the reload script,
or your database will be slow.
regards, tom lane
And how long is normal for Vacuum analyze to take on bout 350 megs of DATA? Jason --- Jason Slagle Network Administrator - Toledo Internet Access - Toledo Ohio - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 On Wed, 10 Mar 1999, Tom Lane wrote: > "Brett W. McCoy" <bmccoy@lan2wan.com> writes: > >> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius > >> detail files as a matter of fact. Apart from COPY taking forever to load > >> that (probably due to my several indexes), it seems the select is VERY > >> slow. Any tips? > > > I found that if you create an index before doing a bulk COPY, yes, it does > > take forever to load, and the select is slow. What I did was drop the > > indices built from the COPY and rebuild them. Speeded the selects up > > significantly. So now I don't build any indices until after I load my > > huge databases in. > > Not building the indexes until you've done the bulk load is good advice; > it does seem a lot faster to build an index on an already-loaded table > than to construct it piecemeal during the COPY. However, either way > should result in the same index, so I don't see why it'd affect the > speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE > both times? The system is likely to ignore the index until you have > vacuumed the table. > > In short, best bulk load procedure is > > CREATE TABLE ... > COPY ... > CREATE INDEX(es) on table > Repeat as needed for all tables being bulk-loaded > VACUUM ANALYZE > > BTW, if you use pg_dump to dump and reload a big database, pg_dump > knows about the create-indexes-last trick. But it doesn't do a VACUUM > for you; you have to do that by hand after running the reload script, > or your database will be slow. > > regards, tom lane >
Depends on the indices that have to be gone through. It can take quite a while. -- -- Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl I ain't even *authorized* to speak for anyone other than myself, so give up now on trying to associate my words with any particular organization. On Wed, Mar 10, 1999 at 01:27:20PM -0500, Jason Slagle wrote: > And how long is normal for Vacuum analyze to take on bout 350 megs of > DATA? > > Jason > > --- > Jason Slagle > Network Administrator - Toledo Internet Access - Toledo Ohio > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 > > On Wed, 10 Mar 1999, Tom Lane wrote: > > > "Brett W. McCoy" <bmccoy@lan2wan.com> writes: > > >> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius > > >> detail files as a matter of fact. Apart from COPY taking forever to load > > >> that (probably due to my several indexes), it seems the select is VERY > > >> slow. Any tips? > > > > > I found that if you create an index before doing a bulk COPY, yes, it does > > > take forever to load, and the select is slow. What I did was drop the > > > indices built from the COPY and rebuild them. Speeded the selects up > > > significantly. So now I don't build any indices until after I load my > > > huge databases in. > > > > Not building the indexes until you've done the bulk load is good advice; > > it does seem a lot faster to build an index on an already-loaded table > > than to construct it piecemeal during the COPY. However, either way > > should result in the same index, so I don't see why it'd affect the > > speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE > > both times? The system is likely to ignore the index until you have > > vacuumed the table. > > > > In short, best bulk load procedure is > > > > CREATE TABLE ... > > COPY ... > > CREATE INDEX(es) on table > > Repeat as needed for all tables being bulk-loaded > > VACUUM ANALYZE > > > > BTW, if you use pg_dump to dump and reload a big database, pg_dump > > knows about the create-indexes-last trick. But it doesn't do a VACUUM > > for you; you have to do that by hand after running the reload script, > > or your database will be slow. > > > > regards, tom lane > > > >
Haven't created any yet, as I have not yet determined what I want to index on. I just created the database and bulk loaded 211 megs of : delimited data, generating a 350 meg file in the data dir. It has been vacuuming for 45 mins or so now... Prob needs more horsepower :D Jason --- Jason Slagle Network Administrator - Toledo Internet Access - Toledo Ohio - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 On Wed, 10 Mar 1999, Karl Denninger wrote: > Depends on the indices that have to be gone through. It can take quite a > while. > > -- > -- > Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl > I ain't even *authorized* to speak for anyone other than myself, so give > up now on trying to associate my words with any particular organization. > > > On Wed, Mar 10, 1999 at 01:27:20PM -0500, Jason Slagle wrote: > > And how long is normal for Vacuum analyze to take on bout 350 megs of > > DATA? > > > > Jason > > > > --- > > Jason Slagle > > Network Administrator - Toledo Internet Access - Toledo Ohio > > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 > > > > On Wed, 10 Mar 1999, Tom Lane wrote: > > > > > "Brett W. McCoy" <bmccoy@lan2wan.com> writes: > > > >> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius > > > >> detail files as a matter of fact. Apart from COPY taking forever to load > > > >> that (probably due to my several indexes), it seems the select is VERY > > > >> slow. Any tips? > > > > > > > I found that if you create an index before doing a bulk COPY, yes, it does > > > > take forever to load, and the select is slow. What I did was drop the > > > > indices built from the COPY and rebuild them. Speeded the selects up > > > > significantly. So now I don't build any indices until after I load my > > > > huge databases in. > > > > > > Not building the indexes until you've done the bulk load is good advice; > > > it does seem a lot faster to build an index on an already-loaded table > > > than to construct it piecemeal during the COPY. However, either way > > > should result in the same index, so I don't see why it'd affect the > > > speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE > > > both times? The system is likely to ignore the index until you have > > > vacuumed the table. > > > > > > In short, best bulk load procedure is > > > > > > CREATE TABLE ... > > > COPY ... > > > CREATE INDEX(es) on table > > > Repeat as needed for all tables being bulk-loaded > > > VACUUM ANALYZE > > > > > > BTW, if you use pg_dump to dump and reload a big database, pg_dump > > > knows about the create-indexes-last trick. But it doesn't do a VACUUM > > > for you; you have to do that by hand after running the reload script, > > > or your database will be slow. > > > > > > regards, tom lane > > > > > > > >
Jason Slagle wrote: > And how long is normal for Vacuum analyze to take on bout 350 megs of > DATA? > > Jason > My database is 1.4 Gig. It takes about 2-3 minutes for a vacuum analyze verbose. I have had problems in the past with a corrupted main index (the table of tables). In such a case, the vacuum never stops even after > 48 hours. Try vacuuming your tables one-by-one if you have really long vaccums (> 1 hour). If the tables vacuum one-by-one alright, then you have a corrupted main index. -Tony
That's a waste - a vacuum on a table with no deletes having been executed and no indices will go through everything and do absolutely nothing :-) -- -- Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl I ain't even *authorized* to speak for anyone other than myself, so give up now on trying to associate my words with any particular organization. On Wed, Mar 10, 1999 at 01:39:19PM -0500, Jason Slagle wrote: > Haven't created any yet, as I have not yet determined what I want to index > on. I just created the database and bulk loaded 211 megs of : delimited > data, generating a 350 meg file in the data dir. It has been vacuuming > for 45 mins or so now... Prob needs more horsepower :D > > Jason > > --- > Jason Slagle > Network Administrator - Toledo Internet Access - Toledo Ohio > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 > > On Wed, 10 Mar 1999, Karl Denninger wrote: > > > Depends on the indices that have to be gone through. It can take quite a > > while. > > > > -- > > -- > > Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl > > I ain't even *authorized* to speak for anyone other than myself, so give > > up now on trying to associate my words with any particular organization. > > > > > > On Wed, Mar 10, 1999 at 01:27:20PM -0500, Jason Slagle wrote: > > > And how long is normal for Vacuum analyze to take on bout 350 megs of > > > DATA? > > > > > > Jason > > > > > > --- > > > Jason Slagle > > > Network Administrator - Toledo Internet Access - Toledo Ohio > > > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 > > > > > > On Wed, 10 Mar 1999, Tom Lane wrote: > > > > > > > "Brett W. McCoy" <bmccoy@lan2wan.com> writes: > > > > >> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius > > > > >> detail files as a matter of fact. Apart from COPY taking forever to load > > > > >> that (probably due to my several indexes), it seems the select is VERY > > > > >> slow. Any tips? > > > > > > > > > I found that if you create an index before doing a bulk COPY, yes, it does > > > > > take forever to load, and the select is slow. What I did was drop the > > > > > indices built from the COPY and rebuild them. Speeded the selects up > > > > > significantly. So now I don't build any indices until after I load my > > > > > huge databases in. > > > > > > > > Not building the indexes until you've done the bulk load is good advice; > > > > it does seem a lot faster to build an index on an already-loaded table > > > > than to construct it piecemeal during the COPY. However, either way > > > > should result in the same index, so I don't see why it'd affect the > > > > speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE > > > > both times? The system is likely to ignore the index until you have > > > > vacuumed the table. > > > > > > > > In short, best bulk load procedure is > > > > > > > > CREATE TABLE ... > > > > COPY ... > > > > CREATE INDEX(es) on table > > > > Repeat as needed for all tables being bulk-loaded > > > > VACUUM ANALYZE > > > > > > > > BTW, if you use pg_dump to dump and reload a big database, pg_dump > > > > knows about the create-indexes-last trick. But it doesn't do a VACUUM > > > > for you; you have to do that by hand after running the reload script, > > > > or your database will be slow. > > > > > > > > regards, tom lane > > > > > > > > > > > > >
And there off :D Reloading it. We'll try again :blah: Thanks, Jason --- Jason Slagle Network Administrator - Toledo Internet Access - Toledo Ohio - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 On Wed, 10 Mar 1999, Karl Denninger wrote: > That's a waste - a vacuum on a table with no deletes having been executed > and no indices will go through everything and do absolutely nothing :-) > > -- > -- > Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl > I ain't even *authorized* to speak for anyone other than myself, so give > up now on trying to associate my words with any particular organization. > > > On Wed, Mar 10, 1999 at 01:39:19PM -0500, Jason Slagle wrote: > > Haven't created any yet, as I have not yet determined what I want to index > > on. I just created the database and bulk loaded 211 megs of : delimited > > data, generating a 350 meg file in the data dir. It has been vacuuming > > for 45 mins or so now... Prob needs more horsepower :D > > > > Jason > > > > --- > > Jason Slagle > > Network Administrator - Toledo Internet Access - Toledo Ohio > > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 > > > > On Wed, 10 Mar 1999, Karl Denninger wrote: > > > > > Depends on the indices that have to be gone through. It can take quite a > > > while. > > > > > > -- > > > -- > > > Karl Denninger (karl@denninger.net) http://www.mcs.net/~karl > > > I ain't even *authorized* to speak for anyone other than myself, so give > > > up now on trying to associate my words with any particular organization. > > > > > > > > > On Wed, Mar 10, 1999 at 01:27:20PM -0500, Jason Slagle wrote: > > > > And how long is normal for Vacuum analyze to take on bout 350 megs of > > > > DATA? > > > > > > > > Jason > > > > > > > > --- > > > > Jason Slagle > > > > Network Administrator - Toledo Internet Access - Toledo Ohio > > > > - raistlin@tacorp.net - jslagle@toledolink.com - WHOIS JS10172 > > > > > > > > On Wed, 10 Mar 1999, Tom Lane wrote: > > > > > > > > > "Brett W. McCoy" <bmccoy@lan2wan.com> writes: > > > > > >> I have 1,400,000 entries (200MB) I'm inserting into a database. Radius > > > > > >> detail files as a matter of fact. Apart from COPY taking forever to load > > > > > >> that (probably due to my several indexes), it seems the select is VERY > > > > > >> slow. Any tips? > > > > > > > > > > > I found that if you create an index before doing a bulk COPY, yes, it does > > > > > > take forever to load, and the select is slow. What I did was drop the > > > > > > indices built from the COPY and rebuild them. Speeded the selects up > > > > > > significantly. So now I don't build any indices until after I load my > > > > > > huge databases in. > > > > > > > > > > Not building the indexes until you've done the bulk load is good advice; > > > > > it does seem a lot faster to build an index on an already-loaded table > > > > > than to construct it piecemeal during the COPY. However, either way > > > > > should result in the same index, so I don't see why it'd affect the > > > > > speed of a subsequent SELECT. Did you remember to do VACUUM ANALYZE > > > > > both times? The system is likely to ignore the index until you have > > > > > vacuumed the table. > > > > > > > > > > In short, best bulk load procedure is > > > > > > > > > > CREATE TABLE ... > > > > > COPY ... > > > > > CREATE INDEX(es) on table > > > > > Repeat as needed for all tables being bulk-loaded > > > > > VACUUM ANALYZE > > > > > > > > > > BTW, if you use pg_dump to dump and reload a big database, pg_dump > > > > > knows about the create-indexes-last trick. But it doesn't do a VACUUM > > > > > for you; you have to do that by hand after running the reload script, > > > > > or your database will be slow. > > > > > > > > > > regards, tom lane > > > > > > > > > > > > > > > > > > >
> That's a waste - a vacuum on a table with no deletes having been executed > and no indices will go through everything and do absolutely nothing :-) Ah, but VACUUM ANALYZE does statistics, which is needed. I should break out ANALYZE as a separate command. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026