MySQL Performance: MySQL => Terrible Performance... Will Pay For Help!

Terrible Performance... Will Pay For Help! [message #1084]

Thu, 12 April 2007 21:02

Juicy
Messages: 2
Registered: April 2007
Location: New York

Junior Member

Hi All,

I currently run a Website which gets around 100,000 hits a day. The CMS software is Joomla, I run Coppermine Photo Gallery, and PHPBB. The messageboard has around 280,000 posts... and on average it's around 300 users online.

For some reason MYSQL performs very very slow. I have two servers. I use one as a dedicated Web Server, and the other as a dedicated database server. The specs for both servers identical:

Each Server Has:

2 X Dual Core Xeon 5110 /4MB Cache
4 Gigs Of Ram
2 X 300GB SAS Drives

The load on the webserver never reaches more than 10%. I use Apache/ Windows Server 2003, and MYSQL 5.037 . Database server frequently uses 700mb of memory and load fluctuates between 20-50%. Sometimes the site runs fast, and at other times it runs very very slow. I was wondering if someone could assist me in optimizing MYSQL so my site could be blazing fast like it should be.

Here is my my.ini file:

#
[client]

port=3306

[mysql]

default-character-set=latin1

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

default-character-set=latin1
default-storage-engine=INNODB

max_connections=800

query_cache_size=166M
table_cache=1520
tmp_table_size=256M
thread_cache_size=38

myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=256M

key_buffer_size=768M
read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K

#*** INNODB Specific options ***

#skip-innodb

innodb_additional_mem_pool_size=11M

innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=6M
innodb_buffer_pool_size=500M
innodb_log_file_size=100M
innodb_thread_concurrency=10

If you are willing to help me then private message me some contact information. E-Mail/AOL/YAhoo/ICQ is fine. I prefer instant message though.

Thanks!

Report message to a moderator