Nous nous trouvons maintenant devant la mort du logiciel de forum Vanilla. Let's go sur le post concerné que j'ai publié sur le site de Vanilla Forums.

#vanillaforums #developpement #php #mysql #community

Značky: #mysql, #Arduino, #Elektro, #grafana, #ESP8266

MySQL and MariaDB are relational database management systems (RDBMS) best known for their mutual compatibility and their identical command and query syntaxes. In fact, MariaDB is a free and open source fork of MySQL that inherited many of that database’s characteristics.

The MariaDB and MySQL database management systems have a lot in common, which can make it difficult to choose when you need to decide on a database solution for your needs.

An in-depth comparison of MariaDB vs MySQL based on license model, popularity, features, performance, and support.

See https://linuxiac.com/mysql-vs-mariadb/

#technology #databases #opensource #MySQL #MariaDB

Taking aim at competitors including Amazon Aurora and Snowflake , Oracle has enhanced the MySQL HeatWave in-memory query accelerator in the Oracle Cloud’s MySQL Database Service by leveraging advanced machine learning. But Oracle insists the improvements do not mean the MySQL Database Service is encroaching on its flagship Oracle Database .

To read this article in full, please click here

Značky: #Database, #Rozne, #MySQL

MySQL and MariaDB are the most widely used relational database management systems (RDMS) when it comes to website hosting and CMS systems such as Joomla, WordPress, Drupal, and Typo 3. In this article, I will explain how to speed up and optimize your MySQL and MariaDB database server.

Probably not much need to worry about this is you just have a single WordPress instance, but if you're running a few different services all using a MySQL database and quite a few users, it may be well worth looking into to tweak performance a bit. Just always make sure you've done backups before making changes.

See https://vitux.com/tune-and-optimize-mysql-mariadb/

#technology #databases #MySQL #MariaDB

Enlarge / Warning: Learning the care and feeding of MySQL instances does not grant knowledge of or safe interaction with actual marine mammals. (credit: Oracle)

One of the tasks nearly any sysadmin frequently encounters is the care and feeding of the MySQL database server. You can build an entire career around nothing but this topic—making you a DB admin, not a humble sysadmin like yours truly—but for today, we're just going to cover the basics.

For this guide, we're going to be using Ubuntu Linux as the underlying operating system—but most of these steps and tips will be either the same, or broadly similar, across nearly any OS or distribution you might install MySQL on.

Installing MySQL

• 

  If you're even vaguely familiar with Ubuntu or Debian, the installation process shouldn't be surprising: apt install mysql-server and you're off to the races. [credit: Jim Salter ]

Installing MySQL on a fresh Ubuntu instance is quite simple: sudo apt update if necessary, then sudo apt install mysql-server and you're off to the races. Once the package is downloaded and installed, mysql is fired up automatically (and will be after each system reboot).

Read 55 remaining paragraphs | Comments

1. при её использовании в базе данных Zabbix не нужно создавать дополнительную нестандартную таблицу manage_partitions,
2. при её использовании имеется возможность делить таблицы не только на секции месячного или суточного размера, но и на секции произвольного размера, в том числе более мелкого.

Включение плагина TokuDB в MariaDB

# apt-get install mariadb-plugin-tokudb

$ cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

[always] madvise never

GRUB_CMDLINE_LINUX="ipv6.disable=1 transparent_hugepage=never"

# update-grub

Создание новых таблиц истории и тенденций

DROP TABLE history;
DROP TABLE history_uint;
DROP TABLE history_str;
DROP TABLE history_log;
DROP TABLE history_text;
DROP TABLE trends;
DROP TABLE trends_uint;

RENAME TABLE history TO history_bak;
RENAME TABLE history_uint TO history_uint_bak;
RENAME TABLE history_str TO history_str_bak;
RENAME TABLE history_log TO history_log_bak;
RENAME TABLE history_text TO history_text_bak;
RENAME TABLE trends TO trends_bak;
RENAME TABLE trends_uint TO trends_uint_bak;

CREATE TABLE `history` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `value`                  double(16,4)    DEFAULT '0.0000'          NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
CREATE INDEX `history_1` ON `history` (`itemid`,`clock`) CLUSTERING=yes;

CREATE TABLE `history_uint` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `value`                  bigint unsigned DEFAULT '0'               NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
CREATE INDEX `history_uint_1` ON `history_uint` (`itemid`,`clock`) CLUSTERING=yes;

CREATE TABLE `history_str` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `value`                  varchar(255)    DEFAULT ''                NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
CREATE INDEX `history_str_1` ON `history_str` (`itemid`,`clock`) CLUSTERING=yes;

CREATE TABLE `history_log` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `timestamp`              integer         DEFAULT '0'               NOT NULL,
        `source`                 varchar(64)     DEFAULT ''                NOT NULL,
        `severity`               integer         DEFAULT '0'               NOT NULL,
        `value`                  text                                      NOT NULL,
        `logeventid`             integer         DEFAULT '0'               NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
CREATE INDEX `history_log_1` ON `history_log` (`itemid`,`clock`) CLUSTERING=yes;

CREATE TABLE `history_text` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `value`                  text                                      NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;
CREATE INDEX `history_text_1` ON `history_text` (`itemid`,`clock`) CLUSTERING=yes;

CREATE TABLE `trends` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `num`                    integer         DEFAULT '0'               NOT NULL,
        `value_min`              double(16,4)    DEFAULT '0.0000'          NOT NULL,
        `value_avg`              double(16,4)    DEFAULT '0.0000'          NOT NULL,
        `value_max`              double(16,4)    DEFAULT '0.0000'          NOT NULL,
        PRIMARY KEY (itemid,clock) CLUSTERING=yes
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;

CREATE TABLE `trends_uint` (
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `num`                    integer         DEFAULT '0'               NOT NULL,
        `value_min`              bigint unsigned DEFAULT '0'               NOT NULL,
        `value_avg`              bigint unsigned DEFAULT '0'               NOT NULL,
        `value_max`              bigint unsigned DEFAULT '0'               NOT NULL,
        PRIMARY KEY (itemid,clock) CLUSTERING=yes
) ENGINE=TokuDB COMPRESSION=TOKUDB_LZMA;

Разбивка таблиц на секции

#!/usr/bin/python
# -*- coding: UTF-8 -*-

from datetime import datetime, timedelta
from pytz import timezone

def table_partitions(table, start, stop, step):
    print 'ALTER TABLE `%s` PARTITION BY RANGE (`clock`) (' % table

    dt = start
    while dt < stop:
        name = dt.strftime('%Y%m%d%H%M')
        ts = dt.strftime('%s')
        dt += step
        print 'PARTITION p%s VALUES LESS THAN (%s) ENGINE = TokuDB,' % (name, ts)

    name = dt.strftime('%Y%m%d%H%M')
    ts = dt.strftime('%s')
    print 'PARTITION p%s VALUES LESS THAN (%s) ENGINE = TokuDB' % (name, ts)
    print ');'

tz = timezone('UTC')
# Для таблиц тенденций trends и trends_uint
start = datetime(2018, 9, 10, 0, 0, 0, tzinfo=tz)
stop = datetime(2019, 9, 22, 0, 0, 0, tzinfo=tz)
step = timedelta(days=1)
table_partitions('trends', start, stop, step)
table_partitions('trends_uint', start, stop, step)

# Для таблиц истории history и history_uint
start = datetime(2019, 6, 10, 0, 0, 0, tzinfo=tz)
stop = datetime(2019, 9, 22, 0, 0, 0, tzinfo=tz)
step = timedelta(hours=6)
table_partitions('history', start, stop, step)
table_partitions('history_uint', start, stop, step)

# Для таблиц истории history_str, history_text и history_log
start = datetime(2019, 9, 3, 0, 0, 0, tzinfo=tz)
stop = datetime(2019, 9, 22, 0, 0, 0, tzinfo=tz)
step = timedelta(days=1)
table_partitions('history_str', start, stop, step)
table_partitions('history_text', start, stop, step)
table_partitions('history_log', start, stop, step)

$ ./partitions.py > partitions.sql

$ mysql -uzabbix -p zabbix

MariaDB [zabbix]> SOURCE partitions.sql

Перенос имеющихся данных в новые таблицы

$ mysqldump -t -uroot -p zabbix trends_uint_bak | grep ^INSERT | sed 's/^INSERT INTO/INSERT IGNORE/g' | mysql -uroot -p zabbix
$ mysqldump -t -uroot -p zabbix trends_bak | grep ^INSERT | sed 's/^INSERT INTO/INSERT IGNORE/g' | mysql -uroot -p zabbix
$ mysqldump -t -uroot -p zabbix history_bak | grep ^INSERT | sed 's/^INSERT INTO/INSERT IGNORE/g' | mysql -uroot -p zabbix
$ mysqldump -t -uroot -p zabbix history_str_bak | grep ^INSERT | sed 's/^INSERT INTO/INSERT IGNORE/g' | mysql -uroot -p zabbix
$ mysqldump -t -uroot -p zabbix history_text_bak | grep ^INSERT | sed 's/^INSERT INTO/INSERT IGNORE/g' | mysql -uroot -p zabbix
$ mysqldump -t -uroot -p zabbix history_log_bak | grep ^INSERT | sed 's/^INSERT INTO/INSERT IGNORE/g' | mysql -uroot -p zabbix

DROP TABLE history_bak;
DROP TABLE history_uint_bak;
DROP TABLE history_str_bak;
DROP TABLE history_log_bak;
DROP TABLE history_text_bak;
DROP TABLE trends_bak;
DROP TABLE trends_uint_bak;

Настройки плагина TokuDB

tokudb_fanout - максимальное количество дочерних узлов

tokudb_block_size - размер узла в памяти

tokudb_read_block_size - размер базового узла

tokudb_row_format - алгоритм сжатия колонок

• tokudb_default, tokudb_zlib - среднее сжатие при средней нагрузке на процессор.
• tokudb_snappy - хорошее сжатие при низкой нагрузке на процессор.
• tokudb_fast, tokudb_quicklz - слабое сжатие при низкой нагрузке на процессор.
• tokudb_small, tokudb_lzma - лучшее сжатие при высокой нагрузке на процессор.
• tokudb_uncompressed - сжатие не используется.

tokudb_directio - использование прямого ввода-вывода

tokudb_cache_size = 8G ; default = 12G ?
tokudb_directio = OFF
tokudb_empty_scan = disabled ; default - rl
tokudb_read_block_size = 16K ; default - 64K
tokudb_commit_sync = ON
tokudb_checkpointing_period = 900 ; default = 60
tokudb_block_size = 4M
tokudb_cleaner_iterations = 10000 ; default = 5
tokudb_fanout = 128 ; default = 16

tokudb_directio = ON
tokudb_row_format = tokudb_lzma
tokudb_empty_scan = disabled

Решение проблем

6619:20200604:000100.756 [Z3005] query failed: [1526] Table has no partition for value 1591210860 [insert into history
(itemid,clock,ns,value) values (3827556,1591210860,519948235,0.012016),(3827601,1591210860,574265420,0.016382),
(3827553,1591210860,683308669,7.549000),(3827616,1591210860,684083178,7.715000),(3827591,1591210860,684848189,3.199600),
(3827583,1591210860,685585717,0.016474),(3827504,1591210860,689418268,24.000000),(3827564,1591210860,690132132,3.209600),
(3827610,1591210860,690862622,0.014954),(1284053,1591210860,732901317,3.000000),(1283392,1591210860,737607405,23.000000),
(352809,1591210860,737607405,35.000000),(1309072,1591210860,738428022,11.000000),(3827571,1591210860,740171802,7.187000),
(1308475,1591210860,740185955,3.000000),(1292277,1591210860,743020934,1.000000),(3827619,1591210860,743278260,0.014760),
(3827573,1591210860,743976749,3.254600),(3827598,1591210860,744811430,7.577000),(1284110,1591210860,745749025,21.000000),
(3827580,1591210860,746661186,7.580000),(1279841,1591210860,747623084,5.000000),(3827607,1591210860,748043948,7.717000),
(1282792,1591210860,749216640,15.000000);
]

CALL partition_maintenance('zabbix', 'trends', 365, 24, 2);
CALL partition_maintenance('zabbix', 'trends_uint', 365, 24, 2);
CALL partition_maintenance('zabbix', 'history', 90, 6, 8);
CALL partition_maintenance('zabbix', 'history_uint', 90, 6, 8);
CALL partition_maintenance('zabbix', 'history_str', 7, 24, 2);
CALL partition_maintenance('zabbix', 'history_text', 7, 24, 2);
CALL partition_maintenance('zabbix', 'history_log', 7, 24, 2);

$ mysql_upgrade --force -uroot -p mysql
$ mysql_upgrade --force -uroot -p zabbix

USE `zabbix`;
DELIMITER $$

CREATE EVENT IF NOT EXISTS `e_part_manage`
       ON SCHEDULE EVERY 1 DAY
       STARTS '2019-04-04 04:00:00'
       ON COMPLETION PRESERVE
       ENABLE
       COMMENT 'Управление созданием и удалением секций'
       DO BEGIN
              CALL partition_maintenance('zabbix', 'trends', 365, 24, 2);
              CALL partition_maintenance('zabbix', 'trends_uint', 365, 24, 2);
              CALL partition_maintenance('zabbix', 'history', 90, 6, 8);
              CALL partition_maintenance('zabbix', 'history_uint', 90, 6, 8);
              CALL partition_maintenance('zabbix', 'history_str', 7, 24, 2);
              CALL partition_maintenance('zabbix', 'history_text', 7, 24, 2);
              CALL partition_maintenance('zabbix', 'history_log', 7, 24, 2);
       END$$

DELIMITER ;

# systemctl restart mariadb

Značky: #linux, #stretch, #buster, #mariadb, #debian, #zabbix, #Linux, #mysql, #3.4, #tokudb

expire_logs_days

expire_logs_days = 1

transaction_isolation

transaction_isolation = REPEATABLE-READ

innodb_file_per_table

1. остановить MySQL,
2. удалить файлы ibdata1, ib_logfile0 и ib_logfile1,
3. запустить MySQL снова,
4. восстановить базы данных из резервных копий.

innodb_file_per_table = 1

table_cache

table_cache = 512

event_scheduler

event_scheduler = 1

max_connections

max_connections = 140

query_cache_size

query_cache_size = 64M

query_cache_type = 0
query_cache_size = 0

general_log_file

general_log_file = /var/log/mysql/mysql.log

log_error

log_error = /var/log/mysql/mysql.err

log_warnings

log_warnings = 0

character-set-server и collation-server

character-set-server = utf8
collation-server = utf8_general_ci

join_buffer_size

join_buffer_size = 16M

innodb_buffer_pool_size и innodb_buffer_pool_instances

innodb_buffer_pool_size = 512M

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10

innodb_flush_method

innodb_flush_method = O_DIRECT

innodb_log_file_size, innodb_log_buffer_size и innodb_flush_log_at_trx_commit

innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 2

• 1 - каждая транзакция после фиксации записывается на диск (наибольшая надёжность),
• 2 - транзакция после фиксации записывается в кэш операционной системы (компромисс между надёжностью и производительностью),
• 0 - нет требования специально сохранять данные транзакции после её фиксации, данные сохраняются по мере заполнения буфера транзакций (наибольшая производительность, но высокий риск потери данных).

Временные файлы

# cd /
# mkdir mysql-tmp

tmpfs /mysql-tmp tmpfs relatime,nodev,nosuid,noexec,uid=mysql,gid=mysql,mode=0760,size=512M 0 0

# mount /mysql-tmp

tmpdir = /mysql-tmp

# systemctl restart mysql

Značky: #linux, #mysql, #debian, #Linux