MySQL consuming lots of memory during transactions w/ many statements

[saran]

I am having a problem with MySQL consuming a lot of memory and
eventually throwing an Out of Memory error and restarting itself. The
symptoms are that swap usage continues to rise until some breaking
point. The application is a typical web application w/ 2 web servers
running Apache/Tomcat connecting to a dedicated DB server running only
MySQL.

This seems to occur as a result of running many statements in a single
transaction, both against InnoDB tables and MyISAM tables. In one
case, I'm writing all user actions to an audit table (MyISAM), all from
a single application thread doing approximately 5K inserts (as single
INSERTs on a single connection) every few seconds. In the other, I'm
doing a variety of select/update/insert/delete statements against
InnoDB tables, but again totaling several thousand in one transaction.
Both of these problems occur even when there is relatively low activity
elsewhere on the system.

Any help would be much appreciated. I will probably move the auditing
function onto a separate DB, but the other problem is harder to avoid
in my application. If breaking up the work into multiple transactions
would help, I can do that, but I'm hoping it's a configuration issue of
some kind instead, because this doesn't strike me as a volume that
should be an issue. But if this behavior is expected for some reason,
I can change my application appropriately.

My server:

MySQL 5.0.18
Enterprise Red Hat Linux 3.0 - kernel 2.4.21-37
Dual Xeon processors
4GB RAM
2GB Swap space

My my.cnf file:

# Max Connections (2 webapps w/ ~50 each, email w/ ~10, command-line)
# 200 should be plenty - max concurrent we've had is 90
max_connections=200

#
# InnoDB parameters
# innodb_buffer_pool_size (512M)
# + key_buffer_size (50M)
# +
max_connections*(sort_buffer_size+read_buffer_size +binlog_cache_size)
# 200 * (1M + 1M + 32K) = 406M
# + max_connections*2MB = 400M
# MUST NOT BE > 2GB
#
innodb_buffer_pool_size = 512M # reduced from 1GB
innodb_additional_mem_pool_size = 20M
# the default, set to 2 and can lose 1 sec tx but w/ better performance
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
# Set innodb_log_file_size between 1 and 1/(# logs) * buffer_pool_size
innodb_log_file_size = 512M
innodb_lock_wait_timeout = 50 # default

#
# MyISAM params and others
#
skip-external-locking
key_buffer = 50M # Only few MyISAM tables, so this doesn't need to be
huge
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
thread_cache_size = 8

# turn query cache off - all our queries are through prepared
statements
query_cache_type = 0
query_cache_size= 8M

thread_concurrency = 8
open_files_limit=4096

Output from sar (I started the auditing process at 11:18 or so)

10:16:00 AM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree
kbswpused %swpused kbswpcad
10:18:00 AM 53312 4042072 98.70 41872 2066840 1895504
144740 7.09 87180
10:20:00 AM 18036 4077348 99.56 41904 2052660 1895504
144740 7.09 87204
10:22:00 AM 69448 4025936 98.30 41736 2024472 1888508
151736 7.44 94248
10:24:00 AM 24636 4070748 99.40 41792 2059008 1888508
151736 7.44 94272
-------
11:18:00 AM 22892 4072492 99.44 42696 1978296 1889756
150488 7.38 91988
11:20:00 AM 114404 3980980 97.21 42268 1874360 1876972
163272 8.00 103576
11:22:00 AM 40180 4055204 99.02 42360 1936572 1871964
168280 8.25 108592
11:24:00 AM 20688 4074696 99.49 42544 1938640 1861324
178920 8.77 103696
11:26:00 AM 58724 4036660 98.57 42516 1910320 1854764
185480 9.09 95768
11:28:00 AM 25184 4070200 99.39 42464 1916800 1851216
189028 9.26 92692
11:30:00 AM 27652 4067732 99.32 42476 1918600 1846996
193248 9.47 96344
11:32:00 AM 25816 4069568 99.37 42852 1916080 1843812
196432 9.63 93896
11:34:00 AM 28332 4067052 99.31 42892 1912052 1843448
196796 9.65 88120
11:36:00 AM 23480 4071904 99.43 42832 1921680 1837276
202968 9.95 93836
11:38:00 AM 25360 4070024 99.38 42804 1905608 1834232
206012 10.10 88156
11:40:00 AM 20776 4074608 99.49 42920 1915008 1829128
211116 10.35 85244
11:42:00 AM 18048 4077336 99.56 42912 1924228 1825368
214876 10.53 85032
11:44:00 AM 53908 4041476 98.68 42772 1884004 1815200
225044 11.03 87628
11:46:00 AM 39784 4055600 99.03 42776 1908400 1809480
230764 11.31 84816
11:48:00 AM 21604 4073780 99.47 42864 1946080 1809480
230764 11.31 81416
11:50:00 AM 21092 4074292 99.48 42432 1940928 1809692
230552 11.30 77992
11:52:00 AM 17916 4077468 99.56 42348 1943732 1809692
230552 11.30 81092
11:54:00 AM 30684 4064700 99.25 42268 1933580 1809692
230552 11.30 81096
11:56:00 AM 18032 4077352 99.56 42204 1904984 1805388
234856 11.51 85504
11:58:00 AM 33072 4062312 99.19 42280 1888468 1803640
236604 11.60 79684
12:00:00 PM 28416 4066968 99.31 42232 1887656 1801864
238380 11.68 82088
12:02:00 PM 26012 4069372 99.36 42324 1886856 1801452
238792 11.70 80528
12:04:00 PM 24428 4070956 99.40 42368 1894824 1799092
241152 11.82 83652
12:06:00 PM 25156 4070228 99.39 42420 1927332 1798424
241820 11.85 83188
12:08:00 PM 18756 4076628 99.54 42452 1923044 1797544
242700 11.90 85988
12:10:00 PM 258124 3837260 93.70 42096 1719520 1775700
264544 12.97 94252
12:12:00 PM 146912 3948472 96.41 42252 1818536 1772992
267252 13.10 98516
12:14:00 PM 54248 4041136 98.68 42252 1901680 1770864
269380 13.20 103080
12:16:00 PM 21584 4073800 99.47 42052 1933524 1770864
269380 13.20 94728
12:18:00 PM 20040 4075344 99.51 41760 1728828 1766220
274024 13.43 114904
12:20:00 PM 54424 4040960 98.67 41144 1590524 1722632
317612 15.57 144496
12:22:00 PM 52568 4042816 98.72 41356 1690840 1715372
324872 15.92 160616

[Bill Karwin]

"saran" <sa*********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I am having a problem with MySQL consuming a lot of memory and
eventually throwing an Out of Memory error and restarting itself.
Reading this page: http://dev.mysql.com/doc/refman/5.0/en/memory-use.html
the docs claim that there are no known memory leaks in MySQL, given that
they use several leak detection testing tools. This web page is interesting
reading, to understand how memory is used in MySQL.

If it is an error in the client, reading this page:
http://dev.mysql.com/doc/refman/5.0/...of-memory.html gives the
impression that the "Out of Memory" error is caused by a SQL query that
exceeds memory because of the size of the result set. This easily could be
caused by an accidental Cartesian product against relatively large tables.
You might want to review your queries throughout your code for possible
Cartesian products.
This seems to occur as a result of running many statements in a single
transaction, both against InnoDB tables and MyISAM tables.
Well, MyISAM tables do not support transactions. They are effectively
running under an "autocommit" mode. So if you see the error while running
against MyISAM tables, then it's not likely to be an issue of too many
statements per transaction.

Here's a thread on the MySQL Java forum that suggests that using updateable
result sets requires inserted rows to be cached on the client, and after a
few hundred thousand of these, causes an Out of Memory error:
http://lists.mysql.com/java/6297
Output from sar (I started the auditing process at 11:18 or so)

10:16:00 AM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree
kbswpused %swpused kbswpcad
10:18:00 AM 53312 4042072 98.70 41872 2066840 1895504

.. . .

So your physical memory is pretty much pegged at 97-100% usage. I've seen
Linux abort MySQL and/or Tomcat processes when physical RAM is filled up
like this, even if there still seems to be some swap space available. If
you can't reduce the memory usage by changing your SQL queries, your options
may be either:
1) Add more RAM to your primary server, or else:
2) Run Tomcat and MySQL on separate hosts, and allocate appropriate physical
RAM on each host.

Regards,
Bill K.

[saran]

Thanks for responding, Bill. Some clarification:

You might want to review your queries throughout your code for possible
Cartesian products.
For the situation in which the queries seem to be causing the problem,
the query in question will only ever return 100-500 rows. We do
however, run over a thousand of these queries in the same transaction.
And to get to those 100 rows, we're actually joining a very large table
on itself (> 6M rows), of couse with the proper where clauses in place
to restrict the results.
Well, MyISAM tables do not support transactions. They are effectively
running under an "autocommit" mode. So if you see the error while running
against MyISAM tables, then it's not likely to be an issue of too many
statements per transaction.
Right, what I meant for this situation since it's MyISAM and not InnoDB
is that all of the inserts are happening on the same connection,
without the connection ever being closed and re-opened. Is there any
state that would be held and not released on the server side that could
cause memory usage to grow? The table definition and insert we're
doing are:

CREATE TABLE db_audit (
query_id VARCHAR(100) NOT NULL,
query_sql TEXT NULL,
start_time DATETIME,
elapsed INTEGER,
INDEX (query_id),
INDEX (query_sql(50)),
INDEX (start_time)
) ENGINE = MYISAM;

INSERT INTO db_audit (query_id, query_sql, start_time, elapsed) VALUES
(a, b, c, d);

Any reason why running 5K of these simple inserts every few seconds
should use a lot of memory?
2) Run Tomcat and MySQL on separate hosts, and allocate appropriate physical
RAM on each host.

We are running Tomcat and MySQL on separate physical machines. The
MySQL box is not running anything but MySQL.

Thanks,
Saran