To answer your questions I'd like to ask mine

1) What storage engine do you use for your tables: innodb/myisam?

2) What CPU do you use on your server: 32-bit/64 bit?

3) What OS do you use: name, version, 32/64 bit?

1. Currently Myisam but I'm working on converting to innodb
2. OS & CPU are 64 bit
3. OS is centOS..not sure where the OS version info is ).

Is there some tool that dumps the size of/info about various mysql buffers/caches?

untenable wrote on Tue, 25 September 2007 16:01

1. Currently Myisam but I'm working on converting to innodb 2. OS & CPU are 64 bit 3. OS is centOS..not sure where the OS version info is ). Is there some tool that dumps the size of/info about various mysql buffers/caches?

1. Great - innodb could be much better here because it could cache data pages along with indexes in memory.
2. Ok. 64 bit would allow you to use more than 4gb in mysql.
3. uname -a, please

Ah thanks. Unfortunately I spent too much time in Windows.
Here's the full version info:

Linux 2.6.9-55.0.6.ELsmp #1 SMP Tue Sep 4 21:36:00 EDT 2007 i686 i686 i386 GNU/Linux

So you're saying there is no utility that tells you how MySQL is using memory?

untenable wrote on Tue, 25 September 2007 16:10

Linux 2.6.9-55.0.6.ELsmp #1 SMP Tue Sep 4 21:36:00 EDT 2007 i686 i686 i386 GNU/Linux

Unfortunately this means your mysql can't use more than 4Gb ram - you have 32bit version of Linux kernel running on your server (

Oh. Urgh.

Well MySQL is only using 2GB of that 4GB address space. Why )?

Usually you reserve half of the process addressspace for OS usage.
That's why you can only use 2GB.

There are too many things which could be tuned to have mysql using more memory. Major ones are:

* key_buffer_size
Used by MyISAM tables to cache Index only, not data
30% of memory for MyISAM only system
4GB limit per key buffer, can have multiple of these
MyISAM tables used for temporary tables anyway

* innodb_buffer_pool_size
Used by Innodb tables
70% of memory for Innodb only system
Innodb performance is very critical to this setting

* query_cache_size
Set if using query cache

* read_buffer_size, read_rnd_buffer_size
sequential read buffers used by MyISAM and some others
Allocated when needed.
1M/4M typically good to start

* sort_buffer_size
buffer used for sorting. Increase if a lot of large sorts are done

* tmp_table_size
Maximum size of in-memory table allowed (used by complex
queries)
May need more than one temporary table per query
max_heap_table_size limit is also used


So, if you use myisam on your server, I'd like you to increase your key_buffer_size which could improve your work with indexes. In case of innodb - innodb_buffer_pool_size is what you'd like to increase. But anyways, when you'll increase any values you have too keep in mind, that you have 4Gb of RAM only (if mysql would try to allocate more, it could crash).

Yeah, I got those covered but it's still not consuming more than 2GB RAM.

I'd love to have you spend a couple hours looking at it and pay you for that time instead of asking lots of questions on the forums.

But from what I hear you guys charge $200/hour?

I could do like $400 for the job. It might take you less than two ours but could take up to 4 or 6...

sterin wrote on Tue, 25 September 2007 22:35

Usually you reserve half of the process addressspace for OS usage. That's why you can only use 2GB.

From my post two steps up.

If you want to use your servers potential you should run a 64bit OS and 64bit MySQL.

untenable wrote on Tue, 25 September 2007 16:43

Yeah, I got those covered but it's still not consuming more than 2GB RAM. I'd love to have you spend a couple hours looking at it and pay you for that time instead of asking lots of questions on the forums. But from what I hear you guys charge $200/hour? I could do like $400 for the job. It might take you less than two ours but could take up to 4 or 6...

Of course we could try to help. Please, fill out our consulting request form and we'll contact you ASAP.