MySQL & High Server Load

steliosd

• November 25, 2013 04:53

Hi, I have a hexacore dedicated server, with 64GB RAM, running CentOS 6.4. Vendor: GenuineIntel Name: Intel(R) Core(TM) i7-3930K CPU @ 3.20GHz Speed: 1200.000 MHz Cache: 12288 KB The problem I am dealing with is that mysql consumes up to 600% of my cpu (server load up to 70!) every five minutes when the record are taking place.This situation last about 1-2 minutes before the server load comes back to normal at 4-5. I need some help with the configuration of my.cnf file. [mysqld] max_connections=250 max_user_connections=150 query_cache_type=1 query_cache_size=256M query_cache_limit=70M tmp_table_size=8M max_heap_table_size=8M thread_cache_size=64 table_open_cache=1024 wait_timeout=300 interactive_timeout=300 innodb_file_per_table=1 innodb_buffer_pool_size=4G innodb_log_file_size=512M default-storage-engine=MyISAM local-infile=0 max_allowed_packet=64M log-slow-queries=/var/lib/mysql/slow.log open_files_limit=2846 table_cache=4096 join_buffer_size=4M key_buffer_size=2M
Here is the output of mysqltuner: >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at

• 

  steliosd

  • December 30, 2013 13:23

  Hi, thanks a lot for you help once again!I really appreciate it. The code isn't mine that's why I am struggling to find out what is going on.. I have changed indexes again as you suggested, except PRIMARY of id column because it is auto increment and phpmyadmin won't let me, but explain extended output for my query remains the same as you can see. [url=http://postimg.org/image/fldvuoh8h/]View image: explain extended What else should I do? I know that my query is very time consuming but I can't help it.Your query doesn't do the job I want. What I want is for every bet_field type to get the latest ( max(rec_time) ) registered information for every other field. For example: id home_team visit_team code start_timestamp rec_time bet_field from_value to_value status status_game 14698 chelsea liverpool 227 1388320200 1388314382 b1 2 2.10 NULL NULL 14698 chelsea liverpool 227 1388320200 1388314400 b1 2.10 2.30 NULL NULL 14698 chelsea liverpool 227 1388320200 1388314400 b2 3.20 3.15 NULL NULL I want to get the second row from the above result because it is the most resent concerning bet_field "b1" and the third one because it is the only record for bet_field "b2".

  0
• 

  steliosd

  • January 07, 2014 10:37

  Which indexes should I have for the following queries? SELECT COUNT(*) AS expression FROM (SELECT kouponidb_kino_wins_xls.drawNo AS drawno, kouponidb_kino_wins_xls.timestamp AS kouponidb_kino_wins_xls_timestamp, kouponidb_kino_wins_xls.idCategory AS kouponidb_kino_wins_xls_idcategory, kouponidb_kino_wins_xls.idSelectedCategory AS kouponidb_kino_wins_xls_idselectedcategory, SUM(kouponidb_kino_wins_xls.winners) AS kouponidb_kino_wins_xls_winners, SUM(kouponidb_kino_wins_xls.winningAmount) AS kouponidb_kino_wins_xls_winningamount, 1 AS expression FROM kouponidb_kino_wins_xls kouponidb_kino_wins_xls WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T17:16') )) GROUP BY drawno) subquery
SELECT DISTINCT kouponidb_kino_wins_xls.drawNo AS drawno, kouponidb_kino_wins_xls.timestamp AS kouponidb_kino_wins_xls_timestamp, kouponidb_kino_wins_xls.idCategory AS kouponidb_kino_wins_xls_idcategory, kouponidb_kino_wins_xls.idSelectedCategory AS kouponidb_kino_wins_xls_idselectedcategory, SUM(kouponidb_kino_wins_xls.winners) AS kouponidb_kino_wins_xls_winners, SUM(kouponidb_kino_wins_xls.winningAmount) AS kouponidb_kino_wins_xls_winningamount FROM kouponidb_kino_wins_xls kouponidb_kino_wins_xls WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T20:42') )) GROUP BY drawno ORDER BY kouponidb_kino_wins_xls_winningamount DESC
SELECT kouponidb_kino_delaysrepeats.drawNo AS drawno, kouponidb_kino_delaysrepeats.referred_drawNo AS kouponidb_kino_delaysrepeats_referred_drawno, kouponidb_kino_delaysrepeats.number AS kouponidb_kino_delaysrepeats_number, kouponidb_kino_delaysrepeats.timestamp AS kouponidb_kino_delaysrepeats_timestamp, kouponidb_kino_delaysrepeats.type AS kouponidb_kino_delaysrepeats_type, kouponidb_kino_delaysrepeats.times AS kouponidb_kino_delaysrepeats_times_1, MAX(kouponidb_kino_delaysrepeats.times) AS kouponidb_kino_delaysrepeats_times FROM kouponidb_kino_delaysrepeats kouponidb_kino_delaysrepeats WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_delaysrepeats.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-06T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_delaysrepeats.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-06T22:00') AND (kouponidb_kino_delaysrepeats.times > '0') )) GROUP BY drawno, kouponidb_kino_delaysrepeats_referred_drawno, kouponidb_kino_delaysrepeats_number, kouponidb_kino_delaysrepeats_timestamp, kouponidb_kino_delaysrepeats_type, kouponidb_kino_delaysrepeats_times_1 ORDER BY kouponidb_kino_delaysrepeats_times_1 DESC LIMIT 300 OFFSET 0
  

  0
• 

  steliosd

  • January 07, 2014 13:40

  When my server load reaches 30-40 out of 12 processors, top command looks like this: top - 16:40:29 up 105 days, 20:20, 2 users, load average: 15.52, 10.86, 11.32 Tasks: 607 total, 132 running, 475 sleeping, 0 stopped, 0 zombie Cpu(s): 89.0%us, 10.7%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 65256396k total, 51683136k used, 13573260k free, 719384k buffers Swap: 33554296k total, 0k used, 33554296k free, 44309028k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 16897 mysql 20 0 12.6g 1.1g 6760 S 19.0 1.7 9:32.60 mysqld 12453 root 20 0 148m 12m 896 R 8.8 0.0 0:00.47 lfd - (child) p 12544 masterbe 20 0 210m 40m 6472 R 8.8 0.1 0:00.34 php 12552 masterbe 20 0 210m 40m 6472 R 8.8 0.1 0:00.34 php 12561 masterbe 20 0 210m 40m 6472 R 8.8 0.1 0:00.33 php 12562 masterbe 20 0 210m 40m 6472 R 8.8 0.1 0:00.33 php 12571 masterbe 20 0 210m 40m 6472 R 8.8 0.1 0:00.33 php 12541 masterbe 20 0 210m 40m 6472 R 8.2 0.1 0:00.32 php 12653 masterbe 20 0 204m 35m 6440 R 8.2 0.1 0:00.25 php 12656 masterbe 20 0 194m 29m 6340 R 7.9 0.0 0:00.24 php 12662 masterbe 20 0 172m 10m 6392 R 7.9 0.0 0:00.24 php 12663 masterbe 20 0 191m 22m 6464 R 7.9 0.0 0:00.24 php 12658 masterbe 20 0 202m 33m 6500 R 7.5 0.1 0:00.23 php 12659 masterbe 20 0 201m 32m 6508 R 7.5 0.1 0:00.23 php 12665 masterbe 20 0 0 0 0 R 7.5 0.0 0:00.23 php 12666 masterbe 20 0 192m 30m 6356 R 7.5 0.0 0:00.23 php 12668 masterbe 20 0 171m 10m 6416 R 7.5 0.0 0:00.23 php 12675 masterbe 20 0 201m 32m 6464 R 7.5 0.1 0:00.23 php 12676 masterbe 20 0 192m 23m 6484 R 7.5 0.0 0:00.23 php 12677 masterbe 20 0 201m 32m 6504 R 7.5 0.1 0:00.23 php 12678 masterbe 20 0 204m 35m 6456 R 7.5 0.1 0:00.23 php 12680 masterbe 20 0 201m 32m 6508 R 7.5 0.1 0:00.23 php 12714 masterbe 20 0 201m 32m 6476 R 7.5 0.1 0:00.23 php 12755 masterbe 20 0 201m 32m 6468 R 7.5 0.1 0:00.23 php 12657 masterbe 20 0 201m 32m 6444 S 7.2 0.1 0:00.22 php 12661 masterbe 20 0 199m 29m 6448 R 7.2 0.0 0:00.22 php 12667 masterbe 20 0 202m 32m 6532 R 7.2 0.1 0:00.22 php 12669 masterbe 20 0 201m 32m 6440 S 7.2 0.1 0:00.22 php 12671 masterbe 20 0 204m 35m 6456 R 7.2 0.1 0:00.22 php 12672 masterbe 20 0 201m 32m 6436 S 7.2 0.1 0:00.22 php 12673 masterbe 20 0 201m 32m 6464 R 7.2 0.1 0:00.22 php 12682 masterbe 20 0 200m 31m 6484 R 7.2 0.0 0:00.22 php 12686 masterbe 20 0 201m 32m 6436 R 7.2 0.1 0:00.22 php 12687 masterbe 20 0 201m 32m 6436 S 7.2 0.1 0:00.22 php 12689 masterbe 20 0 201m 32m 6420 R 7.2 0.1 0:00.22 php 12697 masterbe 20 0 201m 32m 6420 S 7.2 0.1 0:00.22 php 12733 masterbe 20 0 201m 32m 6420 S 7.2 0.1 0:00.22 php 12735 masterbe 20 0 200m 31m 6340 R 7.2 0.1 0:00.22 php 12739 masterbe 20 0 201m 32m 6464 R 7.2 0.1 0:00.22 php 12740 masterbe 20 0 200m 31m 6392 R 7.2 0.1 0:00.22 php 12742 masterbe 20 0 201m 32m 6360 R 7.2 0.1 0:00.22 php 12745 masterbe 20 0 201m 32m 6420 S 7.2 0.1 0:00.22 php 12750 masterbe 20 0 201m 32m 6420 S 7.2 0.1 0:00.22 php 12752 masterbe 20 0 200m 31m 6340 R 7.2 0.1 0:00.22 php 12757 masterbe 20 0 201m 32m 6492 R 7.2 0.1 0:00.22 php 12760 masterbe 20 0 201m 32m 6488 R 7.2 0.1 0:00.22 php 12763 masterbe 20 0 201m 32m 6420 S 7.2 0.1 0:00.22 php
  

  0
• 

  thinkbot

  • January 07, 2014 14:17

  Sorry but I can't help you thru forums like that, it would take too much time Your case requires access to the code/server for the more detailed review About queries DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T17:16') )) you need to do some study on mysql indexes etc. those are simple mistakes, this part above should be rewritten so that on the left is the column, and on the right all data modifications, so that query can utilize index

  0
• 

  steliosd

  • January 08, 2014 14:13

  Can you please give me an example how this query should be?

  0
• 

  thinkbot

  • January 08, 2014 15:21

  just modify the query so that WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') <= '2014-01-03T17:16') )) GROUP BY drawno) subquery columns are on the left without any operations on them: WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND kouponidb_kino_wins_xls.timestamp >= /* here comes the operations on the date */ '2014-01-03T09:00' and then add index on timestamp

  0
• 

  steliosd

  • January 09, 2014 08:05

  Sorry but I quite don't get it. You mean something like this: WHERE (( (kouponidb_kino_wins_xls.winners > '0') AND kouponidb_kino_wins_xls.timestamp >=(DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' '2014-01-03T09:00': this isn't a static value.

  0
• 

  thinkbot

  • January 09, 2014 10:56

  No, I mean that on the left WHERE (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(kouponidb_kino_wins_xls.timestamp), SEC_TO_TIME(7200)), '%Y-%m-%d\T%H:%i') >= '2014-01-03T09:00' There should NOT be any operations on the column, instead operations should be on the value on the right WHERE kouponidb_kino_wins_xls.timestamp < (ADDTIME(FROM_UNIXTIME('2014-01-03T09:00'), SEC_TO_TIME(7200))) or in the dynamic code directly WHERE kouponidb_kino_wins_xls.timestamp < 2014-01-03T11:00'

  0

Please sign in to leave a comment.