Skip navigation links

MySQL Forums :: Performance :: INNODB large database performance


Advanced Search

INNODB large database performance
Posted by: Paul Smith ()
Date: February 16, 2013 03:11AM

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.

Options: ReplyQuote


Subject Views Written By Posted
INNODB large database performance 1654 Paul Smith 02/16/2013 03:11AM
Re: INNODB large database performance 567 Rick James 02/20/2013 05:31PM
Re: INNODB large database performance 505 Paul Smith 02/21/2013 02:51AM
Re: INNODB large database performance 472 Rick James 02/21/2013 11:12PM
Re: INNODB large database performance 485 Paul Smith 02/22/2013 03:38AM
Re: INNODB large database performance 413 Rick James 02/23/2013 12:04AM
Re: INNODB large database performance 424 Paul Smith 02/23/2013 03:28PM
Re: INNODB large database performance 389 Rick James 02/24/2013 02:10PM
Re: INNODB large database performance 420 Paul Smith 02/24/2013 03:02PM
Re: INNODB large database performance 458 Rick James 02/25/2013 10:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.