Добро пожаловать, Гость. Пожалуйста авторизуйтесь здесь.
FGHIGate на GaNJa NeTWoRK ST@Ti0N - Просмотр сообщения в эхоконференции RU.UNIX.BSD
Введите FGHI ссылку:


Присутствуют сообщения из эхоконференции RU.UNIX.BSD с датами от 18 Jan 11 22:51:00 до 18 Jan 24 18:16:22, всего сообщений: 10753
Ответить на сообщение К списку сообщений Предыдущее сообщение Следующее сообщение
= Сообщение: 10257 из 10753 ==================================== RU.UNIX.BSD =
От   : Sergey Anohin                    2:5034/10.1        25 Jul 21 13:35:16
Кому : Andrey Melnikoff                                    25 Jul 21 13:35:16
Тема : Re: InnoDB+UFS+SSD
FGHI : area://RU.UNIX.BSD?msgid=2:5034/10.1+c9e4d4fb
На   : area://RU.UNIX.BSD?msgid=<1187515102@banana.localnet>+71dfa87e
= Кодировка сообщения определена как: CP866 ==================================
==============================================================================
Hello, Andrey!

>> Для фидо пойдет.
AM> туда и sqlite3 пойдет.

Там что угодно пойдет, если ты умеешь на php кодить, чтобы сделать обертку и прикрутить
какой-нить pdo, хотя как бы сейчас и используется php-mysqli

>> У меня MariaDB, там веб-моpды база кpутится от фидо, хочу унести на SSD под
>> эхотагом.
AM> Я бы понял, если бы ты какой говнозаббикс крутил - там дааа, тюнить надо,
AM> даже под SSD. А для фидо - всё в aria сконвертить и потянет, без тормоза innodb.

База у меня торчит на ZFS, SSD под кеш ZFS.
Ну некоторые таблицы я перевел на Aria, но самые огромные оставил InnoDB, база большая ~20гиг.
Вообще кстати, эксперимент с кешем SSD думаю прошел удачно. Раньше первый раз когда заходило на веб морду,
тупило около минуты наверно, может больше. Через несколько дней работы ZFS плюс SSD кеш, работает почти моментально.
Это все визуально на глаз, не подтверждено измерениями. Настройки ковырял и в MariaDB и в ZFS.
Будет веселее на 13м эхотаге, когда кеш ZFS не будет обнуляться при ребуте, но походу еще рано на 13ую уходить?

Настройки:
# zfs get all zroot/var/db/mysql
NAME                PROPERTY              VALUE                          SOURCE
zroot/var/db/mysql  type                  filesystem                     -
zroot/var/db/mysql  creation              пт марта 16 22:18 2018  -
zroot/var/db/mysql  used                  19,9G                          -
zroot/var/db/mysql  available             1,31T                          -
zroot/var/db/mysql  referenced            19,4G                          -
zroot/var/db/mysql  compressratio         1.51x                          -
zroot/var/db/mysql  mounted               yes                            -
zroot/var/db/mysql  quota                 none                           default
zroot/var/db/mysql  reservation           none                           default
zroot/var/db/mysql  recordsize            16K                            local
zroot/var/db/mysql  mountpoint            /var/db/mysql                  inherited from zroot/var
zroot/var/db/mysql  sharenfs              off                            default
zroot/var/db/mysql  checksum              fletcher4                      inherited from zroot
zroot/var/db/mysql  compression           lz4                            local
zroot/var/db/mysql  atime                 off                            local
zroot/var/db/mysql  devices               on                             default
zroot/var/db/mysql  exec                  off                            inherited from zroot/var/db
zroot/var/db/mysql  setuid                off                            inherited from zroot/var/db
zroot/var/db/mysql  readonly              off                            default
zroot/var/db/mysql  jailed                off                            default
zroot/var/db/mysql  snapdir               hidden                         default
zroot/var/db/mysql  aclmode               discard                        default
zroot/var/db/mysql  aclinherit            restricted                     default
zroot/var/db/mysql  createtxg             11403386                       -
zroot/var/db/mysql  canmount              on                             default
zroot/var/db/mysql  xattr                 off                            temporary
zroot/var/db/mysql  copies                1                              default
zroot/var/db/mysql  version               5                              -
zroot/var/db/mysql  utf8only              off                            -
zroot/var/db/mysql  normalization         none                           -
zroot/var/db/mysql  casesensitivity       sensitive                      -
zroot/var/db/mysql  vscan                 off                            default
zroot/var/db/mysql  nbmand                off                            default
zroot/var/db/mysql  sharesmb              off                            default
zroot/var/db/mysql  refquota              none                           default
zroot/var/db/mysql  refreservation        none                           default
zroot/var/db/mysql  guid                  13227566777127831999           -
zroot/var/db/mysql  primarycache          metadata                       local
zroot/var/db/mysql  secondarycache        all                            local
zroot/var/db/mysql  usedbysnapshots       0                              -
zroot/var/db/mysql  usedbydataset         19,4G                          -
zroot/var/db/mysql  usedbychildren        478M                           -
zroot/var/db/mysql  usedbyrefreservation  0                              -
zroot/var/db/mysql  logbias               throughput                     local
zroot/var/db/mysql  objsetid              257                            -
zroot/var/db/mysql  dedup                 off                            default
zroot/var/db/mysql  mlslabel                                             -
zroot/var/db/mysql  sync                  disabled                       local
zroot/var/db/mysql  dnodesize             legacy                         default
zroot/var/db/mysql  refcompressratio      1.52x                          -
zroot/var/db/mysql  written               19,4G                          -
zroot/var/db/mysql  logicalused           29,9G                          -
zroot/var/db/mysql  logicalreferenced     29,4G                          -
zroot/var/db/mysql  volmode               default                        default
zroot/var/db/mysql  filesystem_limit      none                           default
zroot/var/db/mysql  snapshot_limit        none                           default
zroot/var/db/mysql  filesystem_count      none                           default
zroot/var/db/mysql  snapshot_count        none                           default
zroot/var/db/mysql  redundant_metadata    all                            default
zroot/var/db/mysql  special_small_blocks  0                              default

# zfs get all zroot/var/db/mysql/ibdata
NAME                       PROPERTY              VALUE                          SOURCE
zroot/var/db/mysql/ibdata  type                  filesystem                     -
zroot/var/db/mysql/ibdata  creation              пт марта 16 22:20 2018  -
zroot/var/db/mysql/ibdata  used                  4,89M                          -
zroot/var/db/mysql/ibdata  available             1,31T                          -
zroot/var/db/mysql/ibdata  referenced            4,89M                          -
zroot/var/db/mysql/ibdata  compressratio         3.74x                          -
zroot/var/db/mysql/ibdata  mounted               yes                            -
zroot/var/db/mysql/ibdata  quota                 none                           default
zroot/var/db/mysql/ibdata  reservation           none                           default
zroot/var/db/mysql/ibdata  recordsize            16K                            local
zroot/var/db/mysql/ibdata  mountpoint            /var/db/mysql/ibdata           inherited from zroot/var
zroot/var/db/mysql/ibdata  sharenfs              off                            default
zroot/var/db/mysql/ibdata  checksum              fletcher4                      inherited from zroot
zroot/var/db/mysql/ibdata  compression           lz4                            inherited from zroot/var/db/mysql
zroot/var/db/mysql/ibdata  atime                 off                            inherited from zroot/var/db/mysql
zroot/var/db/mysql/ibdata  devices               on                             default
zroot/var/db/mysql/ibdata  exec                  off                            inherited from zroot/var/db
zroot/var/db/mysql/ibdata  setuid                off                            inherited from zroot/var/db
zroot/var/db/mysql/ibdata  readonly              off                            default
zroot/var/db/mysql/ibdata  jailed                off                            default
zroot/var/db/mysql/ibdata  snapdir               hidden                         default
zroot/var/db/mysql/ibdata  aclmode               discard                        default
zroot/var/db/mysql/ibdata  aclinherit            restricted                     default
zroot/var/db/mysql/ibdata  createtxg             11403406                       -
zroot/var/db/mysql/ibdata  canmount              on                             default
zroot/var/db/mysql/ibdata  xattr                 off                            temporary
zroot/var/db/mysql/ibdata  copies                1                              default
zroot/var/db/mysql/ibdata  version               5                              -
zroot/var/db/mysql/ibdata  utf8only              off                            -
zroot/var/db/mysql/ibdata  normalization         none                           -
zroot/var/db/mysql/ibdata  casesensitivity       sensitive                      -
zroot/var/db/mysql/ibdata  vscan                 off                            default
zroot/var/db/mysql/ibdata  nbmand                off                            default
zroot/var/db/mysql/ibdata  sharesmb              off                            default
zroot/var/db/mysql/ibdata  refquota              none                           default
zroot/var/db/mysql/ibdata  refreservation        none                           default
zroot/var/db/mysql/ibdata  guid                  12007562024988368572           -
zroot/var/db/mysql/ibdata  primarycache          metadata                       inherited from zroot/var/db/mysql
zroot/var/db/mysql/ibdata  secondarycache        all                            inherited from zroot/var/db/mysql
zroot/var/db/mysql/ibdata  usedbysnapshots       0                              -
zroot/var/db/mysql/ibdata  usedbydataset         4,89M                          -
zroot/var/db/mysql/ibdata  usedbychildren        0                              -
zroot/var/db/mysql/ibdata  usedbyrefreservation  0                              -
zroot/var/db/mysql/ibdata  logbias               throughput                     inherited from zroot/var/db/mysql
zroot/var/db/mysql/ibdata  objsetid              263                            -
zroot/var/db/mysql/ibdata  dedup                 off                            default
zroot/var/db/mysql/ibdata  mlslabel                                             -
zroot/var/db/mysql/ibdata  sync                  disabled                       local
zroot/var/db/mysql/ibdata  dnodesize             legacy                         default
zroot/var/db/mysql/ibdata  refcompressratio      3.74x                          -
zroot/var/db/mysql/ibdata  written               4,89M                          -
zroot/var/db/mysql/ibdata  logicalused           17,9M                          -
zroot/var/db/mysql/ibdata  logicalreferenced     17,9M                          -
zroot/var/db/mysql/ibdata  volmode               default                        default
zroot/var/db/mysql/ibdata  filesystem_limit      none                           default
zroot/var/db/mysql/ibdata  snapshot_limit        none                           default
zroot/var/db/mysql/ibdata  filesystem_count      none                           default
zroot/var/db/mysql/ibdata  snapshot_count        none                           default
zroot/var/db/mysql/ibdata  redundant_metadata    all                            default
zroot/var/db/mysql/ibdata  special_small_blocks  0                              default

# zfs get all zroot/var/db/mysql/iblogs
NAME                       PROPERTY              VALUE                          SOURCE
zroot/var/db/mysql/iblogs  type                  filesystem                     -
zroot/var/db/mysql/iblogs  creation              пт марта 16 22:20 2018  -
zroot/var/db/mysql/iblogs  used                  474M                           -
zroot/var/db/mysql/iblogs  available             1,31T                          -
zroot/var/db/mysql/iblogs  referenced            474M                           -
zroot/var/db/mysql/iblogs  compressratio         1.08x                          -
zroot/var/db/mysql/iblogs  mounted               yes                            -
zroot/var/db/mysql/iblogs  quota                 none                           default
zroot/var/db/mysql/iblogs  reservation           none                           default
zroot/var/db/mysql/iblogs  recordsize            128K                           local
zroot/var/db/mysql/iblogs  mountpoint            /var/db/mysql/iblogs           inherited from zroot/var
zroot/var/db/mysql/iblogs  sharenfs              off                            default
zroot/var/db/mysql/iblogs  checksum              fletcher4                      inherited from zroot
zroot/var/db/mysql/iblogs  compression           lz4                            inherited from zroot/var/db/mysql
zroot/var/db/mysql/iblogs  atime                 off                            inherited from zroot/var/db/mysql
zroot/var/db/mysql/iblogs  devices               on                             default
zroot/var/db/mysql/iblogs  exec                  off                            inherited from zroot/var/db
zroot/var/db/mysql/iblogs  setuid                off                            inherited from zroot/var/db
zroot/var/db/mysql/iblogs  readonly              off                            default
zroot/var/db/mysql/iblogs  jailed                off                            default
zroot/var/db/mysql/iblogs  snapdir               hidden                         default
zroot/var/db/mysql/iblogs  aclmode               discard                        default
zroot/var/db/mysql/iblogs  aclinherit            restricted                     default
zroot/var/db/mysql/iblogs  createtxg             11403407                       -
zroot/var/db/mysql/iblogs  canmount              on                             default
zroot/var/db/mysql/iblogs  xattr                 off                            temporary
zroot/var/db/mysql/iblogs  copies                1                              default
zroot/var/db/mysql/iblogs  version               5                              -
zroot/var/db/mysql/iblogs  utf8only              off                            -
zroot/var/db/mysql/iblogs  normalization         none                           -
zroot/var/db/mysql/iblogs  casesensitivity       sensitive                      -
zroot/var/db/mysql/iblogs  vscan                 off                            default
zroot/var/db/mysql/iblogs  nbmand                off                            default
zroot/var/db/mysql/iblogs  sharesmb              off                            default
zroot/var/db/mysql/iblogs  refquota              none                           default
zroot/var/db/mysql/iblogs  refreservation        none                           default
zroot/var/db/mysql/iblogs  guid                  2433669013503756839            -
zroot/var/db/mysql/iblogs  primarycache          metadata                       inherited from zroot/var/db/mysql
zroot/var/db/mysql/iblogs  secondarycache        all                            inherited from zroot/var/db/mysql
zroot/var/db/mysql/iblogs  usedbysnapshots       0                              -
zroot/var/db/mysql/iblogs  usedbydataset         474M                           -
zroot/var/db/mysql/iblogs  usedbychildren        0                              -
zroot/var/db/mysql/iblogs  usedbyrefreservation  0                              -
zroot/var/db/mysql/iblogs  logbias               latency                        local
zroot/var/db/mysql/iblogs  objsetid              269                            -
zroot/var/db/mysql/iblogs  dedup                 off                            default
zroot/var/db/mysql/iblogs  mlslabel                                             -
zroot/var/db/mysql/iblogs  sync                  disabled                       local
zroot/var/db/mysql/iblogs  dnodesize             legacy                         default
zroot/var/db/mysql/iblogs  refcompressratio      1.08x                          -
zroot/var/db/mysql/iblogs  written               474M                           -
zroot/var/db/mysql/iblogs  logicalused           512M                           -
zroot/var/db/mysql/iblogs  logicalreferenced     512M                           -
zroot/var/db/mysql/iblogs  volmode               default                        default
zroot/var/db/mysql/iblogs  filesystem_limit      none                           default
zroot/var/db/mysql/iblogs  snapshot_limit        none                           default
zroot/var/db/mysql/iblogs  filesystem_count      none                           default
zroot/var/db/mysql/iblogs  snapshot_count        none                           default
zroot/var/db/mysql/iblogs  redundant_metadata    all                            default
zroot/var/db/mysql/iblogs  special_small_blocks  0                              default

# cat /usr/local/etc/my.cnf
[mysqld]

#innodb_force_recovery=6
default-time-zone = "+03:00"

performance_schema=OFF
datadir = /var/db/mysql
basedir = /usr/local
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
skip-name-resolve

thread_cache_size = 24
thread_pool_size = 2

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M

max_connections = 80
key_buffer_size = 1M
max_allowed_packet = 128M
table_cache = 4096

innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_file_per_table = 1
innodb_log_file_size = 256M

innodb_data_home_dir=/var/db/mysql/ibdata
innodb_log_group_home_dir = /var/db/mysql/iblogs
innodb_flush_method = O_DIRECT
skip-innodb_doublewrite

#ZFS
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite=0
innodb_log_checksums=OFF
innodb_checksum_algorithm=none
innodb_use_native_aio = 0
innodb_log_write_ahead_size=16384

aria_pagecache_buffer_size = 48M
sort_buffer_size = 4M
join_buffer_size = 2M
tmp_table_size = 256M
max_heap_table_size = 256M
net_buffer_length = 16K
read_buffer_size = 512K
read_rnd_buffer_size = 1M
auto_increment_offset = 1
auto_increment_increment = 1
server-id       = 1
character-set-server = utf8
wait_timeout = 28800
skip-character-set-client-handshake
character_set_server=utf8
collation_server = utf8_unicode_ci
init_connect='SET NAMES utf8 collate utf8_unicode_ci
init_connect='SET NAMES utf8'

long_query_time = 10
back_log = 120
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
log_error = /var/log/mysql/error.log
general_log=0
general_log_file = /var/log/mysql/query.log

sql_mode =

[client]
port            = 3306
socket          = /tmp/mysql.sock
default-character-set=utf8

[mysqldump]
quick
default-character-set = utf8
max_allowed_packet = 1G

[mysql]
no-auto-rehash
default-character-set = utf8

[myisamchk]
key_buffer_size = 30M
sort_buffer_size = 20M
read_buffer_size = 2M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout

[safe_mysqld]
err-log=/var/log/mysql/mysqld.log




С наилучшими пожеланиями, Sergey Anohin.

--- wfido
* Origin: https://5034.ru/wfido (2:5034/10.1)

К главной странице гейта
Powered by NoSFeRaTU`s FGHIGate
Открытие страницы: 0.081161 секунды