Crippling mysql queries

[sk8er_boi]

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]

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]

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]

Ok after many hours post another output if you didn't solve your issue

[sk8er_boi]

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

[ASTRAPI]

I am always recommend MariaDB