Re: pg_restore taking 4 hours!
От | Joshua D. Drake |
---|---|
Тема | Re: pg_restore taking 4 hours! |
Дата | |
Msg-id | 41AF486C.8090706@commandprompt.com обсуждение исходный текст |
Ответ на | Re: pg_restore taking 4 hours! (Thierry Missimilly <Thierry.Missimilly@bull.net>) |
Список | pgsql-general |
Thierry Missimilly wrote: > > Rodrigo Carvalhaes a écrit : > >> Hi! >> >> I am using PostgreSQL with a proprietary ERP software in Brazil. The >> database have around 1.600 tables (each one with +/- 50 columns). >> My problem now is the time that takes to restore a dump. My customer >> database have arount 500mb (on the disk, not the dump file) and I am >> making the dump with pg_dump -Fc, my dumped file have 30mb. To make >> the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore >> ) it it takes 4 - 5 hours!!! > > > I have notice that fac and one way to improve the restore prefomances, > is to avoid build indexes and checking the foreign key in the same > step than the restore. > So, as it is not possible to disable indexes and Foreign key, you have > to drop them and recreate them once the restore step has finished. To > do that you should have a script to recreate the indexes and the > Foreign Key afterward. > There are a couple of things you can do. 1. Turn off Fsync for the restore 2. Restore in three phases: 1. Schema without constraints or indexes 2. Restore data 3. Apply rest of schema with constraints and indexes 3. Increase the number of transaction logs. Sincerely, Joshua D. Drake >> >> Our machine it's a Dell Server Power Edge 1600sc (Xeon 2,4Ghz, with >> 1GB memory, 7200 RPM disk). I don't think that there is a machine >> problem because it's a server dedicated for the database and the cpu >> utilization during the restore is around 30%. >> >> Looking on the lists arquives I found some messages about this and >> Tom Lane was saying that then you have a lot of convertions the dump >> can delay too much. 90% of the columns on my database are char >> columns and I don't have large objects on the database. The restore >> is delaying too much because the conversion of the char columns ? How >> can I have a better performance on this restore? >> >> I need to find a solution for this because I am convincing customers >> that are using SQL Server, DB2 and Oracle to change to PostgreSQL but >> this customers have databases of 5GB!!! I am thinking that even with >> a better server, the restore will take 2 days! >> >> My data: >> Conectiva Linux 10 , Kernel 2.6.8 >> PostgreSQL 7.4.6. >> >> postgresql.conf modified parameters (the other parameters are the >> default) >> tcpip_socket = true >> max_connections = 30 >> shared_buffers = 30000 >> sort_mem = 4096 vacuum_mem = 8192 >> max_fsm_pages = 20000 >> max_fsm_relations = 1000 >> >> Regards, >> >> Rodrigo Carvalhaes >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Вложения
В списке pgsql-general по дате отправления: