visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: MySql 5.0.27 suddenly consuming a lot of memory (RAM) after upgrade and despite no InnoDB use
By: Nono Great Britain  Date: 18/01/2007 14:10:54  English French  Points: 20 Status: Answered
Quality : Excellent
I'm using MySql for years on *nix and zindoze alike. I am usually running a Win2K SP4 server and used MySql 3.23.x to MySql 5.0.20 with no problem. tweaking the file my.ini is usually very minimal. Memory consumption (InnoDB and MyISAM engines only) is usually less than 64 MB.

Now I installed the latest (MySql 5.0.27) and one more GB of RAM (so I have 2 GB) and the Mysql service consumes 600 MB of RAM as soon as it starts ! Stop the service : 307 MB RAM consumed ; start the service : 910 MB !

Is this a nominal situation ?

thanks for advice
By: VGR Date: 18/01/2007 14:12:04 English  Type : Comment
what are your my.ini/my.cnf settings ?
did you leave the default values in there ?
By: Nono Date: 18/01/2007 14:12:38 English  Type : Comment
As said above, My.ini File is minimal :

[mysqld]
basedir=D:/MySQL
datadir=S:/datadir

Server Status Values
show status : normal

Server Info 5.0.27-community-nt
Host Info localhost via TCP/IP
Client Info 5.0.27
Proto Info 10

Last Lines from Err File

(none)

I'm now looking at the variables to check if, by default and any unlock, MySql installator isn't using too much memory.

If anybody knows a rule like "by default, mysql installator takes 50% of available RAM for the query cache", I'm interested ;-)
By: VGR Date: 18/01/2007 14:32:06 English  Type : Answer
ok, then the problem is the same than the one I had some days ago.

It lies in MySql's default config at installation time. It's silly and I wouldn't have believed it, but it's sadly true :
-InnoDB (that I kept by charity) is configured to take ***a lot*** of RAM (say, 320 MB ;-) for its buffer_pool - that's crazyness
the query cache size is over 100MB
the key buffer size is 164 MB
-MyISAM is also configured to use too much RAM
-Nobody asked your advice on the settings put in my.ini by the installator

I was accustomed to MySql installator's default values for RAM consumption being ***reasonable*** in the 16 - to 64 MB range.

The problem is thus two-fold :
1) new default installator behaviour is "take as much memory as seems fit to me and write to my.ini without warning the user"
2) new default MySqld behaviour is "allocate InnoDB caches and stuff even if innoDB is disabled"

So it introduces a ***new behaviour*** for you when you install & configure a MySql Server :
1) review all my.ini file and change default values for caches, buffers etc *** even for InnoDB if you don't use InnoDB and have "disabled" it *** with have_innoDB=DISABLED (new directive for 5.0.x AFAIK)
2) this is different with the previous versions'
3) try to stop and start the MySqld service to see how much RAM is allocated on start

Now we have to fine-tune every new MySqld server installation for a lower fingerprint.

Thanks for the unnecessary changes, MySql people :/

Couldn't we have an installation option "do things like in 3.23" ? ;-)
or a new configuration option "keep_innodb_minimal" or "fingerprint_size" = [minimal|reasonable|guessed|optimal|user_value] ?

For your reference, I posted a long time ago an online tool to analyze a running MySqld server, and hereafter are the default 5.0.27-community MySql server settings when installing on a 2GB zindoze machine, as extracted by my tool :

Memory Footprint read buffer size = 61440 (60 KB) sort buffer size = 262136 (256 KB) standard max memory = key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections standard max memory = 196984032 (187.86 MB) InnoDB Log buffer size (flushed once/s anyway) = 4194304 (4 MB) InnoDB Cache for Indices and Rows (80% RAM max.) = 319815680 (305 MB) InnoDB Additional Memory pool (64MB default) = 7340032 (7 MB) InnoDB Allocated memory = 331350016 (316 MB) Query cache size (0=disabled) = 104857600 (100 MB) Key Index cache size (0=disabled) = 164626432 (157 MB) Caches Allocated memory = 269484032 (257 MB) ---------- Min Allocated memory = 767078360 (731.54 MB) Max Allocated memory = 797818080 (760.86 MB)



As you can see, it's about time to lower the footprint ;-)

As to solve your problem, I recommend to adapt your my.ini file as mine : (extract of relevant sections)

[mysqld] default-storage-engine=MyISAM #VGR14012007 MOD from 100 to 20 max_connections=20 #VGR14012007 MOD from 100M to 16M (as in my-large.ini...) query_cache_size=16M #VGR ADDed from my-large.ini # ? skip-locking max_allowed_packet = 1M sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 2 #EoAdd #*** MyISAM Specific options #VGR14012007 MOD from 100G to 2G myisam_max_sort_file_size=2G #VGR14012007 MOD from 100G to 2G myisam_max_extra_sort_file_size=2G #VGR14012007 MOD from 205M to 64M (as in my-large.ini...) myisam_sort_buffer_size=64M #VGR14012007 MOD from 157M to 16M key_buffer = 16M key_buffer_size=16M #*** INNODB Specific options *** # Use this option if you have a MySQL server with InnoDB support enabled # but you do not plan to use it. This will save memory and disk space # and speed up some things. #VGR14012007 MOD uncommented to save memory & disk and speedup startup skip-innodb



If you want, you can also add the following files from my-large.ini template, that are missing from the my.ini file produced by the installator :

#VGR14012007 ADDed from my-large.ini [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout



Those changes have put my server back at a reasonable consumption of 54 MB or so
By: VGR Date: 18/01/2007 14:36:06 English  Type : Comment
I hope this will help you to solve your problem
By: VGR Date: 18/01/2007 14:39:41 English  Type : Comment
if not, give me some more feedback, thanks
By: VGR Date: 18/01/2007 14:44:43 French  Type : Comment
ok ?
By: Nono Date: 19/01/2007 07:16:48 English  Type : Comment
COOL STUFF, problem solved easily.

Nice to have YOU around !
By: Nono Date: 19/01/2007 07:17:15 English  Type : Comment
where is the test page you talked about ?
By: VGR Date: 19/01/2007 07:24:44 English  Type : Comment
AFAIK, it's listed on this Edaìn Works page (name is "survserveur")

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 385.914800 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page