MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
Yes you are right, these are Thread specific settings, leave them at 1M or 2M, atleast that's what I set on our productions systems, else you risk at running out of memory under high loads, and it works great, the other parameters as cited in the book High performance Mysql , by Jeremy Zawodny says this min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer Hope thsi helps Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
Since I can't get the book until this evening ... In most sample my.cnf files online I see the following setup: [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer=16M write_buffer = 16M If these are per thread -- aren't they a bit high? thanks. erin --- Kishore Jalleda [EMAIL PROTECTED] wrote: Yes you are right, these are Thread specific settings, leave them at 1M or 2M, atleast that's what I set on our productions systems, else you risk at running out of memory under high loads, and it works great, the other parameters as cited in the book High performance Mysql , by Jeremy Zawodny says this min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer Hope thsi helps Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Discover Yahoo! Get on-the-go sports scores, stock quotes, news and more. Check it out! http://discover.yahoo.com/mobile.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server Parameter (v. 4.0.20-pc-linux-i686)
Yes they are very high, also key_buffer_size is a global parameter shared by all threads, it is recommended to be around 25% of your RAM, I would suggest since you have 2GB ram the following key_buffer_size = 500M ( greatly improves speed ) sort_buffer_size= 2M read_buffer_size=2M write_buffer_size = 2M and for the section myisamck in my.cnf which is used only for table analysis and repair (that means when you run mysiasmchk) Note: myisamchk uses no more memory than you specify , you could use these key_buffer = 256M sort_buffer = 256M read_buffer=2M write_buffer = 2M Key_Buffer* is always global, I guess in this case( myisamchk) sort_buffer is also global , also you have couple of chapters which might interest you from that book online at http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://dev.mysql.com/books/hpmysql-excerpts/ch07.html Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: Since I can't get the book until this evening ... In most sample my.cnf files online I see the following setup: [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer=16M write_buffer = 16M If these are per thread -- aren't they a bit high? thanks. erin --- Kishore Jalleda [EMAIL PROTECTED] wrote: Yes you are right, these are Thread specific settings, leave them at 1M or 2M, atleast that's what I set on our productions systems, else you risk at running out of memory under high loads, and it works great, the other parameters as cited in the book High performance Mysql , by Jeremy Zawodny says this min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer Hope thsi helps Kishore Jalleda On 6/23/05, erin oneill [EMAIL PROTECTED] wrote: I'm working on fine tuning the Server Parameters for a machine (with 2 GB of RAM). The database has a fair number of tables. Many of them are full of blobs. The version of our production MySQL is: 4.0.20-pc-linux-i686. In trying to decide on some of the buffer variables I've seen some very desparate choices with a couple of them. They are: read_buff_size, sort_buffer_size, read_rnd_buffer_size. Many people have these set rather high (like 128M+). But I read somewhere that these settings are PER CONNECTION and not for the server itself. If that is so - shouldn't they be more like 8M, 8M 6M ?? Are there other variables that are PER CONNECTION and not for the whole server that I need to re-think? thanks, erin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Discover Yahoo! Get on-the-go sports scores, stock quotes, news and more. Check it out! http://discover.yahoo.com/mobile.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
