lyeaf提出的问题 -dba

lyeaf

Asked: 2023-11-28 02:59:47 +0800 CST

什么数据库可以存储20~十亿行

5

我计划归档邮件，但不确定应该使用哪个。

它应该能够容纳最多 200 亿行（这就是我期望的总数）

每行将包含三列：user_id、消息、日期

user_id 是一个 30 个字符的字符串。该消息的长度介于 1 - 20 000 个字符之间。我预计平均为 140 个字符。（UTF-8，它应该允许表情符号、不同的字母表等）

我只想要 user_id 的索引，而不是消息/日期的索引。

我只计划进行 INSERT 查询，并且非常简单 SELECT * WHERE user_id = XXXXX 会有很少的 SELECT，我预计峰值为 10 个/分钟。SELECT 不需要太快，1 到 20 秒之间的任何时间都可以。

但是会有很多INSERT。大概每秒5000-10000次。

我的服务器将配备： CPU：AMD Ryzen™ 9 7950X3D RAM：128 GB DDR5 ECC 驱动器：1x 7.68 TB NVMe SSD 数据中心（来自 Hetzner）

lyeaf

Asked: 2023-03-11 05:34:13 +0800 CST

是否可以估计 DROP 语句还剩多少时间？

11

我提出了这个查询：ALTER TABLE dbname.tablename DROP COLUMN columnname它已经运行了几个小时，我想知道它还能运行多长时间。

大约有 750m 行。

谢谢！

如果您需要更多详细信息，我会提供，我不想提供无用的信息

lyeaf

Asked: 2022-12-27 18:31:16 +0800 CST

MariaDB 服务器每隔几个小时就会崩溃（已经一周了，是的，崩溃了几十次），然后自动重启。在之前的 2 次崩溃中，服务器没有再次启动，我不得不用启动它mysqld --innodb_force_recovery=3，然后转储数据库/模式，擦除 mysql/mariadb 的所有数据，重新安装它，然后“导入“通过新安装的 MariaDB 服务器上的 .sql mysql < dump.sql（然后它工作了，“工作”意味着数据库至少可以运行，但它仍然每隔几个小时就会崩溃）

我是新手。只是想让你知道。

我使用 MariaDB，这是版本：mysql Ver 15.1 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

我使用的所有表都在 InnoDB 上。我所有的查询都很简单，没有做任何复杂的事情。INSERT INTO table(a,b,c) VALUES(1,2,3)<= 它并没有比这种类型的查询复杂得多，真的。（它主要是一个用于存档的数据库，所以有很多 INSERT，一些 UPDATE，很少的 SELECT）。90% 的请求是通过本地主机进行的。（带有aiomysql模块的 python 脚本将通过互联网获取数据，然后将其发送到本地 MariaDB 服务器）

我使用的 VPS 是：4.19.0-21-amd64 #1 SMP Debian 4.19.249-2 (2022-06-30) x86_64 GNU/Linux，使用 SSD，有 24GB 内存，14 个 vCores。CPU 使用率勉强为 20%，内存使用率也为 20%（根据我在top命令中看到的）

根据我在MySQL Workbench上看到的Server Status，大约有2700个InnoDB Writes per Second/ 420 Selects per second/ 120 Reads per seconds/ 2185个Connections，数据库总大小应该在140GB左右~

这些对我来说是正常的数字。我有 200 多个运行的 python 脚本实例，每个实例都创建一个连接池，aiomysql最少 10 个连接/最多 100 个连接，因此最少有 2000 个连接。

在my的内容下面.cnf file，我跟大家说了我是新手，所以慢慢的填上我认为可以解决问题的内容。在此之下，内容：/var/log/mysql/error.log这里是部分内容的链接/var/log/syslog：https ://ctxt.io/2/AAAQl4uRFg （对我来说有趣的部分是那两行Out 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

谢谢你的帮助，我现在很绝望。如果您有更多信息，我会尽力提供。请详细说明获取方式

（来自评论）

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

lyeaf

Asked: 2022-12-23 05:07:26 +0800 CST

重复，有条件的更新，不工作

5

我有一个包含两列的表：id(int)、data(text)

data 包含一个字符串数组。我想在其中插入字符串，如果它们不存在的话。

搜索了一下，想出了这个：

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)

它不会将“3”插入数据，无论它是否已经存在。它不会返回错误。我想知道我应该怎么做，为什么这不起作用

谢谢

lyeaf

Asked: 2022-07-23 16:19:22 +0800 CST

未知字符集：utf8mb4

1

上下文：我通过 Python 脚本和 mysql.connector 模块向我的 MariaDB 服务器（Ver 15.1 Distrib 10.3.34-MariaDB）发送查询。（我不知道它在我列表中的这 3 个中是哪一个： mysql-connector 2.2.9 mysql-connector-python 8.0.29 mysql-connector-python-rf 2.2.2 ）

我遇到了第一个问题，例如这个查询：

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)

将返回此错误：

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

经过无数小时的寻找，我发现一个帖子说这个功能： conn.set_charset_collation(charset=charset, collation=collation)

应该修复它。因此，在我的 Python 脚本连接到我的数据库并获得我的 conn（用于获取光标的那个）之后，我会这样做

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

现在我收到一条新的错误消息：unknown encoding: utf8mb4

我还发现另一个帖子说要编辑/etc/mysql/my.cnf，所以我做了，现在看起来如下（但它没有解决问题）：

[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

（我做了systemctl restart mysql和systemctl restart mariadb之后）我会非常感谢任何有助于解决这个问题的东西。我通常知道我可以在寻找几个小时后找到解决方案，但在这里我觉得我永远无法修复它

lyeaf

Asked: 2022-06-18 14:36:44 +0800 CST

MySQL 不区分大小写和区分重音的字符集/排序规则是什么？

2

我正在寻找一个字符集/排序规则，当我做一个

SELECT * FROM table_name WHERE username = "Warrior"

它只返回用户名 = "Warrior"、"warrior" 或 "WARRIOR" 的行，而不是 "WÂRRÎOR" "Wârrîor" 等。

我找到了一个部分解决方案，通过将 Charset 更改为“utf8mb4”并将排序规则更改为“utf8mb4_bin”，现在它似乎区分了重音，它将“Wârrîor”与“Warrior”区分开来，但它也区分大小写，所以“Warrior”是不同的而不是我想要的“战士”。

我尝试了不同的排序规则，但我无法让一个完全按照我的意愿去做。有任何想法吗？

以下是“utf8mb4”字符集中可供我使用的不同排序规则的屏幕截图：

什么数据库可以存储20~十亿行

是否可以估计 DROP 语句还剩多少时间？

数据库每隔几个小时就会崩溃

重复，有条件的更新，不工作

未知字符集：utf8mb4

MySQL 不区分大小写和区分重音的字符集/排序规则是什么？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

lyeaf's questions