How to speed up insert and select MyISAM?

Question

Percona

Here is the current config file:

cat /etc/mysql/mariadb.conf.d/50-server.cnf
#
# 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	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /mnt/wwn-0x50014ee056a4fa64-part1/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking

#ignore-builtin-innodb
default-storage-engine = myisam
default_tmp_storage_engine=MyISAM

#skip-external-locking
#skip-name-resolve

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size		= 4096M
max_allowed_packet	= 256M
thread_stack		= 192K
thread_cache_size       = 16k
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
max_connections        = 512
table_cache            = 3072
#thread_concurrency     = 10
delay_key_write=1

wait_timeout=2147483
interactive_timeout=2147483

#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 64M

#
# * 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 = 1
#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

#
# * 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!
innodb_buffer_pool_size=50000M
innodb_buffer_pool_instances=16
innodb_log_file_size=8192M
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=16
innodb_flush_method=O_DIRECT
innodb_file_per_table=ON
innodb_additional_mem_pool_size = 16M
innodb_use_sys_malloc=0
innodb_lock_wait_timeout = 60
innodb_write_io_threads = 64
innodb_read_io_threads = 64
bulk_insert_buffer_size = 64M
transaction_isolation = REPEATABLE-READ




#
# * 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

#
# * 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.1 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.1]

There are tables with 100 million rows, data from 24 processes are written to them. All MyISAM tables. Please tell me what settings to turn, otherwise myisam is now writing data to the disk at a speed of 1 MB / s MAXIMUM, and the disk can accelerate to 20 MB / s for sure. 8GB RAM, Intel 2.4ghz*8, SATA 1tb disk.

here is the list of "STATUS VARS":
https://pastebin.com/J5uhuGq6

I will display what is in red separately here:

Handler read rndDocumentation	4.7 M	The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler read rnd nextDocumentation	3.3 M	The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Opened tablesDocumentation	642	The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Table locks waitedDocumentation	3 M	The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

CREATE TABLE `_links` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `added_at` int(11) NOT NULL,
 `crc16` smallint(5) unsigned NOT NULL,
 `d_idx` binary(20) NOT NULL,
 `a_idx` binary(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `link0` (`crc16`,`d_idx`,`a_idx`) USING BTREE,
 KEY `link1` (`d_idx`,`a_idx`)
) ENGINE=MyISAM AUTO_INCREMENT=81997147 DEFAULT CHARSET=utf8

What are the options?
queries are simple, like SELECT id FROM .. where d_idx=? and a_idx=?; well, INSERT.

Answer 1

so.
comes to you

ø per second: 3,169

of them

Statements # ø per hour %
insert 75,460 k 8,288.7 k 72.66

those. 2302 as many as 59 hundredths of additions of records of size n bytes each per second
attention to the question. if we write everything

1 Mb/s MAXIMUM

, then this very n from above is limited by how many bytes? and why should a muscle write faster if it simply has nothing to write faster ( (
up . synapse_people synapse_people

Answer 2

1)

queries are simple, like SELECT id FROM .. where d_idx=? and a_idx=?; well, INSERT.

Still, check the slow-query-log, there may be heavy queries that do not use the index
2) as far as I understand, with a large number of inserts, InnoDB will work faster, because no lock on whole table on write

Answer 3

It seems to me that after the words

... tables with 100 million rows, 24 processes write data to them...
... myisam writes data to disk at a speed of 1 MB/s MAXIMUM, and the disk can accelerate to 20 MB/s exactly...
... disk SATA 1tb...

it should become clear that the base simply rests on the physical limitations of the disk.
There is even a set of problems here.
The fact that the disk can accelerate to 20 Mb / s is in ideal conditions when sequential recording is in progress. But in reality, data on a SATA disk is not written sequentially. This will be a lot of head movement across the disks. And it takes a long time to rebuild the index when inserting data ...
System lock for 10-30 seconds - or maybe the disk has already received a small one with a scythe ...
In my opinion, one should not expect performance growth on the current hardware. Think about raid 10 on SAS if you want reliability, well, or SSD on M.2 or PCI (but if there are a lot of writes, then it will be a pity)