mysql - MySQL在Centos 7中的性能不佳

我运行一个非常简单的MySQL数据库构造。我只喜欢
我认为id、TimeStamp和OP_fs155e列是非常重要的
基本设置。

MariaDB [gadbdfm]> desc optical_power;
+-----------+------------------+------+-----+----------------------+-----------------------------+
| Field     | Type             | Null | Key | Default              | Extra                       |
+-----------+------------------+------+-----+----------------------+-----------------------------+
| id_record | int(10) unsigned | NO   | PRI | NULL                 | auto_increment              |
| TimeStamp | timestamp(6)     | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP |
| OP_fs155e | varchar(30)      | YES  | MUL | NULL                 |                             |
| data1     | varchar(30)      | YES  |     | NULL                 |                             |
| data2     | varchar(30)      | YES  |     | NULL                 |                             |
| data3     | varchar(30)      | YES  |     | NULL                 |                             |
| data4     | varchar(30)      | YES  |     | NULL                 |                             |
| data5     | varchar(30)      | YES  |     | NULL                 |                             |
+-----------+------------------+------+-----+----------------------+-----------------------------+
8 rows in set (0.00 sec)

然而，我开始注意到我的选择是非常缓慢的首先
覆盆子Pi3（主服务器）和Centos7（从服务器-8GB肌肉服务器）。
这是我在从服务器上选择的所有东西，它花费了几分钟。我以为问题是树莓皮3太慢了，但是
当我发现它在一个真正的服务器从机上是一样的，它肯定有问题。

MariaDB [gadbdfm]> select TimeStamp,OP_fs155e from optical_power;

| 2017-01-01 17:41:03.697000 | -24       |
| 2017-01-01 17:42:03.666000 | -24       |
| 2017-01-01 17:43:03.701000 | -24       |
| 2017-01-01 17:44:03.675000 | -24       |
| 2017-01-01 17:45:03.676000 | -24       |
| 2017-01-01 17:46:03.692000 | -24       |
| 2017-01-01 17:47:03.686000 | -24       |
| 2017-01-01 17:48:03.539000 | -24       |
| 2017-01-01 17:49:03.581000 | -24       |
+----------------------------+-----------+
23044062 rows in set (37.24 sec)

掌握my.cnf

pi@rpi3jantoth - /opt/FlightStrata155E cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -v "#" | grep -v "^$"
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 0.0.0.0
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err

从my.cnf：

[root@fiber ~]# cat /etc/my.cnf | grep -v "#" | grep -v "^$"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id = 2
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

我知道我的方案没有优化。
再举一个例子：
这个精选的覆盆子皮3大约需要7分钟

mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;

最佳答案

覆盆子Pi使用SD卡进行存储，因此可以理解，I/O性能将比真正的服务器磁盘系统差得多。
您的第一个查询需要37.24秒才能返回2300万行。这是相当多的I/O。我估计每16KB页面可以容纳大约500行（假设数据列平均每个大约3字节），所以需要读取46000多页才能读取2300万行，总共755MB。
我敢打赌，这或多或少是你的表的数据长度，你可以检查：

SHOW TABLE STATUS LIKE 'optical_power'\G

但SD卡上随机读取的I/O速率介于2.28MB/sec和8.10MB/sec之间，因此从SD卡读取755MB需要93到331秒。那只是数学。
也许一些数据页已经被缓存在MySQL的缓冲池中，或者InnoDB能够做一些预读优化来帮助这里。
您的第二个查询优化得不好。它必须使用文件排序，因为TimeStamp列上没有索引。文件排序可能使用临时存储空间，这会导致写入I/O。写入速度比SD卡上的读取速度慢得多。因此，做ORDER BY TimeStamp LIMIT 15查询需要7分钟一点也不奇怪。
显然，SD卡的品牌有很大的不同。有关一些比较，请参见http://www.jeffgeerling.com/blogs/jeff-geerling/raspberry-pi-microsd-card。
但是，即使你得到一张速度更快的SD卡，你仍然会因为低效率地使用I/O而导致它的磨损。最好避免I/O。
在TimeStamp列上创建索引，以便ORDER BY TimeStamp可以使用它而不是执行文件排序。索引对于优化查询非常重要。查看我的演示文稿How to Design Indexes, Really。
许多Raspberry Pi用户将MySQL数据存储在MyISAM存储引擎中。MyISAM只使用缓冲I/O，没有崩溃安全特性。这将有助于提高I/O性能并减少SD卡的磨损。索引和复制在MyISAM表中工作得很好。MyISAM存储数据的空间也比InnoDB小。
确保将sync_binlog=0设置为允许复制日志也使用异步I/O。
如果您必须使用NONDB，请调整它以达到最大缓存和最小耐用性：
尽可能多地增加innodb_buffer_pool_size。但是不要让它太大，以至于其他进程没有足够的内存。再使用10%的缓冲池，所以如果将其设置为512M，则实际需要563M。
尽可能避免同步I/O并使用缓冲I/O。见https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html

innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DSYNC
innodb_doublewrite=0

回复您的评论：
我用16GB的USB东芝棒作为存储设备，而不是od SD卡，因为我在使用SD卡时遇到了很多麻烦
USB闪存驱动器（记忆棒）在性能上与SD卡差别不大。这两个设备都针对顺序读/写进行了优化，而不是随机读/写。它们作为文件系统或数据库工作非常糟糕。
底线是，如果你需要一个真正的服务器的性能，数据的规模属于一个服务器，那么不要使用树莓Pi。
我希望用于数据收集的Raspberry Pi解决方案不会在Pi上存储任何数据，而是将数据立即发布到网络上的服务器。一个好的解决方案是运行消息队列服务器，收集由各种Pi设备发布的事件。然后编写一个脚本来使用来自消息队列的数据并将其批量发布到数据库。

关于mysql - MySQL在Centos 7中的性能不佳，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41418232/