D
D
Dmitry Sidorenko2016-08-03 14:44:40
MySQL
Dmitry Sidorenko, 2016-08-03 14:44:40

What's wrong with MySQL optimization?

Support replied:
Diagnostics showed that there is no load on the server, but a large number of connections are in a hung state. You need to check your web server and mysql settings.
I installed the tuner and that's what he gave out
. What's wrong here? :)
[[email protected] ~]# perl mysqltuner.pl
>> MySQLTuner 1.6.14 - Major Hayden
>> Bug reports, feature requests, and downloads at mysqltuner.com
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supporte d MySQL version 5.5.44-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------- -------------------------------------------------------- ------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYIS AM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 3M (Tables: 15)
[- -] Data in InnoDB tables: 36M (Tables: 45)
[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: 4m 50s (198 q [0.683 qps], 16 conn, TX: 258K, RX: 16K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[ --] Physical Memory : 993.4M
[--] Max MySQL memory : 836.0M
[--] Other process memory: 90.9M
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[- -] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum memory usage reached: 418.8M (42.16% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (84.15% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/198)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 25.00% (4/16)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)
[!!] Joins performed without indexes: 2
[OK] Temporary tables created on disk: 5% (4 on disk / 73 total)
[OK] Table cache hit rate: 142% (87 open / 61 opened)
[OK] Open file limit used: 5% (53/1K)
[OK] Table locks acquired immediately: 100% (129 immediate / 129 locks)
-------- Performance schema ----------------------- ----------------------------------------------
[ --] Performance schema is disabled.
-------- ThreadPool Metrics---------------------------------------- ------------ --------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (5.5.44-MariaDB)
-------- MyISAM Metrics---------------------------------------- ---------------- --------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/345.0K
-------- AriaDB Metrics --------------------- ---------------------------------------------- -----
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.4% (645 cached / 4 reads)
-------- InnoDB Metrics---------------------------------------- ---------------- --------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/36.3M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 8.01% (656 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 97.80 % (29188 hits/ 29844 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)
-------- TokuDB Metrics ------------------------------------------------ -------------------------------------
[--] TokuDB 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
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve =1
Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_type (=0)
join_buffer_size (> 128.0K, or always use indexes with joins)
Thanks in advance!

Answer the question

In order to leave comments, you need to log in

[[+comments_count]] answer(s)
S
shagguboy, 2016-08-03
@shagguboy

dev.mysql.com/doc/refman/5.7/en/server-system-vari...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question