Thread: pg_restore taking 4 hours!

From:
Rodrigo Carvalhaes
Date:

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

From:
Shridhar Daithankar
Date:

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

From:
Tom Lane
Date:

Shridhar Daithankar <> 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

From:
"Riccardo G. Facchini"
Date:

--- 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.

From:
Josh Berkus
Date:

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

From:
Andrew McMillan
Date:

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.
-------------------------------------------------------------------------


From:
Rodrigo Carvalhaes
Date:

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
>
>
>

From:
Hannu Krosing
Date:

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


From:
Vivek Khera
Date:

>>>>> "RC" == Rodrigo Carvalhaes <> 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:        Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

From:
Josh Berkus
Date:

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

From:
Josh Berkus
Date:

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

From:
Tom Lane
Date:

Josh Berkus <> 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

From:
"Iain"
Date:

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