MySQL Ram high

[cyberhostpro]

HI

We setup a client with a good spec dedicated server. so far hes only hosting a single MySQL database on it. Plesk shows 15GB of 16GB Ram being used for MySQL.


[root@ds-135 ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show processlist '
+-----+-------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------+-----------+------+---------+------+-------+------------------+
| 72 | admin | localhost | psa | Sleep | 884 | | NULL |
| 355 | admin | localhost | psa | Sleep | 400 | | NULL |
| 357 | admin | localhost | psa | Sleep | 400 | | NULL |
| 681 | admin | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+-------+-----------+------+---------+------+-------+------------------+
[root@ds-135 ~]#


I dont see anything really using MySQL? running service mysqld restart brings it down slightly.

IS their anything anyone can recommend to me to do to find whats causing the spike in Ram?

Copy of my.cnf:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

OS CentOS 6.5 (Final)
Panel version 11.5.30 Update #37, last updated at Mar 20, 2014 03:08 AM

The system is up-to-date; last checked at Mar 20, 2014 03:07 AM

i've run yum update too so everything is up to date.

 

[abdi]

You can use the tool mytop to see what (slow) queries, db users and other info related to what is running then ...This can give you an idea of which user / db is consuming much of your resources ..

You can install it with

yum -y install mytop

To run use mytop

mytop -uadmin -p`cat /etc/psa/.psa.shadow` -h localhost

Hope that helps ...

However FYI, if one of the most used DBs has very many records, ie millions of records this is very typical with a default MySQL installation / configuration, you would have to optimize your MySQL to handle such.

 

[cyberhostpro]

HI

Thanks for the quick response. At first mytop was not found in yum however i installed by by doing the following

wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
rpm -Uvh rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
yum clean all

I then run

mytop -uadmin -p`cat /etc/psa/.psa.shadow` -h localhost

as suggested above, however i get error:

Error in option spec: "long|!"

 

[abdi]

Mytop has started to break recently when upgrading from RHEL or CentOS 5.x to 5.3. It appears that some of the perl modules have change.d

The fix for this is to edit the mytop script and comment out the line containing long|!

pico /usr/bin/mytop

Run a search for long|! and comment out the following line:

"long|!" => \$config{long_nums},

Start mytop and you should be fine!

 

[cyberhostpro]

HI, thanks for the quick response. I now get a new error

[root@ds-135 ~]# mytop -uadmin -p`cat /etc/psa/.psa.shadow` -h localhost
Cannot connect to MySQL server. Please check the:

* database you specified "test" (default is "test")
* username you specified "admin" (default is "root")
* password you specified "**Removed**" (default is "")
* hostname you specified "localhost" (default is "localhost")
* port you specified "3306" (default is 3306)
* socket you specified "" (default is "")

The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.

Here's the exact error from DBI. It might help you debug:

Unknown database 'test'

[root@ds-135 ~]#

 

[cyberhostpro]

I edited the config again and stopped it checking for db test by commenting out the line:


it now runs and I get:

MySQL on localhost (5.1.73) up 0+00:46:49 [12:27:35]
Queries: 15.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
qps now: 0 Slow qps: 0.0 Threads: 6 ( 1/ 0) 00/00/00/00
Key Efficiency: 80.9% Bps in/out: 0.1/ 19.7 Now in/out: 8.3/ 1.5k

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
3197 admin localhost 0 Query show full processlist
3210 user01039 95.215.226.136 admin_carc 0 Sleep
1190 admin localhost psa 151 Sleep
1742 admin localhost psa 453 Sleep
1744 admin localhost psa 453 Sleep
72 admin localhost psa 2738 Sleep



i too optimized & repaired all tables in MySQL and restarted MySQL. but the ram usage still shows high in plesk.

Update, this maybe just plesk wrong as top says otherwise:

14994 mysql 20 0 563m 30m 6320 S 2.0 0.2 0:30.26 mysqld

 

[abdi]

Try to recalculate RAM on your server with the command as root privilege :

/usr/local/psa/bin/sw-engine-pleskrun /usr/local/psa/admin/plib/scripts/setup-health-monitor.php

 

[cyberhostpro]

HI

I tried that

Real memory usage 77.3% used (12.0 GB of 15.6 GB) (?)
Swap usage 0.5% used (42.1 MB of 7.84 GB) (?)
Swapping throughput 0 pages

 

[abdi]

What do you get with

free -m

 

[cyberhostpro]

[root@ds-135 ~]# free -m
total used free shared buffers cached
Mem: 15941 14730 1210 0 229 2158
-/+ buffers/cache: 12343 3598
Swap: 8031 43 7988
[root@ds-135 ~]#

 

[abdi]

Based on those results, plesk is right to report what usage as you see it ...
You just need to identify which process is consuming the highest percentage of RAM.

On Linux in the top process you can press < key to shift the output display sort left. By default it is sorted by the %CPU so if you press the key 4 times you will sort it by VIRT which is virtual memory size giving you your answer.

Another way to do this is:

ps -e -o pid,vsz,comm= | sort -n -k 2

should give you and output sorted by processes virtual size.

Here's the long version:

ps --everyone --format=pid,vsz,comm= | sort --numeric-sort --key=2

 

[cyberhostpro]

Abdi, thanks for all the quick replies.

I run that and get this:

[root@ds-135 ~]# ps -e -o pid,vsz,comm= | sort -n -k 2
1000 0 kdmflush
10 0 watchdog/1
1035 0 jbd2/dm-2-8
1036 0 ext4-dio-unwrit
1037 0 ext4-dio-unwrit
1038 0 ext4-dio-unwrit
1039 0 ext4-dio-unwrit
1047 0 flush-253:0
11 0 migration/2
111 0 kstriped
12 0 migration/2
13 0 ksoftirqd/2
1372 0 kondemand/0
1373 0 kondemand/1
1374 0 kondemand/2
1375 0 kondemand/3
14 0 watchdog/2
15 0 migration/3
16 0 migration/3
17 0 ksoftirqd/3
18 0 watchdog/3
19 0 events/0
20 0 events/1
2 0 kthreadd
21 0 events/2
22 0 events/3
23 0 cgroup
24 0 khelper
25 0 netns
257 0 scsi_eh_0
258 0 fw_event0
26 0 async/mgr
264 0 poll_0_status
27 0 pm
28 0 sync_supers
29 0 bdi-default
30 0 kintegrityd/0
3 0 migration/0
31 0 kintegrityd/1
32 0 kintegrityd/2
33 0 kintegrityd/3
34 0 kblockd/0
35 0 kblockd/1
36 0 kblockd/2
37 0 kblockd/3
38 0 kacpid
39 0 kacpi_notify
398 0 kdmflush
400 0 kdmflush
40 0 kacpi_hotplug
4 0 ksoftirqd/0
41 0 ata/0
42 0 ata/1
426 0 jbd2/dm-0-8
427 0 ext4-dio-unwrit
428 0 ext4-dio-unwrit
429 0 ext4-dio-unwrit
430 0 ext4-dio-unwrit
43 0 ata/2
44 0 ata/3
45 0 ata_aux
46 0 ksuspend_usbd
470 0 kauditd
47 0 khubd
4760 0 flush-253:2
48 0 kseriod
49 0 md/0
50 0 md/1
5 0 migration/0
51 0 md/2
52 0 md/3
53 0 md_misc/0
54 0 md_misc/1
55 0 md_misc/2
56 0 md_misc/3
57 0 khungtaskd
58 0 kswapd0
59 0 ksmd
60 0 khugepaged
6 0 watchdog/0
61 0 aio/0
62 0 aio/1
63 0 aio/2
64 0 aio/3
65 0 crypto/0
66 0 crypto/1
67 0 crypto/2
68 0 crypto/3
7 0 migration/1
73 0 kthrotld/0
74 0 kthrotld/1
75 0 kthrotld/2
76 0 kthrotld/3
77 0 kipmi0
79 0 kpsmoused
80 0 usbhid_resumer
8 0 migration/1
9 0 ksoftirqd/1
PID VSZ
5162 4060 mingetty
5164 4060 mingetty
5166 4060 mingetty
5168 4060 mingetty
5170 4060 mingetty
5172 4060 mingetty
4327 4064 courierlogger
4345 4064 courierlogger
4388 4064 courierlogger
4498 4064 courierlogger
4507 4064 courierlogger
4392 4076 acpid
1396 10816 irqbalance
24850 11124 udevd
4499 11900 couriertcpd
4508 11900 couriertcpd
4328 11904 couriertcpd
4346 11904 couriertcpd
17700 15036 top
4441 17804 hald-addon-acpi
4402 18104 hald-runner
1 19352 init
4430 20220 hald-addon-inpu
5147 21492 atd
4464 22132 xinetd
4401 25244 hald
4389 29972 authdaemond
4391 32076 authdaemond
4393 32076 authdaemond
4394 32076 authdaemond
4395 32076 authdaemond
4397 32076 authdaemond
10322 34372 kavehost
1479 45136 ctmilter.bin
1540 45328 sw-cp-serverd
26776 45760 nginx
1541 45872 sw-cp-serverd
26777 45908 nginx
6301 57872 master
19253 58044 tlsmgr
6304 58152 qmgr
15363 58336 pickup
25503 66608 sshd
5199 93196 auditd
4362 97312 dbus-daemon
17740 108136 ps
14889 108204 mysqld_safe
30843 108204 abrt-dump-oops
17256 109864 sshd
30800 110320 abrtd
17265 110440 bash
25637 117328 crond
17741 155432 sort
17410 173856 crond
25655 183548 rsyslogd
9930 196252 monit
4540 210684 hspc-plugin-rpc
17725 235688 drwebd.real
5077 235688 drwebd.real
25467 240084 spamd
25476 240084 spamd
25475 240612 spamd
11914 245016 httpd
25590 250112 named
9899 336052 sw-engine
30353 341252 sw-engine-fpm
17416 341568 sw-engine
16499 341692 sw-engine
11900 343176 httpd
11933 343176 httpd
11934 343176 httpd
11935 343176 httpd
11936 343176 httpd
11937 343176 httpd
11938 343176 httpd
11939 343176 httpd
11940 343176 httpd
4552 478088 psa-pc-remote
5122 536672 sw-collectd
14994 1169212 mysqld
11734 6582816 java

 

[abdi]

http://forum.parallels.com/showthread.php?260744-Have-high-CPU-memory-usage-Read-this!

 

[cyberhostpro]

HI
Tried them changes. No effect on ram

 

[IgorG]

I think that it is really difficult to find a reason of consuming RAM by MySQL in scope of forum discussion. abdi just gave you tools and directions but if you really want to find a reason - deep investigation directly on your server is required. Therefore I suggest you contact Parallels Support Team.

 

[ScottGoddard]

Have you tried looking in phpMyAdmin? There are some excellent status and reporting tools in there which may help find an errant process, statement or connection that is causing the high usage.