Re: pg_restore taking 4 hours! - Mailing list pgsql-performance

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