Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 10.0
-
Fix Version/s: 10.0
-
Component/s: Data Definition - Alter Table
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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
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.
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?
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 |
ops.. * to problema is stage/progress = the problem is stage/progress