Mysql – adding a column with default value to slow in thesq 5.1

I am adding a char(1) column to a table with this:

mysql> alter table TransaccionesConvenio add Test2 char(1) default 's';

Query OK, 6711062 rows affected (52 min 27.75 sec) Records:
6711062 Duplicates: 0 Warnings: 0

As you can see it takes almost 1h, that seems too much.

The mysql is 5.1.72, 64 bit, with 16GB of ram, and using this config:

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name eos-tst generated for cbergero@netlabs.com.uy at 2016-07-22 13:37:36

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/run/mysqld/mysql.pid
skip-name-resolve

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP


# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
#sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
#sysdate-is-now                 = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 14G

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql-slow.log

Other values:

innodb_log_file_size 268435456

innodb_log_buffer_size = 8388608

The table has 6.7 millon rows and 30 columns. No blob or big columns.
The disks are fast, with almost 100MB/s secuencial read. (No problems there).

UPDATE: adding create table statement

CREATE TABLE `TransaccionesConvenio` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `cedIdentidad` varchar(15) DEFAULT NULL,
   `fechaHora` datetime NOT NULL COMMENT 'fechaHora',
   `nroLote` varchar(20) NOT NULL COMMENT 'nroLote',
   `nroTicket` varchar(20) NOT NULL COMMENT 'nroTicket',
   `confirmada` varchar(1) NOT NULL COMMENT 'S/N. Si la Transacción ha sido confirmada o no desde la Terminal',
   `facturada` varchar(1) NOT NULL COMMENT 'S/N. Si la Transacción ha sido facturada o no desde el Administrador',
   `idDispositivoConvenio` bigint(19) DEFAULT NULL,
   `idConvenio` bigint(20) DEFAULT NULL,
   `idEmpresa` bigint(20) DEFAULT NULL,
   `idTerminalConcesionario` bigint(20) DEFAULT NULL,
   `idTerminalConcesionarioReal` bigint(20) DEFAULT NULL,
   `codTipoTransaccion` varchar(5) DEFAULT NULL COMMENT 'codTipoTransaccion',
   `idTransaccionOriginal` bigint(20) DEFAULT NULL,
   `codAutorizacion` varchar(255) DEFAULT NULL,
   `mensajeConaprole` varchar(255) DEFAULT NULL COMMENT 'Mensaje devuelto por CONAPROLE',
   `nroReferenciaConaprole` int(11) DEFAULT NULL COMMENT 'Número de referencia de CONAPROLE',
   `nroOrden` varchar(255) DEFAULT NULL COMMENT 'Número de Orden',
   `pan` varchar(255) DEFAULT NULL COMMENT 'Valor del pan de las tarjetas de CONAPROLE',
   `track2` varchar(255) DEFAULT NULL COMMENT 'Valor del track2 de las tarjetas de CONAPROLE',
   `mti` varchar(255) DEFAULT NULL COMMENT 'Identificador de la trn para CONAPROLE',
   `auditNumberConaprole` int(11) DEFAULT NULL COMMENT 'Audit Number CONAPROLE',
   `nroTicketConaprole` int(11) DEFAULT NULL COMMENT 'Nro Ticket CONAPROLE',
   `nroLoteConaprole` int(11) DEFAULT NULL COMMENT 'Nro LoteConaprole CONAPROLE',
   `idMonedaImporteFinal` bigint(20) DEFAULT NULL,
   `totalImporteFinal` decimal(19,5) DEFAULT NULL,
   `anulada` varchar(1) DEFAULT NULL COMMENT 'S/N. Si la Transacción ha sido anulada o no desde la Terminal',
   `fechaVtoTarjetaConaprole` varchar(4) DEFAULT NULL,
   `idSolicitudExternaDYF` bigint(20) DEFAULT NULL,
   `idLote` bigint(20) DEFAULT NULL,
   `fechaOrdenUso` datetime DEFAULT NULL COMMENT 'fechaOrdenUso',
   `ordenFechaAnterior` varchar(1) DEFAULT NULL COMMENT 'S/N. Si la Transacción es una orden con fecha anterior',
   `debitada` varchar(1) DEFAULT NULL COMMENT 'S/N. Si la Transacción ha sido debitada o no',
   `codTipoEmisorFactura` varchar(2) DEFAULT NULL COMMENT 'codTipoEmisorFactura',
   `fechaEstadoDeCuenta` datetime DEFAULT NULL COMMENT 'fechaEstadoDeCuenta',
   `liquidada` varchar(1) DEFAULT NULL COMMENT 'S/N. Si la Transacción ha sido liquidada o no',
   `idUpdatePrestamo` bigint(20) DEFAULT NULL,
   `idCotizacion` bigint(20) DEFAULT NULL,
   `vehiculoKilometraje` int(11) DEFAULT NULL COMMENT 'Kilometraje ingresado del vehiculo.',
   `idChofer` bigint(20) DEFAULT NULL,
   `generoDescuentoPorConsumo` varchar(1) DEFAULT NULL COMMENT 'generoDescuentoPorConsumo',
   `nroReserva` varchar(20) DEFAULT NULL,
   `idExterno` varchar(20) DEFAULT NULL,
   `precioPorCantidad` decimal(19,5) DEFAULT NULL,
   `horasMotor` decimal(19,2) DEFAULT NULL COMMENT 'Horas de motor',
   `manguera` int(11) DEFAULT NULL COMMENT 'Numero de manguera',
   `idFlota` bigint(20) DEFAULT NULL,
   `online` varchar(1) DEFAULT NULL COMMENT 'S/N. Si la Transacción ha sido online o no',
   `fechaHoraDespacho` datetime DEFAULT NULL COMMENT 'fechaHoraDespacho',
   `version` int(11) NOT NULL,
   `ordenElectronica` varchar(1) DEFAULT NULL,
   `Test` char(1) DEFAULT 's',
   PRIMARY KEY (`id`),
   KEY `FK7B99AD40F36DB272` (`idConvenio`),
   KEY `FK7B99AD4081132FD3` (`idTerminalConcesionario`),
   KEY `FK7B99AD406C15B554` (`idDispositivoConvenio`),
   KEY `FK7B99AD40C1587D66` (`idTransaccionOriginal`),
   KEY `FK7B99AD40709E24F1` (`idTerminalConcesionarioReal`),
   KEY `FK7B99AD40628614DD` (`idEmpresa`),
   KEY `FK7B99AD40AA986B97` (`idMonedaImporteFinal`),
   KEY `FK7B99AD407E6D3AB3` (`idSolicitudExternaDYF`),
   KEY `idxAnulada` (`anulada`),
   KEY `idxConfirmada` (`confirmada`),
   KEY `idxFacturada` (`facturada`),
   KEY `idxFechaHora` (`fechaHora`),
   KEY `idxNroTicket` (`nroTicket`),
   KEY `idxNroLote` (`nroLote`),
   KEY `idxCodTipoTransaccion` (`codTipoTransaccion`),
   KEY `FK7B99AD40402D497C` (`idCotizacion`),
   KEY `FK7B99AD401F5E6B12` (`idUpdatePrestamo`),
   KEY `FK7B99AD40F55C913E` (`idChofer`),
   KEY `idxNroOrden` (`nroOrden`),
   KEY `idxCodAutorizacion` (`codAutorizacion`),
   KEY `FK7B99AD4050AC685C` (`idDispositivoConvenio`),
   KEY `FK7B99AD406B5B2FC0` (`idFlota`),
   KEY `idxOnline` (`online`),
   KEY `idxLiquidada` (`liquidada`),
   KEY `idxDebitada` (`debitada`),
   KEY `idxIdLote` (`idLote`),
   KEY `FK7B99AD40A7597AAA` (`idLote`),
   KEY `idxNroReserva` (`nroReserva`),
   CONSTRAINT `FK7B99AD401F5E6B12` FOREIGN KEY (`idUpdatePrestamo`) REFERENCES `UpdatesPrestamos` (`id`),
   CONSTRAINT `FK7B99AD40402D497C` FOREIGN KEY (`idCotizacion`) REFERENCES `Cotizaciones` (`id`),
   CONSTRAINT `FK7B99AD4050AC685C` FOREIGN KEY (`idDispositivoConvenio`) REFERENCES `DispositivosConvenio` (`id`),
   CONSTRAINT `FK7B99AD40628614DD` FOREIGN KEY (`idEmpresa`) REFERENCES `Empresas` (`id`),
   CONSTRAINT `FK7B99AD406B5B2FC0` FOREIGN KEY (`idFlota`) REFERENCES `Flotas` (`id`),
   CONSTRAINT `FK7B99AD40709E24F1` FOREIGN KEY (`idTerminalConcesionarioReal`) REFERENCES `TerminalesConcesionario` (`id`),
   CONSTRAINT `FK7B99AD407E6D3AB3` FOREIGN KEY (`idSolicitudExternaDYF`) REFERENCES `SolicitudesExternasDYF` (`id`),
   CONSTRAINT `FK7B99AD4081132FD3` FOREIGN KEY (`idTerminalConcesionario`) REFERENCES `TerminalesConcesionario` (`id`),
   CONSTRAINT `FK7B99AD40A7597AAA` FOREIGN KEY (`idLote`) REFERENCES `LotesTransaccionesConvenio` (`id`),
   CONSTRAINT `FK7B99AD40AA986B97` FOREIGN KEY (`idMonedaImporteFinal`) REFERENCES `Monedas` (`id`),
   CONSTRAINT `FK7B99AD40C1587D66` FOREIGN KEY (`idTransaccionOriginal`) REFERENCES `TransaccionesConvenio` (`id`),
   CONSTRAINT `FK7B99AD40F36DB272` FOREIGN KEY (`idConvenio`) REFERENCES `Convenios` (`id`),
   CONSTRAINT `FK7B99AD40F55C913E` FOREIGN KEY (`idChofer`) REFERENCES `Choferes` (`id`),
   CONSTRAINT `TransaccionesConvenio_ibfk_1` FOREIGN KEY (`idDispositivoConvenio`) REFERENCES `DispositivosConvenio` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6183341 DEFAULT CHARSET=latin1

Questions:

Is this time within de acceptable, or something wrong can be noticed? Is like unacceptable to put something in production with 1h window just to add a row.
Any suggestion or variable to look up?

Thanks

Best Answer

That's true.

In 5.1 (and 5.5), adding a column (of any type) to a table required locking the table and copying the entire table over, plus rebuilding all the indexes. 5.6 improves significantly on the task -- many ALTERs can be done with little or no interruption of other activity. (The time taken varies with the task.)

You should plan for upgrading to 5.5, 5.6, or even 5.7. The farther behind you get, the harder it is to upgrade.

innodb-buffer-pool-size = 14G is dangerously high for a 16GB machine. 11G would be safer. If MySQL "swaps", performance becomes terrible.

The table has an awful lot of indexes.

Are you familiar with "composite" indexes?

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – The total number of locks exceeds the lock table size, even after increasing buffer pool size

Related Question