MySQL Forums
Forum List  »  Performance

Remote mysql server slow
Posted by: Denis Karovic
Date: August 01, 2012 09:22AM

I need some help on speeding websites loading.
I have two virtual machines created on my ESXi host. Thay are both on public IP addresses and that is how they communicate.
On one virtual machines I have apache installed and configured and that is where the websites are located. It is shared hosting. And on the second virtual machine I have mysql 5.5 installed.
I configured it all and it all works. My websites are loading, but the problem is speed. I have waiting time on website loading from 1.5s to 3s. This seems a little bit high and I want to reduce it.
When I put my websites databases on localhost (where the apache is also) it opens very fast with waiting time in 100-500ms. Is there a way to do it?

Here is the ping from apache to database VM:
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=1 ttl=63 time=0.374 ms
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=2 ttl=63 time=0.953 ms
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=3 ttl=63 time=0.369 ms
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=4 ttl=63 time=0.630 ms
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=5 ttl=63 time=0.278 ms
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=6 ttl=63 time=0.408 ms
64 bytes from xxx.xxx.xxx.xxx: icmp_seq=7 ttl=63 time=0.325 ms

Mysql VM has 4 GB of RAM, and I have set mysql to use about 3 GB of it.
I have configured mysql with this options:
skip-name-resolve
skip-host-cache
local_infile=0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 4
max_allowed_packet = 12MB
table_cache = 2048
table_open_cache = 2048
table_definition_cache = 2048
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
key_buffer_size = 128M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 128M
query_cache_limit = 4M
tmp_table_size = 512MB
max_heap_table_size = 512MB
innodb_buffer_pool_size = 800M
innodb_additional_mem_pool_size = 128M
innodb_file_per_table
innodb_log_file_size = 256M
innodb_flush_method=O_DIRECT
max_connections=80
wait_timeout = 30
interactive_timeout = 30

How can I get rid of all this latency? Mysqltuner and tuning-primer scripts tells me that I have configured almost everything ok. But there is still high level of temp tables created on disk:
[!!] Temporary tables created on disk: 41% (893 on disk / 2K total).
How can I reduce this? Because it is shared hosting I don't know how much BLOB or TEXT fields are there in databases.

Or is there a way that I can connect these two VMs internally (not by public IP addresses, but also to remain public) so maybe that will improve performance?

If you need anything else I will post it (like mysqltuner report,....). Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Remote mysql server slow
3704
August 01, 2012 09:22AM
1450
August 01, 2012 09:47AM
1542
August 03, 2012 09:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.