Вот текущий файл конфигурации:
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]
Есть таблицы с 100 млн строками, в них пишут данные 24 процесса. Все таблицы MyISAM. Просьба подсказать, какие настройки покрутить, а то сейчас myisam пишет данные на диск со скоростью 1 мб/с МАКСИМУМ, а диск может разогнаться до 20 мб/с точно. 8 ГБ ОЗУ, Intel 2.4ghz*8, диск SATA 1tb.
вот список "STATUS VARS":
https://pastebin.com/J5uhuGq6
выведу то, что красным отдельно здесь:
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
какие есть варианты?
запросы простые, типа SELECT id FROM .. where d_idx=? and a_idx=?; ну и INSERT.
Простой
