Terrible Performance... Will Pay For Help!

MySQL & MariaDB Other MySQL® Questions
1

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!

2

Another thing, whenever I try to increase the following settings:

read_buffer_size=64K [ Tried 2M ]
read_rnd_buffer_size=256K [Tried 2M ]
sort_buffer_size=256K [I tried 6M]

The database goes even slower. Why is that?

3

What % of your tables are InnoDB?

How much free memory does the server have when running at full capacity?

4

Something I noticed about setting high values where not needed in your my.cnf is if MySQL cannot allocate resources given your hardware for the settings in your cnf then it will just use its own defaults instead or fail outright at any sort of reliability. Looking at your max connection count, I don’t believe that your 100k hits a day require that many connections and to set your connection count that high is requiring 8x the default settings requirements. If you revisit the documentation regarding your buffers and max connections, i think it will do you a lot of good performance wise.