Have you seen this post?

http://www.mysqlperformanceblog.com/2006/05/17/mysql-server- memory-usage/


Just a hunch, it might be your max_allowed_packet and your max_connections settings. 150 x 32MB = 4.6875 GB. As large packets get sent to each connection, it's possible they allocate more memory to accomodate the large packet and never release it.

Try setting your max packet to something smaller.

Also look at reducing your number of connections. If you don't need 150, reduce the number.

For instance, on my busy forums (1M PHP hits daily), I have it limited to 48. I have 32 php-fastcgi processes, each which keeps a persistent connection to MySQL. I keep a few more around for things Debian maintenance scripts and running mytop or logging in myself.

Thanks for your hint regarding max_allowed_packet, haven't considered this. I will check it out next week (don't want to change during the weekend).

There were in fact changes to blobs during the last year when the crashes started. Well, the number of blobs - esp. with large size - highliy decreased, but sometimes changes have strange effects. Possible that the frequent use of larger blobs helped to free unused memory...

Further lowering the number of connections would be quite hard. The crash reports usually showed 110-401 max connections (the setting was 400 before my first changes). The report above was quite extraordinary regarding this fact.

BTW: The server has 8GB of physical memory. Would have been better to set it up with a 64-bit-install, but I worried additional troubles. atm there is no way to change this.

No luck. max_allowed_packet_size does not seem to change anything.
I lowered the value from 32 to 4 mb and restarted on 16.3., 19:00. The rise seems to quite the same as before.

I had calculated a max of 1617MB (1017MB + 150*4MB) for the new setting, after 4 days i allready see 2000MB. The rise is usually slower during the weekend, but I expect the server to crash on monday or tuesday.

Any other ideas/suggestions? I welcome any hint...
Best regards, Stephan

memory usage was as following:
virt. res.
17.3. 10:30 585m 497m
17.3. 14:39 677m 588m
18.3. 09:43 970m 878m
18.3. 12:58 1044m 953m
18.3. 23:55 1280m 1.2g
19.3. 08:55 1343m 1.2g
19.3. 17:07 1540m 1.4g
19.3. 20:53 1621m 1.5g
20.3. 11:02 1778m 1.6g
20.3. 20:30 2085m 1.9g

Max. connections used was 30 during the 4 days.

Hmm... I'm not sure then.

What is the output of
mysql> show status;
?

We might find a clue there.

show status (cleaned a bit)


+-----------------------------------+------------+| Variable_name | Value |+-----------------------------------+------------+| Aborted_clients | 130 || Aborted_connects | 0 || Binlog_cache_disk_use | 318 || Binlog_cache_use | 2937516 || Bytes_received | 117 || Bytes_sent | 170 || Com_admin_commands | 0 |... all zero ...| Com_savepoint | 0 || Com_select | 1 || Com_set_option | 0 |... all zero ...| Com_show_slave_status | 0 || Com_show_status | 1 || Com_show_storage_engines | 0 |... all zero ...| Com_xa_start | 0 || Compression | OFF || Connections | 2908869 || Created_tmp_disk_tables | 0 || Created_tmp_files | 919 || Created_tmp_tables | 1 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 0 |... all zero ...| Handler_update | 0 || Handler_write | 132 || Innodb_buffer_pool_pages_data | 15761 || Innodb_buffer_pool_pages_dirty | 22 || Innodb_buffer_pool_pages_flushed | 892741 || Innodb_buffer_pool_pages_free | 1 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 622 || Innodb_buffer_pool_pages_total | 16384 || Innodb_buffer_pool_read_ahead_rnd | 4547 || Innodb_buffer_pool_read_ahead_seq | 10603 || Innodb_buffer_pool_read_requests | 2259044321 || Innodb_buffer_pool_reads | 117628 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 59661331 || Innodb_data_fsyncs | 6025381 || Innodb_data_pending_fsyncs | 0 || Innodb_data_pending_reads | 0 || Innodb_data_pending_writes | 0 || Innodb_data_read | 2730332160 || Innodb_data_reads | 174968 || Innodb_data_writes | 6651957 || Innodb_data_written | 222682112 || Innodb_dblwr_pages_written | 892741 || Innodb_dblwr_writes | 41706 || Innodb_log_waits | 0 || Innodb_log_write_requests | 4738969 || Innodb_log_writes | 5905524 || Innodb_os_log_fsyncs | 5941323 || Innodb_os_log_pending_fsyncs | 0 || Innodb_os_log_pending_writes | 0 || Innodb_os_log_written | 1015899648 || Innodb_page_size | 16384 || Innodb_pages_created | 25643 || Innodb_pages_read | 952945 || Innodb_pages_written | 892741 || Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 817 || Innodb_row_lock_time_avg | 11 || Innodb_row_lock_time_max | 273 || Innodb_row_lock_waits | 72 || Innodb_rows_deleted | 3059761 || Innodb_rows_inserted | 3139589 || Innodb_rows_read | 23998375 || Innodb_rows_updated | 569052 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 7234 || Key_blocks_used | 982 || Key_read_requests | 21803056 || Key_reads | 111883 || Key_write_requests | 4245782 || Key_writes | 0 || Last_query_cost | 0.000000 || Max_used_connections | 30 || Not_flushed_delayed_rows | 0 || Open_files | 62 || Open_streams | 0 || Open_tables | 681 || Opened_tables | 0 || Prepared_stmt_count | 0 || Qcache_free_blocks | 0 || Qcache_free_memory | 0 || Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 0 || Qcache_queries_in_cache | 0 || Qcache_total_blocks | 0 || Questions | 101516527 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 1 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | ON || Slow_launch_threads | 0 || Slow_queries | 0 || Sort_merge_passes | 0 || Sort_range | 0 || Sort_rows | 0 || Sort_scan | 0 || Table_locks_immediate | 111831235 || Table_locks_waited | 48845 || Tc_log_max_pages_used | 0 || Tc_log_page_size | 0 || Tc_log_page_waits | 0 || Threads_cached | 19 || Threads_connected | 2 || Threads_created | 35 || Threads_running | 2 || Uptime | 362018 |+-----------------------------------+------------+

Nothing looks amiss there.

How big are your memory tables?

Many thanks for your efforts Mark, Checked the memory tables.
In fact it is only one (plus those in information_schema).

Size is 100-500 rows, 7 columns (about 100 bytes per row). But the table gets about 1/3 of the write requests.
Values are only valid for 3-90 seconds and old ones are deleted about twice per minute.

What's your max_heap_table_size set to?

Max heap table size was 16M.
I just reduced it to 4M and restarted the server. Memory usage was up to 2390M.

When you are looking at top, what column are you looking at?

The RES column is the actual amount of RAM being used. Don't forget that a process can map files on disk to memory locations, without actually loading them (though MySQL eventually will as pages are referenced). That's what the VIRT column shows. The pages of the files will only be moved into memory if needed, and multiple processes can map the same files (the amount of shared files shows up in the SHR column).

A 32 bit operating system is limited to 4GB of addressable space. Some of that 4GB is used up for things like the PCI bus, memory mapped to a graphics card, etc. That means less than 4GB of files can be mapped into memory at once, not to mention the other uses for the RAM.

If this is the problem, the best thing would be to move to a 64 bit system. The other thing we can try is limiting the number of open tables. The rule of thumb for table_cache is concurrent connections x n, where is the max number of tables used in joins in any query you may execute, plus some extra for temporary tables. With a setting of 1350, and 30 max connections, that would be up to 45 tables in a join... which seems a bit high. I don't know your queries, but a setting around 200 might be more appropriate. Try that?

I look at both columns. Before the last restart the values were 2390m and 2.2g.
I think the problem is virtual memory (32bit and 3/1g memory split).

Swapping (physical memory) is not an issue at all. Even when mysql is close to crashing there are 2.5GB used for file cache. On normal days IO-wait is about 0.15% during the high load times.

You are absolutely right about the 64bit system, but this would cause quite a bit of work...

I will check the results of the change in max_heap_table_size and try table_cache as well.

The strange thing is that the server ran well for half a year with 450 max connections (the table_cache 1350 from that setting) and much higher memory settings. On the other hand there were more changes to tables/databases during that time and also some manual restarts. Looks like the only way is to reconstruct the history from backups.

As far as I remember the only major change during the time I noticed the problems was the move of some large blob columns to the file system. Unfortunately the changes were made over half a year and any other - small change - might be the real issue as well.
I'll check if I can somehow correlate the issues with these changes...

Yeah, I'd say it's definitely the 3GB limit. You can tweak that little in the kernel, but you'll still be limited to less than 4GB at an absolute max with 32 bit Linux.

It's work, yes, but moving to 64 bits is the only way around that memory limit.

The only other thing I can think of is that the combined size of the InnoDB log files can't exceed 4 GB. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.h tml

Looks like I have a lead. After the last set of changes the server seems to be jailed within 1.1-1.2g (virtual memory). Thanks Mark.

Here is a short summary of the last changes:

The lowered max_heap_table_size does not look to have any relevance. I restarted after 4 days at 2.1g/1.9g (virt./res.) on 25.3. About the same rise as before and even before the changes in march (since I opened the topic).

Those were the changes for the last restart (25.3. 21:00) with much better mem usage:
- max_connections 150 > 100
- table_cache 1350 > 400
- open_files_limit 4000 > 1200
- thread_cache_size 20 > 10

I will try to isolate the responsible setting and report back.
Will try to check performance impact as well, but this has only minor relevance.

I also managed to nail down the set of changes that caused the issues. Well hidden in times of low usage and config changes so the problems appeared quite a bit later.
Anyway, there is no way to revert.

if you have many tables with blobs and those blobs are large, then table_cache can consume more memory than you think...

see http://bugs.mysql.com/bug.php?id=38002 for an example of this.