Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

We provide a B2B solution, which is doing quite a lot of UPDATE, INSERT an DELETE operations. Our infrastructure at the moment looks like this:

  • Home-Server ( MySQL (INNODB) + website )
  • 5 worker servers (business logic and most of the transactions on Home-Server)

We are running into a problem, where the Home-Server looks quite busy. SELECT statements are taking much too long. Sometimes even causing 500 Internal Server Error. Our business has grown fast and now we are faced with this problem.

Do you know a solution, which helps us to discharge our Home-Server? How should we change our infrastructure to improve performance, flexibility, stability even if we grow even further?

top on our Home-Server: 

Tasks: 104 total,   3 running, 101 sleeping,   0 stopped,   0 zombie
Cpu(s): 86.3%us, 10.7%sy,  0.0%ni,  2.8%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4194304k total,  3671092k used,   523212k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
20159 mysql     15   0 1759m 1.3g 7988 S  178 31.4   6025:04 mysqld

mysqlreport (We did an application update about 4 days ago, which increased the speed so maybe the facts are not on the newest level)

MySQL 5.1.63-0+squeeze1  uptime 25 16:44:58     Fri Dec 14 14:59:30 2012

__ Key _________________________________________________________________
Buffer used    44.00k of  16.00M  %Used:   0.27
  Current       2.92M            %Usage:  18.26
Write hit     100.00%
Read hit      100.00%

__ Questions ___________________________________________________________
Total           1.27G   572.8/s
  DMS           1.03G   465.4/s  %Total:  81.25
  Com_        151.51M    68.2/s           11.91
  COM_QUIT     76.06M    34.3/s            5.98
  QC Hits      10.95M     4.9/s            0.86
  -Unknown      7.99k     0.0/s            0.00
Slow 2 s      866.22k     0.4/s            0.07  %DMS:   0.08  Log:  ON
DMS             1.03G   465.4/s           81.25
  INSERT      538.79M   242.7/s           42.37         52.14
  UPDATE      326.22M   146.9/s           25.65         31.57
  DELETE      123.74M    55.7/s            9.73         11.98
  SELECT       44.50M    20.0/s            3.50          4.31
  REPLACE           0       0/s            0.00          0.00
Com_          151.51M    68.2/s           11.91
  set_option   76.07M    34.3/s            5.98
  show_fields  49.65M    22.4/s            3.90
  begin        12.88M     5.8/s            1.01

__ SELECT and Sort _____________________________________________________
Scan           50.13M    22.6/s %SELECT: 112.64
Range          23.67M    10.7/s           53.18
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan     375.32k     0.2/s
Sort range     41.41M    18.6/s
Sort mrg pass 154.81k     0.1/s

__ Query Cache _________________________________________________________
Memory usage  598.36k of  16.00M  %Used:   3.65
Block Fragmnt  17.65%
Hits           10.95M     4.9/s
Inserts         9.23M     4.2/s
Insrt:Prune   77.24:1     4.1/s
Hit:Insert     1.19:1

__ Table Locks _________________________________________________________
Waited             33     0.0/s  %Total:   0.00
Immediate       1.03G   465.4/s

__ Tables ______________________________________________________________
Open              128 of  128    %Cache: 100.00
Opened          2.84k     0.0/s

__ Connections _________________________________________________________
Max used          366 of 1000      %Max:  36.60
Total          76.06M    34.3/s

__ Created Temp ________________________________________________________
Disk table     49.68M    22.4/s
Table          50.18M    22.6/s    Size:  32.0M
File          306.32k     0.1/s

__ Threads _____________________________________________________________
Running            31 of  238
Cached              4 of    8      %Hit:  96.98
Created         2.30M     1.0/s
Slow                6     0.0/s

__ Aborted _____________________________________________________________
Clients         3.23k     0.0/s
Connects        6.17k     0.0/s

__ Bytes _______________________________________________________________
Sent          179.79G   81.0k/s
Received      278.29G  125.3k/s

__ InnoDB Buffer Pool __________________________________________________
Usage         1000.00 of 1000.00  %Used: 100.00
Read hit       99.54%
Pages
  Free              0            %Total:   0.00
  Data         60.94k                     95.21 %Drty:   1.60
  Misc           3064                      4.79
  Latched           0                      0.00
Reads          91.09G   41.0k/s
  From file   415.97M   187.3/s            0.46
  Ahead Rnd   4057370     1.8/s
  Ahead Sql   2606908     1.2/s
Writes          9.54G    4.3k/s
Flushes        34.48M    15.5/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits          466195     0.2/s
Current             3
Time acquiring
  Total     694423830 ms
  Average        1489 ms
  Max           52284 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads       448.45M   202.0/s
  Writes       50.90M    22.9/s
  fsync        32.05M    14.4/s
  Pending
    Reads           0
    Writes          0
    fsync           1

Pages
  Created     610.10k     0.3/s
  Read        672.86M   303.0/s
  Written      34.48M    15.5/s

Rows
  Deleted     502.41M   226.3/s
  Inserted    503.46M   226.8/s
  Read          9.59G    4.3k/s
  Updated     397.73M   179.1/s
share|improve this question

1 Answer

up vote 2 down vote

The short answer is more than likely indexing (slow selects) and possibly deploying a read-only slave.

In order to start finding where the indexes need to go, pick a few of the queries that are slow, and run them with explain.

It also looks like you should increase your innodb buffer size, as it is consistently full. That makes for more disk reads, and time for the engine to portion out the limited space it has. Also, make sure that you have enough memory in the server to cover what you've committed in your config files + what the OS requires.

share|improve this answer
Thanks Bryan. Well i think our indexes are fine, but buffer size is a topic which i will try. – Michael Hirn Dec 15 '12 at 18:03
Are you using the vanilla MySQL binaries? You may also want to look at Percona, or Maria; both are drop in binary compatible, and both have some performance benefits. Percona specifically deals with several locking issues, which could be contributing. – Bryan Agee Dec 15 '12 at 18:14
Never heard of vanilla or Percona or Maria. After a short search i guess these are kind of mods for MySQL-Server? A Engine like INNODB? But Percona also provides a DB-Service. Can i outsource my DB to them and they will support and optimize? – Michael Hirn Dec 16 '12 at 13:27
Sorry--just saw this. Percona and Maria are forks of MySql, so they have a ton in common. Percona has extended InnoDB, so you can snap-in convert from InnoDB to XtraDB, and Percona does provide a lot of value added services. – Bryan Agee Feb 9 at 1:30
Shoot Percona an email, and they would be happy to tell you about the options they have. Much cheaper than hiring on onsite DBA until you are scaled up. – Bryan Agee Feb 9 at 1:31

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.