Re: MySQL server configuration for a 8 cpu+20gb RAM server

[Ahamed kabeer]

Hi,

I am a Software Engineer. I have installed MySQL 5.5 on a 64Bit Linux Server (fedora 11). I have developed a web application using JSP (Server: tomcat 6.0). My Application is running very slow when more than 90 Users are logged in. When the number of users is less then application is running very fast.
I checked JVM, and there is no problem in JVM and tomcat settings. There is no memory leakage also. MySQL administrator shows more than 100 running queries when more than 90 users are using my application. Most of the queries have GROUP BY and DISTINCT clauses. More over I am using InnoDB engine for transaction safe.

I think, there is some problem in MySQL variables I set in the my.cnf file. Here is the my.cnf file (for 32 GB RAM)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#old_passwords=1
#skip-innodb
#skip-networking
default-storage-engine=InnoDB
innodb_data_home_dir = /var/lib/mysql
# Data files must be able to hold your data and indexes
#innodb_data_file_path = /var/lib/mysql/ibdata1:1000M:autoextend
innodb_buffer_pool_size=8192M
innodb_additional_mem_pool_size=1024M
default-storage-engine=INNODB
max_connections=500
max_user_connections=500
#skip-name-resolve
thread_concurrency=16
thread_cache_size=16
key_buffer_size=16384M
sort_buffer_size=512M
read_buffer_size=512M
max_heap_table_size=1024M
join_buffer_size=512M
read_rnd_buffer_size=512M

query_cache_type=0
tmp_table_size=1024M

interactive_timeout=600
wait_timeout=600
connect_timeout=600
table_cache=512
thread_cache=128
max_allowed_packet = 10M
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_lock_wait_timeout=100
innodb_thread_concurrency=32

[myisamchk]
key_buffer=4092M
sort_buffer=1536M
read_buffer=1536M
write_buffer=1024M

[mysql.server]
user=mysql
basedir=/var/lib
default-character-set=latin1

[client]
default-character-set=latin1

Following is the server configuration
RAM: 32 GB
Disk Space: 160 GB
Operating System: Fedora 11
CPU: Intel(R) Xeon(R) CPU E5410 @ 2.33GHz
Number of CPUs: 8
Memory allocated to Tomcat: 2560 MB
Size of MySQL Database : 4 GB

Could you please help me to identify why my application is slow when the number of users are more?

Thanks in Advance.