MySQL maximum memory usage

Question

I would like to know how it is possible to set an upper limit on the amount of memory MySQL uses on a Linux server    Right now  MySQL will keep taking up memory with every new query requested so that it eventually runs out of memory  Is there a way to place a limit so that no more than that amount is used by MySQL

User · Answer

We use these settings   etc my cnf innodb buffer pool size   384M key buffer   256M query cache size   1M query cache limit   128M thread cache size   8 max connections   400 innodb lock wait timeout   100   for a server with the following specifications   Dell Server CPU cores  Two Processor s   1x Dual Xeon Clock Speed   gt   2 33GHz RAM  2 GBytes Disks  1  250 GB SATA

User · Answer

MySQL s maximum memory usage very much depends on hardware  your settings and the database itself   Hardware  The hardware is the obvious part  The more RAM the merrier  faster disks ftw  Don t believe those monthly or weekly news letters though  MySQL doesn t scale linear - not even on Oracle hardware  It s a little trickier than that   The bottom line is  there is no general rule of thumb for what is recommend for your MySQL setup  It all depends on the current usage or the projections   Settings  amp  database  MySQL offers countless variables and switches to optimize its behavior  If you run into issues  you really need to sit down and read the  f ing  manual   As for the database -- a few important constraints    table engine  InnoDB  MyISAM       size indices usage   Most MySQL tips on stackoverflow will tell you about 5-8 so called important settings  First off  not all of them matter - e g  allocating a lot of resources to InnoDB and not using InnoDB doesn t make a lot of sense because those resources are wasted   Or - a lot of people suggest to up the max connection variable -- well  little do they know it also implies that MySQL will allocate more resources to cater those max connections -- if ever needed  The more obvious solution might be to close the database connection in your DBAL or to lower the wait timeout to free those threads   If you catch my drift -- there s really a lot  lot to read up on and learn   Engines  Table engines are a pretty important decision  many people forget about those early on and then suddenly find themselves fighting with a 30 GB sized MyISAM table which locks up and blocks their entire application   I don t mean to say MyISAM sucks  but InnoDB can be tweaked to respond almost or nearly as fast as MyISAM and offers such thing as row-locking on UPDATE whereas MyISAM locks the entire table when it is written to   If you re at liberty to run MySQL on your own infrastructure  you might also want to check out the percona server because among including a lot of contributions from companies like Facebook and Google  they know fast   it also includes Percona s own drop-in replacement for InnoDB  called XtraDB   See my gist for percona-server  and -client  setup  on Ubuntu   http   gist github com 637669  Size  Database size is very  very important -- believe it or not  most people on the Intarwebs have never handled a large and write intense MySQL setup but those do really exist  Some people will troll and say something like   Use PostgreSQL   111   but let s ignore them for now   The bottom line is  judging from the size  decision about the hardware are to be made  You can t really make a 80 GB database run fast on 1  GB of RAM   Indices  It s not  the more  the merrier  Only indices needed are to be set and usage has to be checked with EXPLAIN  Add to that that MySQL s EXPLAIN is really limited  but it s a start   Suggested configurations  About these my-large cnf and my-medium cnf files -- I don t even know who those were written for  Roll your own   Tuning primer  A great start is the tuning primer  It s a bash script  hint  you ll need linux  which takes the output of SHOW VARIABLES and SHOW STATUS and wraps it into hopefully useful recommendation  If your server has ran some time  the recommendation will be better since there will be data to base them on   The tuning primer is not a magic sauce though  You should still read up on all the variables it suggests to change   Reading  I really like to recommend the mysqlperformanceblog  It s a great resource for all kinds of MySQL-related tips  And it s not just MySQL  they also know a lot about the right hardware or recommend setups for AWS  etc   These guys have years and years of experience   Another great resource is planet-mysql  of course

User · Answer

mysqld exe was using 480 mb in RAM  I found that I added this parameter to my ini  table definition cache   400   that reduced memory usage from 400 000  kb down to 105 000kb

User · Answer

If you are looking for optimizing your docker mysql container then the below command may help  I was able to run mysql docker container from a default 480mb to mere 100 mbs  docker run -d -p 3306 3306 -e MYSQL DATABASE test -e MYSQL ROOT PASSWORD tooor -e MYSQL USER test -e MYSQL PASSWORD test -v  mysql  var lib mysql --name mysqldb mysql --table definition cache 100 --performance schema 0 --default-authentication-plugin mysql native password

User · Answer

in  etc my cnf    mysqld       performance schema   0  table cache   0 table definition cache   0 max-connect-errors   10000  query cache size   0 query cache limit   0        Good work on server with 256MB Memory

User · Answer

Database memory usage is a complex topic   The MySQL Performance Blog does a good job of covering your question  and lists many reasons why it s hugely impractical to  reserve  memory   If you really want to impose a hard limit  you could do so  but you d have to do it at the OS level as there is no built-in setting   In linux  you could utilize ulimit  but you d likely have to modify the way MySQL starts in order to impose this     The best solution is to tune your server down  so that a combination of the usual MySQL memory settings will result in generally lower memory usage by your MySQL installation   This will of course have a negative impact on the performance of your database  but some of the settings you can tweak in my ini are   key buffer size query cache size query cache limit table cache max connections tmp table size innodb buffer pool size   I d start there and see if you can get the results you want   There are many articles out there about adjusting MySQL memory settings     Edit   Note that some variable names have changed in the newer 5 1 x releases of MySQL   For example   table cache   Is now   table open cache

[mysql] MySQL maximum memory usage

Examples related to mysql

Examples related to memory

Examples related to ram