just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes.... is useless
On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: > Hi everybody, > I have a MySQL database with MyISAM tables. > > As we're experiencing a lot of locking-related problems I decided to > migrate to InnoDB. > > Our database is composed by a lot of small tables (1.000 - 10.000 rows) > and a huge table containing 7.000.000 rows, this big table is a sort of > a log of our subscriber's phone calls. > > I have a query I often run on the big table that is performing really > poorly on InnoDB (18mins Innodb vs 29secs MyISAM) > > This is my query > > SELECT > DATE_FORMAT(calldate, '%d') AS day, > count(*) AS num, > disposition > FROM cdr > WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' > GROUP BY day, disposition; > > Using EXPLAIN I see that the query on the InnoDB table isn't using > indexes at all but the one on MyISAM table (same structure, same > indexes, same data) is choosing the correct index. > > Here are my EXPLAIN results > > MyISAM: > id: 1 > select_type: SIMPLE > table: cdr > type: range > possible_keys: calldate,date-context-cause > key: calldate > key_len: 8 > ref: NULL > rows: 697688 > Extra: Using where; Using temporary; Using filesort > > Innodb: > id: 1 > select_type: SIMPLE > table: cdr_innodb > type: ALL > possible_keys: calldate,date-context-cause > key: NULL > key_len: NULL > ref: NULL > rows: 5035407 > Extra: Using where; Using temporary; Using filesort > > As you can see, Innodb doesn't use the calldate index (which seems to me > the correct choice) > > Probably I can solve this query performance problem with an index on > calldate, disposition but I'd like to understand deeper the causes of > that to avoide re-analizing every query ad retry to optimize it as I did > with MyISAM. > > I have got a Xeon quad core with SAS disks and 4 GB of RAM > I'm using a config file taken from MySQL sources optimized for innodb > and 4G RAM (my-innodb-heavy-4G.cnf) > > I followed some simple optimization rules as putting InnoDB data dir on > a different array of disks on a different channel, etc... > > Im using MySQL 5.0.32 on a Debian stable. > > Tnx in advance for help > > Regards > > Edoardo Serra > WeBRainstorm S.r.l. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
