VPS Server slowdown - mySQL problem

[moowalker]

Hi,

I am experiencing a problem with mySQL on my VPS and I would like some help. I am experiencing major server slowdowns, load average can go up to 30, when a mySQL operation is performed. I have tried to tweak the setting for mySQL but with no result.

VPS Spec:
OpenVZ
RAM: 1GB (2GB Burst)

Server version: 5.1.66-cll

my.cnf

cat /etc/my.cnf
[mysqld]
max_connections=300
slow_query_log
general_log
safe-show-database
skip-networking

query_cache_limit=4M
query_cache_size=64M
query_cache_type=1


key_buffer_size=256M
max_allowed_packet = 1M
table_open_cache = 300

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 64M

thread_cache_size = 8
join_buffer_size=1M

wait_timeout=150

mysqltuner output

./mysqlmymonlite.sh mysqltuner

-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.4.2 mysqlmymon.com
compiled by George Liu (eva2000) vbtechsupport.com
-------------------------------------------------------------

Report Generated:
Fri Dec 28 17:22:31 EET 2012

-------------------------------------------------
mysqltuner output
-------------------------------------------------
mysqltuner.pl [found]

 >>  MySQLTuner 1.2.1 mod - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 321M (Tables: 768)
[--] Data in InnoDB tables: 7M (Tables: 86)
[!!] Total fragmented tables: 99

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 12m 57s (6M q [59.249 qps], 35K conn, TX: 4B, RX: 1B)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 346.0M global + 8.2M per thread (300 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.7G (136% of installed RAM)
[OK] Slow queries: 0% (188/6M)
[OK] Highest usage of available connections: 30% (90/300)
[OK] Key buffer size / total MyISAM indexes: 256.0M/292.7M
[OK] Key buffer hit rate: 100.0% (38B cached / 343K reads)
[OK] Query cache efficiency: 86.9% (5M cached / 5M selects)
[!!] Query cache prunes per day: 91159
[OK] Sorts requiring temporary tables: 1% (168 temp sorts / 13K sorts)
[OK] Temporary tables created on disk: 0% (2K on disk / 287K total)
[OK] Thread cache hit rate: 98% (407 created / 35K connections)
[!!] Table cache hit rate: 2% (300 open / 14K opened)
[OK] Open file limit used: 33% (499/1K)
[OK] Table locks acquired immediately: 98% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 8.0M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:
  *** MySQL maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
  *** Please note this recommendation is not entirely accurate.
      The formula used to calculate max memory usage assumes all queries utilise
      all memory buffers simultaneously. When in reality it is very rare for a
      query to engage & utilise all memory buffers simultaneously.

      Formula also assumes all predefined max_connections are reached.
      You could have set max_connections = 1000 and in a whole year
      of usage never hit beyond 50 max_used_connections. So your real MySQL
      memory usage is only 1/20th of theorectical max memory usage reported.

      So real max memory usage will never reach this peak.
      So do not be too concerned with this warning.

      It is better to monitor your real MySQL max_used_connection and MySQL
      memory usage over time and adjust accordingly.

      You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
      monitor your MySQL memory usage over time.

      MySQL performs optimally when its required amount of memory is met.
      Reducing and starving MySQL memory allocation to adhere to this
      artificial max memory warning - of which in reality will never be reached,
      will only reduce MySQL performance in many cases ***

    query_cache_size (> 64M)
    table_cache (> 300, table_open_cache hit rate <20%)


Report Complete:
Fri Dec 28 17:22:32 EET 2012
----------------------------

Dont hesitate to ask my any info I can provide.

Thank you in advance,
Ilias

 

[LaceHost-Ishan]

[OK] Highest usage of available connections: 30% (90/300)

As you are using <90 connections , lower max_connections to 125 or so to reduce MySQL memory usage.

Also, disable mysql logging in my.cnf . It is good to find the slow queries but once you know them , you can disable the log.

 

[moowalker]

Hello.

I just wanted to report that my problem was solved. The problem was opencart and some heavy (and wrong) querys.

Thank you