Slow DB Access

I am setting up a master slave replication setup on a 2 VPS linux servers and I'm having some major performance issues and don't know where to begin to solve this problem. When I first started testing our new website on the master I immediatly noticed the slowness, so I created very simple test program which I ran on all 4 of our servers. The first part of the test simply did multiplication, additions, and division (3 million total). The second part of the test simply did a simple count(*) from a table with 240 rows in it. It does this test 100,000 times for a total of 100,000 select statements (on the same database connection).

Heres the results:
Master Server (powervps vps / 4.1.14-standard-log ):
Elapsed Time For Algorithm Test (1000000 loops): 3.3365828990936 seconds
Elapsed Time For Database Test (100000 selects): 31.405340909958 seconds

Slave Server (dehe vps / 4.1.13-standard ):
Elapsed Time For Algorithm Test (1000000 loops): 4.32417011261 seconds
Elapsed Time For Database Test (100000 selects): 13.8433449268 seconds

Development Server ( westhost vps / 4.1.9-standard-log - not in replication pool ) :
Elapsed Time For Algorithm Test (1000000 loops): 3.91952490807 seconds
Elapsed Time For Database Test (100000 selects): 8.67899799347 seconds

My Laptop ( windows xp / 4.1.15-nt - not in replication pool ) :
Elapsed Time For Algorithm Test (1000000 loops): 12.261327981949 seconds
Elapsed Time For Database Test (100000 selects): 17.570058107376 seconds

All of these servers are shared servers (even though they have dedicated resources) so there are other sites running on them, but there was zero load on them caused by my own sites. The mysql databases have zero load on them and this was confirmed by looking at the mysql server process list.

It appears the slowness is being caused by the database since all or the algo tests are about the same, but the database times for the Master and Slave are high.

Could this be caused by the replication? What is the best way to diagnose and test this problem. I'm attaching my my.cnf in case that might shed some light. I picked the settings for the Master and Slave my.cnf based on recommendations from this forum.

Anything else that might help I would be glad to provide. Any help appreciated.

--------------------
Master Server my.cnf
--------------------
[mysqld]
safe-show-database
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
log-bin
server-id = 1

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M

--------------------
Slave Server my.cnf - By the way I'm using SSH forwarding here
--------------------
[mysqld]
safe-show-database
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
server-id = 2
master-host = localhost
master-user = REMOVED
master-password = REMOVED
master-connect-retry=60
master-port = 3307
read-only

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M

--------------------
Development Server my.cnf
--------------------
[mysqld]
log-bin
server-id = 1
port = 3306
user = REMOVED
bind-address = XXX.XXX.XXX.XXX
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
log = /dev/null
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
set-variable = key_buffer=16M

 

 

 

 

Top