We have an Opteron server with 6 gig of RAM.
The issue used to be 4 gig - the max amount of memory a 32-bit processor could access. With 64-bit processors, the amount of accessible memory has jumped into the terrabyte range.
Pick a distribution that is for the AMD-64 (we use SuSE 8 Enterprise) and use the 64-bit binary. We used the hints inside the my.cnf for huge databases.
You need to apply that formula that you can find in the InnoDB section of the MySQL documentation:
Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB
Because you are using InnoDB, you can set your key_buffer_size fairly low.
On our machine with 6-gig, we have the following settings:
sort_buffer_size = 512K read_buffer_size = 512K max_connections = 1400 innodb_buffer_pool_size = 3G innodb_additional_mem_pool_size = 20M key_buffer = 16M
To apply the formula to our server, we get
3000 meg + 16 meg + (1400 * (.5 meg + .5 meg)) + 2800 meg
Which is about 7.2 gig of memory that might be used by MySQL in a worst case scenario (with all 1400 connections open). We never expect to hit 1400 connections, but we wanted to set it too high at first, and then shrink it down slowly.
MySQL is currently using about 3.1 gig at 168 queries per second, with 95 to 98 percent reads. Most of our database is in RAM at any given time. MySQL is using about 5 percent of the two CPUs under this configuration. On our busiest day, when our load is 30% higher, MySQL uses about 10% of the CPU cycles.
You should have no problem throwing more RAM into an Opteron. One thing to note, we had to compile our own MySQL - we were getting segfaults with the default binary (something to do with fpic, I believe - I didn't do the actual compilation).
Hope that helps.
David
Mark Steele wrote:
Hi folks,
I have to setup some high performance servers that will be used for MySQL databases and have a couple questions regarding MySQL running on Linux AMD-64 (Opteron).
We are looking at setting up these machines with 16-64 gb of RAM, can MySQL running on Linux handle this amount of RAM efficiently? Also most of the tables (almost all of them) will be using the InnoDB storage engine, any pointers on what configuration settings we should use? (for example on a 16 gb RAM server)
Anyone have experience with this kind of setup?
Regards,
Mark Steele
Implementation Director
CDT Inc.
Tel: (514) 842-7054
Fax: (514) 221-3395
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
