Re: InnoDB+UFS+SSD
Sergey Anohin написал(а) к Andrey Melnikoff в Jul 21 13:35:16 по местному времени:
Нello, 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 specialsmallblocks 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 specialsmallblocks 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 specialsmallblocks 0 default
# cat /usr/local/etc/my.cnf
[mysqld]
#innodbforcerecovery=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
threadcachesize = 24
threadpoolsize = 2
querycachetype = 1
querycachesize = 64M
querycachelimit = 4M
max_connections = 80
keybuffersize = 1M
maxallowedpacket = 128M
table_cache = 4096
innodbbuffer_poolsize = 2G
innodbbuffer_poolinstances = 2
innodbfile_pertable = 1
innodblog_filesize = 256M
innodbdata_homedir=/var/db/mysql/ibdata
innodblog_group_homedir = /var/db/mysql/iblogs
innodbflush_method = ODIRECT
skip-innodb_doublewrite
#ZFS
sync_binlog = 0
innodbflush_log_at_trxcommit = 0
innodb_doublewrite=0
innodblogchecksums=OFF
innodbchecksumalgorithm=none
innodbuse_nativeaio = 0
innodblog_write_aheadsize=16384
ariapagecache_buffersize = 48M
sortbuffersize = 4M
joinbuffersize = 2M
tmptablesize = 256M
maxheap_tablesize = 256M
netbufferlength = 16K
readbuffersize = 512K
readrnd_buffersize = 1M
autoincrementoffset = 1
autoincrementincrement = 1
server-id = 1
character-set-server = utf8
wait_timeout = 28800
skip-character-set-client-handshake
charactersetserver=utf8
collationserver = utf8_unicodeci
initconnect='SET NAMES utf8 collate utf8_unicodeci
init_connect='SET NAMES utf8'
longquerytime = 10
back_log = 120
slowquerylog=1
slowquery_logfile=/var/log/mysql/slow.log
log_error = /var/log/mysql/error.log
general_log=0
generallogfile = /var/log/mysql/query.log
sql_mode =
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
[mysqldump]
quick
default-character-set = utf8
maxallowedpacket = 1G
[mysql]
no-auto-rehash
default-character-set = utf8
[myisamchk]
keybuffersize = 30M
sortbuffersize = 20M
readbuffersize = 2M
writebuffersize = 2M
[mysqlhotcopy]
interactive-timeout
[safe_mysqld]
err-log=/var/log/mysql/mysqld.log
С наилучшими пожеланиями, Sergey Anohin.
--- wfido
|