lyeaf's questions

Pretendo arquivar mensagens e não tenho certeza de qual devo usar.

Deve ser capaz de conter no máximo 20 bilhões de linhas (é para isso que espero que o número total tenda)

Cada linha terá três colunas: user_id, mensagem, data

O user_id é uma sequência de 30 caracteres. A mensagem tem entre 1 e 20.000 caracteres. Em média, espero que tenha 140 caracteres. (UTF-8, deve permitir emojis, alfabetos diferentes, etc)

Quero apenas um índice para o user_id, não para a mensagem/data.

Eu só pretendo fazer consultas INSERT, e muito simples SELECT * WHERE user_id = XXXXX Haverá muito poucos SELECTs, espero no pico de 10/minuto. Os SELECTs não precisam ser rápidos, qualquer coisa entre 1 e 20 segundos está bom.

Mas haverá muitos INSERTs. Provavelmente cerca de 5.000-10.000 por segundo.

Meu servidor terá: CPU: AMD Ryzen™ 9 7950X3D RAM: 128 GB DDR5 ECC Drives: 1x 7,68 TB NVMe SSD Datacenter (da Hetzner)

Fiz esta consulta: ALTER TABLE dbname.tablename DROP COLUMN columnnamee está rodando há algumas horas e queria saber quanto tempo ainda estaria rodando.

São aproximadamente 750m de linhas.

Obrigado !

Se você precisar de mais detalhes, eu os darei, não queria dar detalhes que não fossem úteis

O servidor MariaDB falhará a cada poucas horas (já faz uma semana, sim, dezenas de falhas) e reiniciará automaticamente. Em 2 travamentos anteriores, o servidor não inicializou novamente e tive que iniciá-lo com mysqld --innodb_force_recovery=3, fazer um dump dos bancos de dados/esquemas, apagar todos os dados do mysql/mariadb, reinstalá-lo e "importar " o .sql no servidor MariaDB recém-instalado mysql < dump.sql(então funcionou, por "funcionou" significa que o banco de dados poderia pelo menos ser executado, mas ainda travaria a cada poucas horas)

Eu sou um novato. Só para você saber.

Eu uso MariaDB, aqui está a versão:mysql Ver 15.1 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Todas as tabelas que estou usando estão no InnoDB. Todas as minhas consultas são simples , nada complicado está sendo feito. INSERT INTO table(a,b,c) VALUES(1,2,3)<= não fica muito mais complicado do que esse tipo de consulta, na verdade. (É principalmente um banco de dados destinado a arquivamento, portanto, muitos INSERTs, alguns UPDATEs, poucos SELECTs). 90% das requisições são via localhost. (Um script python com aiomysqlmódulo buscará dados pela Internet e os enviará para o servidor MariaDB local)

O VPS que uso é ligado: 4.19.0-21-amd64 #1 SMP Debian 4.19.249-2 (2022-06-30) x86_64 GNU/Linux, usa SSD, tem 24GB de memória, 14 vCores. O uso da CPU mal chega a 20%, o uso da memória também está em 20% (com base no que vejo no topcomando)

Com base no status do servidor que vejo no MySQL Workbench, há cerca de 2700 InnoDB Writes per Second/420 Selects per second/120 Reads per seconds/2185 Connections, o tamanho total do banco de dados deve ser de cerca de 140 GB~

Esses são números normais para mim. Eu tenho mais de 200 instâncias de um script python em execução, cada uma cria um pool de conexões com aiomysqlum mínimo de 10 conexões/máximo de 100, ou seja, um mínimo de 2.000 conexões.

Abaixo do conteúdo do meu .cnf file, eu disse que era novato, então preenchi aos poucos com o que achei que resolveria o problema. E abaixo disso, o conteúdo de : /var/log/mysql/error.logE aqui um link de uma parte do conteúdo de /var/log/syslog: https://ctxt.io/2/AAAQl4uRFg (A parte interessante para mim são essas 2 linhasOut of memory: Kill process 10042 (mysqld) score 333 or sacrifice child. Killed process 10042 (mysqld)

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
#port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
innodb_buffer_pool_size = 16G
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M

    #
    # * Fine Tuning
    #
    #key_buffer_size        = 16M
    max_allowed_packet     = 2G
    #thread_stack           = 192K
    thread_cache_size      = 38
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    #myisam_recover_options = BACKUP
    max_connections        = 10000
    table_cache            = 3000
    #thread_concurrency     = 10
    innodb_read_io_threads  = 8
    innodb_write_io_threads = 8
    innodb_file_per_table   = 1
    innodb_flush_log_at_trx_commit = 2
    enforce_storage_engine  = InnoDB
    #
    # * Query Cache Configuration
    #
    #query_cache_limit      = 1M
    query_cache_type                = 0
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file       = /var/log/mysql/mysql.log
    #general_log            = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Enable the slow query log to see queries with especially long duration
    #slow_query_log_file    = /var/log/mysql/mariadb-slow.log
    #long_query_time        = 10
    #log_slow_rate_limit    = 1000
    #log_slow_verbosity     = query_plan
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    #max_binlog_size        = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = exclude_database_name
    
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    #chroot = /var/lib/mysql/
    #
    # For generating SSL certificates you can use for example the GUI tool "tinyca".
    #
    #ssl-ca = /etc/mysql/cacert.pem
    #ssl-cert = /etc/mysql/server-cert.pem
    #ssl-key = /etc/mysql/server-key.pem
    #
    # Accept only connections using the latest and most secure TLS protocol version.
    # ..when MariaDB is compiled with OpenSSL:
    #ssl-cipher = TLSv1.2
    # ..when MariaDB is compiled with YaSSL (default in Debian):
    #ssl = on
    
    #
    # * Character sets
    #
    # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
    # utf8 4-byte character set. See also client.cnf
    #
    character-set-server  = utf8mb4
    collation-server      = utf8mb4_general_ci
    
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    
    #
    # * Unix socket authentication plugin is built-in since 10.0.22-6
    #
    # Needed so the root database user can authenticate without a password but
    # only when running as the unix root user.
    #
    # Also available for other users if required.
    # See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
    
    # this is only for embedded server
    [embedded]
    
    # This group is only read by MariaDB servers, not by MySQL.
    # If you use the same .cnf file for MySQL and MariaDB,
    # you can put MariaDB-only options here
    [mariadb]
    # This group is only read by MariaDB-10.3 servers.
    # If you use the same .cnf file for MariaDB of different versions,
    # use this group for options that older servers don't understand
    [mariadb-10.3]

2022-12-26 23:20:30 0 [Note] InnoDB: Buffer pool(s) load completed at 221226 23:20:30
2022-12-26 23:48:18 0 [Note] InnoDB: Using Linux native AIO
2022-12-26 23:48:18 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-26 23:48:18 0 [Note] InnoDB: Uses event mutexes
2022-12-26 23:48:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-12-26 23:48:18 0 [Note] InnoDB: Number of pools: 1
2022-12-26 23:48:18 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-12-26 23:48:18 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 8, chunk size = 128M
2022-12-26 23:48:19 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-26 23:48:19 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-26 23:48:20 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=244433492785
2022-12-26 23:48:27 0 [Note] InnoDB: 54 transaction(s) which must be rolled back or cleaned up in total 54 row operations to undo
2022-12-26 23:48:27 0 [Note] InnoDB: Trx id counter is 145319537
2022-12-26 23:48:27 0 [Note] InnoDB: Starting final batch to recover 267086 pages from redo log.
2022-12-26 23:48:34 0 [Note] InnoDB: To recover: 231381 pages from log
2022-12-26 23:48:49 0 [Note] InnoDB: To recover: 142626 pages from log
2022-12-26 23:49:04 0 [Note] InnoDB: To recover: 64756 pages from log
2022-12-26 23:49:19 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-12-26 23:49:19 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
2022-12-26 23:49:19 0 [Note] InnoDB: To roll back: 54 transactions, 54 rows
2022-12-26 23:49:19 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-12-26 23:49:19 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-12-26 23:49:19 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-26 23:49:19 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319470
2022-12-26 23:49:19 0 [Note] InnoDB: Waiting for purge to start
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319063
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319071
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319337
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319497
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319425
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319510
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319389
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319079
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319106
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319189
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319518
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319269
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319477
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319083
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319525
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319070
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319311
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319227
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319363
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319387
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319522
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319469
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319107
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319094
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319075
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319456
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319306
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319080
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319266
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319280
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319234
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319276
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319172
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319392
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319404
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145318827
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319408
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319402
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319340
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319480
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319072
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319064
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319364
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319520
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319330
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319304
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319500
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319508
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319069
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319073
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319495
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319109
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319511
2022-12-26 23:49:19 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2022-12-26 23:49:19 0 [Note] InnoDB: 10.3.36 started; log sequence number 244434151651; transaction id 145319592
2022-12-26 23:49:19 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-12-26 23:49:19 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-12-26 23:49:19 0 [Note] Recovering after a crash using tc.log
2022-12-26 23:49:19 0 [Note] Starting crash recovery...
2022-12-26 23:49:19 0 [Note] Crash recovery finished.
2022-12-26 23:49:19 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-12-26 23:49:19 0 [Note] Reading of all Master_info entries succeeded
2022-12-26 23:49:19 0 [Note] Added new Master_info '' to hash table
2022-12-26 23:49:19 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.36-MariaDB-0+deb10u2'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 10
2022-12-26 23:49:27 7418 [Note] InnoDB: Number of pools: 2
2022-12-26 23:50:28 47992 [Note] InnoDB: Number of pools: 3

Obrigado pela ajuda, estou desesperado neste momento. Se você tiver mais informações, tentarei fornecê-las. Por favor, seja detalhado sobre como obtê-lo

(Dos comentários)

CREATE TABLE : mysql CREATE TABLE `messages` (
    `message_id` varchar(46) CHARACTER SET utf8mb4
           COLLATE utf8mb4_bin NOT NULL, 
    `message` text CHARACTER SET utf8mb4
           COLLATE utf8mb4_bin DEFAULT NULL, 
    `user_id` int(11) NOT NULL, 
    `date_sent` timestamp NOT NULL
          DEFAULT current_timestamp()
          ON UPDATE current_timestamp(),
    `channel_sent` int(11) NOT NULL, 
    `stream_sent` varchar(13) NOT NULL, 
    PRIMARY KEY (`message_id`), 
    KEY `idx_user_id` (`user_id`), 
    KEY `idx_channel_sent` (`channel_sent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Eu tenho uma tabela com duas colunas: id(int), data(text)

data contém um array de strings. Quero inserir strings nele, se ainda não estiverem presentes.

Pesquisei um pouco, cheguei a isso:

INSERT INTO schema_name.table_name(id, data) VALUES(1, '["3"]') 
ON DUPLICATE KEY UPDATE data= IF(JSON_CONTAINS(data,'3', '$'), JSON_ARRAY_APPEND(data, '$', '3'), data)

Ele não insere "3" nos dados, estando ou não presente. Não retorna erro. Estou me perguntando como devo fazer isso e por que isso não funciona

Obrigado

Contexto: Eu envio consultas para meu servidor MariaDB (Ver 15.1 Distrib 10.3.34-MariaDB) através de um script Python e módulo mysql.connector. (Não sei qual está entre os 3 da minha lista: mysql-connector 2.2.9 mysql-connector-python 8.0.29 mysql-connector-python-rf 2.2.2 )

Eu tive um primeiro problema em que esta consulta, por exemplo:

INSERT INTO messages(message_id, message, user_id, date_sent, channel_sent, stream_sent) VALUES('1c5e6b29-cd11-4a8b-bd20-583f65b4a130', '?', 121797523, FROM_UNIXTIME(1658512097), 24147592, 1539530627)

Retornaria este erro:

1366 (22007): Incorrect string value: '\xF0\x9F\x99\x83' for column `table_name`.`messages`.`message` at row 1

Depois de inúmeras horas procurando, encontro um post dizendo esta função: conn.set_charset_collation(charset=charset, collation=collation)

deve corrigi-lo. Então, logo após conectar ao meu banco de dados no meu script Python e obter meu conn (o que você usa para obter o cursor), eu faço

conn.set_charset_collation(charset="utf8mb4", collation="utf8mb4_bin")

Agora recebo uma nova mensagem de erro:unknown encoding: utf8mb4

Eu também encontrei outro post dizendo para editar /etc/mysql/my.cnf, então eu fiz e agora se parece com o seguinte (mas não corrigiu o problema):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 500
character-set-client-handshake                  = 0
init_connect                                    = 'SET character_set_system = utf8mb4'
init_connect                                    = 'SET character_set_connection = utf8mb4'
init_connect                                    = 'SET character_set_database = utf8mb4'
init_connect                                    = 'SET character_set_results = utf8mb4'
init_connect                                    = 'SET collation_database = utf8mb4_bin'
init_connect                                    = 'SET collation_connection = utf8mb4_bin'
init_connect                                    = 'SET NAMES utf8mb4'
character_set_client                            = utf8mb4
collation-server                                = utf8mb4_bin

(Eu fiz systemctl restart mysqle systemctl restart mariadbdepois) Eu ficaria MUITO GRATO por qualquer coisa que ajudasse a consertar isso. Eu geralmente sei que posso encontrar uma solução depois de procurar por algumas horas, mas aqui eu sinto que nunca vou conseguir consertar

Estou procurando um charset/collation que o torne assim quando eu fizer um

SELECT * FROM table_name WHERE username = "Warrior"

Ele só me retorna as linhas onde username = "Warrior", "warrior" ou "WARRIOR", e não "WÂRRÎOR" "Wârrîor" etc.

Eu encontrei uma solução parcial, alterando o Charset para "utf8mb4" e o Collation para "utf8mb4_bin", agora parece sensível ao acento, diferencia "Wârrîor" de "Warrior", mas também diferencia maiúsculas de minúsculas, então "Warrior" é diferente do que "WARRIOR" que não é o que eu quero.

Eu tentei um agrupamento diferente, mas não consegui que um fizesse exatamente o que eu queria. Alguma ideia ?

Abaixo está uma captura de tela dos diferentes Collations disponíveis para mim no Charset "utf8mb4":