Mysql running very slow with high CPU usage and High load average, Please Help
Hi,
I have running a website with database mysql on RHEL 6 but mysql using 90-95% CPU and very high load average. It slow down with every connection for website. Please help
here is my my.cnf
[mysqld]
datadir=/test
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
mysql> show full processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 89085
Current database: *** NONE ***
+-------+------+-----------------+----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------------+----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 89026 | remote | 10.59.7.7:45606 | remote_db | Execute | 7 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:24')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:24')>=0 andremlic.status_code = 'A' |
| 89030 | remote | 10.59.7.7:45607 | remote_db | Execute | 31 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 3383,2580,2986,11266) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:00')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:00')>=0 andremlic.status_code = 'A' |
| 89033 | remote | 10.59.7.7:45608 | remote_db | Sleep | 0 | | NULL |
| 89047 | remote | 10.59.7.6:50608 | remote_db | Execute | 10 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 3383,2580,2986,11266) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:21')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:21')>=0 andremlic.status_code = 'A' |
| 89048 | remote | 10.59.7.6:50610 | remote_db | Execute | 1 | preparing | SELECT site.site_id ,SIT_DISPLAY_NAME ,SIT_LOGO_PATH, site.company, site.SIT_ACCOUNT_NUMBER ,site.country FROM site_accounts site where site.SIT_ACCOUNT_STATUS='ACTIVE' and site.DELETED_IND=0 and site.site_id in (SELECT site_id from cdp_range WHERE '2071287841' between low_address AND high_address AND upper(status)='APPROVED' AND DELETED_IND=0) |
| 89049 | remote | 10.59.7.6:50633 | remote_db | Sleep | 6 | | NULL |
| 89050 | remote | 10.59.7.6:50643 | remote_db | Sleep | 2 | | NULL |
| 89051 | remote | 10.59.7.7:45610 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 3383,2580,2986,11266) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:14')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:14')>=0 andremlic.status_code = 'A' |
| 89052 | remote | 10.59.7.6:50647 | remote_db | Sleep | 1 | | NULL |
| 89053 | remote | 10.59.7.7:45611 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:14')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:14')>=0 andremlic.status_code = 'A' |
| 89054 | remote | 10.59.7.6:50648 | remote_db | Sleep | 79 | | NULL |
| 89055 | remote | 10.59.7.7:45613 | remote_db | Execute | 35 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 722,2141,2731,11266) and DATEDIFF(remlic.startdate,'2014-02-10 06:02:56')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:02:56')>=0 andremlic.status_code = 'A' |
| 89056 | remote | 10.59.7.6:50653 | remote_db | Execute | 23 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 3433,2580,2731,2986,11266) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:08')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:08')>=0 andremlic.status_code = 'A' |
| 89057 | remote | 10.59.7.6:50654 | remote_db | Execute | 24 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:07')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:07')>=0 andremlic.status_code = 'A' |
| 89058 | remote | 10.59.7.6:50655 | remote_db | Sleep | 0 | | NULL |
| 89060 | remote | 10.59.7.6:50657 | remote_db | Execute | 5 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:26')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:26')>=0 andremlic.status_code = 'A' |
| 89061 | remote | 10.59.7.6:50659 | remote_db | Execute | 36 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:02:55')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:02:55')>=0 andremlic.status_code = 'A' |
| 89062 | remote | 10.59.7.7:45614 | remote_db | Sleep | 0 | | NULL |
| 89065 | remote | 10.59.7.7:45616 | remote_db | Execute | 30 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:01')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:01')>=0 andremlic.status_code = 'A' |
| 89066 | remote | 10.59.7.6:50676 | remote_db | Execute | 21 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:10')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:10')>=0 andremlic.status_code = 'A' |
| 89070 | remote | 10.59.7.7:45617 | remote_db | Execute | 21 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:10')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:10')>=0 andremlic.status_code = 'A' |
| 89071 | remote | 10.59.7.7:45619 | remote_db | Execute | 54 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 14,1567) and DATEDIFF(remlic.startdate,'2014-02-10 06:02:37')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:02:37')>=0 andremlic.status_code = 'A' |
| 89072 | remote | 10.59.7.7:45620 | remote_db | Execute | 29 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:02')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:02')>=0 andremlic.status_code = 'A' |
| 89073 | remote | 10.59.7.7:45621 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:14')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:14')>=0 andremlic.status_code = 'A' |
| 89074 | remote | 10.59.7.7:45622 | remote_db | Execute | 33 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:02:58')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:02:58')>=0 andremlic.status_code = 'A' |
| 89075 | remote | 10.59.7.7:45623 | remote_db | Execute | 13 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:18')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:18')>=0 andremlic.status_code = 'A' |
| 89080 | remote | 10.59.7.7:45625 | remote_db | Execute | 23 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:08')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:08')>=0 andremlic.status_code = 'A' |
| 89081 | remote | 10.59.7.7:45626 | remote_db | Execute | 14 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id and remlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:17')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:17')>=0 and remlic.status_code = 'A' |
| 89082 | remote | 10.59.7.7:45627 | remote_db | Sleep | 4 | | NULL |
| 89083 | remote | 10.59.7.7:45628 | remote_db | Execute | 13 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate, remlic.enddate, lic_types.name,CASE WHEN remlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHEN remlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p, remlic remlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id = remlic.type_id and remlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:18')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:18')>=0 and remlic.status_code = 'A' |
| 89084 | remote | 10.59.7.7:45629 | remote_db | Execute | 1 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.enddate, lic_types.name,CASE WHENremlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_accessfrom,1,4) END AS contAccessFrom,CASE WHENremlic.cont_accessto IS NULL THEN '9999' ELSE SUBSTRING(remlic.cont_accessto,1,4) END AS contAccessTo, art.ART_COPYRIGHT_YEAR as cry from products p,remlicremlic, remlic_types lic_types, Article art where upper(p.PRODUCT_TYPE) = 'UNIT' AND remlic.PRODUCT_ID =p.product_id and lic_types.id =remlic.type_id andremlic.site_id in ( 13) and DATEDIFF(remlic.startdate,'2014-02-10 06:03:30')<=0 and DATEDIFF(remlic.expirey,'2014-02-10 06:03:30')>=0 andremlic.status_code = 'A' | |
+-------+------+-----------------+----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
32 rows in set (0.03 sec)
Are your tables indexed?
If so, how often do you have an ANALYZE TABLE issued to update the index statistics?
Can you get an explain plan for that query that shows up over and over in that output you provided?
Three months is way too old. Use the analyze table command and set up a weekly or more often scheduled job to run it. http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
Note that there is a table lock while this operation is performed; normally it is scheduled for when low activity is expected. In your case, it sounds like an amergency so run it asap but then schedule it for slow times.
If so, how often do you have an ANALYZE TABLE issued to update the index statistics?
Can you get an explain plan for that query that shows up over and over in that output you provided?