psql, a CLI do PostgreSQL, usa readline para sua interface de usuário. Readline permite o modo de edição emacs e vi e psqlusa o modo emacs por padrão. Eu, por outro lado, uso o vi para tudo; existe um comando para definir o modo de edição do vi no psql?
Eu tenho um banco de dados RDS de mestre único provisionado (5.6.mysql_aurora.1.23.0) com um gravador e um leitor. Os clientes são um monte de aplicativos jetty com um pool de até 250 conexões cada.
Há algum tempo venho lutando com problemas intermitentes: as tentativas de conexão começam a expirar, principalmente. Parece correlacionar-se com cargas altas, e comecei a copiar o todo information_schema.processlistpara uma tabela de log em intervalos regulares (com um carimbo de data/hora, obviamente), e parece que o problema se torna mais pronunciado quando o número de conexões atinge 2000. Nunca chega perto max_connectionsou max_user_connections:
mysql> show variables like "%max%conn%";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| aurora_max_connections_limit | 16000 |
| max_connect_errors | 100 |
| max_connections | 3000 |
| max_user_connections | 0 |
+------------------------------+-------+
4 rows in set (0.03 sec)
Então, em quais parâmetros devo me concentrar para superar esse problema? Não encontrei nada provável, isso explica por que 2000 conexões parecem causar isso; é claro, pode ser simplesmente uma coincidência que um número tão redondo apareça.
Mysql 5.6 no Debian 9: Eu tenho uma tabela muito grande em um servidor, serverA, e defini uma conexão do meu banco de dados no serverB para o banco de dados no serverA, usando create server connA ..., e defini uma tabela no serverB que se conecta via connA a a mesa muito grande.
Quando consulto a tabela federada no serverB, funciona bem, se a consulta for processada rapidamente no serverA:
# on serverB:
mysql> select * from game_action where game_action_id=4;
+----------------+---------+---------+------------------+-------+--------+----------+---------------------+
| game_action_id | game_id | user_id | game_instance_id | type | amount | currency | created_timestamp |
+----------------+---------+---------+------------------+-------+--------+----------+---------------------+
| 4 | 1096 | 1 | 4 | WAGER | 1.00 | GBP | 2017-09-06 14:37:15 |
+----------------+---------+---------+------------------+-------+--------+----------+---------------------+
1 row in set (0.40 sec)
Mas (ainda no serverB):
mysql> select count(*) from game_action;
ERROR 2013 (HY000): Lost connection to MySQL server during query
A tabela é muito grande, então não é surpresa que demore muito para processar, mas eu preciso ser capaz de fazer coisas que demoram muito. Tivemos problemas com conexões caindo devido a tempos limite no passado, e eles têm sido muito difíceis de solucionar; alterar diferentes tempos limite em my.cnf não ajudou.
Qual é a melhor prática para solucionar esse problema específico?
Editar
A coisa muito estranha, eu sinto, é que não parece haver nenhuma variável de tempo limite que corresponda ao que eu vejo - eu configurei uma instância do mysql apenas para testar isso:
$ # On serverB:
$ time mysql -u root -pAtauseq01 gameiom -e "select count(*) from game_action;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
real 9m23.275s
user 0m0.014s
sys 0m0.004s
Então, cerca de 9 minutos de tempo limite, se for um tempo limite. Mas no servidor remoto (serverA):
mysql> show variables like "%timeout%";
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_print_lock_wait_timeout_info | OFF |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 28800 |
+-------------------------------------+----------+
15 rows in set (0.00 sec)
Editar 2
Servidor A:
mysql> show variables like "flush%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| flush | OFF |
| flush_time | 0 |
+---------------+-------+
2 rows in set (0.00 sec)
Servidor B:
mysql> show variables like "flush%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| flush | OFF |
| flush_time | 0 |
+---------------+-------+
2 rows in set (0.01 sec)
Editar 3
No servidorA:
mysql> pager grep -v PARTITION
PAGER set to 'grep -v PARTITION'
mysql> show create table game_action\G
*************************** 1. row ***************************
Table: game_action
Create Table: CREATE TABLE `game_action` (
`game_action_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`game_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`game_instance_id` bigint(20) unsigned DEFAULT NULL,
`type` varchar(15) NOT NULL,
`amount` decimal(18,2) NOT NULL,
`currency` varchar(15) NOT NULL,
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`game_action_id`,`created_timestamp`),
KEY `GA_IX01` (`game_id`),
KEY `GA_IX02` (`user_id`),
KEY `GA_IX03` (`game_instance_id`),
KEY `game_action_created_timestamp` (`created_timestamp`),
KEY `ga_id_cur_tstmp` (`game_id`,`currency`,`created_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=1064199804 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
No servidor B:
mysql> show create table game_action\G
*************************** 1. row ***************************
Table: game_action
Create Table: CREATE TABLE `game_action` (
`game_action_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`game_id` int NOT NULL,
`user_id` int NOT NULL,
`game_instance_id` bigint unsigned DEFAULT NULL,
`type` varchar(15) NOT NULL,
`amount` decimal(18,2) NOT NULL,
`currency` varchar(15) NOT NULL,
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`game_action_id`,`created_timestamp`),
KEY `GA_IX01` (`game_id`),
KEY `GA_IX02` (`user_id`),
KEY `GA_IX03` (`game_instance_id`),
KEY `game_action_created_timestamp` (`created_timestamp`),
KEY `ga_id_cur_tstmp` (`game_id`,`currency`,`created_timestamp`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='gameiom'
1 row in set (0.00 sec)
MySQL 5.6: Estou experimentando copiar dados de uma tabela para outra de maneiras diferentes. A tabela de origem está em um servidor remoto e possui cerca de 500.000 linhas - eu uso o mecanismo federado para conectar. Eu tentei isso primeiro:
mysql > create table tgt as select * from src;
Isso é muito rápido, leva apenas alguns segundos, mas dá avisos:
...
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265975 |
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265976 |
...
64 rows in set (0.00 sec)
Em vez disso, tentei fazer isso com um procedimento armazenado, abrindo um cursor, buscando linhas e inserindo-as, mas demora uma eternidade; Cancelei depois de 10 min.
Então, existe uma maneira de localizar as linhas que causam o problema? Eu tentei select ... limit #first_row,#last_row;, mas não parece funcionar, e não tenho certeza se é totalmente confiável de alguma forma.
Eu tenho um servidor MySQL (versão 5.6) que está bem equipado com hardware (Linux, na AWS, 32 GB de RAM, 56 CPUs), e de acordo com top e iotop, nem está quente; ainda esta consulta leva algo como 2 horas (a consulta real, não a explicação):
mysql> explain
-> SELECT
-> DATE_FORMAT(DATE('2020-04-14'), '%m/%d/%Y') AS "Gaming Day",
-> g.name AS "Game name",
-> u.username AS "User Id",
-> ga.game_instance_id AS "Game Round Id",
-> gt.user_transaction_id AS "Transaction Id",
-> ga.type AS "Transaction Type",
-> ga.amount AS "Transaction Amount",
-> CONVERT_TZ(ga.created_timestamp, 'UTC', 'SYSTEM') AS "Transaction Date Time (EST)"
-> FROM spin.game_action ga
-> INNER JOIN spin.game_instance gi
-> ON gi.game_instance_id = ga.game_instance_id
-> INNER JOIN spin.game_transaction gt
-> ON gt.game_action_id = ga.game_action_id
-> INNER JOIN spin.user u
-> ON ga.user_id = u.user_id
-> INNER JOIN spin.organisation_site os
-> ON u.organisation_site_id = os.organisation_site_id
-> INNER JOIN spin.game g
-> ON g.game_id = ga.game_id
-> WHERE os.hostname = 'nyx'
-> AND gi.end_datetime BETWEEN CONVERT_TZ('2020-04-14 00:00:00', 'SYSTEM', 'UTC') AND CONVERT_TZ('2020-04-21 23:59:59', 'SYSTEM', 'UTC')
-> AND gi.status IN ('RESOLVED', 'AUTO_COMPLETED');
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | g | index | PRIMARY | BG_UK1 | 202 | NULL | 60 | Using index |
| 1 | SIMPLE | ga | ref | PRIMARY,GA_IX01,GA_IX02,GA_IX03 | GA_IX01 | 4 | spin.g.game_id | 674 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY,U_UK01,U_IX_04 | PRIMARY | 4 | spin.ga.user_id | 1 | NULL |
| 1 | SIMPLE | os | eq_ref | PRIMARY | PRIMARY | 4 | spin.u.organisation_site_id | 1 | Using where |
| 1 | SIMPLE | gi | ref | PRIMARY | PRIMARY | 8 | spin.ga.game_instance_id | 1 | Using where |
| 1 | SIMPLE | gt | ref | GT_IX03 | GT_IX03 | 9 | spin.ga.game_action_id | 1 | Using index |
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------+------+-------------+
6 rows in set (0.01 sec)
Eu tentei executá-lo em perfil, mas isso é basicamente inútil:
mysql> show profile for query 2;
+----------------------+------------+
| Status | Duration |
+----------------------+------------+
| starting | 0.000160 |
| checking permissions | 0.000005 |
| checking permissions | 0.000002 |
| checking permissions | 0.000003 |
| checking permissions | 0.000003 |
| checking permissions | 0.000003 |
| checking permissions | 0.000005 |
| Opening tables | 0.000067 |
| init | 0.000133 |
| System lock | 0.000201 |
| optimizing | 0.000049 |
| statistics | 0.000416 |
| preparing | 0.000050 |
| executing | 0.000005 |
| Sending data | 999.999999 |
| end | 0.000010 |
| query end | 0.000008 |
| closing tables | 0.010543 |
| freeing items | 0.000062 |
| logging slow query | 0.000002 |
| logging slow query | 0.110968 |
| cleaning up | 0.003693 |
+----------------------+------------+
22 rows in set, 1 warning (0.01 sec)
Pelo que entendi, Sending dataabrange tanto a transmissão real dos resultados ao cliente quanto uma parte importante do processamento. Agora estou considerando como analisar isso ainda mais com o performance_schema; é provável que revele detalhes mais úteis?
Editar
Saída de SHOW GLOBAL STATUS\G: https://pastebin.com/QejRk9RA
Saída de SHOW GLOBAL VARIABLES\G: https://pastebin.com/b3B76v21
Saída de SHOW FULL PROCESSLIST\G: https://pastebin.com/N85YUwFj
Relatório do MySQLtuner: https://pastebin.com/HJJsCCzL
Editar 2
$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) 0
memory(kbytes) unlimited
locked memory(kbytes) 64
process 128223
nofiles 1024
vmemory(kbytes) unlimited
locks unlimited
rtprio 0
db3 root = iostat -xm 5 3
Linux 4.9.0-11-amd64 (db3.spin-production.gamingrealms.org) 05/27/2020 _x86_64_ (56 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.72 0.00 0.07 0.04 0.00 99.17
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.11 11.30 20.83 17.03 3.66 0.66 233.37 0.05 1.24 1.41 1.04 0.51 1.94
dm-0 0.00 0.00 0.04 0.04 0.00 0.00 8.13 0.01 99.38 3.62 184.79 1.05 0.01
dm-1 0.00 0.00 20.88 27.29 3.66 0.66 183.42 0.05 1.05 1.42 0.76 0.40 1.94
avg-cpu: %user %nice %system %iowait %steal %idle
3.60 0.00 0.16 0.08 0.00 96.16
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 18.60 121.40 13.80 30.00 0.27 458.59 0.13 0.93 0.92 1.10 0.49 6.64
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 121.40 32.40 30.00 0.27 403.13 0.13 0.82 0.91 0.47 0.43 6.64
avg-cpu: %user %nice %system %iowait %steal %idle
1.59 0.00 0.16 0.03 0.00 98.23
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 15.00 49.00 17.60 12.02 0.21 375.95 0.04 0.64 0.87 0.00 0.37 2.48
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 49.00 32.60 12.02 0.21 306.84 0.04 0.52 0.87 0.00 0.30 2.48
db3 root = top -c
top - 04:36:25 up 167 days, 21:28, 1 user, load average: 1.97, 2.28, 2.18
Tasks: 584 total, 2 running, 582 sleeping, 0 stopped, 0 zombie
%Cpu(s): 3.4 us, 0.2 sy, 0.0 ni, 96.4 id, 0.1 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 32848684 total, 5421432 free, 19970372 used, 7456880 buff/cache
KiB Swap: 66916348 total, 66844172 free, 72176 used. 12286984 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
43108 root 20 0 4440 1312 1044 R 89.2 0.0 243:45.28 /bin/gzip -
18933 root 20 0 4440 1332 1048 S 61.0 0.0 107:19.61 /bin/gzip -
43107 root 20 0 249232 40072 11788 S 9.8 0.1 24:21.13 /usr/bin/innobackupex --stream=tar --user=backupuser --password=x xxxxxx --safe-slave-backup --slave-info --databa+
18932 root 20 0 249232 40024 11740 S 7.2 0.1 10:17.08 /usr/bin/innobackupex --stream=tar --user=backupuser --password=x xxxxxx --safe-slave-backup --slave-info --databa+
18934 root 20 0 1058508 147260 9648 S 4.3 0.4 6:46.18 /usr/bin/python3 /usr/bin/aws s3 cp - s3://spin.db.backup/2020-05-27-spin.tar.gz --region us-east-1
43109 root 20 0 1069768 174488 9404 S 3.6 0.5 14:16.26 /usr/bin/python3 /usr/bin/aws s3 cp - s3://spin.db.backup/2020-05-26-spin.tar.gz --region us-east-1
54915 root 20 0 45472 4396 3240 R 1.6 0.0 0:00.29 top -c
39686 mysql 20 0 21.178g 0.018t 8980 S 1.3 58.2 2979:59 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-err+
41601 root 20 0 33196 11696 5100 S 0.7 0.0 70:42.86 /usr/share/filebeat/bin/filebeat -c /etc/filebeat/filebeat.yml -path.home /usr/share/filebeat -path.config /etc/fi+
312 root 20 0 0 0 0 S 0.3 0.0 0:36.25 [ksoftirqd/50]
1661 sensu 20 0 1594004 28004 5676 S 0.3 0.1 127:00.25 /opt/sensu/embedded/bin/ruby /opt/sensu/bin/sensu-client -c /etc/sensu/config.json -d /etc/sensu/conf.d -e /etc/se+
11120 root 20 0 109512 684 660 S 0.3 0.0 132:39.67 /var/ossec/bin/wazuh-modulesd
24552 sensu 20 0 135144 24696 0 S 0.3 0.1 217:07.55 /usr/sbin/sensu-agent start
1 root 20 0 57396 5144 3668 S 0.0 0.0 11:17.41 /sbin/init
...
Eu executei essa consulta quando o servidor parece estar sem carga (de acordo com top e show processlist) e quando alguns trabalhos SQL pesados o empurram para cerca de 1000% da CPU, e parece não fazer muita diferença, acredite ou não. As ~ 2 horas é o que é preciso com nada acontecendo.
Editar3
MOSTRAR STATUS GLOBAL: https://pastebin.com/5PeBEkz7
MOSTRAR VARIÁVEIS GLOBAIS: https://pastebin.com/SnGS28rD
MOSTRAR LISTA DE PROCESSOS COMPLETA: https://pastebin.com/AR2WZbnM
Editar4
db3 root = hdparm -I /dev/sda
/dev/sda:
SG_IO: bad/missing sense data, sb[]: 70 00 05 00 00 00 00 0d 00 00 00 00 20 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
ATA device, with non-removable media
Standards:
Likely used: 1
Configuration:
Logical max current
cylinders 0 0
heads 0 0
sectors/track 0 0
--
Logical/Physical Sector size: 512 bytes
device size with M = 1024*1024: 0 MBytes
device size with M = 1000*1000: 0 MBytes
cache/buffer size = unknown
Capabilities:
IORDY not likely
Cannot perform double-word IO
R/W multiple sector transfer: not supported
DMA: not supported
PIO: pio0
Editar
show create table e mostre o status da tabela: https://pastebin.com/s9Y61GTb
Eu tenho uma configuração que tenho certeza que não é inédita (este é o MySQL 5.5, mas atualizando):
Eu tenho um banco de dados OLTP em rápido crescimento, que estou replicando para um banco de dados OLAP. Quero começar a arquivar as tabelas que mais crescem no banco de dados OLTP, mas quero que o banco de dados OLAP acumule tudo sem a parte de arquivamento. Existe uma maneira fácil de conseguir isso, ou então inventei meus próprios procedimentos?
Eu atualizei recentemente o mysqld (servidor percona) de 5.5.60-38.12 para 5.5.62-38.14. Isso está no Debian, e a atualização foi feita assim:
# apt-get update
# apt-get upgrade
Estou surpreso ao descobrir que a versão é relatada de forma diferente internamente do que recebo na linha de comando:
db3 root = mysql -V
mysql Ver 14.14 Distrib 5.5.62-38.14, for debian-linux-gnu (x86_64) using readline 5.1
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.5.60-38.12-log |
+------------------+
1 row in set (0.00 sec)
Isso é normal? Ou preciso fazer algo para resolver esse problema?
Edit: Eu também verifiquei os pacotes instalados:
db3 root = dpkg -l | grep percona
ii libperconaserverclient18 5.5.62-rel38.14-1.stretch amd64 Percona Server database client library
ii percona-server-client-5.5 5.5.62-rel38.14-1.stretch amd64 Percona Server database client binaries
ii percona-server-common-5.5 5.5.62-rel38.14-1.stretch amd64 Percona Server database common files
ii percona-server-server-5.5 5.5.62-rel38.14-1.stretch amd64 Percona Server database server binaries
ii percona-toolkit 3.1.0-2.stretch amd64 Advanced MySQL and system command-line tools
ii percona-xtrabackup 2.3.10-1.stretch amd64 Open source backup tool for InnoDB and XtraDB