MariaDB 10.6 Performace

Question

I'm migrating my database more to my surprise I had a drop in the performance search, I already checked all the indexes of the tables and they are the same

Server New MariaDB 10.6.8 CentOS 7 8 VCpu 2.8 16 RAM

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	NULLABLE	INDEX_TYPE	IGNORED
def	shop	nfe	0	shop	PRIMARY	1	fk_venda	A	389502		BTREE	NO
def	shop	nfe	0	shop	fk_venda	1	fk_venda	A	194751		BTREE	NO
def	shop	nfe	1	shop	numero	1	numero	A	194751		BTREE	NO
def	shop	vendas	0	shop	PRIMARY	1	id_venda	A	120978		BTREE	NO
def	shop	vendas	0	shop	codigo	1	codigo	A	120978	YES	BTREE	NO
def	shop	vendas	0	shop	guid	1	guid	A	120978	YES	BTREE	NO
def	shop	vendas	0	shop	erp	1	fk_loja	A	2	YES	BTREE	NO
def	shop	vendas	0	shop	erp	2	erp	A	15122	YES	BTREE	NO
def	shop	vendas	1	shop	vendas	1	id_venda	A	120978		BTREE	NO
def	shop	vendas	1	shop	vendas	2	fk_loja	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	vendas	3	fk_cliente	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	vendas	4	fk_frete	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	vendas	5	fk_status	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	fk_loja	1	fk_loja	A	2	YES	BTREE	NO
def	shop	vendas	1	shop	fk_cliente	1	fk_cliente	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	fk_frete	1	fk_frete	A	72	YES	BTREE	NO
def	shop	vendas	1	shop	fk_status	1	fk_status	A	26	YES	BTREE	NO
def	shop	vendas	1	shop	fk_cupom	1	fk_cupom	A	2	YES	BTREE	NO
def	shop	vendas	1	shop	fk_estado	1	fk_estado	A	52	YES	BTREE	NO
def	shop	vendas	1	shop	fk_pais	1	fk_pais	A	2	YES	BTREE	NO
def	shop	vendas	1	shop	fk_pagamento	1	fk_pagamento	A	38	YES	BTREE	NO
def	shop	vendas	1	shop	phpsessid	1	phpsessid	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	notificar	1	notificar	A	4	YES	BTREE	NO
def	shop	vendas	1	shop	vendas_ibfk_9	1	fk_marketplace	A	48	YES	BTREE	NO
def	shop	vendas	1	shop	data_compra	1	data_compra	A	120978	YES	BTREE	NO
def	shop	vendas	1	shop	tags	1	tags	A	8	YES	BTREE	NO
def	shop	vendas	1	shop	coupon_id	1	coupon_id	A	32	YES	BTREE	NO
def	shop	vendas	1	shop	created_at	1	created_at	A	120978		BTREE	NO

CREATE TABLE `vendas` (
  `guid` varchar(40) DEFAULT uuid(),
  `id_venda` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `codigo` varchar(25) DEFAULT NULL,
  `erp` varchar(20) DEFAULT NULL,
  `fk_loja` int(11) DEFAULT NULL,
  `fk_cliente` int(11) DEFAULT NULL,
  `fk_frete` int(11) DEFAULT NULL,
  `fk_status` int(11) DEFAULT NULL,
  `fk_cupom` int(11) DEFAULT NULL,
  `shipment_id` int(11) DEFAULT NULL,
  `phpsessid` varchar(32) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `desconto` decimal(10,2) DEFAULT 0.00,
  `abatimento` decimal(10,2) DEFAULT 0.00,
  `desconto_boleto` decimal(12,4) DEFAULT 0.0000,
  `desconto_boleto_rede` decimal(12,4) DEFAULT 0.0000,
  `desconto_cartao` decimal(12,4) DEFAULT 0.0000,
  `acrescimo` decimal(10,2) DEFAULT 0.00,
  `tarifa` decimal(10,2) DEFAULT 0.00,
  `juros` decimal(10,2) DEFAULT 0.00,
  `data_compra` datetime DEFAULT '0000-00-00 00:00:00',
  `data_pagamento` datetime DEFAULT '0000-00-00 00:00:00',
  `data_preparo` datetime DEFAULT '0000-00-00 00:00:00',
  `data_envio` datetime DEFAULT '0000-00-00 00:00:00',
  `data_estimada` datetime DEFAULT NULL,
  `data_entrega` datetime DEFAULT '0000-00-00 00:00:00',
  `data_cancelamento` datetime DEFAULT '0000-00-00 00:00:00',
  `numero_objeto` varchar(100) DEFAULT '',
  `comentario` text DEFAULT NULL,
  `tipo_residencia` char(1) DEFAULT NULL,
  `endereco` varchar(100) DEFAULT NULL,
  `numero` varchar(50) DEFAULT NULL,
  `bairro` varchar(50) DEFAULT '',
  `complemento` varchar(100) DEFAULT '',
  `cidade` varchar(50) DEFAULT NULL,
  `fk_estado` int(11) DEFAULT NULL,
  `fk_pais` int(11) DEFAULT NULL,
  `provincia` varchar(100) DEFAULT NULL,
  `referencia` text DEFAULT NULL,
  `entregar_para` varchar(100) DEFAULT NULL,
  `cep` char(9) DEFAULT NULL,
  `fk_pagamento` int(11) DEFAULT 0,
  `parcelas` char(2) DEFAULT NULL,
  `fone1` varchar(15) DEFAULT NULL,
  `fone2` varchar(15) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `cpf_cnpj` varchar(20) DEFAULT NULL,
  `prazo` int(2) DEFAULT 0,
  `ultima_alteracao` datetime DEFAULT NULL,
  `integrada` int(1) DEFAULT 0,
  `notificar` int(1) DEFAULT NULL,
  `host` varchar(100) DEFAULT NULL,
  `fk_marketplace` int(11) DEFAULT 1,
  `dynamic_cols` blob DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `whatsapp` int(1) DEFAULT 0,
  `coupon_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_venda`),
  UNIQUE KEY `codigo` (`codigo`) USING BTREE,
  UNIQUE KEY `guid` (`guid`),
  UNIQUE KEY `erp` (`fk_loja`,`erp`) USING BTREE,
  KEY `vendas` (`id_venda`,`fk_loja`,`fk_cliente`,`fk_frete`,`fk_status`),
  KEY `fk_loja` (`fk_loja`),
  KEY `fk_cliente` (`fk_cliente`),
  KEY `fk_frete` (`fk_frete`),
  KEY `fk_status` (`fk_status`),
  KEY `fk_cupom` (`fk_cupom`),
  KEY `fk_estado` (`fk_estado`),
  KEY `fk_pais` (`fk_pais`),
  KEY `fk_pagamento` (`fk_pagamento`),
  KEY `phpsessid` (`phpsessid`),
  KEY `notificar` (`notificar`),
  KEY `vendas_ibfk_9` (`fk_marketplace`) USING BTREE,
  KEY `data_compra` (`data_compra`),
  KEY `tags` (`tags`),
  KEY `coupon_id` (`coupon_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `vendas_ibfk_1` FOREIGN KEY (`fk_loja`) REFERENCES `lojas` (`id_loja`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_10` FOREIGN KEY (`coupon_id`) REFERENCES `discount_coupon` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_2` FOREIGN KEY (`fk_cliente`) REFERENCES `clientes` (`id_cliente`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_3` FOREIGN KEY (`fk_frete`) REFERENCES `fretes` (`id_frete`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_4` FOREIGN KEY (`fk_status`) REFERENCES `status` (`id_status`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_5` FOREIGN KEY (`fk_cupom`) REFERENCES `cupom` (`id_cupom`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_6` FOREIGN KEY (`fk_estado`) REFERENCES `estados` (`id_estado`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_7` FOREIGN KEY (`fk_pais`) REFERENCES `paises` (`id_pais`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_8` FOREIGN KEY (`fk_pagamento`) REFERENCES `pagamentos` (`id_pagamento`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_9` FOREIGN KEY (`fk_marketplace`) REFERENCES `marketplace` (`id_marketplace`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=693460 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE nfe (
  fk_venda int(11) NOT NULL,
  numero int(11) NOT NULL,
  serie int(2) NOT NULL,
  chave varchar(60) DEFAULT NULL,
  emissao timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  xml longtext DEFAULT NULL,
  arquivo blob DEFAULT NULL,
  updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  created_at timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (fk_venda),
  UNIQUE KEY fk_venda (fk_venda) USING BTREE,
  KEY numero (numero)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Server Old MariaDB 10.5.16 CentOS 7 8 VCpu 2.2 16 RAM

CREATE TABLE `vendas` (
  `guid` varchar(40) DEFAULT uuid(),
  `id_venda` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `codigo` varchar(25) DEFAULT NULL,
  `erp` varchar(20) DEFAULT NULL,
  `fk_loja` int(11) DEFAULT NULL,
  `fk_cliente` int(11) DEFAULT NULL,
  `fk_frete` int(11) DEFAULT NULL,
  `fk_status` int(11) DEFAULT NULL,
  `fk_cupom` int(11) DEFAULT NULL,
  `shipment_id` int(11) DEFAULT NULL,
  `phpsessid` varchar(32) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `desconto` decimal(10,2) DEFAULT 0.00,
  `abatimento` decimal(10,2) DEFAULT 0.00,
  `desconto_boleto` decimal(12,4) DEFAULT 0.0000,
  `desconto_boleto_rede` decimal(12,4) DEFAULT 0.0000,
  `desconto_cartao` decimal(12,4) DEFAULT 0.0000,
  `acrescimo` decimal(10,2) DEFAULT 0.00,
  `tarifa` decimal(10,2) DEFAULT 0.00,
  `juros` decimal(10,2) DEFAULT 0.00,
  `data_compra` datetime DEFAULT '0000-00-00 00:00:00',
  `data_pagamento` datetime DEFAULT '0000-00-00 00:00:00',
  `data_preparo` datetime DEFAULT '0000-00-00 00:00:00',
  `data_envio` datetime DEFAULT '0000-00-00 00:00:00',
  `data_estimada` datetime DEFAULT NULL,
  `data_entrega` datetime DEFAULT '0000-00-00 00:00:00',
  `data_cancelamento` datetime DEFAULT '0000-00-00 00:00:00',
  `numero_objeto` varchar(100) DEFAULT '',
  `comentario` text DEFAULT NULL,
  `tipo_residencia` char(1) DEFAULT NULL,
  `endereco` varchar(100) DEFAULT NULL,
  `numero` varchar(50) DEFAULT NULL,
  `bairro` varchar(50) DEFAULT '',
  `complemento` varchar(100) DEFAULT '',
  `cidade` varchar(50) DEFAULT NULL,
  `fk_estado` int(11) DEFAULT NULL,
  `fk_pais` int(11) DEFAULT NULL,
  `provincia` varchar(100) DEFAULT NULL,
  `referencia` text DEFAULT NULL,
  `entregar_para` varchar(100) DEFAULT NULL,
  `cep` char(9) DEFAULT NULL,
  `fk_pagamento` int(11) DEFAULT 0,
  `parcelas` char(2) DEFAULT NULL,
  `fone1` varchar(15) DEFAULT NULL,
  `fone2` varchar(15) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `cpf_cnpj` varchar(20) DEFAULT NULL,
  `prazo` int(2) DEFAULT 0,
  `ultima_alteracao` datetime DEFAULT NULL,
  `integrada` int(1) DEFAULT 0,
  `notificar` int(1) DEFAULT NULL,
  `host` varchar(100) DEFAULT NULL,
  `fk_marketplace` int(11) DEFAULT 1,
  `dynamic_cols` blob DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `whatsapp` int(1) DEFAULT 0,
  `coupon_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_venda`),
  UNIQUE KEY `codigo` (`codigo`) USING BTREE,
  UNIQUE KEY `guid` (`guid`),
  UNIQUE KEY `erp` (`fk_loja`,`erp`) USING BTREE,
  KEY `vendas` (`id_venda`,`fk_loja`,`fk_cliente`,`fk_frete`,`fk_status`),
  KEY `fk_cliente` (`fk_cliente`),
  KEY `fk_frete` (`fk_frete`),
  KEY `fk_status` (`fk_status`),
  KEY `fk_cupom` (`fk_cupom`),
  KEY `fk_estado` (`fk_estado`),
  KEY `fk_pais` (`fk_pais`),
  KEY `fk_pagamento` (`fk_pagamento`),
  KEY `phpsessid` (`phpsessid`),
  KEY `notificar` (`notificar`),
  KEY `vendas_ibfk_9` (`fk_marketplace`) USING BTREE,
  KEY `tags` (`tags`),
  KEY `coupon_id` (`coupon_id`),
  KEY `created_at` (`created_at`),
  KEY `data_compra` (`data_compra`),
  CONSTRAINT `vendas_ibfk_10` FOREIGN KEY (`coupon_id`) REFERENCES `discount_coupon` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_11` FOREIGN KEY (`fk_loja`) REFERENCES `lojas` (`id_loja`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_2` FOREIGN KEY (`fk_cliente`) REFERENCES `clientes` (`id_cliente`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_3` FOREIGN KEY (`fk_frete`) REFERENCES `fretes` (`id_frete`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_4` FOREIGN KEY (`fk_status`) REFERENCES `status` (`id_status`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_5` FOREIGN KEY (`fk_cupom`) REFERENCES `cupom` (`id_cupom`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_6` FOREIGN KEY (`fk_estado`) REFERENCES `estados` (`id_estado`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_7` FOREIGN KEY (`fk_pais`) REFERENCES `paises` (`id_pais`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_8` FOREIGN KEY (`fk_pagamento`) REFERENCES `pagamentos` (`id_pagamento`) ON UPDATE CASCADE,
  CONSTRAINT `vendas_ibfk_9` FOREIGN KEY (`fk_marketplace`) REFERENCES `marketplace` (`id_marketplace`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=693701 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE nfe (
  fk_venda int(11) NOT NULL,
  numero int(11) NOT NULL,
  serie int(2) NOT NULL,
  chave varchar(60) DEFAULT NULL,
  emissao timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  xml longtext DEFAULT NULL,
  arquivo blob DEFAULT NULL,
  updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  created_at timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (fk_venda),
  UNIQUE KEY fk_venda (fk_venda) USING BTREE,
  KEY numero (numero)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	NULLABLE	INDEX_TYPE
def	shop	vendas	0	shop	PRIMARY	1	id_venda	A	120554		BTREE
def	shop	vendas	0	shop	codigo	1	codigo	A	120554	YES	BTREE
def	shop	vendas	0	shop	guid	1	guid	A	120554	YES	BTREE
def	shop	vendas	0	shop	erp	1	fk_loja	A	2	YES	BTREE
def	shop	vendas	0	shop	erp	2	erp	A	17222	YES	BTREE
def	shop	vendas	1	shop	vendas	1	id_venda	A	120554		BTREE
def	shop	vendas	1	shop	vendas	2	fk_loja	A	120554	YES	BTREE
def	shop	vendas	1	shop	vendas	3	fk_cliente	A	120554	YES	BTREE
def	shop	vendas	1	shop	vendas	4	fk_frete	A	120554	YES	BTREE
def	shop	vendas	1	shop	vendas	5	fk_status	A	120554	YES	BTREE
def	shop	vendas	1	shop	fk_cliente	1	fk_cliente	A	120554	YES	BTREE
def	shop	vendas	1	shop	fk_frete	1	fk_frete	A	74	YES	BTREE
def	shop	vendas	1	shop	fk_status	1	fk_status	A	24	YES	BTREE
def	shop	vendas	1	shop	fk_cupom	1	fk_cupom	A	2	YES	BTREE
def	shop	vendas	1	shop	fk_estado	1	fk_estado	A	52	YES	BTREE
def	shop	vendas	1	shop	fk_pais	1	fk_pais	A	2	YES	BTREE
def	shop	vendas	1	shop	fk_pagamento	1	fk_pagamento	A	36	YES	BTREE
def	shop	vendas	1	shop	phpsessid	1	phpsessid	A	120554	YES	BTREE
def	shop	vendas	1	shop	notificar	1	notificar	A	4	YES	BTREE
def	shop	vendas	1	shop	vendas_ibfk_9	1	fk_marketplace	A	50	YES	BTREE
def	shop	vendas	1	shop	tags	1	tags	A	8	YES	BTREE
def	shop	vendas	1	shop	coupon_id	1	coupon_id	A	30	YES	BTREE
def	shop	vendas	1	shop	created_at	1	created_at	A	120554		BTREE
def	shop	vendas	1	shop	data_compra	1	data_compra	A	120554	YES	BTREE
def	shop	nfe	0	shop	PRIMARY	1	fk_venda	A	196585		BTREE
def	shop	nfe	0	shop	fk_venda	1	fk_venda	A	196585		BTREE
def	shop	nfe	1	shop	numero	1	numero	A	196585		BTREE

score 2 · Answer 1 · answered Jun 23 '22 at 02:04

2

Three possibilities that I can think of:

vendas: Replace INDEX(fk_loga) with INDEX(fk_loja, codigo). I would expect a better EXPLAIN.
I assume that status has PRIMARY KEY(status). The JOIN to status seems to be useless. Will you get the same answer if you remove it? That will also speed up the query.
SHOW VARIABLES LIKE "query_cache%"; on each server. There may have been a change there.

answered Jun 23 '22 at 02:04

Rick James

78,038
5
47
113

the first suggestion does not take effect, the second suggestion to remove the join, I would have the same answer, I need them, in this case I left the sql simpler just to exemplify that the join in the tables is screwing with the query, other joins what I do with this table is slow on this new server
equal values on both servers
query_cache_limit=1048576
query_cache_min_res_unit=4096
query_cache_size=1048576
query_cache_strip_comments=OFF
query_cache_type=OFF
query_cache_wlock_invalidate=OFF
– Elvis Reis Jun 23 '22 at 02:59
@ElvisReis - Please show us the full query, there may be subtle things lost in the simplification. – Rick James Jun 23 '22 at 03:04

score 1 · Answer 2 · answered Jun 23 '22 at 03:15

1

after adding an index to the status.tag

it makes no sense for me to exceed my index bank in this case the status table is small with less than 30 records

answered Jun 23 '22 at 03:15

Elvis Reis

41
4

MariaDB 10.6 Performace

2 Answers2