3

My MySQL server is running out of physical memory and is beginning to use swap. How do I tell what is using the most memory? Ideally, it would be a specific table or group of indices and I could optimize just a small number of items.

My DB is over 100GB and my current machine has 15GB memory.

Thanks.

user353859
  • 73
  • 1
  • 8
  • If you're using InnoDB storage engine, check your `innodb_buffer_pool_size` variable. I'll go out on a limb and assume you have assigned most of your memory for innodb_buffer_pool and the remainder of the processes are struggling to get some ram to work. – Furicane Dec 20 '11 at 08:24
  • http://stackoverflow.com/questions/1178736/mysql-maximum-memory-usage/3987796#3987796 – Robert Harvey Jan 18 '12 at 22:34

2 Answers2

6

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 & 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.

This answer is referenced from a similar question on stackoverflow regarding Mysql Memory Usage: MySQL maximum memory usage

Hope this helps.

Community
  • 1
  • 1
talha2k
  • 24,937
  • 4
  • 62
  • 81
0

Take a look at the results of a SHOW FULL PROCESSLIST query or turn on slow query log to pinpoint the slower queries.

ChrisR
  • 14,370
  • 16
  • 70
  • 107
  • Does slow query always equal to using most memory? I have some update and lookup queries that use the primary key running very slow. Thanks. – user353859 Dec 20 '11 at 08:07