MySQL Forums
Forum List  »  Newbie

Newbie question about correct settings to achieve better performance
Posted by: Justin Wood
Date: June 07, 2009 09:27AM

It seems that it takes an awful long time (almost 2 seconds with 93658 rows with the Select listed below) to return data from a table in my database. Unfortunately, the basic table design is stuck how it is because of the way an external software package is set up. I can't do anything to change the way it Inserts or Selects data from the table. The most I can do is to change the index/indices and the engine used and possibly data types if it would help with anything.

The problem is, I have to write to this database every second. And every time a chart opens, I have to read the data, back a certain period of time determined by the user opening the chart.

All of these is extremely CPU intensive-I would like to cut down a bit on the amount of processing by tweaking my settings, but feel a bit uncomfortable doing much of this without having some suggestions from the more experienced.

I would prefer to leave MySQL on a machine shared with other software because the machine is in a very remote location, plaqued by storms, and power outages, mounted in a weatherproof enclosure with climate control, 15 ft in the air on a communications tower. Its a Windows box, Intel Core 2 Quad Q6600 Kentsfield 2.4GHz with 4 GB Ram and 1TB RAID.

Any input is appreciated

Here is the insert that the software uses:
INSERT INTO TREND001 (Time_Stamp, Time_Stamp_ms, SK00_SC_MODA_CH3, SK00_SC_MODA_CH4, SK00_LP_Rate, SK00_FL_RateAll, SK00_FL_RateOil, SK00_FL_RateWater, SK00_HP_Rate, SK00_SC_MODA_CH1, Bias ) VALUES (_latin1'2009-06-07 10:03:42', 193, 5.00015258789062500e+001, 5.00015258789062500e+001, 1.61515501325249430e+000, 8.36653643300294370e+000, 2.50996092990088350e-001, 8.11554034001285630e+000, 6.07288194806405550e+000, 8.99993896484375000e+002, 300 )

And the select it uses to read the data
SELECT Time_Stamp, Time_Stamp_ms, SK00_SC_MODA_CH3, SK00_SC_MODA_CH4, SK00_LP_Rate, SK00_FL_RateAll, SK00_FL_RateOil, SK00_FL_RateWater, SK00_HP_Rate, SK00_SC_MODA_CH1, Bias FROM TREND001 WHERE  ( ( Time_Stamp > _latin1'2009-06-06 22:59:00' OR ( Time_Stamp = _latin1'2009-06-06 22:59:00' AND Time_Stamp_ms >= 100)) AND ( Time_Stamp < _latin1'2009-06-07 09:50:47' OR ( Time_Stamp = _latin1'2009-06-07 09:50:47' AND Time_Stamp_ms <= 634)) AND Bias = 300 ) OR ( ( Time_Stamp > _latin1'2009-06-06 21:59:00' OR ( Time_Stamp = _latin1'2009-06-06 21:59:00' AND Time_Stamp_ms >= 100)) AND ( Time_Stamp < _latin1'2009-06-07 08:50:47' OR ( Time_Stamp = _latin1'2009-06-07 08:50:47' AND Time_Stamp_ms <= 634)) AND Bias = 360 ) ORDER BY Time_Stamp ASC, Time_Stamp_ms ASC

DROP TABLE IF EXISTS `clscada`.`trend001`;
CREATE TABLE  `clscada`.`trend001` (
  `Time_Stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Time_Stamp_ms` int(11) NOT NULL DEFAULT '0',
  `SK00_SC_MODA_CH3` double DEFAULT NULL,
  `SK00_SC_MODA_CH4` double DEFAULT NULL,
  `SK00_LP_Rate` double DEFAULT NULL,
  `SK00_FL_RateAll` double DEFAULT NULL,
  `SK00_FL_RateOil` double DEFAULT NULL,
  `SK00_FL_RateWater` double DEFAULT NULL,
  `SK00_HP_Rate` double DEFAULT NULL,
  `SK00_SC_MODA_CH1` double DEFAULT NULL,
  `Bias` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Time_Stamp`,`Time_Stamp_ms`,`Bias`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=1;



Edited 1 time(s). Last edit at 06/07/2009 09:27AM by Justin Wood.

Options: ReplyQuote


Subject
Written By
Posted
Newbie question about correct settings to achieve better performance
June 07, 2009 09:27AM


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.