MySQLTalk.org Forum Index MySQLTalk.org
MYSQL discussions groups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Archive engine for log tables?

 
Post new topic   Reply to topic    MySQLTalk.org Forum Index -> MySQL Internals
View previous topic :: View next topic  
Author Message
Giuseppe Maxia
Guest





PostPosted: Mon May 07, 2007 4:45 pm    Post subject: Archive engine for log tables? Reply with quote



Hi,
Can anyone tell me why I can't change the log tables to use the ARCHIVE
storage engine?

It makes sense to use it instead of CSV. Log tables must only support
INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a
sensible choice, since logs usually grow large.

BTW, you can assign the Archive engine to a log table, using a workaround.

use mysql;
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE gl1 like general_log;
ALTER TABLE gl1 ENGINE=ARCHIVE;
RENAME TABLE general_log to gl0, gl1 to general_log;

Thanks for any insight in this matter

Best regards

Giuseppe

--
Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com
Back to top
Geoffroy Cogniaux
Guest





PostPosted: Mon May 07, 2007 7:33 pm    Post subject: RE: Archive engine for log tables? Reply with quote



Hi,

Guiseppe Maxia wrote :
Quote:
Can anyone tell me why I can't change the log tables to use the ARCHIVE
storage engine?

The mysql_alter_table functions disables alter of log tables to unsupported
engine. This means that the engine of a log table must have the
HTON_SUPPORT_LOG_TABLE flag.
Only Tina and MyIsam engine have it.
But I don't know if Archive could.

Perhaps Archive should be used only as "backup" table, not as "online".

Regards,
Geoffroy.

-----Message d'origine-----
De : Giuseppe Maxia [mailto:giuseppe (AT) mysql (DOT) com]
Envoyé : lundi 7 mai 2007 13:45
À : internals (AT) lists (DOT) mysql.com
Objet : Archive engine for log tables?

Hi,
Can anyone tell me why I can't change the log tables to use the ARCHIVE
storage engine?

It makes sense to use it instead of CSV. Log tables must only support
INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a
sensible choice, since logs usually grow large.

BTW, you can assign the Archive engine to a log table, using a workaround.

use mysql;
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE gl1 like general_log;
ALTER TABLE gl1 ENGINE=ARCHIVE;
RENAME TABLE general_log to gl0, gl1 to general_log;

Thanks for any insight in this matter

Best regards

Giuseppe

--
Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:
http://lists.mysql.com/internals?unsub=geoffroy.cogniaux (AT) outreweb (DOT) fr
Back to top
Brian Aker
Guest





PostPosted: Mon May 07, 2007 8:41 pm    Post subject: Re: Archive engine for log tables? Reply with quote



Hi!

We are working on a new patch for Log tables right now that makes
them available for all engines. Right now log tables require a
special flag, and Archive does not have that flag.

Cheers,
-Brian

On May 7, 2007, at 4:45 AM, Giuseppe Maxia wrote:

Quote:
Hi,
Can anyone tell me why I can't change the log tables to use the
ARCHIVE
storage engine?

It makes sense to use it instead of CSV. Log tables must only support
INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a
sensible choice, since logs usually grow large.

BTW, you can assign the Archive engine to a log table, using a
workaround.

use mysql;
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE gl1 like general_log;
ALTER TABLE gl1 ENGINE=ARCHIVE;
RENAME TABLE general_log to gl0, gl1 to general_log;

Thanks for any insight in this matter

Best regards

Giuseppe

--
Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?
unsub=brian (AT) tangent (DOT) org
Back to top
Sergei Golubchik
Guest





PostPosted: Mon May 07, 2007 10:42 pm    Post subject: Re: Archive engine for log tables? Reply with quote

Hi!

On May 07, Geoffroy Cogniaux wrote:
Quote:
Guiseppe Maxia wrote :
Can anyone tell me why I can't change the log tables to use the
ARCHIVE storage engine?

The mysql_alter_table functions disables alter of log tables to
unsupported engine. This means that the engine of a log table must
have the HTON_SUPPORT_LOG_TABLE flag.
Only Tina and MyIsam engine have it.
But I don't know if Archive could.

If only MyISAM and CSV have it, it may be because of concurrent inserts.
They both support it, ARCHIVE does not, and current logging code relies
on it.

Regards / Mit vielen Grüssen,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg (AT) mysql (DOT) com>
/ /|_/ / // /\ \/ /_/ / /__ Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373 München
<___/ Geschäftsführer: Kaj Arnö - HRB München 162140
Back to top
Brian Aker
Guest





PostPosted: Mon May 07, 2007 10:47 pm    Post subject: Re: Archive engine for log tables? Reply with quote

Hi!

On May 7, 2007, at 10:42 AM, Sergei Golubchik wrote:

Quote:
If only MyISAM and CSV have it, it may be because of concurrent
inserts.
They both support it, ARCHIVE does not, and current logging code
relies
on it.

Just to point this out, Archive, like Innodb and others, has
concurrent insert. None of these engines rely on MySQL locking
behavior in the upper end to support this.

The new log table patch removes the need for engines to declare this
though, so its not an issue in the future.

Cheers,
-Brian

Quote:

Regards / Mit vielen Grüssen,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg (AT) mysql (DOT) com
/ /|_/ / // /\ \/ /_/ / /__ Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373
München
___/ Geschäftsführer: Kaj Arnö - HRB
München 162140

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?
unsub=brian (AT) tangent (DOT) org
Back to top
Giuseppe Maxia
Guest





PostPosted: Tue May 08, 2007 10:46 pm    Post subject: Re: Archive engine for log tables? Reply with quote

Hi Brian,
Thanks for the news.
I wrote an article about hacking table logs, in order to use the Archive or
Federated storage engines for logging. Through federated tables, you can
then use any other storage, including InnoDB (which crashes the server when
used directly - see Bug#28274).

http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_logs.html

Cheers

Giuseppe

Brian Aker wrote:
Quote:
Hi!

We are working on a new patch for Log tables right now that makes them
available for all engines. Right now log tables require a special flag,
and Archive does not have that flag.

Cheers,
-Brian

On May 7, 2007, at 4:45 AM, Giuseppe Maxia wrote:

Hi,
Can anyone tell me why I can't change the log tables to use the ARCHIVE
storage engine?

It makes sense to use it instead of CSV. Log tables must only support
INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a
sensible choice, since logs usually grow large.

BTW, you can assign the Archive engine to a log table, using a
workaround.

use mysql;
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE gl1 like general_log;
ALTER TABLE gl1 ENGINE=ARCHIVE;
RENAME TABLE general_log to gl0, gl1 to general_log;

Thanks for any insight in this matter

Best regards

Giuseppe

--Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com

--MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:
http://lists.mysql.com/internals?unsub=brian (AT) tangent (DOT) org



--
Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com
Back to top
Antony T Curtis
Guest





PostPosted: Wed May 09, 2007 5:37 am    Post subject: Re: Archive engine for log tables? Reply with quote

Hi Giuseppe,

I think the patch that Brian is referring to is WL#3771 which would
permit logging directly to any local storage engine and would also
permit triggers.

Regards,
Antony,

On Tue, 2007-05-08 at 19:46 +0200, Giuseppe Maxia wrote:
Quote:
Hi Brian,
Thanks for the news.
I wrote an article about hacking table logs, in order to use the Archive or
Federated storage engines for logging. Through federated tables, you can
then use any other storage, including InnoDB (which crashes the server when
used directly - see Bug#28274).

http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_logs.html

Cheers

Giuseppe

Brian Aker wrote:
Hi!

We are working on a new patch for Log tables right now that makes them
available for all engines. Right now log tables require a special flag,
and Archive does not have that flag.

Cheers,
-Brian

On May 7, 2007, at 4:45 AM, Giuseppe Maxia wrote:

Hi,
Can anyone tell me why I can't change the log tables to use the ARCHIVE
storage engine?

It makes sense to use it instead of CSV. Log tables must only support
INSERT statements, not UPDATE or DELETE ones. Thus, Archive seems a
sensible choice, since logs usually grow large.

BTW, you can assign the Archive engine to a log table, using a
workaround.

use mysql;
DROP TABLE IF EXISTS gl0, gl1,
CREATE TABLE gl1 like general_log;
ALTER TABLE gl1 ENGINE=ARCHIVE;
RENAME TABLE general_log to gl0, gl1 to general_log;

Thanks for any insight in this matter

Best regards

Giuseppe

--Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com

--MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:
http://lists.mysql.com/internals?unsub=brian (AT) tangent (DOT) org



--
Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com

--

Antony T Curtis, Senior Software Developer
MySQL Inc, www.mysql.com
SIP: 4468 (AT) sip (DOT) mysql.com
Back to top
Calvin Sun
Guest





PostPosted: Wed May 09, 2007 10:11 am    Post subject: Re: Archive engine for log tables? Reply with quote

Here is the correct link to the article Giuseppe mentioned:

http://www.oreillynet.com/databases/blog/2007/05/hacking_mysql_table_logs.html


Thanks,
Calvin Sun
Back to top
Display posts from previous:   
Post new topic   Reply to topic    MySQLTalk.org Forum Index -> MySQL Internals All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
Perl Forums  |  Education Forums  |  Learning Forums  |  File Systems Forums  |  Lustre / XFS Forums
SEO toolkit © 2004-2006 webmedic