Hello, I've just set up a MySQL server. I've installed the rpm and run the server before on multi-purpose servers (also running mail, httpd, etc) but this is the first time I've set up a dedicated SQL sever and spent any time tuning it.
First, the specs: Dell PowerEdge 2450 2GB RAM 5 disk RAID 5 for a total of 67GB disk space, all under / I've spent the last 2 days reading the performance and tuning documentation and I have a few questions. I'd also appreciate it if someone of you would review my my.cnf (below) and see if I've done anything wrong. Most of our connections will be via a LAN connection to our other servers (web servers, etc). Also we are using InnoDB (MySQL-Max) I started with my-huge.cnf and modified it from there. Running SuSE Linux 7.3 I asked my boss to give a high estimate of and "average" for max. number of connections at any one time. He thought 200 connections. at first I had things set up that way, including max_connection=256, but then I found a warning about memory usage: "Warning: on Linux x86 you must be careful you do not set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. Make sure innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB is significantly smaller than 2 GB. Each thread will use a stack (often 2 MB, but in MySQL AB binaries only 256 kB) and in the worst case also sort_buffer + record_buffer additional memory." This kinda boggles me. Maybe I'm totally misunderstanding it or my logic is screwy, but it seems to me that that formula makes things worse the more RAM you have. It's somewhat inconsistent with the following: "# Set buffer pool size to 50 - 80 % # of your computer's memory, but # make sure on Linux x86 total # memory usage is < 2 GB" and "# Set the log file size to about # 15 % of the buffer pool size" (BTW, farther down the page it says innodb_log_file_size should be 1/nth of the buffer_pool size, where n is what you have innodb_log_files_in_group set to - in my case, innodb_log_files_in_group=3, so n is 3, and that's 1/3 of 1024, which is NOT the same as 15%. What's up with that? I've left it at 15% like the example conf says - should I increase it?) I decided to make buffer_pool_size 50% (1024MB) and even at that I wasn't really able to use the settings I thought I needed to use(how on earth does anyone use 80% as buffer_poolk size? I couldn't work for anything but a very few connections!). Here's what I get for max_connections=100 innodb_buffer_pool_size 1024 [50% of RAM] + key_buffer 384 [sorry, I can't remember why I set this to 384 but there was a reason] + (max_connections 100 * (sort_buffer 2 + record_buffer 2 = 4 ) = 400) + ( max_connections 100 * 2 MB) 10234 + 384 + 400 + 200 _____ 2008 MB 2048MB - 2008 MB doesn't leave much for anything else! What am I doing wrong? How on earth could anyone use more then 50% of their RAM for buffer_pool_size? What's odd is that if you add more RAM thinking that you can then have more max_connections, you shoot yourself in the foot because if you increase max_connections any things get even worse. What's wrong with my logic here? Look what I would get if I tried to use max_conenctions=256 (which is what I started off trying to use): innodb_buffer_pool_size 1024 + key_buffer 384 + (max_connections 256 * (sort_buffer 2 + record_buffer 2 = 4 ) = 400) + ( max_connections 256 * 2 MB) innodb_buffer_pool_size 1024 + key_buffer 384 + (max_connections 256 * (2 + 2 = 4 ) = 1024) + ( max_connections 256 * 2 MB = 512) 1024 + 384 + 1024 + 512 _____ 2944 MB Uhh, obviously that won't work and it goes over 2GB limit too. I fell quite certain that I'm not figuring something out correctly there (or else the documentation writers are just plain nuts :-) ) - can some wise soul please help sort this out for me? --------------------------------------------------------------------------- Ok, here's my /etc/my.cnf Please say something if anything looks screwy :-) [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = max_connections=256 # Trimmed down for InnoDB's sake set-variable = max_connections=100 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=64M log-bin server-id = 1 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=384M #set-variable = bdb_max_lock=100000 innodb_data_file_path = ibdata1:2G innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ #set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 # Conflicting instructions. One says 15% of innodb_buffer_pool_size, # the other says 1/3 of it. # 1/3 is 341.2992 MB # We use 15% cause it's smaller :-) # set-variable = innodb_log_file_size=156M #set-variable = innodb_log_buffer_size=8M set-variable = innodb_log_buffer_size=12M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=1024M #start with 2 MB, add more if you have many tables set-variable = innodb_additional_mem_pool_size=8M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 # threads: default is 8. If you have low performance and innodb_monitor reveals many threads waiting for semaphores,\ then you may have thread thrashing and should try setting this parameter lower. #If you have a computer with many processors and disks, you can try setting this value higher to better utilize the \resources of you computer. A value 'number of processors + number of disks' is recommended. #leaving it set to default for now (would be 7 for us with dual procs and 5-disk RAID5) #set-variable = innodb_thread_concurrency=5 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout # # Added by JW 2002-01-26 per MySQL manual.txt suggestion # [safe_mysqld] open-files-limit=256 TIA for any help anyone can give me :-) ---------------------------------------------------- Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com --------------------------------------------------------------------- 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
