PLEASE HELP WEBSITE DATABASE CRASHED

performance

Hi Guys,

i really need your help, one of my site started giving this "Error establishing a database connection" when ever it gets traffic spike. To get it up again i type this in command. service vesta restart

i am using Ubuntu 14.04 with nginx vestacp. i contacted support and they told me this

–---

From your Linodes console output it appears that your Linode is OOMing, meaning something inside your Linode is consuming all of the available virtual memory:

Out of memory: Kill process 2937 (mysqld) score 103 or sacrifice child

Killed process 2937 (mysqld) total-vm:872088kB, anon-rss:40656kB, file-rss:0kB

Out of memory: Kill process 3334 (apache2) score 56 or sacrifice child

Killed process 3334 (apache2) total-vm:529464kB, anon-rss:43696kB, file-rss:0kB

Out of memory: Kill process 3333 (apache2) score 55 or sacrifice child

Killed process 3333 (apache2) total-vm:528632kB, anon-rss:54336kB, file-rss:132kB

Out of memory: Kill process 27809 (mysqld) score 107 or sacrifice child

Killed process 27809 (mysqld) total-vm:884572kB, anon-rss:20688kB, file-rss:0kB

Out of memory: Kill process 6140 (mysqld) score 93 or sacrifice child

Killed process 6140 (mysqld) total-vm:860692kB, anon-rss:28912kB, file-rss:0kB

please help me fix this issue.

thank you so much

21 Replies

forum:akerl 8 years, 7 months ago

It sounds like you ought to find out what's using so much RAM and then either get more RAM or use less of it:

https://www.linode.com/docs/search?query=memory

https://www.linode.com/docs/search?query=resize

forum:webmedia 8 years, 7 months ago

i tried reading the help and upgraded my server to 4gb ram but still got the same error again, please somebody help me out

when i type freee -m this is what i get

total used free shared buffers cached

Mem: 3998 1639 2359 65 82 513

-/+ buffers/cache: 1043 2955

Swap: 511 0 511

i restarted server 3 hours ago and already 3998 used.

forum:dasand 8 years, 7 months ago

~~@webmedia:~~

i tried reading the help and upgraded my server to 4gb ram but still got the same error again, please somebody help me out

when i type freee -m this is what i get

total used free shared buffers cached

Mem: 3998 1639 2359 65 82 513

-/+ buffers/cache: 1043 2955

Swap: 511 0 511

i restarted server 3 hours ago and already 3998 used.

You have 3998 available, of which 1639 is being used and 2359 is not. If you subtract the buffers and cache from the used, then your apps are using 1043.

The first thing that I would look at are your Apache settings. Search the forum for "mpm_prefork".

forum:webmedia 8 years, 7 months ago

thank you so much for helping out. here are my settings in /etc/apache2/mods-available/mpm_prefork.conf

StartServers 5

MinSpareServers 5

MaxSpareServers 10

MaxRequestWorkers 150

MaxConnectionsPerChild 0

forum:dasand 8 years, 7 months ago

~~@webmedia:~~

thank you so much for helping out. here are my settings in /etc/apache2/mods-available/mpm_prefork.conf

StartServers 5

MinSpareServers 5

MaxSpareServers 10

MaxRequestWorkers 150

MaxConnectionsPerChild 0

Try changing MaxRequestWorkers to a much lower number. Try 30. And then reload Apache.

Wether this causes response time problems at the client will depend on how fast requests come in and how fast the application sends the response.

forum:webmedia 8 years, 7 months ago

Hey buddy before the change this was the memory status

free -m total used free shared buffers cached

Mem: 3998 3703 294 65 166 2456

-/+ buffers/cache: 1080 2918

Swap: 511 0 511

what does this mean > MaxRequestWorkers

our site will get very high traffic starting from day after tomorow, around 500+ users will be online at a time and ordering, viewing stuff.

should i still make the change. do you want any log files to pinpoint the exact problem. If you can tell me the path i will send you that file.

thanks you again for taking the time and helping out.

forum:dasand 8 years, 6 months ago

~~@webmedia:~~

Hey buddy before the change this was the memory status

free -m total used free shared buffers cached

Mem: 3998 3703 294 65 166 2456

-/+ buffers/cache: 1080 2918

Swap: 511 0 511

what does this mean > MaxRequestWorkers

our site will get very high traffic starting from day after tomorow, around 500+ users will be online at a time and ordering, viewing stuff.

should i still make the change. do you want any log files to pinpoint the exact problem. If you can tell me the path i will send you that file.

thanks you again for taking the time and helping out.

What follows is my understanding since I switched to Nginx several years ago. Take it as such.

MaxRequestWorkers is the maximum number of connections that Apache can handle at one time. If a request takes 1 second to process, then that works out to 150 connections per second. If they each take 10 seconds, then you are looking at 15 connections per second.

Each Apache session takes a certain amount of memory. If you are running PHP in Apache, then the memory requirements are much larger. If your average session size is 50MB, then you need over 7GB to handle the 150 sessions. If you reduce MaxRequestWorkers, you reduce the memory impact of traffic spikes. Depending on the real request rate, some people may see delays getting their request processed.

The output from the free command is snapshot. Unless you happen to run it during a period of high activity, you will not see the spikes.

On the MySQL side run mysqltuner and post the output. You may need to install it first ( sudo apt-get install mysqltuner ).

forum:webmedia 8 years, 6 months ago

here is the result from mysqltuner

> root@qurbani:/# mysqltuner

MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>

Bug reports, feature requests, and downloads at http://mysqltuner.com/

Run with '–help' for additional options and output filtering

Please enter your MySQL administrative login: muslimrelief_u

Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1

[OK] Operating on 64-bit architecture

–------ Storage Engine Statistics -------------------------------------------

[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[–] Data in InnoDB tables: 23M (Tables: 32)

[–] Data in MyISAM tables: 5K (Tables: 1)

[!!] Total fragmented tables: 5

–------ Security Recommendations -------------------------------------------

ERROR 1142 (42000) at line 1: SELECT command denied to user 'muslimrelief_u'@'localhost' for table 'user'

[OK] All database users have passwords assigned

–------ Performance Metrics -------------------------------------------------

[–] Up for: 2d 8h 45m 58s (509K q [2.492 qps], 10K conn, TX: 5B, RX: 59M)

[–] Reads / Writes: 97% / 3%

[–] Total buffers: 168.0M global + 2.8M per thread (200 max threads)

[OK] Maximum possible memory usage: 718.0M (72% of installed RAM)

[OK] Slow queries: 0% (0/509K)

[OK] Highest usage of available connections: 4% (9/200)

[OK] Key buffer size / total MyISAM indexes: 8.0M/4.0K

[OK] Key buffer hit rate: 100.0% (1M cached / 6 reads)

[!!] Query cache is disabled

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 56K sorts)

[!!] Temporary tables created on disk: 36% (27K on disk / 74K total)

[!!] Thread cache is disabled

[!!] Table cache hit rate: 4% (400 open / 8K opened)

[OK] Open file limit used: 18% (187/1K)

[OK] Table locks acquired immediately: 100% (478K immediate / 478K locks)

[OK] InnoDB data size / buffer pool: 23.2M/128.0M

–------ Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Enable the slow query log to troubleshoot bad queries

When making adjustments, make tmptablesize/maxheaptable_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Set threadcachesize to 4 as a starting value

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

querycachesize (>= 8M)

tmptablesize (> 16M)

maxheaptable_size (> 16M)

threadcachesize (start at 4)

table_cache (> 400)

forum:webmedia 8 years, 6 months ago

also i have changed max connection to 30

> StartServers 5

MinSpareServers 5

MaxSpareServers 10

MaxRequestWorkers 30

MaxConnectionsPerChild 0

vim: syntax=apache ts=4 sw=4 sts=4 sr noet

forum:dasand 8 years, 6 months ago

~~@webmedia:~~

also i have changed max connection to 30

> StartServers 5

MinSpareServers 5

MaxSpareServers 10

MaxRequestWorkers 30

MaxConnectionsPerChild 0

vim: syntax=apache ts=4 sw=4 sts=4 sr noet

Some recommendations:

[OK] Highest usage of available connections: 4% (9/200)

Change MySQL max_connections to a much smaller numbers, maybe 50.

MaxRequestWorkers      30

This is still too high considering the max connection value for MySQL. Also, assuming 50MB per connection, that is 1.5GB. See the next item.

[OK] Maximum possible memory usage: 718.0M (72% of installed RAM)

It appears that you are running on a 1GB Linode. You might consider upgrading to a 2GB Linode.

forum:webmedia 8 years, 6 months ago

Hey Buddy it seems something is using alot of ram all the time. i upgraded the linode to 4GB plan last week but after every 24 hours i had to run this command to free the ram echo 3 > /proc/sys/vm/drop_caches

if i dont run this command, the server will give this error "Error establishing a database connection"

please tell me how to pin point this and should i create Swap on my Ubuntu server

forum:rohara 8 years, 6 months ago

~~@webmedia:~~

Hey Buddy it seems something is using alot of ram all the time. i upgraded the linode to 4GB plan last week but after every 24 hours i had to run this command to free the ram echo 3 > /proc/sys/vm/drop_caches

if i dont run this command, the server will give this error "Error establishing a database connection"

please tell me how to pin point this and should i create Swap on my Ubuntu server

What output do you get from these commands?

  iostat -c 1 20
  vmstat 1 10
  free -m
  top -b -n1 | head -5
  ps auxf | sort -nr -k 4 | head -5
  ps auxf | sort -nr -k 3 | head -5
  echo [PID]  [MEM]  [PATH] && echo -------------------- &&  ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 5

forum:webmedia 8 years, 6 months ago

````
root@qurbani:/# iostat -c 1 20
Linux 4.1.5-x86_64-linode61 (qurbani.com) 10/06/2015 _x8664 (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
3.89 0.00 1.04 0.16 0.02 94.89

avg-cpu: %user %nice %system %iowait %steal %idle
1.00 0.00 1.00 0.00 0.00 98.00

avg-cpu: %user %nice %system %iowait %steal %idle
1.00 0.00 0.00 0.00 0.00 99.00