Forum archive 2000-2006

John Jones - mysql connections exhausted problem

John Jones - mysql connections exhausted problem

by Arnold Pizer -
Number of replies: 0
inactiveTopicmysql connections exhausted problem topic started 5/27/2004; 3:27:39 PM
last post 6/8/2004; 10:13:03 PM
userJohn Jones - mysql connections exhausted problem  blueArrow
5/27/2004; 3:27:39 PM (reads: 3666, responses: 3)

I was demonstrating webwork2 for some instructors and got "DBI - too many connection" errors in the middle of the demonstration.  I was accessing only a few mysql courses.  Aside from being embarrassing, it is a little worrying.  Almost no one is using the system yet and the mysql part is overloaded.  I restarted the web server and could get back in, but that isn't a long term solution.

I checked mysql (with "show variables"); its connection limit is 100.  I then learned another mysql command "show processlist".  The number of connections started growing again.  There are many of the same type (e.g., webworkRead) to the same database.  I know that webwork leaves database connections open in the hope of reusing them.  Presumably, as a user accesses multiple pages, he gets a different mod_perl thread, each of which opens a connection and leaves it there for future requests.

So, if I were to reset the number of connections mysql will allow, the right number should be at least

(number of courses + epsilon) * 2 * (number of modperl instances)

The epsilon is for other tables (ProblemLibrary, maybe a dvipng cache), and the 2 in the middle is for read and write connections.  The number of modperl instances is presumable MaxClients in http.conf?  Does this sound right?


<| Post or View Comments |>

userJohn Jones - Re: mysql connections exhausted problem  blueArrow
6/1/2004; 3:58:10 PM (reads: 3944, responses: 0)
I have done more reading of the mysql manual.  I understand more, but I am also more confused.  If there are any mysql and/or unix experts out there, it would be great if they could comment on this problem.

I restarted mysql with max_connections set much higher (5000).  For the moment, that "fixed" the problem.  For a fall semester, it would have to be bigger for us.  I can change it, but it looks like I am not out of the woods yet.

The mysql manual says that I should also increase the size of the table_cache to "max_connections * N, where N is the maximum number of tables in a join".  Unfortunately, I don't know the value of N in spite of the explanation.  For WW, what should this be?

I would be happy to go big on the size of the table_cache, but there are more warnings from the mysql manual.  Every "open table" might need 2 open file descriptors, and there is a danger of running out of open file descriptors from the system.  I think my system currently has a limit of 1024 per user (or maybe it is per process).


<| Post or View Comments |>

userWilliam Wheeler - Re: mysql connections exhausted problem  blueArrow
6/8/2004; 5:59:00 PM (reads: 3949, responses: 0)


I think that in the current WeBWorK2 environment,
the following inequality involving the MySQL Table_cache_size
and the number of MySQL file_descriptors should almost always
yield almost optimal MySQL performance:

  2 x Max_Apache_child. <= 2 x Table_cache_size <= Num_file_descr. <= 256

More specifically, one should arrange for

   Max_Apache_child. <=  Table_cache_size <= 128  .

If one's operating system permits more than 256 file descriptors
per process, then the 128 can be replaced by
    Max_file_descriptors_per_process / 2   .

To be on the safe side, one should  arrange for  the inequalities to be strict.


The MySQL documentation is imprecise, but it seems to be implying
the following:

(1) An active_table is a MySQL table that is being used
    in at least one currently executing MySQL query/update
(2) An active_table_use is a use of a MySQL table in a currently
    executing MySQL query/update.  
(3) An active MySQL connection/thread is a conection/thread
    that has a MySQL query/update currently executing.
(4) For each active MySQL connection/thread t,
    let M(t) = number of tables being actively used by its
    current query/update, i.e., the number of active_table_uses
    attributable to that connection/thread.  

The distinction between (1) and (2) is that a table might be being
used by more than one currently executing MySQL query, because
several MySQL threads can concurrently read the same table.
Furthermore, a single MySQL thread can actively "use" the same
table more than once by joining the table to itself.

Note: An SQL join of tables is a subset of the Cartesian product
of the tables.

Definition: Let N = maximum number of tables in an active MySQL join
 (= maximum number of factors in the corresponding Cartesian products).

Lemma 1:  N = max{M(t): t is an active connection/thread}

Explanation:  A query with an SQL join is the only SQL situation
              in which more than one table needs to be open

Lemma 2:  For an active WeBWorK->MySql connection/thread w, M(w)=1.

   The  WW  database methods do not currently support SQL joins.  
   WW does table joins programmatically.  For example, programmatically joins  
   problemRecord data and studentRecord data to produce
   the Student Progress Report.

Assumptions:  Currently, there is only one WeBWorK thread per Apache child
   and the WeBWorK programs themselves are single-threaded.

Notation:  A = {t: t is an active MySQL connection/thread}
           W = {t: t is a WeBWorK MySQL connection/thread}

Corollary:  | W \cap A | <= Max_Apache_children.

Justification:  Although a WeBWorK thread (in Apache) may
   have more than one MySQL connection/thread, only one
   of them can be active at any time due to the (assumption of)

Remark:  | W | may be large if WeBWorK2 does not close its MySQL
   connections, because of the persistence of the WeBWorK2 programs
   in Apache's mod_perl environment.  So one may perhaps need
   a large upper bound for the number MySQL connections.  
   But, at any instant, most of these connections
   will be dormant.

------ Table Cache Size ------------------------

Proposition:  The table_cache_size_needed (TCSN) needed (at any moment) is

   TCSN = total number of active_table_uses
        = Sum_{t in A} M(i)
        = Sum_{t in (W \cap A)} M(i)  +  Sum_{t in A-W} M(i)
        = Sum_{t in (W \cap A)} 1     +  Sum_{t in A-W} M(i)
        = | W \cap A |  +  Sum_{t in A-W} M(i)
        = | W \cap A |  +  | A-W | N


Assumption:  | A-W | = 0 almost always for a WeBWorK MySQL database.  
         The only exceptions should be when an administrator
         or an administrative process (e.g., a roster update
         or a gradebook -> Registrar process) is accessing the
         database.  (For these relatively rare episodes, one can rely
         on MySQL's dynamic resizing of the table cache.)

Corollary:  TCSN <= Max_Apache_children almost always.

------- File Descriptors -------------------------

Proposition:  The number of file descriptors needed (NFDN)  (at any
moment) is

       NFDN = total_number_of_active_table_uses + number_of_active_tables
            = TCSN + number_of_active_tables.

Justification:  MySQL documentation.

Corollary:  NFDN <= TCSN + TCSN = 2 TCSN

Corollary:  NFDN <= 2 Max_Apache_children almost always.

Fact.  The number of file descriptors available to a process apparently
varies from one version of Unix to another and from one installation
to another.  For instance, in a 32-bit Solaris operating system,
the maximum number of file descriptors available to a process is
2^8 = 256, the number of indices representable on a single 8-bit byte.
Because one byte is the smallest possible storage for an unsigned
integer, it seems likely that every Unix operating system does
provide for at least 2^8 = 256 file descriptors per process.


The assertions in the summary are consequences of the preceding
corollaries and facts.




<| Post or View Comments |>

userJohn Jones - Re: mysql connections exhausted problem  blueArrow
6/8/2004; 10:13:03 PM (reads: 3928, responses: 0)

Thanks for looking into this.  It looks like the table_cache size can be kept fairly small even though the size of max_connections needs to be somewhat larger.  I think the computation in the initial post is correct - webwork leaves database connections open.  It is important since exhausting max_connections can cause webwork to fail.

For those who are interested, here is some data.  At this moment our server has a load of 0.04, so it is a slow night for math homework in Tempe.  Apache has 25 children running and 149 threads are open in mysql.  Mysql has 175 tables open, and it looks like 303 file descriptors open for mysql (not including sockets).  Note, I would definitely leave some room for slack in file descriptors for mysql.  At least on linux, it looks like the binary itself gets a file descriptor, along with the error log (and maybe a few other files).

In the running system, there are instances where mysql has more than one file descriptor open to the same file.  This comes from different children having connections open to the same database and using the same table.  I was surprised that mysql would not reuse the open file descriptor, but maybe it doesn't try unless the cache has been exhausted.


<| Post or View Comments |>