Mem Usage in mysql

[Anon]

I am running mysql on linux , it has 30 mysqld connections each taking 32MB, does anyone have any ideas on what could be wrong with my configuration.

I have Innodb but none of the databases are Innodb.

Thanks

[wombatee]

Depending on what you're looking at, this may not be a problem. If you're looking at the output of top, then you may be seeing the totals. top will show 30 mysql processes, but the memory space is shared between them all, so when it says "32M" beside each process, that's really 32 MB between them all. The RSS column of top indicates the physical memory in use by the processes. In all likelihood here, you've got 30 mysql threads using about 1MB each.

With all that in mind, you should check on your mysql server settings. Two important settings here might be the size of your sort_buffer and your record_buffer (See http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html). These amounts are allocated per thread, so you should be careful with them.

Depending on the way you installed MySQL, you might have some sample configurations available... I find these are quite handy and work well for many setups. Do a "locate my-" to find files named like this:

my-huge.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

These are great sample configs that you may want to put in place as a starting point to tuning your server.

Cheers!

Omega Wombat
http://www.drinknation.com

[Anon]

Hey..
thanks for your help, I checked up the TOP and the RSS shows me 35MB , i think i misreported 32 last time, it was 32 then too..
and my sort_buffer and record_buffer are pretty small..

record_buffer 131072
sort_buffer 524280
my thread_cache_size - 0 , is that a problem.. in my mysql server it shows me only 17 connections and all of them are in sleep.. I will look at the sample my.cnf and compare them with mine and see.. if i have anything goofed up..

Thanks

[wombatee]

thread_cache_size is sometimes hard to get a handle on. I don't think I've got it all figured out myself, but I don't think it's that important, either. You can read the description on that same page I mentioned before... I think the line "This variable can be increased to improve performance if you have a lot of new connections" sums it up in terms of what I need to know about it 🙂

Sleeping threads are just an indication that there is an open connection or an existing thread, but it's not currently running a query. For instance, if you run a script on your site that connects to a database, retrieves something at the start of the script, then you output a bunch of HTML, retrieve something else, output some more and stop, all the times you're outputting HTML or processing or whatever else your script is doing, the database thread is sleeping, waiting for the next query. For most threads, this will be most of the time, so you'll see a lot of sleeping threads in there.

Cheers!

Omega Wombat
http://www.drinknation.com