Thread: pg_restore taking 4 hours!
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!!! 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
On Wednesday 01 Dec 2004 4:46 pm, Rodrigo Carvalhaes wrote: > 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 Can you try bumping sort mem lot higher(basically whatever the machine can afford) so that index creation is faster? Just try setting sort mem for the restore session and see if it helps.. Shridhar
Shridhar Daithankar <ghodechhap@ghodechhap.net> writes:
> On Wednesday 01 Dec 2004 4:46 pm, Rodrigo Carvalhaes wrote:
>> 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!
> Can you try bumping sort mem lot higher(basically whatever the machine can
> afford) so that index creation is faster?
It would be a good idea to bump up vacuum_mem as well. In current
sources it's vacuum_mem (well actually maintenance_work_mem) that
determines the speed of CREATE INDEX; I forget just how long that
behavior has been around, but 7.4.6 might do it too.
regards, tom lane
--- Shridhar Daithankar <__> wrote: > On Wednesday 01 Dec 2004 4:46 pm, Rodrigo Carvalhaes wrote: > > 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 > > Can you try bumping sort mem lot higher(basically whatever the > machine can > afford) so that index creation is faster? > > Just try setting sort mem for the restore session and see if it > helps.. > > Shridhar > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > Yes, indexing is probably the issue. You can always ask them to report how long does it take to restore their M$-SQL, DB2 and Oracle from a scripting dump. I've been restoring DB2 for a looong time (on different architectures) and the main problem comes from indexing. As an index is basically a dynamic structure that is created on the physical data (the data stored on the table), what is normally saved is the index DEFINITION, not the index itself, so this is recreated at restore time. Some DB2 architectures (and M$-SQL, and Oracle, and Sybase, and others. others) may have a backup tool that is capable of saving the index data, but is almost never used, as the index space itself can grow well over the data size. I'll give one example: we have one DB2 on iSeries that runs around the 70Gb of Data and Indexes. We do a full backup that occupies only 45Gb of Data and we do that in a little more than 1 hour because we only save the index definitions. We know for sure that this full backup takes something between 5 and 7 hours because of the reindexing. I had this written down in the Restore Procedure Manual, so the user can't complain (they know that the procedure will eventually restore the data and the full functionality). So, make sure that your client knows of their restore times. One small trick that can help you: FIRST restore the tables. THEN restore the foreingn keys, the constraints and the triggers and procedures. LAST restore the indexes and views. LATEST restore the security. This way, if you have complicated views and indexes with a lot of info, the procedure <<<may>>> be shorter. regards, R.
Rodrigo, > 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%. In addition to Tom and Shridhar's advice, a single IDE disk is simply going to make restores slow. A 500MB data file copy on that disk, straight, would take up to 15 min. If this is for your ISV application, you need to seriously re-think your hardware strategy; spending less on processors and more on disks would be wise. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Wed, 2004-12-01 at 09:16 -0200, Rodrigo Carvalhaes wrote:
>
> I am using PostgreSQL with a proprietary ERP software in Brazil. The
> database have around 1.600 tables (each one with +/- 50 columns).
...
> max_fsm_pages = 20000
> max_fsm_relations = 1000
Hi,
I doubt that this will improve your pg_restore performance, but if you
have 1600 tables in the database then you very likely want to increase
the above two settings.
In general max_fsm_relations should be more than the total number of
tables across all databases in a given installation. The best way to
set these is to do a "VACUUM VERBOSE", which will print the appropriate
minimum numbers at the end of the run, along with the current setting.
Cheers,
Andrew.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
Never trust a computer you can't repair yourself.
-------------------------------------------------------------------------
Attachment
Hi !
Thanks for the lots of tips that I received on this matter.
Some points:
1. I bumped the sort_mem and vaccum_mem to 202800 (200mb each) and the
performance was quite the same , the total difference was 10 minutes
2. I made the restore without the index and the total time was 3 hours
so, I don't think that the botle neck is the index creation
3. I changed my max_fsm_pages to 30000 and max_fsm_relations = 2000 as
was recommended on the vacuum analyze but I had no significante change
on the performance.
4. I made the backup with pg_dump -Fc and -Ft . The performance of -Ft
was better (around 10%), maybe because the data it's already uncompressed.
I am thinking that the key point on this delay is the converstions from
char fields because this database is full of char fields, see below one
structure of one table
There is something more that I can try to improve this performance?
Cheers (and thanks for all the oppinions)
Rodrigo Carvalhaes
dadosadv=# \d sb1010
Table
"public.sb1010"
Column | Type
| Modifiers
------------+------------------+---------------------------------------------------------------------------------------------
----------------
b1_filial | character(2) | not null default ' '::bpchar
b1_cod | character(15) | not null default ' '::bpchar
b1_desc | character(30) | not null default
' '::bpchar
b1_tipo | character(2) | not null default ' '::bpchar
b1_codite | character(27) | not null default
' '::bpchar
b1_um | character(2) | not null default ' '::bpchar
b1_locpad | character(2) | not null default ' '::bpchar
b1_grupo | character(4) | not null default ' '::bpchar
b1_picm | double precision | not null default 0.0
b1_ipi | double precision | not null default 0.0
b1_posipi | character(10) | not null default ' '::bpchar
b1_especie | double precision | not null default 0.0
b1_ex_ncm | character(3) | not null default ' '::bpchar
b1_ex_nbm | character(3) | not null default ' '::bpchar
b1_aliqiss | double precision | not null default 0.0
b1_codiss | character(8) | not null default ' '::bpchar
b1_te | character(3) | not null default ' '::bpchar
b1_ts | character(3) | not null default ' '::bpchar
b1_picmret | double precision | not null default 0.0
b1_picment | double precision | not null default 0.0
b1_impzfrc | character(1) | not null default ' '::bpchar
b1_bitmap | character(8) | not null default ' '::bpchar
b1_segum | character(2) | not null default ' '::bpchar
b1_conv | double precision | not null default 0.0
b1_tipconv | character(1) | not null default ' '::bpchar
b1_alter | character(15) | not null default ' '::bpchar
b1_qe | double precision | not null default 0.0
b1_prv1 | double precision | not null default 0.0
b1_emin | double precision | not null default 0.0
b1_custd | double precision | not null default 0.0
b1_mcustd | character(1) | not null default ' '::bpchar
b1_uprc | double precision | not null default 0.0
b1_ucom | character(8) | not null default ' '::bpchar
b1_peso | double precision | not null default 0.0
b1_pesob | double precision | not null default 0.0
b1_estseg | double precision | not null default 0.0
b1_estfor | character(3) | not null default ' '::bpchar
b1_forprz | character(3) | not null default ' '::bpchar
b1_pe | double precision | not null default 0.0
b1_tipe | character(1) | not null default ' '::bpchar
b1_le | double precision | not null default 0.0
b1_lm | double precision | not null default 0.0
b1_conta | character(20) | not null default '
'::bpchar
b1_cc | character(9) | not null default ' '::bpchar
b1_toler | double precision | not null default 0.0
b1_itemcc | character(9) | not null default ' '::bpchar
b1_familia | character(1) | not null default ' '::bpchar
b1_proc | character(6) | not null default ' '::bpchar
b1_lojproc | character(2) | not null default ' '::bpchar
b1_qb | double precision | not null default 0.0
b1_apropri | character(1) | not null default ' '::bpchar
b1_fantasm | character(1) | not null default ' '::bpchar
b1_tipodec | character(1) | not null default ' '::bpchar
b1_origem | character(2) | not null default ' '::bpchar
b1_clasfis | character(2) | not null default ' '::bpchar
b1_datref | character(8) | not null default ' '::bpchar
b1_rastro | character(1) | not null default ' '::bpchar
b1_urev | character(8) | not null default ' '::bpchar
b1_foraest | character(1) | not null default ' '::bpchar
b1_comis | double precision | not null default 0.0
b1_mono | character(1) | not null default ' '::bpchar
b1_mrp | character(1) | not null default ' '::bpchar
b1_perinv | double precision | not null default 0.0
b1_dtrefp1 | character(8) | not null default ' '::bpchar
b1_grtrib | character(3) | not null default ' '::bpchar
b1_notamin | double precision | not null default 0.0
b1_prvalid | double precision | not null default 0.0
b1_numcop | double precision | not null default 0.0
b1_contsoc | character(1) | not null default ' '::bpchar
b1_conini | character(8) | not null default ' '::bpchar
b1_irrf | character(1) | not null default ' '::bpchar
b1_codbar | character(15) | not null default ' '::bpchar
b1_grade | character(1) | not null default ' '::bpchar
b1_formlot | character(3) | not null default ' '::bpchar
b1_localiz | character(1) | not null default ' '::bpchar
b1_fpcod | character(2) | not null default ' '::bpchar
b1_operpad | character(2) | not null default ' '::bpchar
b1_contrat | character(1) | not null default ' '::bpchar
b1_desc_p | character(6) | not null default ' '::bpchar
b1_desc_gi | character(6) | not null default ' '::bpchar
b1_desc_i | character(6) | not null default ' '::bpchar
b1_vlrefus | double precision | not null default 0.0
b1_import | character(1) | not null default ' '::bpchar
b1_opc | character(80) | not null default '
'::bpchar
b1_anuente | character(1) | not null default ' '::bpchar
b1_codobs | character(6) | not null default ' '::bpchar
b1_sitprod | character(2) | not null default ' '::bpchar
b1_fabric | character(20) | not null default '
'::bpchar
b1_modelo | character(15) | not null default ' '::bpchar
b1_setor | character(2) | not null default ' '::bpchar
b1_balanca | character(1) | not null default ' '::bpchar
b1_tecla | character(3) | not null default ' '::bpchar
b1_prodpai | character(15) | not null default ' '::bpchar
b1_tipocq | character(1) | not null default ' '::bpchar
b1_solicit | character(1) | not null default ' '::bpchar
b1_grupcom | character(6) | not null default ' '::bpchar
b1_numcqpr | double precision | not null default 0.0
b1_contcqp | double precision | not null default 0.0
b1_revatu | character(3) | not null default ' '::bpchar
b1_inss | character(1) | not null default ' '::bpchar
b1_codemb | character(20) | not null default '
'::bpchar
b1_especif | character(80) | not null default '
'::bpchar
b1_mat_pri | character(20) | not null default '
'::bpchar
b1_redinss | double precision | not null default 0.0
b1_nalncca | character(7) | not null default ' '::bpchar
b1_aladi | character(3) | not null default ' '::bpchar
b1_nalsh | character(8) | not null default ' '::bpchar
b1_redirrf | double precision | not null default 0.0
b1_tab_ipi | character(2) | not null default ' '::bpchar
b1_grudes | character(3) | not null default ' '::bpchar
b1_datasub | character(8) | not null default ' '::bpchar
b1_pcsll | double precision | not null default 0.0
b1_pcofins | double precision | not null default 0.0
b1_ppis | double precision | not null default 0.0
b1_mtbf | double precision | not null default 0.0
b1_mttr | double precision | not null default 0.0
b1_flagsug | character(1) | not null default ' '::bpchar
b1_classve | character(1) | not null default ' '::bpchar
b1_midia | character(1) | not null default ' '::bpchar
b1_midia | character(1) | not null default ' '::bpchar
b1_qtmidia | double precision | not null default 0.0
b1_vlr_ipi | double precision | not null default 0.0
b1_envobr | character(1) | not null default ' '::bpchar
b1_qtdser | double precision | not null default 0.0
b1_serie | character(20) | not null default '
'::bpchar
b1_faixas | double precision | not null default 0.0
b1_nropag | double precision | not null default 0.0
b1_isbn | character(10) | not null default ' '::bpchar
b1_titorig | character(50) | not null default
' '::bpchar
b1_lingua | character(20) | not null default '
'::bpchar
b1_edicao | character(3) | not null default ' '::bpchar
b1_obsisbn | character(40) | not null default
' '::bpchar
b1_clvl | character(9) | not null default ' '::bpchar
b1_ativo | character(1) | not null default ' '::bpchar
b1_pesbru | double precision | not null default 0.0
b1_tipcar | character(6) | not null default ' '::bpchar
b1_vlr_icm | double precision | not null default 0.0
b1_vlrselo | double precision | not null default 0.0
b1_codnor | character(3) | not null default ' '::bpchar
b1_corpri | character(6) | not null default ' '::bpchar
b1_corsec | character(6) | not null default ' '::bpchar
b1_nicone | character(15) | not null default ' '::bpchar
b1_atrib1 | character(6) | not null default ' '::bpchar
b1_atrib2 | character(6) | not null default ' '::bpchar
b1_atrib3 | character(6) | not null default ' '::bpchar
b1_regseq | character(6) | not null default ' '::bpchar
b1_ucalstd | character(8) | not null default ' '::bpchar
b1_cpotenc | character(1) | not null default ' '::bpchar
b1_potenci | double precision | not null default 0.0
b1_qtdacum | double precision | not null default 0.0
b1_qtdinic | double precision | not null default 0.0
b1_requis | character(1) | not null default ' '::bpchar
d_e_l_e_t_ | character(1) | not null default ' '::bpchar
r_e_c_n_o_ | double precision | not null default 0.0
Indexes:
"sb1010_pkey" primary key, btree (r_e_c_n_o_)
"sb10101" btree (b1_filial, b1_cod, r_e_c_n_o_, d_e_l_e_t_)
"sb10102" btree (b1_filial, b1_tipo, b1_cod, r_e_c_n_o_, d_e_l_e_t_)
"sb10103" btree (b1_filial, b1_desc, b1_cod, r_e_c_n_o_, d_e_l_e_t_)
"sb10104" btree (b1_filial, b1_grupo, b1_cod, r_e_c_n_o_, d_e_l_e_t_)
"sb10105" btree (b1_filial, b1_codbar, r_e_c_n_o_, d_e_l_e_t_)
"sb10106" btree (b1_filial, b1_proc, r_e_c_n_o_, d_e_l_e_t_)
Shridhar Daithankar wrote:
>On Wednesday 01 Dec 2004 4:46 pm, Rodrigo Carvalhaes wrote:
>
>
>>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
>>
>>
>
>Can you try bumping sort mem lot higher(basically whatever the machine can
>afford) so that index creation is faster?
>
>Just try setting sort mem for the restore session and see if it helps..
>
> Shridhar
>
>
>
On P, 2004-12-05 at 21:43, Rodrigo Carvalhaes wrote: > Hi ! > > Thanks for the lots of tips that I received on this matter. > ... > There is something more that I can try to improve this performance? check the speed of your ide drive. maybe tweak some params with /sbin/hdparm . Sometimes the defaults result in 2MB/sec r/w speeds (instead on(30-70 MB/sec) ------------ Hannu
>>>>> "RC" == Rodrigo Carvalhaes <grupos@carvalhaes.net> writes: RC> Hi! RC> I am using PostgreSQL with a proprietary ERP software in Brazil. The RC> database have around 1.600 tables (each one with +/- 50 columns). RC> My problem now is the time that takes to restore a dump. My customer RC> database have arount 500mb (on the disk, not the dump file) and I am RC> making the dump with pg_dump -Fc, my dumped file have 30mb. To make RC> the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore ) RC> it it takes 4 - 5 hours!!! I regularly dump a db that is compressed at over 2Gb. Last time I did a restore on the production box it took about 3 hours. Restoring it into a development box with a SATA RAID0 config takes like 7 hours or so. The biggest improvement in speed to restore time I have discovered is to increase the checkpoint segments. I bump mine to about 50. And moving the pg_xlog to a separate physical disk helps a lot there, too. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Vivek, > The biggest improvement in speed to restore time I have discovered is > to increase the checkpoint segments. I bump mine to about 50. And > moving the pg_xlog to a separate physical disk helps a lot there, too. Don't leave it at 50; if you have the space on your log array, bump it up to 256. -- Josh Berkus Aglio Database Solutions San Francisco
Vivek, > Do I need a correspondingly large checkpoint timeout then? Or does > that matter much? Yes, you do. > And does this advice apply if the pg_xlog is on the same RAID partition > (mine currently is not, but perhaps will be in the future) Not as much, but it's still a good idea to serialize the load. With too few segments, you get a pattern like: Fill up segments Write to database Recycle segments Fill up segments Write to database Recycle segments etc. Compared to doing it in one long run of a single cycle, considerble efficiency is lost. With a proper 2-array setup, the segments become like a write buffer for the database, and you want that buffer as large as you can afford in order to prevent buffer cycling from interrupting database writes. BTW, for members of the studio audience, checkpoint_segments of 256 is about 8GB. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Not as much, but it's still a good idea to serialize the load. With too few
> segments, you get a pattern like:
> Fill up segments
> Write to database
> Recycle segments
> Fill up segments
> Write to database
> Recycle segments
> etc.
Actually I think the problem is specifically that you get checkpoints
too often if either checkpoint_timeout or checkpoint_segments is too
small. A checkpoint is expensive both directly (the I/O it causes)
and indirectly (because the first update of a particular data page
after a checkpoint causes the whole page to be logged in WAL). So
keeping them spread well apart is a Good Thing, as long as you
understand that a wider checkpoint spacing implies a longer time to
recover if you do suffer a crash.
I think 8.0's bgwriter will considerably reduce the direct cost of
a checkpoint (since not so many pages will be dirty when the checkpoint
happens) but it won't do a thing for the indirect cost.
regards, tom lane
Hi, Sorry, I didn't catch the original message, so I'm not sure if the original poster mentioned the postgres version that he's using. I just thought that I'd contribute this observation. I have a DB that takes several hours to restore under 7,1 but completes in around 10 minutes on 7.4. The main reason for this is that by default the 7.4 restore delays creation of PKs and indexes until after the data load, whereas 7.1 doesn't. I noticed that 7.1 has a re-arrange option that reportedly delays the pks and indexes, so presumably this would have alleviated the problem. I also noticed that a dumpfile created under 7.1 took hours to restore using 7.4 to load it as the order remained in the default of 7.1. I don'tknow when the default behaviour changed, but I get the feeling it may have been with 7.4. HTH Iain