Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
sk8er_boi Posted April 19, 2016 Posted April 19, 2016 Can anyone look at my slow queries log and give some advice? I have queries over 500secs. Some queries have lock time over 80 secs. I'm on a vps with 4GB ram. This is the output of mysqltuner >> MySQLTuner 1.6.10 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-1ubuntu1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics --------------------------------------------- -------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My ISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 6G (Tables: 364) [--] Data in InnoDB tables: 224M (Tables: 207) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 215 -------- Security Recommendations ---------------------------------------------- -------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 605 basic passwords in the list. -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1h 29m 19s (2 q [0.000 qps], 9K conn, TX: 181B, RX: 149B) [--] Reads / Writes: 100% / 0% [--] Binary logging is disabled [--] Physical Memory : 3.9G [--] Max MySQL memory : 2.7G [--] Other process memory: 555.9M [--] Total buffers: 2.3G global + 2.7M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 2.3G (60.78% of installed RAM) [OK] Maximum possible memory usage: 2.7G (70.57% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/2) [OK] Highest usage of available connections: 4% (7/151) [OK] Aborted connections: 0.03% (3/9695) [!!] Query cache should be disabled by default due to mutex contention. [OK] No Sort requiring temporary tables [OK] No joins without indexes [OK] No tmp tables created on disk [OK] Thread cache hit rate: 99% (7 created / 9K connections) [OK] Table cache hit rate: 100% (400 open / 0 opened) [OK] Open file limit used: 33% (344/1K) [OK] Table locks acquired immediately: 99% (57K immediate / 57K locks) -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 19.1% (410M used / 2B cache) [OK] Key buffer size / total MyISAM indexes: 2.0G/2.0G [OK] Read Key buffer hit rate: 99.2% (12M cached / 102K reads) [!!] Write Key buffer hit rate: 49.9% (75K cached / 37K writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 256.0M/224.8M [OK] InnoDB buffer pool instances: 1 [OK] InnoDB Used buffer: 100.00% (16383 used/ 16383 total) [OK] InnoDB Read buffer efficiency: 99.98% (34762760 hits/ 34770952 total) [OK] InnoDB Write log efficiency: 98.75% (327870 hits/ 332029 total) [OK] InnoDB log waits: 0.00% (0 waits / 4159 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: Run OPTIMIZE TABLE to defragment tables for better performance 69 CVE(s) found for your MySQL release. Consider upgrading your version ! MySQL started within last 24 hours - recommendations may be inaccurate Variables to adjust: query_cache_type (=0) mysql-slow.log
ASTRAPI Posted April 19, 2016 Posted April 19, 2016 Quote [--] Up for: 1h 29m 19s Very early to give accurate info.... Let it run for a day and post again the output from Mysqltuner I can see already a few issues but is better to have more info. Also if you have more than 500 queries per sec you may need to start looking for a small dedicated server and that will boost your performance a lot
sk8er_boi Posted April 19, 2016 Author Posted April 19, 2016 I don't have 500queries per seconds. I have queries taking 500 seconds to execute. Actually it's mysqltuner's second run. I did the optimizations it advised.
ASTRAPI Posted April 19, 2016 Posted April 19, 2016 Ok after many hours post another output if you didn't solve your issue
sk8er_boi Posted April 19, 2016 Author Posted April 19, 2016 Installed mariadb. Works like a charm. why did i torment myself with mysql all this time? Anyone want to switchto mariadb, just 4 commands to switch. Painless and easy. http://askubuntu.com/questions/531455/how-to-drop-in-replace-mysql-with-mariadb/532453#532453
Recommended Posts
Archived
This topic is now archived and is closed to further replies.