Join Log in
Score:1
goofball avatar Server

Server is always slow at around 2pm for 1 week

no flag
goofball

I did top and got:

Tasks: 590 total, 8 running, 582 sleping, 0 stopped, 0 zombie
Cpus(s) 96.5%us, 2.7%sy, 0.0%ni, 0.4%id, 0.0%wa, 0.0%hi, 0.4%si, 0.0%st
Mem: 32877280k total, 30611188k used, 2266092k free, 4021204k buffers
Swap 0k total, 0k used, 0k free, 4267024k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21672 mysql 20 0 13.1G 8.9G 8496 S 559.1 28.3 124168:04 mysqld
12025 webs 20 0 494m 59m 5544 S 30.8 02 1:09.82 php-fpm
13325 webs 20 0 414m 57m 8014 S 21.4 02 1:08.42 php
11111 webs 20 0 495m 64m 3231 S 18.5 02 1:12.16 php-fpm
13232 webs 20 0 505m 96m 8542 S 16.5 02 1:12.12 php-fpm
13217 webs 20 0 401m 52m 8541 S 15.5 02 1:02.44 php-fpm
13119 webs 20 0 475m 39m 8555 S 15.5 02 1:02.16 php-fpm
12065 webs 20 0 495m 49m 2533 S 14.9 02 1:22.41 php-fpm
12161 webs 20 0 412m 61m 2524 S 10.5 02 1:06.01 php-fpm
12275 webs 20 0 455m 51m 1544 S 10.5 02 1:34.04 php-fpm
12427 webs 20 0 464m 92m 7541 S 10.5 02 1:31.41 php-fpm
12127 webs 20 0 459m 89m 6544 S 10.5 02 1:01.19 php-fpm

Now, I was thinking and I thought the best way to make the server faster would be to restart the mysqld process, but how do I make sure there's no large sql dump going on? I did show full processlist, but I was told it doesn't tell you if there's an ongoing dump, because the insert happens really fast. Also, how do I investigate further? In an article I read, I was told if id and wa is low, I need to check if the CPU is high and if the CPU is high I need to restart the process. How often should I restart, what's the decision tree when there's a server slow down, what are all the things I need to do?

I did show full processlist and got:

1   system user         Daemon      InnoDB purge coordinator    
4   system user         Daemon      InnoDB purge worker 
3   system user         Daemon      InnoDB purge worker 
2   system user         Daemon      InnoDB purge worker 
5   system user         Daemon      InnoDB shutdown handler 
109725  root    localhost:54620     Query   0   Init    show full processlist
142438  cakes   localhost   cakes   Sleep   295     
142449  cakes   localhost   cakes   Sleep   21      
144422  pancakes    localhost   pancakes    Sleep   54      
144426  pancakes    localhost:54038 pancakes    Sleep   56      
145072  bookletv3   localhost   bookletv3   Query   0   Sending data    SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_prod wc ON  wc.post_id = wp_posts.ID AND (0  or 1)  AND (wc.stock = 'm004189' OR wc.vin = 'm004189')  AND ( wc.isbudget IS NULL OR wc.isbudget <> 1 )  AND ( wc.deleted IS NULL OR wc.deleted <> 1 )  WHERE 1=1  AND ( 
  wp_postmeta.meta_key = '_prod_info_new'
) AND wp_posts.post_type = 'used-prod' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wc.SoldDate IS NULL, wc.SoldDate ASC, wc.isnew DESC LIMIT 0, 15
145073  bookletv3   localhost:33840 bookletv3   Sleep   0       
145074  bookletv3   localhost   bookletv3   Query   0   Statistics  -- (brand) product city
      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )
    
      WHERE c.product != '' AND c.product = 'tiramisu' AND s.`city` = 'losangeles' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("HY2112","HY1134","HY90428","HY10223","AR13821","AR90498","AR90613","BR92144","BR9239") 

      LIMIT 1)


      -- brand city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = 'Chewy' AND s.brand != '' AND s.`city` = 'losangeles' AND s.`city` != '' AND s.`prod_type` = 1 AND s.text_es <>'')


      -- city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_city_seo` s
      WHERE `city_slug` = 'losangeles' AND `city_slug` != ''
      LIMIT 1)


      -- product
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )

      WHERE c.product = 'tiramisu' AND c.product != '' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("HY2112","HY1134","HY90428","HY10223","AR13821","AR90498","AR90613","BR92144","BR9239")

      LIMIT 1)


      -- brand
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE (s.brand = 'Chew' OR s.brand = '') AND s.brand != '' AND s.`prod_type` = 1 AND s.text_es <>''

      LIMIT 1)

      -- none
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = '' AND s.product = '' AND s.`prod_type` = 1 AND s.text_es <>'')

      LIMIT 1
145075  bookletv3   localhost   bookletv3   Sleep   0       
145076  bookletv3   localhost   bookletv3   Sleep   0       
145077  bookletv3   localhost:33858 bookletv3   Sleep   0       
145078  bookletv3   localhost:33860 bookletv3   Sleep   0       
145079  bookletv3   localhost:33862 bookletv3   Sleep   0   
145080  bookletv3   localhost   bookletv3   Query   0   Statistics  -- (brand) product city
      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )
    
      WHERE c.product != '' AND c.product = 'muffins' AND s.`city` = 'losangeles' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("HY2112","HY1134","HY90428","HY10223") 

      LIMIT 1)


      -- brand city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = 'Chewy' AND s.brand != '' AND s.`city` = 'losangeles' AND s.`city` != '' AND s.`prod_type` = 1 AND s.text_es <>'')


      -- city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_city_seo` s
      WHERE `city_slug` = 'losangeles' AND `city_slug` != ''
      LIMIT 1)


      -- product
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )

      WHERE c.product = 'muffin' AND c.product != '' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("HY2112","HY1134","HY90428","HY10223","AR13821","AR90498","AR90613","BR92144","BR9239")

      LIMIT 1)


      -- brand
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE (s.brand = 'Chewy' OR s.brand = '') AND s.brand != '' AND s.`prod_type` = 1 AND s.text_es <>''

      LIMIT 1)

      -- none
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = '' AND s.product = '' AND s.`prod_type` = 1 AND s.text_es <>'')

      LIMIT 1   
145081  bookletv3   localhost   bookletv3   Query   0   Statistics  -- (brand) product city
      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )
    
      WHERE c.product != '' AND c.product = 'muffins' AND s.`city` = 'losangeles' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("HY2112","HY10223") 

      LIMIT 1)


      -- brand city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = 'Chewy' AND s.brand != '' AND s.`city` = 'losangeles' AND s.`city` != '' AND s.`prod_type` = 1 AND s.text_es <>'')


      -- city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_city_seo` s
      WHERE `city_slug` = 'losangeles' AND `city_slug` != ''
      LIMIT 1)


      -- product
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )

      WHERE c.product = 'muffin' AND c.product != '' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("HY2112","HY1134")

      LIMIT 1)


      -- brand
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE (s.brand = 'Chewy' OR s.brand = '') AND s.brand != '' AND s.`prod_type` = 1 AND s.text_es <>''

      LIMIT 1)

      -- none
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = '' AND s.product = '' AND s.`prod_type` = 1 AND s.text_es <>'')

      LIMIT 1
145082  bookletv3   localhost   bookletv3   Query   0   Statistics  -- (brand) product city
      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )
    
      WHERE c.product != '' AND c.product = 'tiramisu' AND s.`city` = 'losangeles' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("BR92144","BR9239") 

      LIMIT 1)


      -- brand city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = 'Chewy' AND s.brand != '' AND s.`city` = 'losangeles' AND s.`city` != '' AND s.`prod_type` = 1 AND s.text_es <>'')


      -- city
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_city_seo` s
      WHERE `city_slug` = 'losangeles' AND `city_slug` != ''
      LIMIT 1)


      -- product
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s

      INNER JOIN `wp_prod` c
      ON (
        (c.bestproductname = s.product and c.bestproductname != '')
        OR c.product = s.product
      )

      WHERE c.product = 'tiramisu' AND c.product != '' AND s.`prod_type` = 1 AND s.text_es <>''
      AND c.location in ("BR92144","BR9239")

      LIMIT 1)


      -- brand
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE (s.brand = 'Chew' OR s.brand = '') AND s.brand != '' AND s.`prod_type` = 1 AND s.text_es <>''

      LIMIT 1)

      -- none
      UNION ALL

      (SELECT s.text_es text
      FROM `wp_prod_brandproduct_seo` s
      WHERE s.brand = '' AND s.product = '' AND s.`prod_type` = 1 AND s.text_es <>'')

      LIMIT 1

Any tutorial on this? I am scared, because I am not 100% sure what to do.

60
0 + 6
in flag
Gerald Schneider
By restarting the daemon you are only addressing the symptoms. Find the cause instead. If it happens at specific times there is most probably a cron job running at that time. Check what it does and fix or optimize it.
1
Reply
in flag
Gerald Schneider
If the cause are web requests, check the access logs. Maybe a badly programmed crawler is the cause.
0
Reply
Patrick Mevzek avatar
cn flag
Patrick Mevzek
"Now, I was thinking and I thought the best way to make the server faster would be to restart the mysqld process" Why do you think so actually? RAM is supposed to be used, free ram is useless, see https://www.linuxatemyram.com/ ; if it uses memory it is because it caches a lot of things which **improves** performance, by restarting it you loose all of that and in fact your system will be slower immediately after.
1
Reply
Patrick Mevzek avatar
cn flag
Patrick Mevzek
"Server is always slow at around 2pm for 1 week" Define slow. What is slow? How much from normal? Do you have monitoring in place to keep trends and compare things or is it just "subjective". What does "around 2pm for 1 week" means exactly? Do you have anything as cron jobs running at 2pm? Did you look at your Apache logfiles to see what kind of activity you have at that timeframe? Do you monitor your logfiles and/or Apache behavior? Etc.
0
Reply
Patrick Mevzek avatar
cn flag
Patrick Mevzek
"Any tutorial on this?" Your question is far too broad, you are just asking how to do a sysadmin job based on few details. Your `top` output shows you use Apache, Mysql and PHP. Problems can happen with all of them. You then show a big SQL query. Maybe that is the problem, maybe not. How big is the DB? Do you have indexes? Do you monitor things? Far too many questions... You should probably start by approaching the people in charge with the PHP applications you are running on this server and discuss with them.
0
Reply
Michael Hampton avatar
cz flag
Michael Hampton
Between MySQL and PHP your server is using about 100% of its CPU. This has clearly been going on for quite some time as MySQL has accumulated quite the large amount of runtime. My best guess here is your app has unoptimized or poorly optimized SQL queries which are bogging the system down. Have the app developers review the MySQL slow log and deal with the worst offenders.
0
Reply
Wilson Hauck avatar
jp flag
Wilson Hauck
Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device, for server workload tuning analysis to provide suggestions.
0
Reply
Wilson Hauck avatar
jp flag
Wilson Hauck
This is long but please scan the entire content and then work with EXPLAIN SELECT .... -- https://dev.mysql.com/doc/refman/5.7/en/explain.html - for significant assistance in finding why your queries are SLOW. You will discover if you read very carefully, ANALYZE TABLE tbl_name; will refresh table statistics and sometimes this is the only thing needed.
0
Reply
Wilson Hauck avatar
jp flag
Wilson Hauck
@goofball Please post your slow query log that covers from 1:30 TO 2:30 PM for two normal working days for analysis, please.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.