Web lists-archives.com

Re: mysql strangeness...




Chris, thanks for you prompt reply. Try to raise up some configuration
regarding /etc/security/limits.conf

mysql soft nofile 10240
mysql hard nofile 40960
mysql soft nproc 10240
mysql hard nproc 40960
root soft nofile 10240
root hard nofile 40960
root soft nproc 10240
root hard nproc 40960

[root@server mysql-rpm]# ulimit -a mysql
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 192031
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 90000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 90000
cpu time (seconds, -t) unlimited
max user processes (-u) 90000
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

I've been running some *not that busy* mysql instances with these values
and max_connections=3000 and max_user_connections=0 to deal with
simultaneous connections. It seems to be OS constraint as you mentioned and
the limits configurations above will be a good start to check if the
problem will happen again.

Let us know how's it going, cheers!!


--
*Wagner Bianchi, +55.31.8654.9510*
Oracle ACE
<https://apex.oracle.com/pls/otn/f?p=19297:4:331870797705::NO:4:P4_ID:4541>,
MySQL Certified Professional
Email: me@xxxxxxxxxxxxxxxxx
Skype: wbianchijr

2014-12-08 11:56 GMT-02:00 Chris Knipe <savage@xxxxxxxxxxxxx>:

> On Mon, Dec 8, 2014 at 3:02 PM, Wagner Bianchi
> <wagnerbianchijr@xxxxxxxxx> wrote:
> > Hello Chris,
> >
> > Can pls you share the below command output...
> >
> > SHOW STATUS LIKE 'Threads%';
> > SELECT @@thread_cache_size, @@net_buffer_length, @@max_allowed_packet;
>
> mysql> SHOW STATUS LIKE 'Threads%';
> +-------------------+-------+
> | Variable_name     | Value |
> +-------------------+-------+
> | Threads_cached    | 422   |
> | Threads_connected | 92    |
> | Threads_created   | 514   |
> | Threads_running   | 1     |
> +-------------------+-------+
> 4 rows in set (0.43 sec)
>
> mysql> SELECT @@thread_cache_size, @@net_buffer_length,
> @@max_allowed_packet;
> +---------------------+---------------------+----------------------+
> | @@thread_cache_size | @@net_buffer_length | @@max_allowed_packet |
> +---------------------+---------------------+----------------------+
> |                1024 |               16384 |             16777216 |
> +---------------------+---------------------+----------------------+
> 1 row in set (0.00 sec)
>
> mysql>
>
> The server is very not busy at all right now however.  The issues
> seems to start happening once we've reach the 250 to 300 concurrent
> connection mark.  I'm tempted to just through another 64GB of memory
> to the box and up the max connections, but 1st price of course would
> be to identify and resolve the problem, rather than just throwing
> hardware at the problem :-)
>
>
>
> > What about the limits.conf config on operating system level?
>
> They have been dealt with.  mySQL has 4096 file descriptors available.
> Through all of this, not one single error is logged to the errorlog
> either.
>
> limits.conf:
> mysql soft nofile 4096
> mysql hard nofile 4096
>
> I'm personally actually leading more towards that this is a OS issue
> rather than a mysql issue, but I have no idea to where to even start
> looking to debug this :-(
>
> --
> Chris.
>