List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:November 4 2010 8:21am
Subject:Re: Out of memory error
View as plain text  
Out of your 4 gigabyte of memory, you allocate 2G to the innodb pool.
Assuming you're using mostly innoDB, that's good. Say there's also about
300M allocated to the OS - assuming a dedicated server; that leaves about
1.7G for non-InnoDB operations.

You have configured your server for 500 connections, and specified both
join_buffer_size and sort_buffer_size as 8M.

Every session that needs to do a sort allocates one sort buffer of the
specified size. One join buffer of *minimum* this size is allocated for
every full join between two tables, so multiple buffers may be needed for a
single query.

8M * 500 connections = 2G.

Do you see where this is going ? :-)

Not every connection will need to allocate join or sort buffers, but it may
be well worth to see what's happening on your server when you get these out
of memory errors, and maybe you can optimize some queries to use indices for
sorting, add indices to remove full joins, et cetera.

I keep telling people this: set up Munin or Cacti, and graph anything you
can find about your server. Trending gives you both the ability to look back
at what was going on when a problem occured, and look forward to see
problems coming ahead of time.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Thread
Out of memory error4 Nov
  • Re: Out of memory errorSharl.Jimh.Tsin4 Nov
  • Re: Out of memory errorJohan De Meersman4 Nov