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

Reply via email to