| Mysql Server tuning [message #1449] |
Wed, 20 June 2007 16:25  |
 mrmartin
Messages: 4
Registered: June 2007 |
Junior Member |
|
|
Hello, I am going to switch my mysql server configuration, and
I'd like to know what do you think about this configuration.
I have 2 servers, one (apache2, php5) and the otherone mysql (mysql-server 5.0.38, last debian version).
The mysqlserver specifications are.
3 disks 72GB SAS 15,000 rpm RAID 5
2 Dual Core Intel® Xeon® (2.66GHz, 1333 FSB)
4GB DIMM PC2-5300 (I know.. is too low  )
All tables are Innodb, and database have arround 90M now, but it will grow exponentially. Now have the "standar" mysql configuration and I ll change for this one.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
port = 3306
bind-address = 192.168.1.2
socket = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer = 2048M
max_allowed_packet = 2M
max_connections = 800
table_cache = 5600
open_files_limit = 6000
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 32M
thread_concurrency = 8
skip-name-resolve
log-bin=mysql-bin
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
Thanks !
Martin
|
|
|
|
|
| Re: Mysql Server tuning [message #1453 is a reply to message #1452 ] |
Thu, 21 June 2007 13:06  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
Max connections could be lowered, is there any reason that it is set so high?
Thread level variables would consume a lot of memory with 800 concurrent connections.
Might as well safe guard against that issue by lowering max connections with a more real world value.
Martin Gallagher | Speeple: The latest news
|
|
|
|
|
| Re: Mysql Server tuning [message #1465 is a reply to message #1449 ] |
Fri, 22 June 2007 11:10 |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
You're going to have to backup your databases to change the innodb_data_file_path variable. Backup, then change the variable, then import backup.
Altering the innodb_log_file_size can be achieved without backing up. What you need to do is stop the mysql instance, alter the config to the sizes you desire, then you need to remove the old log files:
rm -f ib_logfile*
Martin Gallagher | Speeple: The latest news
|
|
|
Members
Search
Help
Register
Login
Home
