Hi!
The problem might indeed be a too big tmp_table_size. Try decreasing it to
16M.
Regards,
Heikki
"Heikki Tuuri" <Heikki.Tuuri@innodb.com> kirjoitti
viestissä:YhdWd.34$Le6.24@read3.inet.fi...[color=blue]
> Eddie,
>
> oops, I did not notice this:
>[color=green][color=darkred]
>>> Top shows mysql steadily consuming more resourses over a period of 2
>>> hours, up to 1.2 Gb, before it crashes.[/color][/color]
>
> Please monitor also SHOW PROCESSLIST. Do connections pile up? Are they
> doing sorting?
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> Foreign keys, transactions, and row level locking for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
> MyISAM tables
>
http://www.innodb.com/order.php
>
> Order MySQL Network from
http://www.mysql.com/network/
>
>
>
> "Heikki Tuuri" <Heikki.Tuuri@innodb.com> kirjoitti
> viestissä:lfdWd.32$Le6.21@read3.inet.fi...[color=green]
>> Eddie,
>>
>> what does
>>
>> ulimit -a
>>
>> print?
>>
>> If you monitor the mysqld process size with the 'top' command, how big is
>> it?
>>
>> Clearly, InnoDB is not the root of the problem, as it has only allocated
>> 600 MB of memory.
>>
>> You can try setting tmp table size even lower.
>>
>> Best regards,
>>
>> Heikki Tuuri
>> Innobase Oy
>> Foreign keys, transactions, and row level locking for MySQL
>> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
>> MyISAM tables
>>
http://www.innodb.com/order.php
>>
>> Order MySQL Network from
http://www.mysql.com/network/
>>
>>
>> "Eddie" <e.moojen@chello.nl> kirjoitti
>> viestissä:1109888766.656143.123130@z14g2000cwz.goo glegroups.com...[color=darkred]
>>>I have a MySQL-server running Innodb. We have installed ~ 2GB of
>>> memory in the server. In spite of this MySQL keeps crashing due to
>>> out-of-memory errors. The server is a dual xeon i686 running kernel
>>> Linux 2.4.20-8smp redhat stock.
>>>
>>>
>>> Top shows mysql steadily consuming more resourses over a period of 2
>>> hours, up to 1.2 Gb, before it crashes.
>>>
>>> Any clues?
>>>
>>> ------------------------------
>>>
>>> [root@s10 mysql]# uname -a
>>> Linux s10.xxx.net 2.4.20-8smp #1 SMP Thu Mar 13 17:45:54 EST 2003 i686
>>> i686 i386 GNU/Linux
>>>
>>> ------------------------------
>>>
>>> [root@s10 mysql]# mysql --version
>>> mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)
>>>
>>> ------------------------------
>>>
>>> The error log shows:
>>> [root@s10 mysql]# tail -n 98 /var/lib/mysql/s10.xxx.net.err
>>> 050303 19:26:40 [Warning] Asked for 196608 thread stack, but got 126976
>>> 050303 19:26:41 InnoDB: Started; log sequence number 1 2349435259
>>> /usr/sbin/mysqld: ready for connections.
>>> Version: '4.1.9-standard-log' socket: '/var/lib/mysql/mysql.sock'
>>> port: 3306 Official MySQL RPM
>>> 050303 22:31:43 InnoDB: Fatal error: cannot allocate 8404992 bytes of
>>> InnoDB: memory with malloc! Total allocated memory
>>> InnoDB: by InnoDB 598183234 bytes. Operating system errno: 12
>>> InnoDB: Cannot continue operation!
>>> InnoDB: Check if you should increase the swap file or
>>> InnoDB: ulimits of your operating system.
>>> InnoDB: On FreeBSD check you have compiled the OS with
>>> InnoDB: a big enough maximum process size.
>>> InnoDB: We now intentionally generate a seg fault so that
>>> InnoDB: on Linux we get a stack trace.
>>> mysqld got signal 11;
>>> This could be because you hit a bug. It is also possible that this
>>> binary
>>> or one of the libraries it was linked against is corrupt, improperly
>>> built,
>>> or misconfigured. This error can also be caused by malfunctioning
>>> hardware.
>>> We will try our best to scrape up some info that will hopefully help
>>> diagnose
>>> the problem, but since we have already crashed, something is definitely
>>> wrong
>>> and this may fail.
>>>
>>> key_buffer_size=268435456
>>> read_buffer_size=2093056
>>> max_used_connections=16
>>> max_connections=100
>>> threads_connected=13
>>> It is possible that mysqld could use up to
>>> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
>>> = 671343 K
>>> bytes of memory
>>> Hope that's ok; if not, decrease some variables in the equation.
>>>
>>> thd=0x341dfa78
>>> Attempting backtrace. You can use the following information to find out
>>> where mysqld died. If you see no messages after this, something went
>>> terribly wrong...
>>> Cannot determine thread, fp=0xbfe5d298, backtrace may not be correct.
>>> Stack range sanity check OK, backtrace follows:
>>> 0x808e1b7
>>> 0x82e5a08
>>> 0x827868b
>>> 0x8278716
>>> 0x827c2b9
>>> 0x82310bd
>>> 0x81452d2
>>> 0x814681b
>>> 0x81601fe
>>> 0x817ac7b
>>> 0x8104933
>>> 0x8104e98
>>> 0x80fb593
>>> 0x810d359
>>> 0x809e6fe
>>> 0x80a2109
>>> 0x809c718
>>> 0x809c0e4
>>> 0x809b797
>>> 0x82e31bc
>>> 0x830ca8a
>>> New value of fp=(nil) failed sanity check, terminating stack trace!
>>> Please read
http://dev.mysql.com/doc/mysql/en/Us...ack_trace.html
>>> and follow instructions on how to resolve the stack trace. Resolved
>>> stack trace is much more helpful in diagnosing the problem, so please
>>> do
>>> resolve it
>>> Trying to get some variables.
>>> Some pointers may be invalid and cause the dump to abort...
>>> thd->query at 0xb0da9078 is invalid pointer
>>> thd->thread_id=628
>>> The manual page at
http://www.mysql.com/doc/en/Crashing.html contains
>>> information that should help you find out what is causing the crash.
>>>
>>> ------------------------------
>>>
>>> Relevent my.cnf parameters (after downsizing mem usage) are:
>>>
>>> ...
>>>
>>> # Try number of CPU's*2 for thread_concurrency
>>> thread_concurrency = 8
>>>
>>> ...
>>>
>>> #testing 20050131
>>> key_buffer = 256M #was 512M 19:04 2005-03-03
>>> max_allowed_packet = 1M
>>> table_cache = 256 # 2005-03-03 10:58 from 2048 (works)
>>> sort_buffer_size = 2M
>>> read_buffer_size = 2M
>>> read_rnd_buffer_size = 4M #was 8M 19:04 2005-03-03
>>> myisam_sort_buffer_size = 32M # was 64M 19:04 2005-03-03
>>> thread_cache = 8
>>> query_cache_size = 32M
>>> tmp_table_size=126M # 2005-03-01 10:58 from 126 2005-03-03
>>>
>>> ... innodb ....
>>>
>>> # You can set .._buffer_pool_size up to 50 - 80 %
>>> # of RAM but beware of setting memory usage too high
>>> innodb_buffer_pool_size = 512M
>>> innodb_additional_mem_pool_size = 20M
>>> # Set .._log_file_size to 25 % of buffer pool size
>>> innodb_log_file_size = 100M
>>> innodb_log_buffer_size = 8M
>>> #If you can afford the loss of some latest committed transactions,
>>> #you can set the `my.cnf' parameter innodb_flush_log_at_trx_commit to 0
>>> innodb_flush_log_at_trx_commit = 1
>>> innodb_lock_wait_timeout = 50
>>>
>>> ...
>>>[/color]
>>
>>[/color]
>
>[/color]
