slow performance with MERGE tables
Hello,
I am running a rather simple query on a merge table that seems to be taking
much longer than it should.
First let me show the table status of the tables I have merged into table
'blah':
NameTypeRow_format RowsAvg_row_length Data_length
Max_data_length Index_lengthData_free Auto_increment Create_time
Update_time Check_time Create_options Comment
accounting_2002W29 MyISAM Fixed 4 435 17401868310773759 3072
0 NULL2002-09-19 21:11:30 2002-09-20 19:27:01 NULL
accounting_2002W30 MyISAM Fixed 10 435 43501868310773759 3072
0 NULL2002-09-19 21:24:35 2002-09-20 18:37:51 NULL
accounting_2002W31 MyISAM Fixed 1612741 435 701542335 1868310773759
975564800 NULL2002-09-19 21:08:09 2002-09-20 19:45:32 NULL
accounting_2002W32 MyISAM Fixed 2341152 435 1018401120 1868310773759
142214144 0 NULL2002-09-19 21:08:15 2002-09-20 19:45:32 NULL
accounting_2002W33 MyISAM Fixed 3204059 435 1393765665 1868310773759
195347456 0 NULL2002-09-19 21:08:25 2002-09-20 19:45:32 NULL
accounting_2002W34 MyISAM Fixed 3388325 435 1473921375 1868310773759
207994880 0 NULL2002-09-19 21:08:43 2002-09-20 19:45:32 NULL
accounting_2002W35 MyISAM Fixed 3322128 435 1445125680 1868310773759
204946432 0 NULL2002-09-20 11:26:21 2002-09-20 19:45:32 NULL
And the query I run takes very long:
mysql> select count(distinct nas_ip) from blah;
++
| count(distinct nas_ip) |
++
|116 |
++
1 row in set (15 min 43.27 sec)
I consider this very long because if I do the same query on one of the merged
tables it is much faster:
mysql> select count(distinct nas_ip) from accounting_2002W35;
++
| count(distinct nas_ip) |
++
| 96 |
++
1 row in set (42.03 sec)
Note I have restarted the server before each query to not let caching affect
this. I don't understand why the merge table takes so long. It would be faster
for me to issue a query for the nas_ip in each table, output this to a
temporary table, and then do a select count(distinct) on this table.
Why is the merge table showing bad performance on this query?
Here are some more details.
System: Linux 2.2.16 (Redhat 6.2)
show create table accounting_2002W35;
CREATE TABLE `accounting_2002W35` (
`acct_status_type` enum('Start','Stop') NOT NULL default 'Start',
`user_name` char(80) NOT NULL default '',
`nas_ip` char(15) NOT NULL default '',
`acct_session_time` mediumint(8) unsigned default NULL,
`acct_session_id` char(30) NOT NULL default '',
`timestamp` datetime NOT NULL default '-00-00 00:00:00',
`framed_ip` char(15) default NULL,
`input_bytes` int(10) unsigned default NULL,
`output_bytes` int(10) unsigned default NULL,
`calling_station_id` char(80) default NULL,
`called_station_id` char(80) default NULL,
`baud_rate` mediumint(8) unsigned default NULL,
`framed_protocol` char(20) default NULL,
`class` char(80) default NULL,
`visp_id` mediumint(8) unsigned default NULL,
`country_id` tinyint(3) unsigned default NULL,
`city_id` mediumint(8) unsigned default NULL,
`provider_id` mediumint(8) unsigned default NULL,
UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`),
KEY `nas_ind` (`nas_ip`)
) TYPE=MyISAM
(all accounting tables are the same as above)
show create table blah;
CREATE TABLE `blah` (
`acct_status_type` enum('Start','Stop') NOT NULL default 'Start',
`user_name` char(80) NOT NULL default '',
`nas_ip` char(15) NOT NULL default '',
`acct_session_time` mediumint(8) unsigned default NULL,
`acct_session_id` char(30) NOT NULL default '',
`timestamp` datetime NOT NULL default '-00-00 00:00:00',
`framed_ip` char(15) default NULL,
`input_bytes` int(10) unsigned default NULL,
`output_bytes` int(10) unsigned default NULL,
`calling_station_id` char(80) default NULL,
`called_station_id` char(80) default NULL,
`baud_rate` mediumint(8) unsigned default NULL,
`framed_protocol` char(20) default NULL,
`class` char(80) default NULL,
`visp_id` mediumint(8) unsigned default NULL,
`country_id` tinyint(3) unsigned default NULL,
`city_id` mediumint(8) unsigned default NULL,
`provider_id` mediumint(8) unsigned default NULL,
UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`),
KEY `nas_ind` (`nas_ip`)
) TYPE=MRG_MyISAM
UNION=(accounting_2002W29,accounting_2002W30,accounting_2002W31,accounting_2002W32,accounting_2002W33,accounting_2002W34,accounting_2002W35)
Thank
AIX 5L binaries?
Hello, Are there any plans for releasing AIX 5L pre-compiled binaries? I notice there are some MySQL RPMs available from other locations but they are not the latest version. If not, are there any pointers to AIX 5L compilation steps? Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert delayed and timestamps
On Tue, Apr 02, 2002 at 08:53:27AM -0600, Rick Emery wrote: > What happened when you tried this experiment? I just did the following: In one client connection, issued 'lock tables outgoing write'. Then in another client connection, tried to do an 'insert delayed' without specifying the timestamp. This query finished right away but was not inserted. About a minute later, I issued 'unlock tables' in the first client. The row from the 2nd client was inserted, and it it had the timestamp value of the original delayed insert (not the current time after the unlock). So this answers my question, the value from the delayed temporary table is used. Thanks, Viraj. > -Original Message- > From: Viraj Alankar [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 02, 2002 8:50 AM > To: [EMAIL PROTECTED] > Subject: insert delayed and timestamps > > > Hello, > > When using 'insert delayed' syntax and there is a timestamp field in the > table > but not specified in the insert query, which timestamp is actually used when > the record ends up in the destination table? In other words, will the > timestamp value be the actual timestamp that the record was inserted into to > the destination table (i.e. unknown at insert time) or the temporary > 'delayed' > table? It seems logical it would use the delayed table timestamp value, but > I > would like to confirm. > > sql > > Viraj. > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help optimizing log table deletes
Hello, We have an 'outgoing' table being used to store email header information defined as follows: CREATE TABLE outgoing ( rpath varchar(80) default NULL, auth varchar(80) NOT NULL default '', ip varchar(80) default NULL, hfrom varchar(80) default NULL, hto varchar(80) default NULL, subject varchar(80) default NULL, messageid varchar(80) default NULL, timestamp timestamp(14) NOT NULL, rcpts smallint(5) unsigned default '0', KEY at_ind (auth,timestamp,rcpts) ) TYPE=MyISAM; On average we have about 300k records in this table. Every day we archive data older than 7 days to another table, with a query similar to: $last_time = select now() - interval 7 day; insert into newtable select * from outoing where timestamp < '$last_time'; delete from outgoing where timestamp < '$last_time'; The problem is the delete query can take up to 1 minute to execute. This prevents any other inserts from taking place since the table is locked. I was wondering how I can design this better to avoid this problem. My thought was to go to InnoDB but this seems overkill for this slight issue. sql Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
insert delayed and timestamps
Hello, When using 'insert delayed' syntax and there is a timestamp field in the table but not specified in the insert query, which timestamp is actually used when the record ends up in the destination table? In other words, will the timestamp value be the actual timestamp that the record was inserted into to the destination table (i.e. unknown at insert time) or the temporary 'delayed' table? It seems logical it would use the delayed table timestamp value, but I would like to confirm. sql Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
delete vs delete quick
sql Hello, I'm confused as to what 'delete quick' does and when to use it. The manual states: If you specify the word QUICK then the table handler will not merge index leafs during delete, which may speed up certain kind of deletes. What does this actually mean? Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql help examining log table
Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql> desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select_full_join counter
Hello, The MySQL manual states that the Select_full_join status counter should be 0. Can someone explain to me what this actually counts and why it is bad to not have this 0? I have some queries that I'm not sure how else to optimize. Thanks, Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
database= in my.cnf causes mysqldump to fail
Hello, I'm not sure if this is a bug or not, but I've noticed that if I have a 'database=' in my ~/.my.cnf, calling mysqldump with any possible options always returns: mysqldump: option `--databases' doesn't allow an argument The only way I can get it working is to remove the 'database=' line from ~/.my.cnf. Is this normal? Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help on optimizing
Hello, I'm trying to optimize a table but not sure what more I can do. I have a table like the following: CREATE TABLE `outgoing` ( `rpath` varchar(80) default NULL, `auth` varchar(80) NOT NULL default '', `ip` varchar(80) default NULL, `hfrom` varchar(80) default NULL, `hto` varchar(80) default NULL, `subject` varchar(80) default NULL, `messageid` varchar(80) default NULL, `timestamp` timestamp(14) NOT NULL, `rcpts` smallint(5) unsigned default '0', KEY `at_ind` (`auth`,`timestamp`,`rcpts`) ) TYPE=MyISAM CREATE TABLE `exempt_users` ( `auth` varchar(80) NOT NULL default '', `comment` varchar(80) default NULL, `timestamp` timestamp(14) NOT NULL, PRIMARY KEY (`auth`) ) TYPE=MyISAM Now on these tables I am doing the following query: select count(outgoing.auth),sum(outgoing.rcpts) from outgoing left join exempt_users on outgoing.auth=exempt_users.auth where exempt_users.auth is null and outgoing.auth = 'USER' and timestamp >= (now() - interval 1 day) USER will be varying. I thought the indexes I have would be enough but I'm noticing from 'show status': | Select_full_join | 606616| And it is rising. This is the only query I am doing on the database. The MySQL docs say this should be 0. Is there something wrong my design or is there anything I can do to improve the speed? Thanks, Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
