MySQL poor performance in Centos 7

Question

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)

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)

Master 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

I know that I have zero optimization in my scheme.

One more example:

This select on Raspberry Pi 3 takes like 7 minutes

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

Answer 1

Hello @Bill Karwin and @Bernd Buffen! I highly appreciate your advice! I added like this index as show and explained in the presentation here:

mysql> ALTER TABLE optical_power ADD INDEX(TimeStamp,OP_fs155e);

and the result is 3.24 seconds compared to 2 minutes ot 7 minutes sometimes: AMAZING!!!

   mysql> use gadbdfm;
Database changed
mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;
+----------------------------+-----------+
| TimeStamp                  | OP_fs155e |
+----------------------------+-----------+
| 2017-01-01 17:49:03.581000 | -24       |
| 2017-01-01 17:48:03.539000 | -24       |
| 2017-01-01 17:47:03.686000 | -24       |
| 2017-01-01 17:46:03.692000 | -24       |
| 2017-01-01 17:45:03.676000 | -24       |
| 2017-01-01 17:44:03.675000 | -24       |
| 2017-01-01 17:43:03.701000 | -24       |
| 2017-01-01 17:42:03.666000 | -24       |
| 2017-01-01 17:41:03.697000 | -24       |
| 2017-01-01 17:40:03.688000 | -24       |
| 2017-01-01 17:39:03.574000 | -24       |
| 2017-01-01 17:38:03.596000 | -24       |
| 2017-01-01 17:37:03.545000 | -24       |
| 2017-01-01 17:36:03.667000 | -24       |
| 2017-01-01 17:35:03.544000 | -24       |
+----------------------------+-----------+
15 rows in set (3.24 sec)

Answer 2

Well, regarding to your first suggestion:

Database changed
mysql> SHOW TABLE STATUS LIKE 'optical_power'\G
*************************** 1. row ***************************
           Name: optical_power
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 22030014
 Avg_row_length: 38
    Data_length: 843055104
Max_data_length: 0
   Index_length: 0
      Data_free: 7340032
 Auto_increment: 34973978
    Create_time: 2016-12-30 16:10:49
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

I am using 16GB USB Toshiba stick as the storage instead od SD card, because I has so much trouble with SD cards.For instance, I use all of raspberry pi3 USB ports because there is a need to collect data from multiple sensors. I am trying

ALTER TABLE optical_power ADD INDEX(TimeStamp,OP_fs155e);

I highly appreciate your answer I will try to play with it.

Thanks so much

So it is just simple timestamp and value (OP_fs155e) table.

code mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;
+----------------------------+-----------+
| TimeStamp                  | OP_fs155e |
+----------------------------+-----------+
| 2017-01-01 17:49:03.581000 | -24       |
| 2017-01-01 17:48:03.539000 | -24       |
| 2017-01-01 17:47:03.686000 | -24       |
| 2017-01-01 17:46:03.692000 | -24       |
| 2017-01-01 17:45:03.676000 | -24       |
| 2017-01-01 17:44:03.675000 | -24       |
| 2017-01-01 17:43:03.701000 | -24       |
| 2017-01-01 17:42:03.666000 | -24       |
| 2017-01-01 17:41:03.697000 | -24       |
| 2017-01-01 17:40:03.688000 | -24       |
| 2017-01-01 17:39:03.574000 | -24       |
| 2017-01-01 17:38:03.596000 | -24       |
| 2017-01-01 17:37:03.545000 | -24       |
| 2017-01-01 17:36:03.667000 | -24       |
| 2017-01-01 17:35:03.544000 | -24       |
+----------------------------+-----------+
15 rows in set (2 min 41.32 sec)

Answer 3

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.

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.

I bet that's more or less the data_length of your table, which you can check:

SHOW TABLE STATUS LIKE 'optical_power'\G

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.

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.

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.

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.

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.

• Create an index on your TimeStamp column, so the ORDER BY TimeStamp can 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=0 to allow the replication log to use async I/O as well.

If you must use InnoDB, tune it for maximum caching and minimum durability:

• Increase the innodb_buffer_pool_size as 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
  

---

Re your comment:

I am using 16GB USB Toshiba stick as the storage instead od SD card, because I has so much trouble with SD cards

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.

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.

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.