On Mon, Jan 12, 2015 at 01:43:05AM +0200, Vladislav V. Prodan wrote:
> Еще покажите zfs-stats -a
приложил к письму
> Поднимаем Key buffer size до размеров индексов или чуть больше
поднимал до 1G, время уменшилось до (1 min 34.91 sec)
> innodb_buffer_pool_size (>= 239M)
ну это только недавно заметил, но это имеет отношение только к innodb, а
не к myisam
> Число коннектов понижаем до 150-180
> Размер query_cache понижаем на 15%
> И вдумчиво читаем рекомендации по тюнингу ZFS для Mysql, в частности
> небольшое HOWTO:
ранее писал:
раздел создаю командой
zfs create -o atime=off -o exec=off -o setuid=off -o recordsize=8K -o
primarycache=metadata -o mountpoint=/mnt/zfs mysql/mysql
> zfs create -o atime=off tank/mysql
установлено
> zfs set mountpoint=/var/db/mysql tank/mysql
> zfs create -o recordsize=16k tank/mysql/ibdata
> zfs create -o recordsize=128k tank/mysql/iblogs
recordsize=16k,128k - это опять же для innodb, для myisam рекомендуют 8k
> zfs set primarycache=metadata tank/mysql/ibdata
установлено
> ln -s /var/db/mysql /mysql
> --> my.cnf
> innodb_flush_method = O_DIRECT
> skip-innodb_doublewrite
> innodb_data_home_dir=/var/db/mysql/ibdata
> innodb_log_group_home_dir=/var/db/mysql/iblogs
> innodb_data_file_path=ibdata1:10M:autoextend
это к innodb относится
> <--
> --> /boot/loader.conf
> vfs.zfs.prefetch_disable=1
установлено
> Эти параметры/команды выставлять/выполнять до старта mysql сервера и до
> заливки баз.
да - это все настраивалось до старта mysql
конкретно под innodb не тюнал, recordsize оставил 8k
прописал
innodb_flush_method = O_DIRECT
skip-innodb_doublewrite
innodb_buffer_pool_size =1G
innodb_file_per_table
конвертнул базу - время выполнения составило (42.53 sec)
но на innodb полностью не могу перейти, так как есть базы с fullsearch.
>--
> Vladislav V. Prodan
> System & Network Administrator
> [5]support.od.ua
>
> Посилання
>
> 1. mailto:[email protected]
> 2. mailto:[email protected]
> 3. http://mysqltuner.pl/
> 4. http://bit.ly/1mi7c4C
> 5. http://support.od.ua/
------------------------------------------------------------------------
ZFS Subsystem Report Mon Jan 12 02:52:35 2015
------------------------------------------------------------------------
System Information:
Kernel Version: 1001000 (osreldate)
Hardware Platform: amd64
Processor Architecture: amd64
ZFS Storage pool Version: 5000
ZFS Filesystem Version: 5
FreeBSD 10.1-RELEASE #3 r274675: Thu Dec 18 10:02:09 EET 2014 root
2:52 up 23:39, 7 users, load averages: 0,29 0,20 0,17
------------------------------------------------------------------------
System Memory:
4.77% 184.98 MiB Active, 49.80% 1.89 GiB Inact
43.26% 1.64 GiB Wired, 0.44% 17.00 MiB Cache
1.74% 67.41 MiB Free, 0.00% 4.00 KiB Gap
Real Installed: 4.00 GiB
Real Available: 97.57% 3.90 GiB
Real Managed: 97.08% 3.79 GiB
Logical Total: 4.00 GiB
Logical Used: 50.77% 2.03 GiB
Logical Free: 49.23% 1.97 GiB
Kernel Memory: 152.24 MiB
Data: 82.00% 124.84 MiB
Text: 18.00% 27.40 MiB
Kernel Memory Map: 3.79 GiB
Size: 23.83% 924.68 MiB
Free: 76.17% 2.89 GiB
------------------------------------------------------------------------
ARC Summary: (HEALTHY)
Memory Throttle Count: 0
ARC Misc:
Deleted: 41.79k
Recycle Misses: 19.25k
Mutex Misses: 2
Evict Skips: 31.61k
ARC Size: 32.05% 915.32 MiB
Target Size: (Adaptive) 32.06% 915.55 MiB
Min Size (Hard Limit): 12.50% 356.95 MiB
Max Size (High Water): 8:1 2.79 GiB
ARC Size Breakdown:
Recently Used Cache Size: 72.92% 667.63 MiB
Frequently Used Cache Size: 27.08% 247.92 MiB
ARC Hash Breakdown:
Elements Max: 35.31k
Elements Current: 73.38% 25.91k
Collisions: 772.64k
Chain Max: 4
Chains: 762
------------------------------------------------------------------------
ARC Efficiency: 26.57m
Cache Hit Ratio: 53.67% 14.26m
Cache Miss Ratio: 46.33% 12.31m
Actual Hit Ratio: 53.67% 14.26m
Data Demand Efficiency: 31.76% 17.74m
CACHE HITS BY CACHE LIST:
Most Recently Used: 2.19% 312.38k
Most Frequently Used: 97.81% 13.95m
Most Recently Used Ghost: 0.02% 2.26k
Most Frequently Used Ghost: 0.06% 8.70k
CACHE HITS BY DATA TYPE:
Demand Data: 39.51% 5.63m
Prefetch Data: 0.00% 0
Demand Metadata: 60.49% 8.63m
Prefetch Metadata: 0.00% 7
CACHE MISSES BY DATA TYPE:
Demand Data: 98.34% 12.11m
Prefetch Data: 0.00% 0
Demand Metadata: 1.66% 204.08k
Prefetch Metadata: 0.00% 86
------------------------------------------------------------------------
L2ARC is disabled
------------------------------------------------------------------------
------------------------------------------------------------------------
VDEV cache is disabled
------------------------------------------------------------------------
ZFS Tunables (sysctl):
kern.maxusers 585
vm.kmem_size 4068081664
vm.kmem_size_scale 1
vm.kmem_size_min 0
vm.kmem_size_max 1319413950874
vfs.zfs.arc_max 2994339840
vfs.zfs.arc_min 374292480
vfs.zfs.arc_average_blocksize 8192
vfs.zfs.arc_meta_used 167815256
vfs.zfs.arc_meta_limit 748584960
vfs.zfs.l2arc_write_max 8388608
vfs.zfs.l2arc_write_boost 8388608
vfs.zfs.l2arc_headroom 2
vfs.zfs.l2arc_feed_secs 1
vfs.zfs.l2arc_feed_min_ms 200
vfs.zfs.l2arc_noprefetch 1
vfs.zfs.l2arc_feed_again 1
vfs.zfs.l2arc_norw 1
vfs.zfs.anon_size 184832
vfs.zfs.anon_metadata_lsize 0
vfs.zfs.anon_data_lsize 0
vfs.zfs.mru_size 557948416
vfs.zfs.mru_metadata_lsize 8452608
vfs.zfs.mru_data_lsize 503675904
vfs.zfs.mru_ghost_size 401574400
vfs.zfs.mru_ghost_metadata_lsize 10943488
vfs.zfs.mru_ghost_data_lsize 390630912
vfs.zfs.mfu_size 324072448
vfs.zfs.mfu_metadata_lsize 32010752
vfs.zfs.mfu_data_lsize 288158208
vfs.zfs.mfu_ghost_size 557557248
vfs.zfs.mfu_ghost_metadata_lsize 26506240
vfs.zfs.mfu_ghost_data_lsize 531051008
vfs.zfs.l2c_only_size 0
vfs.zfs.dedup.prefetch 1
vfs.zfs.nopwrite_enabled 1
vfs.zfs.mdcomp_disable 0
vfs.zfs.dirty_data_max 419056435
vfs.zfs.dirty_data_max_max 4294967296
vfs.zfs.dirty_data_max_percent 10
vfs.zfs.dirty_data_sync 67108864
vfs.zfs.delay_min_dirty_percent 60
vfs.zfs.delay_scale 500000
vfs.zfs.prefetch_disable 1
vfs.zfs.zfetch.max_streams 8
vfs.zfs.zfetch.min_sec_reap 2
vfs.zfs.zfetch.block_cap 256
vfs.zfs.zfetch.array_rd_sz 1048576
vfs.zfs.top_maxinflight 32
vfs.zfs.resilver_delay 2
vfs.zfs.scrub_delay 4
vfs.zfs.scan_idle 50
vfs.zfs.scan_min_time_ms 1000
vfs.zfs.free_min_time_ms 1000
vfs.zfs.resilver_min_time_ms 3000
vfs.zfs.no_scrub_io 0
vfs.zfs.no_scrub_prefetch 0
vfs.zfs.metaslab.gang_bang 131073
vfs.zfs.metaslab.fragmentation_threshold70
vfs.zfs.metaslab.debug_load 0
vfs.zfs.metaslab.debug_unload 0
vfs.zfs.metaslab.df_alloc_threshold 131072
vfs.zfs.metaslab.df_free_pct 4
vfs.zfs.metaslab.min_alloc_size 10485760
vfs.zfs.metaslab.load_pct 50
vfs.zfs.metaslab.unload_delay 8
vfs.zfs.metaslab.preload_limit 3
vfs.zfs.metaslab.preload_enabled 1
vfs.zfs.metaslab.fragmentation_factor_enabled1
vfs.zfs.metaslab.lba_weighting_enabled 1
vfs.zfs.metaslab.bias_enabled 1
vfs.zfs.condense_pct 200
vfs.zfs.mg_noalloc_threshold 0
vfs.zfs.mg_fragmentation_threshold 85
vfs.zfs.check_hostid 1
vfs.zfs.spa_load_verify_maxinflight 10000
vfs.zfs.spa_load_verify_metadata 1
vfs.zfs.spa_load_verify_data 1
vfs.zfs.recover 0
vfs.zfs.deadman_synctime_ms 1000000
vfs.zfs.deadman_checktime_ms 5000
vfs.zfs.deadman_enabled 1
vfs.zfs.spa_asize_inflation 24
vfs.zfs.txg.timeout 5
vfs.zfs.vdev.cache.max 16384
vfs.zfs.vdev.cache.size 0
vfs.zfs.vdev.cache.bshift 16
vfs.zfs.vdev.trim_on_init 1
vfs.zfs.vdev.mirror.rotating_inc 0
vfs.zfs.vdev.mirror.rotating_seek_inc 5
vfs.zfs.vdev.mirror.rotating_seek_offset1048576
vfs.zfs.vdev.mirror.non_rotating_inc 0
vfs.zfs.vdev.mirror.non_rotating_seek_inc1
vfs.zfs.vdev.max_active 1000
vfs.zfs.vdev.sync_read_min_active 10
vfs.zfs.vdev.sync_read_max_active 10
vfs.zfs.vdev.sync_write_min_active 10
vfs.zfs.vdev.sync_write_max_active 10
vfs.zfs.vdev.async_read_min_active 1
vfs.zfs.vdev.async_read_max_active 3
vfs.zfs.vdev.async_write_min_active 1
vfs.zfs.vdev.async_write_max_active 10
vfs.zfs.vdev.scrub_min_active 1
vfs.zfs.vdev.scrub_max_active 2
vfs.zfs.vdev.trim_min_active 1
vfs.zfs.vdev.trim_max_active 64
vfs.zfs.vdev.aggregation_limit 131072
vfs.zfs.vdev.read_gap_limit 32768
vfs.zfs.vdev.write_gap_limit 4096
vfs.zfs.vdev.bio_flush_disable 0
vfs.zfs.vdev.bio_delete_disable 0
vfs.zfs.vdev.trim_max_bytes 2147483648
vfs.zfs.vdev.trim_max_pending 64
vfs.zfs.max_auto_ashift 13
vfs.zfs.min_auto_ashift 9
vfs.zfs.zil_replay_disable 0
vfs.zfs.cache_flush_disable 0
vfs.zfs.zio.use_uma 1
vfs.zfs.zio.exclude_metadata 0
vfs.zfs.sync_pass_deferred_free 2
vfs.zfs.sync_pass_dont_compress 5
vfs.zfs.sync_pass_rewrite 2
vfs.zfs.snapshot_list_prefetch 0
vfs.zfs.super_owner 0
vfs.zfs.debug 0
vfs.zfs.version.ioctl 4
vfs.zfs.version.acl 1
vfs.zfs.version.spa 5000
vfs.zfs.version.zpl 5
vfs.zfs.vol.mode 1
vfs.zfs.trim.enabled 1
vfs.zfs.trim.txg_delay 32
vfs.zfs.trim.timeout 30
vfs.zfs.trim.max_interval 1
------------------------------------------------------------------------
