InnoDB - Performance

[Dean Elzey]

I know this forum gets tons of these but I really need a sanity check here for our production Master/Slave setup. We expect data growth to be in the 300+ GB range. We currently have 9 million rows @ 2 GB and just getting started.

we currently have no issues with performance and around 32k users, but before it gets out of hand I would like some additional eyes on it :)

Thanks in advance for the help!


Both servers have the following hardware:

product: ProLiant BL460c G1
*-cpu:0
product: Intel(R) Xeon(R) CPU L5240 @ 3.00GHz
*-cpu:1
product: Intel(R) Xeon(R) CPU L5440 @ 2.83GHz
*-memory
size: 24 GB


And here is the my.cnf

[client]
port = 3306

[mysqld_safe]
nice = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
old_passwords = 1
key_buffer_size = 128M
default-storage-engine = INNODB

innodb_buffer_pool_size = 2GB
myisam_sort_buffer_size = 512M
sort_buffer_size = 128K
read_buffer_size = 128K
join_buffer_size = 4M
read_rnd_buffer_size = 4M
thread_cache = 8
thread_concurrency = 8
max_connections = 4000

innodb_file_per_table
innodb_log_file_size = 256M
innodb_log_buffer_size = 5M

log-slow-queries
long-query-time = 1

# Start Replication Parameters
log-bin = /var/lib/mysql/db02-bin
log-bin-index = /var/lib/mysql/db02.index
log-slave-updates
binlog-do-db = main # The database to be replicated
binlog-ignore-db = mysql # The database to be ignored for replication
server-id = 2

master-host = 192.168.10.10
master-user = replication
master-password = slave
master-port = 3306
innodb_flush_log_at_trx_commit = 1
relay-log = /var/lib/mysql/mysql-relay
relay-log-index = /var/lib/mysql/mysql-relay.index

# End Replication Parameters

# Start Performance Parameters
tmpdir = /data/mysql_tmp
max_heap_table_size = 64M
tmp_table_size = 64M
table_cache = 128M
sort_buffer_size = 16M
query_cache_min_res_unit = 1K
query_cache_limit = 8M
query_cache_size = 512M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
innodb_lock_wait_timeout = 2
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
skip-external-locking
# End Performance Parameters


- Dean Elzey