Ditutup

MySQ running high

the website was designed several years ago and it was running high.

It is hosted at viaverio virtual shared hosting.

Since two weeks ago it is running on high CPU very slow or not opening.

I need an expert to resolve the problem Budget: $50 -200

here is the investigation so far:

It appears the slowness and abuse are all caused by one query. We noticed that MySQL was running at 99% CPU usage and there were 6 of these queries (Query Copying to t SELECT ...) that were running and using most of the CPU.

Here is the exact query that is being run:

SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view] FROM editorialTraffic, editorialDatabase, editorialArea, editorialSection, author_articles WHERE editorialTraffic.editorialid=[url removed, login to view] AND [url removed, login to view] = [url removed, login to view] AND [url removed, login to view] = 7 AND editorialDatabase.sectionID=[url removed, login to view] AND editorialDatabase.areaID=[url removed, login to view]

GROUP BY [url removed, login to view]

I would suggest working with a database administrator to see if there is some way to optimize this query to prevent it from running so heavy.

*******************************

Here is the raw data we found.

>From mtop (mtop --dbuser=**** --password=******)

load averages: [url removed, login to view], [url removed, login to view], [url removed, login to view] mysqld 4.1.7 up 0 day(s), 5:49 hrs

19 threads: 14 running, 0 cached. Queries/slow: [url removed, login to view] Cache Hit: [url removed, login to view]% Opened tables: [url removed, login to view] RRN: [url removed, login to view] TLW: 52 SFJ: [url removed, login to view] SMP: 0 QPS: 16

ID USER HOST DB TIME COMMAND STATE INFO

4082 *** localhost ***** 10998 Query Copying to t SELECT ... FROM editorialTraffic, editorialDatabase, edi

6432 *** localhost ***** 5078 Query Copying to t SELECT ... FROM editorialTraffic, editorialDatabase, edi

6435 *** localhost ***** 5047 Query Copying to t SELECT ... FROM editorialTraffic, editorialDatabase, edi

6798 *** localhost ***** 3751 Query Copying to t SELECT ... FROM editorialTraffic, editorialDatabase, edi

6833 *** localhost ***** 3679 Query Copying to t SELECT ... FROM editorialTraffic, editorialDatabase, edi

6859 *** localhost ***** 3585 Query Copying to t SELECT ... FROM editorialTraffic, editorialDatabase, edi

7785 **** localhost ***** 287 Query Locked OPTIMIZE TABLE `author_articles`

7786 *** localhost ***** 271 Query Locked SELECT ... from author_articles WHERE editorialID=934

7789 *** localhost ***** 259 Query Locked SELECT ... from author_articles WHERE editorialID=2187

7799 *** localhost ***** 141 Query Locked SELECT ... FROM editorialDatabase AS e JOIN author_artic 7800 *** localhost ***** 137 Query Locked SELECT ... from author_articles WHERE editorialID=304

7816 *** localhost ***** 70 Query Locked SELECT ... from author_articles WHERE editorialID=969

7818 *** localhost ***** 34 Query Locked SELECT ... from author_articles WHERE editorialID=3090

7822 *** localhost ***** 1 Query Copying to t SELECT ... fromID ORDER BY [url removed, login to view] D

7801 **** localhost Query show full processlist

Id: 4082 User: *** Host: localhost Db: ***** Time: 11191

Command: Query State: Copying to tmp table

SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view] FROM editorialTraffic, editorialDatabase, editorialArea, editorialSection, author_articles WHERE editorialTraffic.editorialid=[url removed, login to view] AND [url removed, login to view] = [url removed, login to view] AND [url removed, login to view] = 7 AND editorialDatabase.sectionID=[url removed, login to view] AND editorialDatabase.areaID=[url removed, login to view]

GROUP BY [url removed, login to view]

table |type |possible_keys |key | ken_len|ref | rows|

1 |SIMPLE |editorialTraffic |ALL | | | |Using temporary; Using filesort

1 |SIMPLE |author_articles |ALL | | | |Using where

1 |SIMPLE |editorialDatabase |eq_ref | PRIMARY|PRIMARY | 4|Using where

1 |SIMPLE |editorialSection |eq_ref | PRIMARY|PRIMARY | 4|Using index

1 |SIMPLE |editorialArea |eq_ref | PRIMARY|PRIMARY | 4|

That is the query, or something similar that was hanging, that is running the most processes on the server. There is a mysqlcheck setup to run every night at 1:32 AM every [url removed, login to view] the MySQL server and watched it for a while and the queries:

Kemahiran: PHP

Lihat lebih lanjut: mtop query locked, mtop tlw, website opening problem, command group, 233, 11191, mtop qps queries, copying tmp table cpu, mtop qps, mtop coping tmp, state copying temp mtop, mysql locked state select, slow copying tmp table, rrn tlw optimization mysql, mysqlcheck cpu, mysql queries locked state, mtop hagning tmp tables, mysql qps mtop, mtop slow copying, boonex copying tmp table, rrn tlw sfj, mtop mysql locked, query locked mtop, abuse, virtual administrator

Tentang Majikan:
( 0 ulasan ) beverly hills, United States

ID Projek: #369779

6 pekerja bebas membida secara purata $118 untuk pekerjaan ini

DeveloperChris1

Hello I have many years experience with mySQL. my previous job was to maintain a website running from a single mysql server. the website received over 5Million hits per day. with the sql server coping with between 1 Lagi

$50 USD dalam 7 hari
(0 Ulasan)
0.0
anasdev

Hello, Plz check my pm. Thanks.

$150 USD dalam 3 hari
(0 Ulasan)
0.0
mybookspace

$35 per hour. Johnny here. Check my PM for some more questions.

$35 USD dalam sehari
(0 Ulasan)
0.0
mas54321

Please review my PM. Thanks

$175 USD dalam 2 hari
(0 Ulasan)
0.0
metagio

I have experience with RDBMS and Multi-dimensional table design for OLAP and Data warehousing. Proper use of indexing, sub-queries and temporary tables will fix any query processing bottlenecks. DBMS tuning is a fair Lagi

$225 USD dalam 5 hari
(0 Ulasan)
0.0
whitecoffee

Hi! I can help you. Please check you PM box.

$70 USD dalam sehari
(0 Ulasan)
0.0