[MDEV-4417] ALTER TABLE ADD INDEX - PROCESSLIST - PROGRESS NOT RIGHT (extend progress report for partitioning) Created: 2013-04-22  Updated: 2022-09-08

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: roberto spadim Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: verified


 Description   

Hi guys, i was running some ALTER TABLE xxx ADD KEY yyy (zzz)
and watching status from
SELECT time_ms/1000,state,info,stage,max_stage,progress FROM information_schema.processlist WHERE info!=''

and i get 4 status:
1)COPYING TO TMP TABLE
2)REPAIR BY SORTING
3)CREATING INDEX
4)REPAIR BY SORTING

to problema is stage/progress
the max_stage was = 2 while running 1,2
when it started 3,4 the stage,max_stage and progress was set to 0
in other words, the max_stage was 'calculated' wrong at stage 1 and 2, it should be 4 instead of 2
in stage 3,4 the stage, max_stage and progress couldn't help showing the progress of operation (i can't know how many time or progress i will wait)

my table have about 1,5GB of data, in a small table i couldn't see this since it's really fast

thankx

 Comments   
Comment by roberto spadim [ 2013-04-22 ]

ops.. * to problema is stage/progress = the problem is stage/progress

Comment by Elena Stepanova [ 2013-04-28 ]

Hi,

I went up to ~5 Gb of data, so that the whole alter statement took almost half an hour, but still couldn't reproduce the described behavior.
Please provide SHOW CREATE TABLE, my.cnf and show which index you were creating.

Thanks.

Comment by roberto spadim [ 2013-05-02 ]

SHOW CREATE TABLE:
CREATE TABLE `rastreabilidade` (
`codigo_barra` char(20) NOT NULL DEFAULT '',
`table_partition` int(11) NOT NULL DEFAULT '0',
`codigo_barra_raw` char(20) NOT NULL DEFAULT '',
`tipo` enum('interna','caixa','pallet') NOT NULL DEFAULT 'caixa',
`unidade_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`lote_data` date NOT NULL DEFAULT '0000-00-00',
`lote` smallint(5) unsigned NOT NULL DEFAULT '0',
`lote_sif_data` date NOT NULL DEFAULT '0000-00-00',
`lote_sif` int(11) NOT NULL DEFAULT '0',
`op` bigint(20) NOT NULL DEFAULT '0',
`op_seq` int(11) NOT NULL DEFAULT '0',
`spa_tipo` enum('v','c','i') NOT NULL DEFAULT 'v',
`spa_numero` decimal(12,1) NOT NULL DEFAULT '0.0',
`item_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`item_id_red` mediumint(9) NOT NULL DEFAULT '0',
`via_impressao` tinyint(3) unsigned NOT NULL DEFAULT '0',
`produzido` enum('Y','N') NOT NULL DEFAULT 'N',
`excluido` enum('Y','N') NOT NULL DEFAULT 'N',
`composicao_baixada` enum('Y','N') NOT NULL DEFAULT 'N',
`sobra` enum('Y','N') NOT NULL DEFAULT 'N',
`data_etiqueta` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_producao` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_processamento` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_validade` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_etiqueta_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_producao_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_processamento_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_validade_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`quant` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pecas` decimal(17,5) NOT NULL DEFAULT '0.00000',
`quant_mov_un` char(5) NOT NULL DEFAULT 'un',
`proprietario_atual_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`proprietario_atual_id` bigint(20) NOT NULL DEFAULT '0',
`local_atual_un` smallint(5) unsigned NOT NULL DEFAULT '0',
`local_atual_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`local_atual_id` int(10) unsigned NOT NULL DEFAULT '0',
`local_atual_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
`local_atual_org` smallint(5) unsigned NOT NULL DEFAULT '0',
`local_atual_org_red` mediumint(9) NOT NULL DEFAULT '0',
`local_atual_mapa` bigint(20) NOT NULL DEFAULT '0',
`local_atual_mapa_red` bigint(20) NOT NULL DEFAULT '0',
`ultima_cfop_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`ultima_cfop_id_red` mediumint(9) NOT NULL DEFAULT '0',
`rastreabilidade_interna` char(25) NOT NULL DEFAULT '',
`rastreabilidade_externa` char(25) NOT NULL DEFAULT '',
`rastreabilidade_caixa` char(25) NOT NULL DEFAULT '',
`rastreabilidade_pallet` char(25) NOT NULL DEFAULT '',
`contrato_producao` char(25) NOT NULL DEFAULT '',
`externa_tipo` char(25) NOT NULL DEFAULT 'SISBOV',
`proprietario_origem_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`proprietario_origem_id` bigint(20) NOT NULL DEFAULT '0',
`local_origem_un` smallint(5) unsigned NOT NULL DEFAULT '0',
`local_origem_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`local_origem_id` int(10) unsigned NOT NULL DEFAULT '0',
`local_origem_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
`local_origem_org` smallint(5) unsigned NOT NULL DEFAULT '0',
`local_origem_org_red` mediumint(9) NOT NULL DEFAULT '0',
`local_origem_mapa` bigint(20) NOT NULL DEFAULT '0',
`local_origem_mapa_red` bigint(20) NOT NULL DEFAULT '0',
`cfop_origem_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`cfop_origem_id_red` mediumint(9) NOT NULL DEFAULT '0',
`quant_real` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pecas_real` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pliq_real` decimal(17,5) NOT NULL DEFAULT '0.00000',
`pbruto_real` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vliq_real` decimal(17,5) NOT NULL DEFAULT '0.00000',
`vbruto_real` decimal(17,5) NOT NULL DEFAULT '0.00000',
`usuario` char(25) NOT NULL DEFAULT '',
`sessao` char(41) NOT NULL DEFAULT '',
`tara_primaria` decimal(17,5) NOT NULL DEFAULT '0.00000',
`tara_secundaria` decimal(17,5) NOT NULL DEFAULT '0.00000',
`tara_acrescida` decimal(17,5) NOT NULL DEFAULT '0.00000',
`ultima_unidade` mediumint(8) unsigned NOT NULL DEFAULT '0',
`ultima_spa_tipo` enum('v','i','c') NOT NULL DEFAULT 'i',
`ultima_spa_numero` decimal(12,1) NOT NULL DEFAULT '0.0',
`ultima_oe_tipo` enum('op','oe','tr','ex','bx','bx-tr','rp-tr') NOT NULL DEFAULT 'oe',
`ultima_oe` bigint(20) NOT NULL DEFAULT '0',
`ultima_oe_seq` int(11) NOT NULL DEFAULT '0',
`desmontagem_grupo_id` int(11) NOT NULL DEFAULT '0',
`desmontagem_sequencia` mediumint(8) unsigned NOT NULL DEFAULT '0',
`desmontagem_divisoes` tinyint(3) unsigned NOT NULL DEFAULT '0',
`desmontagem_montados` int(11) NOT NULL DEFAULT '0',
`desmontagem_unidade_id` int(11) NOT NULL DEFAULT '0',
`desmontagem_centro_id` int(11) NOT NULL DEFAULT '0',
`montagem_divisao` tinyint(3) unsigned NOT NULL DEFAULT '0',
`montagem_divisoes` tinyint(3) unsigned NOT NULL DEFAULT '0',
`montagem_sequencia` int(11) NOT NULL DEFAULT '0',
`animais_peso_abate` enum('Y','N') NOT NULL DEFAULT 'N',
`classificacao` char(5) NOT NULL DEFAULT '0',
`ultima_alteracao` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lote_rendimento_data` date NOT NULL DEFAULT '0000-00-00',
`lote_rendimento_numero` smallint(5) unsigned NOT NULL DEFAULT '0',
`valor_custo` decimal(15,5) NOT NULL DEFAULT '0.00000',
`centro_exclusao` char(20) NOT NULL DEFAULT '0',
`centro_exclusao_tipo` enum('pdp','pde','pdt') NOT NULL DEFAULT 'pdp',
`pliq_padrao` enum('Y','N') NOT NULL DEFAULT 'N',
`vliq_padrao` enum('Y','N') NOT NULL DEFAULT 'N',
`pecas_padrao` enum('Y','N') NOT NULL DEFAULT 'N',
`numero_reg_est_mov` int(10) unsigned NOT NULL DEFAULT '0',
`custo_rendimento` enum('Y','N') NOT NULL DEFAULT 'N',
`custo_anterior_rendimento` decimal(17,5) NOT NULL DEFAULT '0.00000',
`item_origem_id` int(11) NOT NULL DEFAULT '0',
`item_origem_id_red` int(11) NOT NULL DEFAULT '0',
`operacao` smallint(5) unsigned NOT NULL DEFAULT '0',
`local_atual_data_processamento` date NOT NULL DEFAULT '0000-00-00',
`local_atual_data_ts` decimal(21,7) NOT NULL DEFAULT '0.0000000',
`crossdock_unidade_id` int(10) unsigned NOT NULL DEFAULT '0',
`crossdock_lote_tipo` enum('v','c','i') NOT NULL DEFAULT 'i',
`crossdock_lote_spa` decimal(12,1) NOT NULL DEFAULT '0.0',
`crossdock_oe` int(11) NOT NULL DEFAULT '0',
`crossdock_oe_seq` int(11) NOT NULL DEFAULT '0',
`baixar_rastros_origem` enum('Y','N') NOT NULL DEFAULT 'N',
`imprimir_rastros_destino` enum('Y','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`codigo_barra`,`table_partition`),
UNIQUE KEY `codigo_barra_raw` (`codigo_barra_raw`,`table_partition`),
KEY `pdp` (`local_origem_tipo`,`local_origem_id`,`local_origem_org`,`local_origem_org_red`,`usuario`,`data_producao_gmt`,`sessao`),
KEY `op` (`unidade_id`,`spa_tipo`,`spa_numero`,`op`,`item_id`,`item_id_red`,`lote_data`,`lote`),
KEY `pallet` (`rastreabilidade_pallet`),
KEY `atual` (`local_atual_tipo`,`local_atual_id`,`local_atual_org`,`local_atual_org_red`,`item_id`,`item_id_red`),
KEY `producao` (`unidade_id`,`data_producao_gmt`,`item_id`,`item_id_red`),
KEY `rom` (`unidade_id`,`spa_tipo`,`spa_numero`,`op`,`item_id`,`item_id_red`,`excluido`,`sobra`),
KEY `rom2` (`unidade_id`,`spa_tipo`,`spa_numero`,`op`,`item_id`,`item_id_red`,`sobra`,`local_atual_un`),
KEY `op_origem` (`unidade_id`,`spa_tipo`,`spa_numero`,`op`,`item_origem_id`,`item_origem_id_red`,`lote_data`,`lote`),
KEY `item` (`item_id`,`item_id_red`)
) ENGINE=Aria DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (table_partition)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = Aria,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = Aria) */

alter query:
ALTER TABLE rastreabilidade DROP KEY item,
add KEY item (item_id,item_id_red,local_atual_org_red,local_atual_data_processamento);

my.cnf:
##############################################
[mysqld2]
skip-name-resolve
open_files_limit =4096
default-storage-engine=MYISAM
ledir=/opt/mariadb/bin/
mysqld = /opt/mariadb/bin/mysqld_safe
socket = /tmp/mysql.BH.sock
port = 3307
pid-file = /home/mysql/pid/local.pid
datadir = /home/mysql/data/local/
language = /opt/mariadb/share/portuguese
log-slow-queries = /home/mysql/log/local.slow_query.log
log-error = /home/mysql/log/local.error.log
long_query_time = 2
max_connections = 90
key_buffer = 5000M
table_cache = 350
sort_buffer_size = 6M
read_buffer_size = 6M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
tmp_table_size = 8M
max_allowed_packet = 16M
net_buffer_length = 16K
wait_timeout = 30
sql_mode = ALLOW_INVALID_DATES,PIPES_AS_CONCAT
concurrent_insert = 2
div_precision_increment = 8
query_cache_size = 128M
query_cache_limit = 8M
query_cache_type = 1
query_cache_min_res_unit= 256
thread_cache_size = 20
myisam-recover = BACKUP,FORCE,QUICK

Comment by Elena Stepanova [ 2013-05-03 ]

Thanks, so it's a partitioned table.

For a "normal" (not partitioned) Aria table, I'm getting the following at the end of ALTER:

....
 
236.8702390     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      2       2       74.952
 
time_ms/1000    state   info    stage   max_stage       progress
 
237.8775610     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      2       2       74.952
 
time_ms/1000    state   info    stage   max_stage       progress
 
238.8851770     Saving state    ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000
 
time_ms/1000    state   info    stage   max_stage       progress
 
239.9029480     rename result table     ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000

That is, out of 4 min ALTER, the uncounted operations (Saving state and rename result table) only take 1 second. It also doesn't seem to depend on table size, so I think it's reasonable not to include these operations into the stage count.

For a partitioned table, I'm getting something similar to the original description (the order of states a bit different, but the idea is the same):

.....
 
218.1358460     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      2       2       74.880
 
time_ms/1000    state   info    stage   max_stage       progress
 
219.1438200     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      2       2       74.880
 
time_ms/1000    state   info    stage   max_stage       progress
 
220.1522680     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000
 
time_ms/1000    state   info    stage   max_stage       progress
 
221.1598560     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000
 
time_ms/1000    state   info    stage   max_stage       progress
 
.....
 
242.3328230     Repair by sorting       ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000
 
time_ms/1000    state   info    stage   max_stage       progress
 
243.3410960     Creating index  ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000
 
time_ms/1000    state   info    stage   max_stage       progress
 
244.3486520     rename result table     ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000
 
time_ms/1000    state   info    stage   max_stage       progress
 
245.4016940     rename result table     ALTER TABLE t1 DROP KEY a, ADD KEY a (a,b)      0       0       0.000

That is, up to some point the process goes the usual way, but then it switches from the "normal" Repair by sorting (stage 2 with progress report) to another Repair of sorting, without the stage number and progress report, and this stage takes quite some time. Then Creating index goes, but it's very fast, even on much bigger tables.

My guess is that this part of 'repair by sorting' belongs to partitioning, and it currently does not support progress reporting, which is not exactly a crime. But I suppose we could add it to the wishlist in http://askmonty.org/worklog/Server-RawIdeaBin/?tid=202

A similar thing would happen if instead of a partitioned Aria table there were a MyISAM table, even not partitioned – in this case the whole 'Repair by sorting' operation is marked as stage 2, but doesn't have a progress.

Comment by roberto spadim [ 2013-05-03 ]

hum could at least, use the last stage number instead getting it down to 0?
or add another information about 'no progress information available', and leave stage and progress unchanged?

Comment by roberto spadim [ 2014-08-11 ]

hi guys with 10.0.13 partitions don't have support for second stage progress

can anyone help with this feature?

ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS
55040 rspadim 186.232.81.114:27748 conquista_comercial Query 57 Repair by sorting alter table est_mov
add `viagem_id` INT(11) NOT NULL DEFAULT '0' COMMENT 'Utilizado para controle de despesas de viagens (transporte)',
add `numero_reg_est_mov` SMALLINT(6) NOT NULL DEFAULT '0',
add `rastreabilidade_componente` CHAR(25) NOT NULL DEFAULT '' after rastreabilidade_pallet,
add `estorno` ENUM('Y','N') NOT NULL DEFAULT 'N',
add `excluido` ENUM('Y','N') NOT NULL DEFAULT 'N' 		57971.927 2 2 0.000 350880 0
Generated at Thu Feb 08 06:56:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.