2

I have a small Cloud Server running under Ubuntu 2 core, 2GB ram and 2GB swap, with Plesk Panel, Apache, Nginx as reverse proxy and Mysql 5.5. I have about 18 domains hosted here and each domain has his DB. Most of the websites are running under WordPress or Joomla.

All the websites together doesn't make more than 2000 visits per day. My biggest problem is mysql as it looks like it is eating my RAM and server goes at swap for only 1 day up and as well the server doesn't look like it uses more than 70% of RAM but still it uses swap. Swappines is set to 10.

I have used mysqltuner.pl for tuning Mysql but i can't get it work as it should.

Here is mysqltuner output:

>>  MySQLTuner 1.6.1 - Major Hayden <[email protected]>
>>  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] Currently running supported MySQL version 5.5.46-0ubuntu0.14.04.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MEMORY tables: 0B (Tables: 2)
[--] Data in MyISAM tables: 21M (Tables: 284)
[--] Data in InnoDB tables: 170M (Tables: 920)
[!!] Total fragmented tables: 129

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 15h 23m 44s (632K q [4.458 qps], 11K conn, TX: 2B, RX: 167M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Total buffers: 1.0G global + 3.6M per thread (100 max threads)
[OK] Maximum reached memory usage: 1.1G (55.03% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (70.29% of installed RAM)
[OK] Slow queries: 0% (0/632K)
[OK] Highest usage of available connections: 16% (16/100)
[!!] Aborted connections: 4.23%  (473/11192)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 100K sorts)
[!!] Joins performed without indexes: 10841
[!!] Temporary tables created on disk: 49% (48K on disk / 98K total)
[OK] Thread cache hit rate: 99% (16 created / 11K connections)
[OK] Table cache hit rate: 80% (1K open / 2K opened)
[OK] Open file limit used: 7% (628/8K)
[OK] Table locks acquired immediately: 100% (802K immediate / 802K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.5% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/10.8M
[OK] Read Key buffer hit rate: 96.0% (2M cached / 115K reads)
[!!] Write Key buffer hit rate: 89.0% (27K cached / 3K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 512.0M/170.5M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 33.34% (10925 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 99.99% (78464393 hits/ 78474138 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 28642 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
    Reduce or eliminate unclosed connections and network issues
    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
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 1.0M, or always use indexes with joins)

free -m out:

             total       used       free     shared    buffers     cached
Mem:          1995       1727        267        282        245        692
-/+ buffers/cache:        789       1205
Swap:         1951        370       1581

I will really appreciate any suggestions. Thank you for you time :)

Mysql Global Variables. As there are about 307 lines i added them to Pastebin: http://pastebin.com/bVUUG9zL

I found this on askubuntu, can it be possible? Cuz i checked the server monitor logs and the maximum RAM usage was 25%.

11
  • Look at mysqlcalculator.com and check the variables mentioned in there (and/or add show global variables; output to the question). Seems like you have a bit big per-thread buffers and too many max threads allowed so if there are too many concurrent connections, memory goes to swap.
    – jkavalik
    Commented Nov 11, 2015 at 13:49
  • Pastebin Global Variables
    – emilushi
    Commented Nov 11, 2015 at 14:13
  • Your join_buffer_size is 64MB, thats too much, lower it to 1-2MB or even to the default 256KB. And turn off the query cache too (another 128MB saved).
    – jkavalik
    Commented Nov 11, 2015 at 14:17
  • In this case i should ignore mysqltuner suggestions?
    – emilushi
    Commented Nov 11, 2015 at 14:40
  • Generally you should ignore all tuner suggestions unless you fully understand them. For instance the defragmentation is bogus and optimize won't change it on innodb tables. Your query cache does not have a single hit so it is not helping.
    – jkavalik
    Commented Nov 11, 2015 at 14:53

1 Answer 1

1

I agree with already done advice - check runner script advices only if You full understand what happens, but most important other.

Configuration parameters give only one side view of problem, other it is - what really happens, and what really give loading?

I just suggest us one of tools for check and collect statistics from the running server:

it allow You identify and split problems by 80/20 rules 1-2 queries, can easily take 80-90% of loading, simple - queries NOT using indexes

and this is give You exactly information - which parameters better to tune from suggested by runner script.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.