MySQL 在 Centos 7 中的性能不佳 [英] MySQL poor performance in Centos 7
问题描述
我运行了一个非常简单的 MySQL 数据库结构.我只有喜欢我认为非常重要的 id、TimeStamp 和 OP_fs155e 列基本设置.
I run a very simple MySQL database construct. I only have like id, TimeStamp and OP_fs155e columns which I consider is extremely basic setup.
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)
然而，我开始注意到我的选择在开始时非常慢Raspberry Pi 3(作为主服务器)和 Centos7(作为从服务器 - 8GB 肌肉服务器).这是我在从服务器上选择的所有内容，这花了几分钟.我以为问题是树莓派 3 太慢了，但是当我发现它在真正的服务器奴隶上是一样的时，它肯定有问题.
However, I start noticing that my selects are terribly slow first at Raspberry Pi 3 (as master) and Centos7 (as slave - 8GB muscle server). Here is my select everything at slave server which took several minutes. I thought that the problem is that Raspberry Pi 3 is too slow but when I found out that it is the same at a real server slave, there is definitively something wrong with it.
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
slave 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
我知道我的方案中有零优化.
再举一个例子:
在 Raspberry Pi 3 上进行此选择需要 7 分钟
This select on Raspberry Pi 3 takes like 7 minutes
mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;
推荐答案
Raspberry Pi 使用 SD 卡进行存储，因此 I/O 性能将比真正的服务器磁盘系统差很多是可以理解的.
The Raspberry Pi uses an SD card for storage, and therefore it's understandable that I/O performance is going to be much worse than a real server disk system.
您的第一个查询需要 37.24 秒才能返回 2300 万行.这是相当多的 I/O.我估计每 16KB 页面可以容纳大约 500 行(假设您的数据列平均每个大约 3 个字节)，因此您需要读取超过 46,000 个页面才能读取 2300 万行，即 755MB.
Your first query that takes 37.24 seconds to return 23 million rows. That's quite a bit of I/O. I'd estimate you could fit about 500 rows per 16KB page (assuming your data columns are about 3 bytes each on average), so you'd need to read over 46,000 pages to read 23 million rows, which comes out to 755MB.
我敢打赌这或多或少是你的表的 data_length，你可以检查:
I bet that's more or less the data_length of your table, which you can check:
SHOW TABLE STATUS LIKE 'optical_power'\G
但随机读取 SD 卡的 I/O 速率介于 2.28MB/秒和 8.10MB/秒之间，因此从 SD 卡读取 755MB 需要 93 到 331 秒.那只是数学.
But the I/O rate for random reads on an SD card is between 2.28MB/sec and 8.10MB/sec, so it would take between 93 and 331 seconds to read 755MB from the SD card. That's just math.
也许一些数据页已经缓存在 MySQL 的缓冲池中，或者 InnoDB 能够做一些预读优化来帮助这里.
Perhaps some data pages were already cached in MySQL's buffer pool, or InnoDB was able to do some read-ahead optimizations to help here.
您的第二个查询没有得到很好的优化.它必须使用文件排序，因为您的 TimeStamp 列上没有索引.文件排序可能会使用临时存储空间，这会导致写入 I/O.写入比在 SD 卡上读取要慢得多.因此，执行 ORDER BY TimeStamp LIMIT 15 查询需要 7 分钟也就不足为奇了.
Your second query is not optimized well. It has to use a filesort because there's no index on your TimeStamp column. The filesort might use temporary storage space, which incurs write I/O. Writes are much slower than reads on an SD card. So it's not surprising at all that it takes 7 minutes to do your ORDER BY TimeStamp LIMIT 15 query.
显然，SD 卡的品牌有很大的不同.请参阅 http://www.jeffgeerling.com/blogs/jeff-geerling/raspberry-pi-microsd-card 一些比较.
Apparently the brand of SD card makes a big difference. See http://www.jeffgeerling.com/blogs/jeff-geerling/raspberry-pi-microsd-card for some some comparisons.
但即使您获得速度更快的 SD 卡，您仍然会因 I/O 使用效率低下而造成磨损.最好避免 I/O.
But even if you get an SD card that's faster, you're still causing wear and tear on it by using I/O inefficiently. It'll be better to avoid I/O.
- 在您的
TimeStamp列上创建一个索引，以便ORDER BY TimeStamp可以使用它而不是进行文件排序.索引对于优化查询非常重要.请参阅我的演示文稿如何设计索引，真的.立> - 许多 Raspberry Pi 用户将 MySQL 数据存储在 MyISAM 存储引擎中.MyISAM 仅使用缓冲 I/O，并且没有碰撞安全功能.这应该有助于提高 I/O 性能并减少 SD 卡的磨损.索引和复制在 MyISAM 表上工作得很好.MyISAM 也倾向于在比 InnoDB 更少的空间中存储数据.
- 确保将
sync_binlog=0设置为允许复制日志也使用异步 I/O.
- Create an index on your
TimeStampcolumn, so theORDER BY TimeStampcan use it instead of doing a filesort. Indexing is very important for optimizing queries. See my presentation How to Design Indexes, Really. - Many Raspberry Pi users store MySQL data in the MyISAM storage engine. MyISAM uses only buffered I/O, and has no crash-safety features. This should help improve I/O performance and decrease wear and tear on your SD card. Indexes and replication work just fine with MyISAM tables. MyISAM also tends to store data in less space than InnoDB.
- Make sure you set
sync_binlog=0to allow the replication log to use async I/O as well.
如果您必须使用 InnoDB，请调整它以获得最大缓存和最小持久性:
If you must use InnoDB, tune it for maximum caching and minimum durability:
- 尽可能多地增加
innodb_buffer_pool_size.但是不要让它太大以至于其他进程没有足够的内存.依靠缓冲池使用另外 10%，因此如果将其设置为 512M，则实际需要 563M. 避免同步 I/O 并尽可能使用缓冲 I/O.请参阅 https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html
- Increase the
innodb_buffer_pool_sizeas much as you can spare. But don't make it so large that other processes don't have enough memory. Count on the buffer pool using another 10%, so if you set it to 512M, it'll really take 563M. Avoid synchronous I/O and use buffered I/O wherever possible. See 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 Toshiba 记忆棒代替 od SD 卡作为存储，因为我在使用 SD 卡时遇到了很多麻烦
I am using 16GB USB Toshiba stick as the storage instead od SD card, because I has so much trouble with SD cards
USB 闪存驱动器(棒)在性能上与 SD 卡没有太大区别.两种设备都针对顺序读/写进行了优化，而不是随机读/写.它们作为文件系统或数据库工作非常糟糕.
A USB flash drive (stick) is not much different in performance than an SD card. Both devices are optimized for sequential read/write, not random read/write. They suck terribly as filesystems, or for database work.
最重要的是，如果您需要真实服务器的性能，且数据规模属于服务器，那么请不要使用 Raspberry Pi.
The bottom line is if you need the performance of a real server, with data at a scale that belongs on a server, then don't use a Raspberry Pi.
我希望用于数据收集的 Raspberry Pi 解决方案不会在 Pi 上存储任何数据，而是立即将数据发布到您网络上的服务器.一个好的解决方案是运行一个消息队列服务器，收集各种 Pi 设备发布的事件.然后编写脚本消费消息队列中的数据，并批量发布到数据库中.
I would expect a Raspberry Pi solution for data collection to not store any data on the Pi, but instead post the data immediately to a server on your network. A good solution would be to run a message queue server, collecting events posted by your various Pi devices. Then write a script to consumes the data from the message queue and post it to the database in batches.
这篇关于MySQL 在 Centos 7 中的性能不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
