I have been trying to understand why this query is taking over 60 seconds to
complete SOMETIMES...its not that complicated and the server (4.0.17) is
under very little load. Any help would be appreciated.
They Query:
SELECT Realm, COUNT(*) AS CallCount, SUM(AcctSessionTime) AS RealmTime FROM
ServiceRADIUSAccounting WHERE AcctStartTime < '2003-12-12 16:00:00' AND
AcctStopTime > '2003-12-12 15:00:00' AND (Realm = 'bwsys.net') GROUP BY
Realm
The Explain:
table type possible_keys
key key_len ref rows Extra
ServiceRADIUSAccounting ref
AcctStopTime,Realm,RealmAndStart,StartStopRealm RealmAndStart 65
const 74391 Using where
The following table has 1.4 million records in it (which *should* not be a
problem for MySQL):
CREATE TABLE `ServiceRADIUSAccounting` (
`RadAcctId` int(16) NOT NULL auto_increment,
`AcctSessionId` char(32) NOT NULL default '',
`AcctUniqueId` char(32) NOT NULL default '',
`UserName` char(64) NOT NULL default '',
`Realm` char(64) default NULL,
`NASIPAddress` char(15) NOT NULL default '',
`AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctSessionTime` int(12) NOT NULL default '0',
`XmitSpeed` int(8) NOT NULL default '0',
`RecvSpeed` int(8) NOT NULL default '0',
`AcctInputOctets` int(12) default NULL,
`AcctOutputOctets` int(12) default NULL,
`CalledStationId` char(11) NOT NULL default '0',
`CallingStationId` char(11) NOT NULL default '0',
`AcctTerminateCause` char(8) NOT NULL default '',
`FramedIPAddress` char(15) NOT NULL default '',
PRIMARY KEY (`RadAcctId`),
KEY `UserName` (`UserName`),
KEY `FramedIPAddress` (`FramedIPAddress`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `AcctUniqueId` (`AcctUniqueId`),
KEY `AcctStopTime` (`AcctStopTime`),
KEY `NASIPAddress` (`NASIPAddress`),
KEY `Realm` (`Realm`),
KEY `RealmAndStart` (`Realm`,`AcctStartTime`,`AcctStopTime`),
KEY `StartStopRealm` (`AcctStartTime`,`AcctStopTime`,`Realm`)
) TYPE=InnoDB AUTO_INCREMENT=5397482 ;
Anyway the output (when it fiannly shows up) looks like this:
Realm CallCount RealmTime
bwsys.net 406 2093284
The interesting thing I have noticed is that performance varies depending on
the dates I use (but still only reporting over a 1hr duration..the call
count stays about the same so the SUM function is still being used about the
same amount). This would lead me to believe that MySQL is having a hard
time retrieving the 406 records from the table...this DB server isn't really
that busy until I run one of these queries. For example a query on
12/12/2003 seems to be 10X slower than a query for the same time on
12/1/2003. It seems that the older the query (time wise) the faster it
goes...very strange. And now when I run it for some reason its going fast
again....I am assuming because of disk caching or something along those
lines...it can't seem to make up its mind. All the server is doing is
taking in RADIUS accoutning records and authenticating users...CPU
utilization is around 1% per CPU and the machine has 1GB RAM. Here's my
my.cnf if that helps too:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
server-id = 1
skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=2M
set-variable = sort_buffer=2M
set-variable = innodb_buffer_pool_size=600M
set-variable = innodb_additional_mem_pool_size=16M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=75M
set-variable = innodb_log_buffer_size=8M
set-variable = innodb_flush_log_at_trx_commit=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
----------------------------------------
Michael Shuler, C.E.O.
BitWise Systems, Inc.
1301 W. Pioneer Parkway
Peoria, IL 61615
Office: (217) 585-0357
Cell: (309) 657-6365
Fax: (309) 213-3500
E-Mail: [EMAIL PROTECTED]
Customer Service: (877) 976-0711
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 12/20/2003
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
