1

I am using MySQL 8 on Ubuntu 22.04. All my tables are MyISAM. Recently I bought 1 TB SSD Samsung 980 PRO to replace the existing 7200 rpm HDD in order to boost read performance. My test machine is one old Dell Optiplex 3050 micro with Core i3 (6th gen) and 8 GB RAM. So the machine configuration with HDD and SSD is absolutely the same. So is the test database. Before upgrading to SSD I did a lot of tests with HDD. I run the same tests with SSD. In some cases SSD performance for query executions is far better, in other just a little better, but there are cases when it is slower.

So far I thought that hard drive read operations are the bottleneck, but now I doubt it since the fact that the performance of the configuration with SSD is not better (or even worse) in some of the cases listed below.

Using KDiskMark shows following comparison of Read [MB/s] for both storage devices:

Test SSD HDD
SEQ1M Q8T1 3044 84
SEQ1M Q1T1 2114 84
RND4K Q32T1 660 0.7
RND4K Q1T1 66 0.3

The query joins the statistical table h_cell with a temporary table tmptab, which is populated with a list of objects cell for a user-selected cluster definition. The result set is a table with a number of KPIs, which are aggregated to cluster level values for each date of the reporting interval. The reference between both tables is cell. The statistical table h_cell contains daily values for a number of counters for each cell for each day. This table contains 2 years of statistics for about 50k cells for each day.

Data in h_cell was imported with LOAD DATA INFILE in chronological order. After importing ANALYZE TABLE was run. No changes to table happened afterwards.

SET @DATE1='2019-04-01';SET @DATE2='2019-06-30';
CREATE TEMPORARY TABLE tmptab(cell CHAR(8) NOT NULL,INDEX `Cell` (`Cell`) USING BTREE) Engine=MyISAM;
INSERT INTO tmptab SELECT cell FROM clusters_cust WHERE cluster='cluster3k';
SELECT 'cluster3k' AS Cluster,Time,
ROUND(SUM(counter1)/sum(counter1+counter2)*100,2) as 'KPI1',
.........
FROM h_cell
INNER JOIN tmptab ON tmptab.cell = h_cell.cell
WHERE time >= @DATE1 AND time <= @DATE2
GROUP BY time
ORDER BY time;
DROP TEMPORARY TABLE tmptab;

Below you can find benchmarks for execution time for different scenarios. No caching was implemented for benchmarked queries.

  1. Experiments with a small cluster of 60 cells. Here SSD performance is much better than HDD
  • 1 month: SSD vs HDD - 1s vs 14s
  • 3 months: SSD vs HDD - 3s vs 40s
  • 6 months: SSD vs HDD - 10s vs 3m 01s
  1. Experiments with a medium-sized cluster of 3k cells. SSD performance is just marginally better than HDD one for the 1 month experiment. The huge execution time for HDD for 3 and 6 months is partly due to wrong selection of index by optimizer. This will be a topic for another thread.
  • 1 month: SSD vs HDD - 53s vs 59s
  • 3 months #1: SSD vs HDD - 2m 25s vs 33m 30s (HDD wrong index PRIMARY was used; SSD is using the same index)
  • 3 months #2: SSD vs HDD - 2m 25s vs 2m 49s (HDD version was forced to use the correct index Time; SSD is using PRIMARY index; forcing SSD to use Time gives very similar execution time)
  • 6 months: SSD vs HDD - 4m 50s vs 70m (same situation with the wrong index)
  1. Experiments with a large cluster of 20k cells. Here SSD performance is in most cases worse than HDD one
  • 1 month: SSD vs HDD - 3m 13s vs 3m 10s
  • 3 months: SSD vs HDD - 9m 59s vs 9m 45s
  • 6 months: SSD vs HDD - 19m 37s vs 20m 20s

EXPLAIN for a cluster of 3k cells and 3 months reporting interval (with the wrong index - point '3 months #1' from above):

+----+-------------+--------+------------+-------+---------------------+---------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys       | key     | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------+--------+------------+-------+---------------------+---------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | tmptab | NULL       | index | Cell                | Cell    | 32      | NULL                  | 3000 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | h_cell | NULL       | ref   | PRIMARY,Time,eNodeB | PRIMARY | 32      | ee_4g_hua.tmptab.cell |  671 |     7.94 | Using index condition                        |
+----+-------------+--------+------------+-------+---------------------+---------+---------+-----------------------+------+----------+----------------------------------------------+

EXPLAIN for a cluster of 3k cells and 3 months reporting interval (with the forced index - point '3 months #2' from above):

+----+-------------+--------+------------+-------+---------------+------+---------+-----------------------+---------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref                   | rows    | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------+---------+-----------------------+---------+----------+-----------------------+
|  1 | SIMPLE      | h_cell | NULL       | range | Time          | Time | 3       | NULL                  | 3104736 |   100.00 | Using index condition |
|  1 | SIMPLE      | tmptab | NULL       | ref   | Cell          | Cell | 32      | ee_4g_hua.h_cell.Cell |       1 |   100.00 | Using index           |
+----+-------------+--------+------------+-------+---------------+------+---------+-----------------------+---------+----------+-----------------------+

More details about h_cell table:

CREATE TABLE h_cell (
  Time date NOT NULL,
  Cell char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  LocalCI tinyint NOT NULL,
  counter1 int DEFAULT NULL,
  counter2 int DEFAULT NULL,
  ...................
  counter860 int DEFAULT NULL,
  PRIMARY KEY (Cell,Time) USING BTREE,
  KEY Time (Time,LocalCI) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
SHOW INDEXES IN h_cell;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| h_cell |          0 | PRIMARY  |            1 | Cell        | A         |       58257 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| h_cell |          0 | PRIMARY  |            2 | Time        | A         |    39090988 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| h_cell |          1 | Time     |            1 | Time        | A         |         730 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| h_cell |          1 | Time     |            2 | LocalCI     | A         |       15081 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
mysql> SHOW TABLE STATUS IN xxx;
+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+--------------------+---------+
| Name          | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation          | Checksum | Create_options     | Comment |
+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+--------------------+---------+
| h_cell        | MyISAM |      10 | Dynamic    | 39090988 |           1969 | 76975355392 | 281474976710655 |   1736918016 |         0 |              1 | 2024-03-19 22:30:50 | 2024-03-19 23:51:03 | 2024-03-20 00:17:21 | utf8mb4_0900_ai_ci |     NULL | row_format=DYNAMIC |         |
+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+--------------------+----------+--------------------+---------+
14
  • 2
    myisam is very very old and should be urgently switched to innido
    – nbk
    Mar 25 at 22:13
  • @nbk I will not do this. InnoDb doesn't suit my needs for fast imports (most importantly) and smaller footprint. My database is mostly reads with once a day huge imports, which with InnoDb are taking ages (x20 times slower than MyISAM). More likely is to switch to MariaDb, where MyISAM still have some weight, while this engine in MySQL is totally abandoned.
    – Ivaylo
    Mar 26 at 23:56
  • 1
    @Ivaylo Additional DB information request, please. OS, Version? RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after benchmarks completed and minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide higher performance suggestions. Mar 27 at 0:04
  • 1
    Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions. Mar 27 at 0:04
  • 20 times slower i doubt it and you can always make a inndb table with all data and test
    – nbk
    Mar 27 at 0:19

2 Answers 2

1

Tentative answer... [Please answer the Comments so I can elaborate.]

  • The benchmarks are designed to stress I/O
  • Some of your cases can use the caching of MySQL to avoid much of the I/O; some cannot.
  • The caching opportunities of the two engines are not the same; several things may need changing when the engine is changed.
  • 860 4-byte INTs takes 3440 bytes plus overhead. This stresses each engine in different ways.
  • Deleting and adding rows to MyISAM can lead to fragmentation that gets worse and worse.
  • Deleting and adding rows to InnoDB has other problems.
  • Building and maintaining Summary Tables, if appropriate, would speed up both engines on both disk types.
7
  • I need to clarify that statictics in my table are chronologically ordered, because I imported the raw data in chronological order. As far as I know this helps the optimizer. After LOAD DATA INFILE operations I run ANALYZE TABLE AND OPTIMIZE TABLE (which I guess was not needed). No deletions or other alterations were done after importing.
    – Ivaylo
    Mar 27 at 23:15
  • I haven't used any specific benchmarking tools. I just created a few different versions of clusters with 60, 3k and 20k cells and run the queries. I guess no caching was used, because between runs I was restarting the server and execution times between different versions of clusters (for example with 3k objects) were similar. Even when I run after system restart the same cluster for the same reporting period (absolutely the same query) execution time was almost the same, so no OS/disk caching was used.
    – Ivaylo
    Mar 27 at 23:19
  • The value of key_buffer_size is 16777216. It is not sufficient, but the same value was used in both cases - with HDD and SSD. Regarding the table size you can find one addition to my question at the end (SHOW TABLE STATUS).
    – Ivaylo
    Mar 27 at 23:27
  • Daily imports are always appending to the table. There are no deletions in this table. Very rarely when imported data is not integral it might happen to reload it with INSERT ... ON DUPLICATE KEY UPDATE.
    – Ivaylo
    Mar 27 at 23:33
  • I prefer to keep INT because sometimes counters with usual two-digits values might explode to huge numbers in some circumstances and I cannot risk to have statistics truncated.
    – Ivaylo
    Mar 27 at 23:34
1

Suggestions to consider for improving speed of LOADING TABLES.

innodb_io_capacity=900  # from 200 to use more of available IOPS.
innodb_flush_method=O_DIRECT  # from fsync - O_DIRECT is used on most Linux servers.
innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function.
table_open_cache_instances=1  # from 16 you only have 2 CORES.
bulk_insert_buffer_size=64M  # from 1G to conserve RAM
innodb_adaptive_max_sleep_delay=10000  # from 150000 (seconds) for 1 second delay when busy
innodb_old_blocks_pct=1  # from 37 percent of buffer_pool_size set aside
sort_buffer_size=1M  # from 16M until you see sort_merge_passes > 1 per hour
temptable_max_mmap=86M  # from 1G for 1% of RAM to conserve RAM
temptable_max_ram=86M  # from 1G for 1% of RAM to conserve RAM
innod_change_buffer_max_size=50  # from 25 percent anytime you are pushing HEAVY INSERTS.
innodb_flush_neighbors=2  # from 0 to record all needed on EXTENT in one sweep.
innodb_stats_auto_recalc=OFF  # not needed during loading - ANALYZE table when done loading.
innodb_stats_persistent=OFF  # not needed during loading - ANALYZE table when done loading.
innodb_buffer_pool_size=5G  # from 128M for higher speed loading of data (~ 62% of 8G)

There may be additional opportunities. View my profile, please.

Observation: Most people put all LOG's in one folder /log we see a couple of yours in /var/lib which would not be typical. datadir used to always be in a DATA folder.

4
  • This looks like a fantastic summary. I will give it a try. And what about innodb_flush_log_at_trx_commit? In a lot of places one can find suggestions to change the default 1 to 0 or 2 in order to improve importing speed. In the experiments in my other thread link I didn't experience any improvement.
    – Ivaylo
    Mar 29 at 20:59
  • I tried most of your suggestions. Unfortunately I was not able to wait for LOAD DATA INFILE to finish, because of excessive importing time. The batch that I was loading was with 3 mil rows. The target table was empty with keys disabled. For MyISAM version of the table the loading took about 5 minutes. Now with InnoDb I canceled loading after 1 hour 44 minutes. Judging by the size of .ibd file loading was not more than half way through, so I can estimate loading time of 3+ hours, which is unacceptable.
    – Ivaylo
    Mar 29 at 23:16
  • If you tried my suggestions one at a time, there would be no improvement. What do you mean by most of the suggestions? Mar 30 at 0:11
  • Please provide SHOW CREATE TABLE yourdestinationtable; for a look at the scope of your load. Mar 30 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.