mysql processes with high resident memory!

[masalaman]

I am having several problems with high server load as I posted in this forum earlier. It's especially in peek times when I have over 150 users online. The load is consistently above 8 in peek times.

Following is the top output and "ps aux" output when the server is pretty stable. Yet I notice about 75 mysql processes all with resident memory of almost 10MB. What does this mean? Is it good or bad?. What should I be doing?

Output of top
3:43am up 2 days, 6:25, 1 user, load average: 1.51, 1.38, 1.32
133 processes: 132 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 39.8% user, 22.0% system, 0.0% nice, 38.0% idle
Mem: 517368K av, 433948K used, 83420K free, 306656K shrd, 64648K buff
Swap: 131532K av, 1116K used, 130416K free 158984K cached

Output of "ps aux --sort rss"
(I am listing only mysql processes)

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 13666 0.0 1.9 30292 10016 ? S Jul31 0:33 /usr/sbin/mysqld
mysql 13668 0.0 1.9 30292 10016 ? S Jul31 0:00 /usr/sbin/mysqld
mysql 13669 0.0 1.9 30292 10016 ? S Jul31 0:09 /usr/sbin/mysqld
mysql 13692 0.1 1.9 30292 10016 ? S Jul31 2:57 /usr/sbin/mysqld
mysql 13697 0.1 1.9 30292 10016 ? S Jul31 2:44 /usr/sbin/mysqld
mysql 13827 0.2 1.9 30292 10016 ? S Jul31 5:30 /usr/sbin/mysqld
mysql 14074 0.0 1.9 30292 10016 ? S Jul31 2:25 /usr/sbin/mysqld
mysql 14434 0.0 1.9 30292 10016 ? S Jul31 2:20 /usr/sbin/mysqld
mysql 14569 0.0 1.9 30292 10016 ? S Jul31 2:18 /usr/sbin/mysqld
mysql 14580 0.0 1.9 30292 10016 ? S Jul31 0:45 /usr/sbin/mysqld
mysql 14585 0.1 1.9 30292 10016 ? S Jul31 2:47 /usr/sbin/mysqld
mysql 14589 0.1 1.9 30292 10016 ? S Jul31 4:14 /usr/sbin/mysqld
mysql 15993 0.0 1.9 30292 10016 ? S Jul31 1:58 /usr/sbin/mysqld
mysql 16682 0.2 1.9 30292 10016 ? S Jul31 5:35 /usr/sbin/mysqld
mysql 16807 0.1 1.9 30292 10016 ? S Jul31 3:46 /usr/sbin/mysqld
mysql 16808 0.1 1.9 30292 10016 ? S Jul31 3:59 /usr/sbin/mysqld
mysql 17335 0.1 1.9 30292 10016 ? S Jul31 3:42 /usr/sbin/mysqld
mysql 17337 0.0 1.9 30292 10016 ? S Jul31 2:11 /usr/sbin/mysqld
mysql 17696 0.1 1.9 30292 10016 ? S Jul31 2:39 /usr/sbin/mysqld
mysql 18186 0.1 1.9 30292 10016 ? S Jul31 3:20 /usr/sbin/mysqld
mysql 18187 0.1 1.9 30292 10016 ? S Jul31 3:09 /usr/sbin/mysqld
mysql 18188 0.0 1.9 30292 10016 ? S Jul31 2:11 /usr/sbin/mysqld
mysql 18610 0.1 1.9 30292 10016 ? S Jul31 2:40 /usr/sbin/mysqld
.
.
.
. many more

[eva2000]

nope that is pretty normal

what's you latest mysql extended-status, my.cnf file settings etc?

[masalaman]

eva2000, you seem to be online. My extended status is at http://www.masalatalk.com/masalaboard/status.php and following is current my.cnf settings. If you remember it's a RaQ4i with 512MB memory. I tried switching to new server but that started crashing the server so I am back on RaQ now.

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=350
set-variable = key_buffer=20M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=8M
set-variable = table_cache=1024
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache_size=128
# Try number of CPU's*2 for thread_concurrency
#set-variable = thread_concurrency=8

[masalaman]

I have thread_concurrency commented right now. If it should be no.of CPUs*2 should it be 2 in my case?. Not quite sure what this is!

[eva2000]

it still looks as if it's a cpu not memory problem :(

try changing my.cnf file to

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = join_buffer=3M
set-variable = record_buffer=3M
set-variable = sort_buffer=6M
set-variable = table_cache=600
set-variable = thread_cache_size=128

restart mysql

what was wrong with the new server? might of needed mysql tweaking too

[masalaman]

eva2000, thanks. I will give it a try.

Following is what I posted about my attempt to move to the new server in Trouble Shooting forum but I had no response. I am reproducing it below.

<--- From another thread --->
I had problems with server load being very high having vBulletin 2.0.0 on RaQ4i so I attempted to move the forums to a new server. But I had serious problems with vB crashing the server so I had to back out the server move twice. Following is the configuration of old and new servers:

Old Server
----------
RaQ4i (AMD 450MHz), 512MB memory
Linux on Cobalt
MySQL server version 3.23.37
PHP/4.0.3pl1 Apache/1.3.12 Cobalt (Unix) mod_gzip/1.3.19.1a mod_ssl/2.6.4 OpenSSL/0.9.5a mod_auth_pam/1.0a mod_perl/1.24

New Server
----------
Dual Pentium III 550 Mhz processor, 512MB memory
9GB SCSI drive
FreeBSD 4.2
MySQL server version 3.23.36
PHP/4.0.4pl1 Apache/1.3.19 (Unix) mod_perl/1.25

I know I went one release lower on MySQL on the new server (3.23.37 to 3.23.36) but that's the latest I could get for FreeBSD. The database installed okay so I don't think that's a problem.

This is what I did:
- Did mysqldump of database on old server
- FTP'd the file to new server
- Created database on new server. Verified all tables looked fine in webmin.
- Changed config.php for mysql userid/password
- Changed message board url in Admin cp to make it IP based (until the name server changes are propagated).

This is what happened:
- The server load stayed below .20 for 15 minutes or so when the number of online users was 140.
- The 1 minute server load average jumped suddenly to as high as 28 and next minute it crashed the server. It happened both the times I attempted a server move so I am sure it has got to do with vB and MySQL.
- The new server has only one other domain and the load is always below .20. All the my.cnf parameters are same as the old server. Why does it crash the server?. This is a much more powerful server than the old RaQ4i and the only difference is O/S and MySQL version. What could be happening here?. This is a serious issue since I am having load problems on both servers with just about 150 users being active at the same time.

---------
By the way, I also noticed it bounced MySQL off once and disconnected all users once few minutes before it crashed.

[eva2000]

probably no replies since that post would of belonged in the server config forum not troubleshooting which is for vB not the server ;)

[eva2000]

it might be a freebsd issue but i don't know anything about that :(

have you tried putting linux on that new server instead?

[masalaman]

Hmm, yes. probably that post belonged in Server Config forum. It's risky switching from freebsd to Linux at this point for me because I run a very popular site on the other server. I will try to get later version of MySQL and see if it helps.

[masalaman]

I was wondering if altering "user" and "thread" tables to innobase will make any difference. But that will mean uncommenting all those innodb parameters in my.cnf. Have you tried anything like this?.

[fastforward]

Originally posted by eva2000
it might be a freebsd issue but i don't know anything about that :(
Apparently, there is a known problem with the threads implementation on FreeBSD 4.2

I believe 4.1 and 4.3 are OK.

There's a few usenet posts on my forums mentioning similar problems.

http://dbforums.com/t44815.html
http://dbforums.com/t73918.html
http://dbforums.com/t117395.html

[masalaman]

Originally posted by fastforward
Apparently, there is a known problem with the threads implementation on FreeBSD 4.2

Thanks for your input. I will do more research on those lines.

P.S: You have nice dbforums there.