MySQL Bugs: #10778: Memory allocated by the server is never released

Bug #10778	Memory allocated by the server is never released
Submitted:	20 May 2005 21:28	Modified:	27 Jun 2006 14:17
Reporter:	Kurt Fitzner	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.20	OS:	HP/UX (HP/UX 11.11 PARISC 2.0)
Assigned to:		CPU Architecture:	Any

Description:
Memory allocated by the mysql server daemon never seems to be released.  This continues until the server draws so much memory that it begins to return errors on queries due to low memory conditions.

How to repeat:
This only appears on the HP/UX port.  My only machine is an old B-class B132L PA-Risc 1.1 with HP/UX 11.00, so I can't test this on more modern HP/UX machines.

This bug appears on 4.0.24 and 4.1.12 on both the supplied binaries and binaries compiled on my machine (with GCC 3.1).

The bug is especially egregious when inserting records with large blobs (or any record that exceeds net_buffer_length bytes).  The server seems to allocate more memory for the row when it exceeds net_buffer_length bytes and then does not release the memory after the row is inserted, leading eventually to memory exhaustion.

Would you, please, send the following information.

1. The results of ulimit -a for the mysql user (default values of some kernel paramers are know to be really low on HP-UX 11.00)

2. The results of SHOW VARIABLES

3. Part of the error log for the period when it seems there is not enough memory for mysqld (after insertion of blob values). Sample table and insert statements will be of some interest too.
 
4. The results of SHOW STATUS after a reasonable time of mysqld working.

5. The results of the top command executed under real load.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This has now been tested on a C160 (64-bit PARISC 2.0) running HPUX 11iv1 (11.11) with MySQL versions 4.1.18 and 5.0.20 with identical results.  The original B132L that this was reported under no longer runs HPUX, so is unavilable for testing.

In response to the requested information:
1) ulimit -a produces:
core file size        (blocks, -c) 2097151
data seg size         (kbytes, -d) 524288
file size             (blocks, -f) unlimited
max memory size       (kbytes, -m) unlimited
open files                    (-n) 200
pipe size          (512 bytes, -p) 16
stack size            (kbytes, -s) 16384
cpu time             (seconds, -t) unlimited
max user processes            (-u) 201
virtual memory        (kbytes, -v) unlimited

2) SHOW VARIABLES - will attach as file.

3) The machine being used to test now is used in production for other purposes - this precludes allowing the mysqld process to use all available RAM until it produces an out-of-memory error.

4) Show status (after all backups/restores performed): will attach as file

5) The database is never under any real load on this machine, as this memory issue makes it impossible to use in production.  Here are some samples from top at various times that demonstrate the memory increase:
 a) Just started
 b) Connected to for first time
 c) Disconnected (nothing done between b and c)
 d) Connected, beginning to restore a database (10% complete)
 e) ... 50% complete 
 f) Restore completed, disconnected.
 g) Connected, backing up the database
 TTY    PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND
pts/0  2615 sql      152 20 22536K  3680K run      0:00  2.43  1.66 mysqld a
pts/0  2615 sql      152 20 23504K  4160K run      0:00  0.04  0.04 mysqld b
pts/0  2615 sql      152 20 23372K  4144K run      0:00  0.02  0.02 mysqld c
pts/0  2615 sql      152 20 29300K  9876K run      0:03  8.39  8.38 mysqld d
pts/0  2615 sql      152 20 86900K 60484K run      0:18  6.62  6.60 mysqld e
pts/0  2615 sql      152 20   129M   110M run      1:09  1.55  1.55 mysqld f
pts/0  2615 sql      152 20   131M   111M run      2:22  3.53  3.52 mysqld g

SHOW STATUS result

Attachment: show_status.txt (text/plain), 6.33 KiB.

SHOW VARIABLES result

Attachment: show_variables.txt (text/plain), 6.31 KiB.

Please, try to repeat with a newer version, 5.0.21, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".