|
MySQL.RU - Webboard
 Вернуться
 Mysql грузит диски (Vladimir) 27/02/2017 - 14:13:36
 Re: Mysql грузит диски (Alex) 28/02/2017 - 20:01:44
> Original message text:
> From: Vladimir - 27/02/2017 - 14:13:36
> Subject:Mysql грузит диски
> -----------------
> Друзья, помогите пожалуйста с mysql, который грузит диски а я не могу найти причину. Лог с mysqltuner
>
> >> MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>
> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
> >> Run with '--help' for additional options and output filtering
>
> [--] Skipped version check for MySQLTuner script
> [OK] Logged in using credentials from debian maintenance account.
> [OK] Currently running supported MySQL version 5.5.54-0+deb8u1
> [OK] Operating on 64-bit architecture
>
> -------- Log file Recommendations ------------------------------------------------------------------
> [--] Log file: /var/log/mysql.log(0B)
> [OK] Log file /var/log/mysql.log exists
> [OK] Log file /var/log/mysql.log is readable.
> [!!] Log file /var/log/mysql.log is empty
> [OK] Log file /var/log/mysql.log is smaller than 32 Mb
> [OK] /var/log/mysql.log doesn't contain any warning.
> [OK] /var/log/mysql.log doesn't contain any error.
> [--] 0 start(s) detected in /var/log/mysql.log
> [--] 0 shutdown(s) detected in /var/log/mysql.log
>
> -------- Storage Engine Statistics -----------------------------------------------------------------
> [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
> [--] Data in InnoDB tables: 31G (Tables: 779)
> [--] Data in MyISAM tables: 6G (Tables: 2731)
> [OK] Total fragmented tables: 0
>
> -------- Security Recommendations ------------------------------------------------------------------
> [OK] There are no anonymous accounts for any database users
> [OK] All database users have passwords assigned
> [!!] There is no basic password file list!
>
> -------- CVE Security Recommendations --------------------------------------------------------------
> [--] Skipped due to --cvefile option undefined
>
> -------- Performance Metrics -----------------------------------------------------------------------
> [--] Up for: 15h 30m 8s (11M q [197.767 qps], 415K conn, TX: 154G, RX: 1G)
> [--] Reads / Writes: 94% / 6%
> [--] Binary logging is disabled
> [--] Physical Memory : 31.4G
> [--] Max MySQL memory : 15.1G
> [--] Other process memory: 1.6G
> [--] Total buffers: 9.4G global + 14.6M per thread (400 max threads)
> [--] P_S Max memory usage: 0B
> [--] Galera GCache Max memory usage: 0B
> [OK] Maximum reached memory usage: 13.1G (41.68% of installed RAM)
> [OK] Maximum possible memory usage: 15.1G (48.02% of installed RAM)
> [OK] Overall possible memory usage with other process is compatible with memory available
> [OK] Slow queries: 0% (847/11M)
> [OK] Highest usage of available connections: 65% (260/400)
> [OK] Aborted connections: 0.15% (607/415133)
> [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
> [OK] Sorts requiring temporary tables: 1% (29K temp sorts / 2M sorts)
> [!!] Joins performed without indexes: 57016
> [!!] Temporary tables created on disk: 59% (442K on disk / 744K total)
> [OK] Thread cache hit rate: 99% (774 created / 415K connections)
> [OK] Table cache hit rate: 42% (3K open / 9K opened)
> [OK] Open file limit used: 46% (4K/10K)
> [OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
>
> -------- Performance schema ------------------------------------------------------------------------
> [--] Performance schema is disabled.
> [--] Memory used by P_S: 0B
> [--] Sys schema isn't installed.
>
> -------- ThreadPool Metrics ------------------------------------------------------------------------
> [--] ThreadPool stat is disabled.
>
> -------- MyISAM Metrics ----------------------------------------------------------------------------
> [!!] Key buffer used: 44.9% (482M used / 1B cache)
> [OK] Key buffer size / total MyISAM indexes: 1.0G/833.4M
> [OK] Read Key buffer hit rate: 100.0% (17B cached / 740K reads)
> [OK] Write Key buffer hit rate: 100.0% (1B cached / 463K writes)
>
> -------- InnoDB Metrics ----------------------------------------------------------------------------
> [--] InnoDB is enabled.
> [--] InnoDB Thread Concurrency: 0
> [OK] InnoDB File per table is activated
> [!!] InnoDB buffer pool / data size: 8.0G/31.4G
> [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (6.25 %): 256.0M * 2/8.0G should be equal 25%
> [OK] InnoDB buffer pool instances: 8
> [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
> [OK] InnoDB Read buffer efficiency: 99.99% (4658719402 hits/ 4659064688 total)
> [OK] InnoDB Write log efficiency: 99.75% (17418093 hits/ 17462159 total)
> [OK] InnoDB log waits: 0.00% (0 waits / 44066 writes)
>
> -------- AriaDB Metrics ----------------------------------------------------------------------------
> [--] AriaDB is disabled.
>
> -------- TokuDB Metrics ----------------------------------------------------------------------------
> [--] TokuDB is disabled.
>
> -------- XtraDB Metrics ----------------------------------------------------------------------------
> [--] XtraDB is disabled.
>
> -------- RocksDB Metrics ---------------------------------------------------------------------------
> [--] RocksDB is disabled.
>
> -------- Spider Metrics ----------------------------------------------------------------------------
> [--] Spider is disabled.
>
> -------- Connect Metrics ---------------------------------------------------------------------------
> [--] Connect is disabled.
>
> -------- Galera Metrics ----------------------------------------------------------------------------
> [--] Galera is disabled.
>
> -------- Replication Metrics -----------------------------------------------------------------------
> [--] Galera Synchronous replication: NO
> [--] No replication slave(s) for this server.
> [--] This is a standalone server.
>
> -------- Recommendations ---------------------------------------------------------------------------
> General recommendations:
> MySQL started within last 24 hours - recommendations may be inaccurate
> 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
> Performance should be activated for better diagnostics
> Consider installing Sys schema from https://github.com/mysql/mysql-sys
> Variables to adjust:
> join_buffer_size (> 12.0M, or always use indexes with joins)
> performance_schema = ON enable PFS
> innodb_buffer_pool_size (>= 31G) if possible.
> innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=4G) if possible.
>
>
> Содержимое my.cnf
>
> #
> # The MySQL database server configuration file.
> #
> # You can copy this to one of:
> # - "/etc/mysql/my.cnf" to set global options,
> # - "~/.my.cnf" to set user-specific options.
> #
> # One can use all long options that the program supports.
> # Run program with --help to get a list of available options and with
> # --print-defaults to see which it would actually understand and use.
> #
> # For explanations see
> # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>
> # This will be passed to all mysql clients
> # It has been reported that passwords should be enclosed with ticks/quotes
> # escpecially if they contain "#" chars...
> # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
> [client]
> port = 3306
> socket = /var/run/mysqld/mysqld.sock
> default-character-set = utf8
>
> # Here is entries for some specific programs
> # The following values assume you have at least 32M ram
>
> # This was formally known as [safe_mysqld]. Both versions are currently parsed.
> [mysqld_safe]
> log-error=/var/log/mysql.log
> socket = /var/run/mysqld/mysqld.sock
> nice = 0
>
> [mysqld]
> local-infile=0
> innodb_file_per_table = 1
> #
> # * Basic Settings
> #
> user = mysql
> pid-file = /var/run/mysqld/mysqld.pid
> socket = /var/run/mysqld/mysqld.sock
> port = 3306
> basedir = /usr
> datadir = /var/lib/mysql
> tmpdir = /dev/shm
> lc-messages-dir = /usr/share/mysql
> skip-external-locking
> memlock
> #skip-name-resolve
> #
> # Instead of skip-networking the default is now to listen only on
> # localhost which is more compatible and is not less secure.
> bind-address = localhost
> #
> # * Fine Tuning
> #
> key_buffer = 1024M
> #sort_buffer_size = 16M
> max_allowed_packet = 16M
> thread_stack = 192K
>
> # This replaces the startup script and checks MyISAM tables if needed
> # the first time they are touched
> myisam-recover = BACKUP
> max_connections = 400
> open_files_limit = 10192
> join_buffer_size = 12M
> interactive_timeout = 900
> wait_timeout = 900
> #table_cache = 64
> #thread_concurrency = 24
> #
> # * Query Cache Configuration
> #
> query_cache_limit = 2M
> query_cache_size = 128M
> query_cache_type = 0
> table_open_cache = 4096
> thread_cache_size = 32
>
> #
> # * Logging and Replication
> #
> # Both location gets rotated by the cronjob.
> # Be aware that this log type is a performance killer.
> # As of 5.1 you can enable the log at runtime!
> #general_log_file = /var/log/mysql/mysql.log
> #general_log = 1
> #
> # Error log - should be very few entries.
> #
> log_error = /var/log/mysql/error.log
> #
> # Here you can see queries with especially long duration
> #slow_query_log_file = /var/log/mysql/mysql-slow.log
> #slow_query_log = 1
> #long_query_time = 2
> #log_queries_not_using_indexes
> #
> # The following can be used as easy to replay backup logs or for replication.
> # note: if you are setting up a replication slave, see README.Debian about
> # other settings you may need to change.
> #server-id = 1
> #log_bin = /var/log/mysql/mysql-bin.log
> expire_logs_days = 10
> max_binlog_size = 100M
> #binlog_do_db = include_database_name
> #binlog_ignore_db = include_database_name
> #
> # * InnoDB
> #
> # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
> # Read the manual for more InnoDB related options. There are many!
> #
> # * Security Features
> #
> # Read the manual, too, if you want chroot!
> # chroot = /var/lib/mysql/
> #
> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
> #
> # ssl-ca=/etc/mysql/cacert.pem
> # ssl-cert=/etc/mysql/server-cert.pem
> # ssl-key=/etc/mysql/server-key.pem
>
> tmp_table_size = 256M
> max_heap_table_size = 256M
>
> innodb_buffer_pool_size = 8G
> innodb_buffer_pool_instances = 8
> innodb_log_file_size = 256M
>
> #innodb_io_capacity = 2000
> innodb_read_io_threads = 64
> innodb_thread_concurrency = 0
> innodb_write_io_threads = 64
>
> [mysqldump]
> quick
> quote-names
> max_allowed_packet = 16M
>
> [mysql]
> #no-auto-rehash # faster start of mysql but no tab completition
>
> [isamchk]
> # key_buffer = 16M duplikat vverhu est takoi ze
>
> #
> # * IMPORTANT: Additional settings that can override those from this file!
> # The files must end with '.cnf', otherwise they'll be ignored.
> #
> !includedir /etc/mysql/conf.d/
>
From: Alex - 28/02/2017 - 20:01:44
Subject:Mysql грузит диски
-----------------
Какая операционка? Диски какие? Есть ли RAID?
[Это сообщение - спам!]
Последние сообщения из форума
Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL,
версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
45165
|
|
