[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-win32
Subject:    Performance issue with views
From:       "Ilavajuthy Palanisamy" <ipalanisamy () consentry ! com>
Date:       2006-03-18 4:28:05
Message-ID: 52BBA75459915749B68F93B604B636CD012998CE () neptune ! TidalNetworks ! net
[Download RAW message or body]


Need a solution, please help. 

Hi, 
I have tables in the name 'flowsummary_dateformat', these tables will be
created for every 1 hour. We are using MYSQL5.0.19. 
The table description is given below. All these tables are INNODB
tables. 

mysql> desc flowsummary_2006_03_10_23_00_00; 
+------------------+--------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+------------------+--------------+------+-----+---------+-------+ 
| id | bigint(20) | NO | PRI | 0 | | 
| userId | bigint(20) | NO | MUL | 0 | | 
| startTime | bigint(20) | YES | MUL | | | 
| endTime | bigint(20) | YES | MUL | | | 
| bytesIn | bigint(20) | YES | | | | 
| bytesOut | bigint(20) | YES | | | | 
| packetsIn | bigint(20) | YES | | | | 
| packetsOut | bigint(20) | YES | | | | 
| flowCount | bigint(20) | YES | | | | 
| lastUpdated | bigint(20) | YES | | | | 
| entryStatus | int(11) | YES | | | | 
| deviceId | int(11) | NO | MUL | 0 | | 
| deviceSpecificId | bigint(20) | NO | | 0 | | 
| sourcePort | int(11) | YES | | | | 
| bandwidth | bigint(20) | YES | | | | 
| destIp | varchar(15) | YES | | | | 
| destPort | int(11) | YES | | | | 
| appName | varchar(255) | YES | | | | 
| appProtocol | int(11) | YES | | | | 
| appIdType | int(11) | YES | | | | 
| appCategory | varchar(30) | YES | | | | 
| flowDirection | int(11) | YES | | | | 
+------------------+--------------+------+-----+---------+-------+ 
22 rows in set (0.00 sec) 

Like this we have 73 tables of total 1.6 million records. We are
creating a view out of these 73 tables and executing a query. We found
that the view behaves inconsistent in the below mentioned two queries. 
These queries involves join with user table which is an innodb with 2000
records. 

Query 1 which comes in 37 secs. Create view format and the query on this
view is given below. 

create view flowsummaryview5 as 
(SELECT
flowsummary_2006_03_09_00_00_00.appName,flowsummary_2006_03_09_00_00_00.
appProtocol,flowsummary_2006_03_09_00_00_00.destPort,flowsummary_2006_03
_09_00_00_00.destIp,SUM(flowsummary_2006_03_09_00_00_00.bytesOut) as
a1,SUM(flowsummary_2006_03_09_00_00_00.bytesIn) as
a2,SUM(flowsummary_2006_03_09_00_00_00.packetsOut) as
a3,SUM(flowsummary_2006_03_09_00_00_00.packetsIn) as
a4,MIN(flowsummary_2006_03_09_00_00_00.startTime) as
a5,MAX(flowsummary_2006_03_09_00_00_00.endTime) as
a6,COUNT(flowsummary_2006_03_09_00_00_00.userId) as a7,usr.name FROM usr
JOIN flowsummary_2006_03_09_00_00_00 ON
usr.id=flowsummary_2006_03_09_00_00_00.userId WHERE
(flowsummary_2006_03_09_00_00_00.startTime>0) GROUP BY
flowsummary_2006_03_09_00_00_00.appName,flowsummary_2006_03_09_00_00_00.
appProtocol,flowsummary_2006_03_09_00_00_00.destPort,flowsummary_2006_03
_09_00_00_00.destIp,usr.name) 
UNION 
(SELECT
flowsummary_2006_03_10_23_00_00.appName,flowsummary_2006_03_10_23_00_00.
appProtocol,flowsummary_2006_03_10_23_00_00.destPort,flowsummary_2006_03
_10_23_00_00.destIp,SUM(flowsummary_2006_03_10_23_00_00.bytesOut) as
a1,SUM(flowsummary_2006_03_10_23_00_00.bytesIn) as
a2,SUM(flowsummary_2006_03_10_23_00_00.packetsOut) as
a3,SUM(flowsummary_2006_03_10_23_00_00.packetsIn) as
a4,MIN(flowsummary_2006_03_10_23_00_00.startTime) as
a5,MAX(flowsummary_2006_03_10_23_00_00.endTime) as
a6,COUNT(flowsummary_2006_03_10_23_00_00.userId) as a7,usr.name FROM usr
JOIN flowsummary_2006_03_10_23_00_00 ON
usr.id=flowsummary_2006_03_10_23_00_00.userId WHERE
(flowsummary_2006_03_10_23_00_00.startTime>0) GROUP BY
flowsummary_2006_03_10_23_00_00.appName,flowsummary_2006_03_10_23_00_00.
appProtocol,flowsummary_2006_03_10_23_00_00.destPort,flowsummary_2006_03
_10_23_00_00.destIp,usr.name) 
UNION 
. 
. 
Till 73 tables 

SELECT SQL_CALC_FOUND_ROWS appName, appProtocol, destPort, destIp,
sum(a1), sum(a2), sum(a3), sum(a4), min(a5), max(a6), count(a7), name
FROM flowsummaryview5 group by appName, appProtocol, destPort, destIp,
name limit 10; 

*/*/*/*/*/*************************************** 


Query 2 very similar to the above except the select and group by has
additional two columns 'ipAddress' and 'macAddress', this query takes
very long time (several minutes) or never returning. The view creation
and its query are given below. 

create view flowsummaryview6 as 
(SELECT
flowsummary_2006_03_09_00_00_00.appName,flowsummary_2006_03_09_00_00_00.
appProtocol,flowsummary_2006_03_09_00_00_00.destPort,flowsummary_2006_03
_09_00_00_00.destIp,SUM(flowsummary_2006_03_09_00_00_00.bytesOut) as
a1,SUM(flowsummary_2006_03_09_00_00_00.bytesIn) as
a2,SUM(flowsummary_2006_03_09_00_00_00.packetsOut) as
a3,SUM(flowsummary_2006_03_09_00_00_00.packetsIn) as
a4,MIN(flowsummary_2006_03_09_00_00_00.startTime) as
a5,MAX(flowsummary_2006_03_09_00_00_00.endTime) as
a6,COUNT(flowsummary_2006_03_09_00_00_00.userId) as
a7,usr.name,usr.ipAddress,usr.macAddress FROM usr JOIN
flowsummary_2006_03_09_00_00_00 ON
usr.id=flowsummary_2006_03_09_00_00_00.userId WHERE
(flowsummary_2006_03_09_00_00_00.startTime>0) GROUP BY
flowsummary_2006_03_09_00_00_00.appName,flowsummary_2006_03_09_00_00_00.
appProtocol,flowsummary_2006_03_09_00_00_00.destPort,flowsummary_2006_03
_09_00_00_00.destIp,usr.name,usr.ipAddress,usr.macAddress) 
UNION 
(SELECT
flowsummary_2006_03_10_23_00_00.appName,flowsummary_2006_03_10_23_00_00.
appProtocol,flowsummary_2006_03_10_23_00_00.destPort,flowsummary_2006_03
_10_23_00_00.destIp,SUM(flowsummary_2006_03_10_23_00_00.bytesOut) as
a1,SUM(flowsummary_2006_03_10_23_00_00.bytesIn) as
a2,SUM(flowsummary_2006_03_10_23_00_00.packetsOut) as
a3,SUM(flowsummary_2006_03_10_23_00_00.packetsIn) as
a4,MIN(flowsummary_2006_03_10_23_00_00.startTime) as
a5,MAX(flowsummary_2006_03_10_23_00_00.endTime) as
a6,COUNT(flowsummary_2006_03_10_23_00_00.userId) as
a7,usr.name,usr.ipAddress,usr.macAddress FROM usr JOIN
flowsummary_2006_03_10_23_00_00 ON
usr.id=flowsummary_2006_03_10_23_00_00.userId WHERE
(flowsummary_2006_03_10_23_00_00.startTime>0) GROUP BY
flowsummary_2006_03_10_23_00_00.appName,flowsummary_2006_03_10_23_00_00.
appProtocol,flowsummary_2006_03_10_23_00_00.destPort,flowsummary_2006_03
_10_23_00_00.destIp,usr.name,usr.ipAddress,usr.macAddress) 
. 
. 
Till 73 tables 


SELECT appName, appProtocol, destPort, destIp, sum(a1), sum(a2),
sum(a3), sum(a4), min(a5), max(a6), count(a7), name, ipAddress,
macAddress FROM flowsummaryview4 group by appName, appProtocol,
destPort, destIp, name, ipAddress, macAddress limit 10; 

The show processlist, for the good case shows 'copy to tmp table',
'sorting'. For bad case it shows 'copy to tmp table', copy from HEAP to
MYISAM', 'Sending data' and 'sorting' and it never completes. 

/*******************************************/ 

Also we changed all the above 73 tables to MYISAM and found the query is
faster. So we created 72 tables as MYISAM and created a MERGE out of it.
Now created a VIEW out of this merge and the remaining one INNODB table,
now the query executed on this view is also not returning. The query on
the single table takes around 7 secs and the query on the MERGE table
takes 1.50 mins, so logically the view should have taken ~2 mins, but it
is never returning. 

Also we tried the above view creation out of 3 tables (same format) of
total 9 million records, the query returns within 2 mins. 

It would be great if any one suggests a solution for this problem. 

We can't cant go with MYISAM tables because the tables are getting
locked during update and the subsequent queries are waiting. 


Ila.



[prev in list] [next in list] [prev in thread] [next in thread]