INNODB large database performance

[Paul Smith]

I am building a reporting platform that requires me to store large amounts of data, i have created a master and slave database on 2 seperate servers which is setup and working fine. The issue i have is that i have hit 14 million records on the following database....

*/
/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`database` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `datbase`;

/*Table structure for table `data` */

DROP TABLE IF EXISTS `data`;

CREATE TABLE `data` (
`EntryDate` datetime NOT NULL COMMENT 'DateTime stamp of data',
`DataVal` float NOT NULL COMMENT 'data value',
`ChannelID` int(11) NOT NULL COMMENT 'Channel forgeign key ref',
`SiteRateID` int(11) DEFAULT NULL COMMENT 'Rate/Cost per unit',
PRIMARY KEY (`EntryDate`,`ChannelID`),
KEY `FK_data_ChannelID` (`ChannelID`),
CONSTRAINT `FK_data_ChannelID` FOREIGN KEY (`ChannelID`) REFERENCES `channels` (`ChannelID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

I am now starting to hit issues with performance but was hoping to grow the size of the database to upto 300 odd million. I do have a master database that is replicated down to this slave server and i run a query with a couple of joins to the master.

Please can someone advise on what the issue could be. I am using the following query to grab my data.....

SET group_concat_max_len := @@max_allowed_packet;
SELECT dt.GroupID AS ChannelID, dt.GroupName AS Description, dt.GroupDescription AS DeviceDescription,
GROUP_CONCAT(DATE_FORMAT(`dt`.`Date`,'%M %e, %Y %k:%i:00') ORDER BY `dt`.`Date` SEPARATOR '|') AS `EntryDate`,
GROUP_CONCAT(dt.Total ORDER BY `dt`.`Date` SEPARATOR '|') AS `Data`, GROUP_CONCAT(dt.Rate ORDER BY `dt`.`Date` SEPARATOR '|') AS `RateData`,
SUM(dt.Total) AS ChannelTotal, SUM(`dt`.`Total`) * 0.34 AS `ChannelTotalCO2`, SUM(`dt`.`TotalCost`) AS ChannelTotalCost FROM
(SELECT SUM(d.`DataVal`)/1 AS Total, g.GroupID, g.`GroupName`, g.`GroupDescription`, `d`.`EntryDate` AS `Date`,
SUM(IFNULL(`sr`.Rate, 0) * (d.DataVal/1)) AS `Rate`, SUM(IFNULL(`sr`.Rate, 0) * (d.DataVal/1)) AS `TotalCost`
FROM master.`channelgroup` cg LEFT JOIN master.`groups` g ON cg.`GroupID` = g.`GroupID` LEFT JOIN `data` d
ON cg.ChannelID = d.`ChannelID` LEFT OUTER JOIN `master`.`siterates` `sr` ON `d`.`SiteRateID` = `sr`.`SiteRateID`
WHERE (FIND_IN_SET(g.`GroupID`,'157,78,87,79,91,81')) AND ((d.EntryDate BETWEEN '2013-01-01 00:00:00' AND '2013-02-15 23:59:59'))
GROUP BY g.GroupID, DATE_FORMAT(`d`.`EntryDate`,'%M %e, %Y %k:%i:00')) AS dt GROUP BY dt.GroupID ORDER BY SUM(dt.Total) DESC;

The master db tables i am joining to contain maximum 10,000 records all join tables are indexed appropriately.

PLEASE HELP A STRESSED DEVELOPER :)



Edited 1 time(s). Last edit at 02/23/2013 03:32PM by Paul Smith.