- Предыдущая
- 1
- 2
- 3
- 4
- Показаны 46-56 из 56
#46
19 декабря 2017 в 10:04
пошукайте количество файлов открываемых mysql, а то весь тюнинг в одном месте будет если у вас лимит по открытым файлам будет достигаться 9а он в дефолте маленький стоит), еще зависит от типа носителя.(ssd)
#47
19 декабря 2017 в 11:07
gzip_buffers 8 64k; посмотрите а nginx у вас скомпилён с с этим модулем.
как посмотреть?
#48
19 декабря 2017 в 11:12
пошукайте количество файлов открываемых mysql, а то весь тюнинг в одном месте будет если у вас лимит по открытым файлам будет достигаться 9а он в дефолте маленький стоит), еще зависит от типа носителя.(ssd)
Как его в centos7 изменить?
поиск результатов не дает!
а ssd пока не ставили, зато вынесли на RAID массив на два быстых диска. Сенос помогает правильно работать с рейд
и скорость закачки/скачки сейчас 90-130 мегабайт в секунду (средняя)
Сегодня в 13:57
#49
19 декабря 2017 в 11:24
#50
19 декабря 2017 в 20:38
не совсем,вот, нашел это оно?
Сделал
lsof -u mysql | wc -l
ответ например 1700
Значит в my.cnf
open_files_limit должно быть больше этого значения, а в свою очередь в лимитах должно быть намного больше чем в open_files_limit
Короче в my.cnf
open_files_limit = 102400
а в limits.conf
* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240
Примерно так.
#51
20 декабря 2017 в 07:01
lsof -u mysql | wc -l
[krevetka@localhost ~]# lsof -u mysql | wc -l
lsof: WARNING: can't stat() fuse.gvfsd-fuse file system /run/user/1008/gvfs
Output information may be incomplete.
743
[krevetka@localhost ~]#
#52
21 декабря 2017 в 10:59
Прошло три дня — запустил mysqltuner
>> MySQLTuner 1.6.0 — Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.56-MariaDB
[OK] Operating on 64-bit architecture
— Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 3G (Tables: 972)
[--] Data in InnoDB tables: 10M (Tables: 182)
[!!] Total fragmented tables: 25
— Performance Metrics -------------------------------------------------
[--] Up for: 3d 11h 30m 17s (7M q [23.435 qps], 220K conn, TX: 36B, RX: 1B)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 3.1G global + 4.8M per thread (200 max threads)
[OK] Maximum reached memory usage: 3.2G (41.55% of installed RAM)
[OK] Maximum possible memory usage: 4.0G (52.87% of installed RAM)
[OK] Slow queries: 0% (21/7M)
[OK] Highest usage of available connections: 8% (16/200)
[OK] Aborted connections: 0.01% (27/220447)
[OK] Query cache efficiency: 42.3% (4M cached / 10M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 2% (7K temp sorts / 381K sorts)
[!!] Joins performed without indexes: 3483
[OK] Temporary tables created on disk: 24% (50K on disk / 204K total)
[OK] Thread cache hit rate: 99% (16 created / 220K connections)
[!!] Table cache hit rate: 0% (407 open / 88K opened)
[OK] Open file limit used: 52% (540/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
— MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 20.3% (122M used / 603M cache)
[OK] Key buffer size / total MyISAM indexes: 576.0M/553.3M
[OK] Read Key buffer hit rate: 99.9% (149M cached / 100K reads)
[!!] Write Key buffer hit rate: 45.1% (603K cached / 331K writes)
— InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/10.1M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 19.63% (1608 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (53955220 hits/ 53956769 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2166 writes)
— AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
— Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
— Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 407)
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 407)
По-прежнему беспокоят:
[!!] Joins performed without indexes: 3483
[!!] Table cache hit rate: 0% (407 open / 88K opened)
[!!] Key buffer used: 20.3% (122M used / 603M cache)
[!!] Write Key buffer hit rate: 45.1% (603K cached / 331K writes)
[OK] Query cache efficiency: 42.3% (4M cached / 10M selects)
>> MySQLTuner 1.6.0 — Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.56-MariaDB
[OK] Operating on 64-bit architecture
— Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 3G (Tables: 972)
[--] Data in InnoDB tables: 10M (Tables: 182)
[!!] Total fragmented tables: 25
— Performance Metrics -------------------------------------------------
[--] Up for: 3d 11h 30m 17s (7M q [23.435 qps], 220K conn, TX: 36B, RX: 1B)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 3.1G global + 4.8M per thread (200 max threads)
[OK] Maximum reached memory usage: 3.2G (41.55% of installed RAM)
[OK] Maximum possible memory usage: 4.0G (52.87% of installed RAM)
[OK] Slow queries: 0% (21/7M)
[OK] Highest usage of available connections: 8% (16/200)
[OK] Aborted connections: 0.01% (27/220447)
[OK] Query cache efficiency: 42.3% (4M cached / 10M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 2% (7K temp sorts / 381K sorts)
[!!] Joins performed without indexes: 3483
[OK] Temporary tables created on disk: 24% (50K on disk / 204K total)
[OK] Thread cache hit rate: 99% (16 created / 220K connections)
[!!] Table cache hit rate: 0% (407 open / 88K opened)
[OK] Open file limit used: 52% (540/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
— MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 20.3% (122M used / 603M cache)
[OK] Key buffer size / total MyISAM indexes: 576.0M/553.3M
[OK] Read Key buffer hit rate: 99.9% (149M cached / 100K reads)
[!!] Write Key buffer hit rate: 45.1% (603K cached / 331K writes)
— InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/10.1M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 19.63% (1608 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (53955220 hits/ 53956769 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2166 writes)
— AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
— Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
— Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 407)
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 407)
[!!] Joins performed without indexes: 3483
[!!] Table cache hit rate: 0% (407 open / 88K opened)
[!!] Key buffer used: 20.3% (122M used / 603M cache)
[!!] Write Key buffer hit rate: 45.1% (603K cached / 331K writes)
[OK] Query cache efficiency: 42.3% (4M cached / 10M selects)
#53
21 декабря 2017 в 11:00
table_open_cache (> 407)
#54
27 декабря 2017 в 08:58
Прошло 10 дней с момента оптимизации
— Performance Metrics -------------------------------------------------
[--] Up for: 9d 9h 21m 5s (19M q [23.789 qps], 648K conn, TX: 102B, RX: 2B)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 3.1G global + 4.8M per thread (200 max threads)
[OK] Maximum reached memory usage: 3.2G (41.61% of installed RAM)
[OK] Maximum possible memory usage: 4.0G (52.87% of installed RAM)
[OK] Slow queries: 0% (56/19M)
[OK] Highest usage of available connections: 8% (17/200)
[OK] Aborted connections: 0.01% (34/648837)
[OK] Query cache efficiency: 42.5% (12M cached / 29M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (19K temp sorts / 966K sorts)
[!!] Joins performed without indexes: 8751
[!!] Temporary tables created on disk: 27% (141K on disk / 521K total)
[OK] Thread cache hit rate: 99% (17 created / 648K connections)
[!!] Table cache hit rate: 0% (407 open / 114K opened)
[OK] Open file limit used: 48% (498/1K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
— MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 19.4% (117M used / 603M cache)
[OK] Key buffer size / total MyISAM indexes: 576.0M/603.2M
[OK] Read Key buffer hit rate: 99.9% (338M cached / 450K reads)
[!!] Write Key buffer hit rate: 50.5% (5M cached / 2M writes)
— InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/10.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.62% (1689 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (130496706 hits/ 130498255 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5186 writes)
— AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
— Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
— Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large — reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 407)
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 407)
[--] Up for: 9d 9h 21m 5s (19M q [23.789 qps], 648K conn, TX: 102B, RX: 2B)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 3.1G global + 4.8M per thread (200 max threads)
[OK] Maximum reached memory usage: 3.2G (41.61% of installed RAM)
[OK] Maximum possible memory usage: 4.0G (52.87% of installed RAM)
[OK] Slow queries: 0% (56/19M)
[OK] Highest usage of available connections: 8% (17/200)
[OK] Aborted connections: 0.01% (34/648837)
[OK] Query cache efficiency: 42.5% (12M cached / 29M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (19K temp sorts / 966K sorts)
[!!] Joins performed without indexes: 8751
[!!] Temporary tables created on disk: 27% (141K on disk / 521K total)
[OK] Thread cache hit rate: 99% (17 created / 648K connections)
[!!] Table cache hit rate: 0% (407 open / 114K opened)
[OK] Open file limit used: 48% (498/1K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
— MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 19.4% (117M used / 603M cache)
[OK] Key buffer size / total MyISAM indexes: 576.0M/603.2M
[OK] Read Key buffer hit rate: 99.9% (338M cached / 450K reads)
[!!] Write Key buffer hit rate: 50.5% (5M cached / 2M writes)
— InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/10.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.62% (1689 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (130496706 hits/ 130498255 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5186 writes)
— AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
— Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
— Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large — reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 407)
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 407)
#55
27 декабря 2017 в 09:51
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 407)
Join я же писал сделайте больше. С 8 гигами памяти ставьте около 64-128 мегабайт. Соответственно добавьте table_open_cache на 2000.
#56
27 декабря 2017 в 10:04
Если вам лень искать: _http://artkiev.com/blog/mysql-error-24.htm open_files_limitКак его в centos7 изменить?
- Предыдущая
- 1
- 2
- 3
- 4
- Показаны 46-56 из 56