Memory Settings for MySQL?//Out of memory error

MySQL & MariaDB Other MySQL® Questions
1

Hallo,

since a few weeks I´m searching for a solutoin for my MySQL Problem that makes me crazy. I will try to explain it good as possible (sorry if I use bad english).

I have a soccer browsergame (hobby project) with about 2000 users. In the main time there are about 200-300 users at the same time online and also a game engine calculated the matches. This calculation will take about 15 minutes. For the users there is a Live-Ticker witch is refreshed every minute and it shows them the matches in “real-time” (105 minutes until ending). Between 1,5 and 2,5 hours after the matches are starting (so it could be that they are complety finshed) our server crashed down. In Protocol I find some messages like this: mysqld: Out of memory (Needed 1372680 bytes)

We use an AMD 64 X2 Dual Core 5200+ Processor and 4 GB Ram (Windows 2003 Server) for the Database Server. The Webserver is installed on a seperate machine. The defalut storage is InnoDB but we have much MyISAM tables (a historical thing) in our database.

I think we have not set up the parameters rigt. I hope some of you will see what we make wrong and can give me a tip how to make it better.

This is our my.ini:

[I]

CLIENT SECTION

------------------------------------------------------------ ----------

QL client library initialization.

[client]

port=3306

[mysql]

default-character-set=latin1

SERVER SECTION

------------------------------------------------------------ ----------

[mysqld]

port=3306

basedir=“C:/Program Files/MySQL/MySQL Server 5.1/”

datadir=“C:/Program Files/MySQL/MySQL Server 5.1/Data/”

default-character-set=latin1

default-storage-engine=INNODB

sql-mode=" STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI ON "

max_connections=1000

query_cache_size=1000M

table_cache=512

tmp_table_size=64M

thread_cache_size=8

#*** MyISAM Specific options

myisam_max_sort_file_size=1000M

myisam_max_extra_sort_file_size=1000M

myisam_sort_buffer_size=35M

key_buffer_size=512M

read_buffer_size=2M
read_rnd_buffer_size=8M

sort_buffer_size=64M

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=4M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=4M

innodb_buffer_pool_size=512M

innodb_log_file_size=24M

innodb_thread_concurrency=8

log-slow-queries=slowQuery.log

long_query_time=2

log-queries-not-using-indexes

log-warnings

log-error=errorLog.log
[/I]

2

No one here who know how to change the setting in my.ini for a 4GB machine? (

3

I had the same problem before. I didn’t know how to fix it but since I was messing with memory buffer size and etc at the time, I used MySQL server instant config wizard and with the my.ini file made by the wizard, mysql server became stable.

4

I just registered to tell you to look at the sample my.cnf that comes with the binaries (share/mysql/my-*.cnf). You probably want ‘my-innodb-heavy-4G.cnf’