MyBB Community Forums

Issue

My users get the following mySQL errors during peak hours:

SQL Error:
1203 - User neochi_mybb3 already has more than 'max_user_connections' active connections

SQL Error:
1041 - Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

Hosting

I'm currently on dedicated hosting (standard plan): http://www.hostgator.com/dedicated

Site stats

213,323 posts in 30,033 threads
4,400 members
usually has around 200-300 members on at anytime.

mySQL config file

skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

ulimit -a stats

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 30277
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 1024
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

htop stats

19934 mysql 20 0 2036M 289M 4532 S 192. 7.6 290h /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var
20233 root 20 0 110M 2140 1224 R 1.3 0.1 0:01.01 htop
20581 neochi 20 0 160M 19852 8056 S 0.0 0.5 0:00.04 /usr/bin/php /home/neochi/public_html/private.php
20586 neochi 20 0 160M 19844 8056 S 0.0 0.5 0:00.04 /usr/bin/php /home/neochi/public_htm

Please help! I shouldn't have any issues based on my forum stats and my current hosting...It's got to be a bad setting somewhere that's causing this.

vi /etc/my.cnf

under "[mysqld]", add this:
max_connections=500

Tweak that value as needed. Restart mysql.

Would that also fix my "out of memory" issue?

---

Can I get more info on this?

I'm not sure what's causing that. Grab a screenshot of top next time the problem happens. Are you using innodb or myisam (default) for your tables?

The problem with trying to get a screenshot of top when it happens is that it happens randomly during peak hours so it doesn't always happen...should I just take a screenshot during peak hours then?

I'm assuming I'm using myisam since it's default, but how do you check?

---

19934 mysql 20 0 2036M 277M 4608 S 99.3 7.3 307h /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/host.guardiancross-foru
18789 root 20 0 110M 2560 1228 R 1.3 0.1 0:00.99 htop
30615 nobody 20 0 2012M 16876 2720 S 0.7 0.4 0:08.42 /usr/local/apache/bin/httpd -k start -DSSL
30648 nobody 20 0 2012M 17400 2720 S 0.7 0.4 0:08.25 /usr/local/apache/bin/httpd -k start -DSSL
30566 nobody 20 0 2012M 16528 2720 S 0.7 0.4 0:08.22 /usr/local/apache/bin/httpd -k start -DSSL
30472 nobody 20 0 2012M 16680 2408 S 0.7 0.4 0:08.46 /usr/local/apache/bin/httpd -k start -DSSL
30764 nobody 20 0 2012M 17072 2720 S 0.7 0.4 0:08.21 /usr/local/apache/bin/httpd -k start -DSSL
30682 nobody 20 0 2012M 15968 2712 S 0.0 0.4 0:08.13 /usr/local/apache/bin/httpd -k start -DSSL
30474 nobody 20 0 2012M 16020 2396 S 0.0 0.4 0:08.13 /usr/local/apache/bin/httpd -k start -DSSL
30473 nobody 20 0 2012M 17268 2404 S 0.0 0.4 0:08.06 /usr/local/apache/bin/httpd -k start -DSSL
30724 nobody 20 0 2012M 16268 2720 S 0.0 0.4 0:07.91 /usr/local/apache/bin/httpd -k start -DSSL
19226 neochi 20 0 160M 19820 8056 S 0.0 0.5 0:00.05 /usr/bin/php /home/neochi/public_html/private.php
23929 root 20 0 46452 8560 1856 S 0.0 0.2 2:02.18 tailwatchd
6394 root 20 0 229M 12444 1456 S 0.0 0.3 25:51.63 python /root/bin/sys-snap --start
1 root 20 0 19352 960 740 S 0.0 0.0 0:11.89 /sbin/init
595 root 16 -4 10696 468 316 S 0.0 0.0 0:00.04 /sbin/udevd -d
1593 root 20 0 243M 3808 640 S 0.0 0.1 0:41.42 /sbin/rsyslogd -i /var/run/syslogd.pid -c 5
1634 root 20 0 9144 516 380 S 0.0 0.0 1:47.51 irqbalance
1726 dbus 20 0 21400 628 492 S 0.0 0.0 0:00.42 dbus-daemon --system
1750 root 20 0 4076 396 392 S 0.0 0.0 0:00.00 /usr/sbin/acpid
1759 haldaemon 20 0 25308 1480 868 S 0.0 0.0 0:06.11 hald
1760 root 20 0 18104 580 576 S 0.0 0.0 0:00.00 hald-runner
1788 root 20 0 20220 536 532 S 0.0 0.0 0:00.00 hald-addon-input: Listening on /dev/input/event4 /dev/input/event0 /dev/input/event1
1805 haldaemon 20 0 17804 596 592 S 0.0 0.0 0:00.00 hald-addon-acpi: listening on acpid socket /var/run/acpid.socket
1857 ntp 20 0 30160 868 708 S 0.0 0.0 0:01.80 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g
2073 root 20 0 107M 512 508 S 0.0 0.0 0:00.00 /usr/sbin/abrtd
2082 root 20 0 105M 472 396 S 0.0 0.0 0:09.89 abrt-dump-oops -d /var/spool/abrt -rwx /

Just to the error. Out of memory one. PLEASE SOMEONE HELP, it seems to be happening more often!

---

PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
19934 mysql 20 0 2036M 277M 4608 S 127. 7.3 307h /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/host.guardiancross-foru
18789 root 20 0 110M 2560 1228 R 2.0 0.1 0:01.84 htop
30566 nobody 20 0 2012M 16536 2720 S 0.7 0.4 0:08.37 /usr/local/apache/bin/httpd -k start -DSSL
30764 nobody 20 0 2012M 17060 2720 S 0.7 0.4 0:08.34 /usr/local/apache/bin/httpd -k start -DSSL
19528 neochi 20 0 160M 19828 8056 S 0.0 0.5 0:00.04 /usr/bin/php /home/neochi/public_html/private.php
30474 nobody 20 0 2012M 16040 2396 S 0.0 0.4 0:08.27 /usr/local/apache/bin/httpd -k start -DSSL
30473 nobody 20 0 2012M 17284 2404 S 0.0 0.4 0:08.18 /usr/local/apache/bin/httpd -k start -DSSL
30615 nobody 20 0 2012M 16928 2720 S 0.0 0.4 0:08.62 /usr/local/apache/bin/httpd -k start -DSSL
30682 nobody 20 0 2012M 15972 2712 S 0.0 0.4 0:08.25 /usr/local/apache/bin/httpd -k start -DSSL
30472 nobody 20 0 2012M 16688 2408 S 0.0 0.4 0:08.60 /usr/local/apache/bin/httpd -k start -DSSL
30648 nobody 20 0 2012M 17400 2720 S 0.0 0.4 0:08.38 /usr/local/apache/bin/httpd -k start -DSSL
30724 nobody 20 0 2012M 16268 2720 S 0.0 0.4 0:08.02 /usr/local/apache/bin/httpd -k start -DSSL
1634 root 20 0 9144 516 380 S 0.0 0.0 1:47.51 irqbalance
18709 root 20 0 99868 3876 2932 S 0.0 0.1 0:00.01 sshd: root@pts/1

---

help?

No more help?