Обсуждение: slow INSERTS :((
hi , I have migrated from MySql to PostgreSQL and i find that inserts are extremly slow.The things which used to be done in 10-15 mins in mysql take 1.5 hrs in pgsql(though i expected double or triple the time but not 8 times...omg). I am using pgsql-7.1 with Redhat linux 7.2 Tips to increase and improve performace would be appriciated. thanks for any help -- ------ Varun Do not kill time ... else time will kill you
On Sun, Jun 16, 2002 at 07:21:12PM +0530, Varun Kacholia wrote: > hi , > I have migrated from MySql to PostgreSQL and i find that inserts are > extremly slow.The things which used to be done in 10-15 mins in mysql > take 1.5 hrs in pgsql(though i expected double or triple the time but > not 8 times...omg). > I am using pgsql-7.1 with Redhat linux 7.2 > Tips to increase and improve performace would be appriciated. Are you using transactions? Putting it all in one transaction should speed it up considerably. begin; inserts... commit; Otherwise, shows us the explain for the query. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Varun Kacholia wrote: > hi , > I have migrated from MySql to PostgreSQL and i find that inserts are > extremly slow.The things which used to be done in 10-15 mins in mysql > take 1.5 hrs in pgsql(though i expected double or triple the time but > not 8 times...omg). > I am using pgsql-7.1 with Redhat linux 7.2 > Tips to increase and improve performace would be appriciated. > thanks for any help VACUUM ANALYZE ... may speed up your database if you're making lots of changes. You should do this on a regular basis. Also, if you post details of your table layouts and your insert operations, the gurus on the list will be able to give you more specific suggestions. -- Bill Moran Potential Technologies http://www.potentialtech.com
hi , Actually i am building a high end search engine which requires one time monsterous INSERTs(done by a single script).This is what takes a looot of time and i was worried abt it. I read that setting fsync=false will help (but also since wal logs are used ... will it help that much?).I am not much concerned abt the data loses as i already have it backed up(in raw form which the script parses and inserts in the db ) and am solely concerned that they get *inserted* in the database. For search purpose i am creating a couple of indexes also ... should i drop them initially and create them later? Isnt the following query supported in postgresql (it is surely useful and supported in MySQL ) CREATE INDEX t_index ON (NAME(20) , ID); Mysql users might be knowing NAME(20) that it indexes only on first 20 chars of the field NAME. Postgresql refused to take this and as a result i have indexes made on fields whose length is like 200 chars. any kind of help would be appriciated thanks > > hi , > > I have migrated from MySql to PostgreSQL and i find that inserts are > > extremly slow.The things which used to be done in 10-15 mins in mysql > > take 1.5 hrs in pgsql(though i expected double or triple the time but > > not 8 times...omg). > > I am using pgsql-7.1 with Redhat linux 7.2 > > Tips to increase and improve performace would be appriciated. > > thanks for any help > > To help, we need to know what you are doing, how you have configured, > &c. Are you running in one transaction? Check the archives for lots > of performance posts. > > A > > -- > ---- > Andrew Sullivan 87 Mowat Avenue > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M6K 3E3 > +1 416 646 3304 x110 > -- ------ Varun Do not kill time ... else time will kill you
Varun Kacholia <varunk@cse.iitb.ac.in> writes: > I have migrated from MySql to PostgreSQL and i find that inserts are > extremly slow. See the standard tips at http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/populate.html > Isnt the following query supported in postgresql (it is surely useful and > supported in MySQL ) > CREATE INDEX t_index ON (NAME(20) , ID); I can't see any particular value in that myself, but Postgres supports a generic approach to weird indexing requirements: functional indexes. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/indexes-functional.html regards, tom lane