Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My setup

  • Ubuntu 12.04
  • static ip address
  • mysql 5.5.28

This is a box used in my local network only for developping. So I installed Ubuntu and did not change much but setting the static IP.

Mysql is very slow on this machine. E.g. it takes 15 minutes to dump in a database with a few hundred insert statements, on another smaller machine it takes less than a minute.

I assume that my network configuration is not ok, that something might be missing or simply wrong. How can I debug this problem?

/etc/network/interfaces

auto lo
iface lo inet loopback

auto eth0
iface eth0 inet static
address 192.168.1.80
netmask 255.255.255.0
network 192.168.1.1
broadcast 192.168.1.255
gateway 192.168.1.1
dns-nameservers 8.8.8.8 8.8.8.4
dns-searcch google.com

My.cnf:

bind-address            = 127.0.0.1
share|improve this question
    
Are any of the details above different between the two machines? Are there any differences that you haven't mentioned? –  Ladadadada Dec 9 '12 at 10:37
    
the other mysql server is running in a shared hosting environment not under my control. I just mentioned it to state that it is not a problem of faulty sql queries. –  jamie0726 Dec 9 '12 at 10:39
    
There's too much this could be. It sounds like a bandwidth issue, which could be the port autonegotiating to half-duplex, IP tables or tc throttling. –  Jay Dec 9 '12 at 10:52
    
I forgot to mention that this is a development box. I did not change much but the static ip settings and it is supposed in my local network only. Is there a good way to figure out what's going on on the network level when Mysql is executing requests? E.g. can I see somehow if Mysql tries to connect to some port which is denied? –  jamie0726 Dec 9 '12 at 11:40
    
So the server could have SSDs or 15Krpm drives in RAID 10 while the dev box has a single 5400rpm drive. The server could have 128GB of RAM while your dev box has 256MB. The server could have 64 cores at 1GHz while your dev box has one at 2GHz. Do any of these sound like they might make a difference to performance? You could also be using InnoDB while the server is using MyISAM. The server could be faking fsync (possibly because they have battery-backed write caches, possibly because they don't care about ACID). Binary logs, general logs, indexes, swapping, etc. They all matter. –  Ladadadada Dec 9 '12 at 16:57

1 Answer 1

up vote 1 down vote accepted

I can see several ways of finding the performance bottleneck:

  1. Have two identical piece of hardware, one with the problem and one without. Change one thing at a time on the worse one until it gets better. The last thing you changed was the problem. (Note, there may be more than one problem.)
  2. Run mysqltuner on the bad box and follow whatever suggestions it makes.
  3. Run strace on MySQL with the -tt and -T options and watch what it spends all its time doing. The -c option can be useful too.
  4. Set up a monitoring box running something like Cacti, Zabbix, Munin, etc and watch to see which metric hits 100% when the performance problem occurs.

There is no guarantee that this is a software or configuration problem. Because you are comparing different (potentially completely different) bits of hardware, it's possible your dev box is already at its capacity. It's possible that the only way you can make it faster is to buy better hardware. Without knowing the details of the box you are comparing it to, no one can be sure.

share|improve this answer
    
thanks, that was really helpful input what I could try next :-) –  jamie0726 Dec 9 '12 at 19:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.