RE: Server tuning
Victor, Thanks for all your help with the authentication issue. I still have it, but at least I know what to look for. My server is running RH9 with 3 gigs ram, PIII. I have about 150 users at any given time and they're all coming via IMAP or HTTPD. They all authenticate against MySQL but the mail is not stored there, it's in virtual domain files. My /etc/my.cnf Set-variabe = max_connections=1000 Key_buffer_size=512M Sort_buffer=20M Join_buffer=1M Record_buffer=1M Max_allowed_packet=2M Table_cache=1024 Innodb_buffer_pool_size=256M Are these variables ok? Reading my MySql manual, these were the best I could come up with. Thanks! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 4:41 PM To: 'João Paulo Vasconcellos '; 'Mysql-general Mailing List ' Subject: RE: Server tuning max_memory is roughly equivalent to == key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections -Original Message- From: João Paulo Vasconcellos To: Mysql-general Mailing List Sent: 6/29/04 1:09 PM Subject: Server tuning Hello everybody, I am setting up a server to do POP/SMTP authentication using vpopmail. I took a look at the variables at global my.cnf and wondered what would be the best values for things like key_buffer, read_buffer_size and table_cache. I searched the manual, but it does not go deep in this subject, or I was not capable of finding the right place. What I want to know is how can I calculate how much memory I should give to key_buffer before I start to give away too much memory. That's because I got only 1GB of RAM and I have about 34k domains in my database, averaging from 8 to 15 accounts each. In a normal situation, there are ~400 simultaneous clients. I was wanting to know how can I estimate the memory usage for this scenario, if exists some kind of formula to answer this, like: clients * total size of key fields used in query or if this is some thing that is clear in the manual (if so, my apologies, but I could not find). TIA, -- João Paulo Vasconcellos -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Server tuning
max_memory is roughly equivalent to == key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections -Original Message- From: João Paulo Vasconcellos To: Mysql-general Mailing List Sent: 6/29/04 1:09 PM Subject: Server tuning Hello everybody, I am setting up a server to do POP/SMTP authentication using vpopmail. I took a look at the variables at global my.cnf and wondered what would be the best values for things like key_buffer, read_buffer_size and table_cache. I searched the manual, but it does not go deep in this subject, or I was not capable of finding the right place. What I want to know is how can I calculate how much memory I should give to key_buffer before I start to give away too much memory. That's because I got only 1GB of RAM and I have about 34k domains in my database, averaging from 8 to 15 accounts each. In a normal situation, there are ~400 simultaneous clients. I was wanting to know how can I estimate the memory usage for this scenario, if exists some kind of formula to answer this, like: clients * total size of key fields used in query or if this is some thing that is clear in the manual (if so, my apologies, but I could not find). TIA, -- João Paulo Vasconcellos -- 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: Server tuning
If it starts swapping you have key_buffer set to high. -Eric On Tue, 29 Jun 2004 15:41:20 -0500, Victor Pendleton [EMAIL PROTECTED] wrote: max_memory is roughly equivalent to == key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections -Original Message- From: João Paulo Vasconcellos To: Mysql-general Mailing List Sent: 6/29/04 1:09 PM Subject: Server tuning Hello everybody, I am setting up a server to do POP/SMTP authentication using vpopmail. I took a look at the variables at global my.cnf and wondered what would be the best values for things like key_buffer, read_buffer_size and table_cache. I searched the manual, but it does not go deep in this subject, or I was not capable of finding the right place. What I want to know is how can I calculate how much memory I should give to key_buffer before I start to give away too much memory. That's because I got only 1GB of RAM and I have about 34k domains in my database, averaging from 8 to 15 accounts each. In a normal situation, there are ~400 simultaneous clients. I was wanting to know how can I estimate the memory usage for this scenario, if exists some kind of formula to answer this, like: clients * total size of key fields used in query or if this is some thing that is clear in the manual (if so, my apologies, but I could not find). TIA, -- João Paulo Vasconcellos -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Tuning
"Florian G. Pflug" [EMAIL PROTECTED] writes: Hi I am running Mysql on a Machine with two PIII-600, and 1 GB of RAM. The disks are connected via U2W-SCSI. I tried tuning mysql for maximal performance the last days, but I'm not exactly sure which parameters I should increase, und to what value. Our web application is quite slow at the moment, and often the server is slower in doing the queries than they are created by the users, which steadily increases the number of running mysql-processes until they reach max_connection (which is 100 at the moment). don't do this. you disappear up your own tailpipe. put a second apache behind your first and limit its MaxClients setting. this is a standard mod_perl trick and is in the mod_perl guide. Our Application searches through about 1.000.000 Records, and does heavy grouping, sometimes over 100.000 records. Is there any chance, that we can keep doing this queries on the fly, or will we have to cache them somehow? (which has a _lot_ of downsides, since the data changes quite frequently, about once every 1/2 hour). Have you had a good hard look at your EXPLAINs? Which server parameters should we increase to make things faster? Since a lot of users "do the same thing" on this website, we often have the very _same_ query running 5 to 10 times, one started within 5 seconds or so after the other. Definitely look at some form of cacheing. Do the result sets have the same columns in the result sets? you might want to consider selecting into a new results table...? -- Dave Hodgkinson, http://www.hodgkinson.org Editor-in-chief, The Highway Star http://www.deep-purple.com Apache, mod_perl, MySQL, Sybase hired gun for, well, hire - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
