Month: February 2014

My talk @ Percona Live 2014

Will be giving a lightning talk at Percona Live 2014 – santa clara 🙂

In the world of replication, The Binlog is every ones favorite. 

But what about the Relay log, Don't you feel it can do more ?

Yes, It can. How about, if it can serve the purpose of the GTID 

feature without GTID.

https://www.percona.com/live/mysql-conference-2014/sessions/relay-log-unsung-hero

#MySQL  ( alternative to GTID )  #gtid

by

having a log table ?

I came across a scenario where we were concerned about the size of a table, a 134G table, its a pretty huge table and growing fast.
The nature of the table – its a table which contains logs , sort of a audit table.

Some fundamental questions around this table’s use:

1) would it have a lot of reads ?  — Answer –  No
2) is it always insert only? — Answer – Yes
3) current strategy to restrict size ? — Answer – delete data from table.
4) does one need backup of the table, i.e. is it very important ?  — Answer – No

Well based on the above, it would seem that logging is the theme.

Schema of the table:

CREATE TABLE `SystemLogs` (
  `column1` varchar(255) DEFAULT NULL,
  `column2` varchar(255) DEFAULT NULL,
  `column3` varchar(255) DEFAULT NULL,
  `column4` varchar(255) DEFAULT NULL,
  `column5` text,
  `column6` text,
  `timestamp` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `column7` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`column1`,`column2`,`column3`)

) ENGINE=InnoDB

Some strategies to mitigate the concern:

Strategy-1
———-

Based on the above schema, most columns are TEXT / VARCHAR, which is a good case for

ROW COMPRESSION. This could be employed which would reduce the size of the table on disk.

The results of compression – yet to do some tests.
 
Strategy-2
———–

Since the table is use to hold logs, why not employ a logfile to hold such logs.
 
In the MySQL world, The Binlog could be used to do just that, its an audit log for all changes happening in the server.

Well, one would ask, if binlog logging is enabled, which was the case, as there is a master/slave arrangement,

then is that what we wanted to achieve ?

Yes, but partially,  the size of table on disk is the concern, the size does not decrease just because the binlog is enabled.

Here comes the change which will resolve the concern.

‘alter table SystemLogs engine=BLACKHOLE’.

So, when you insert into the table, now the entry will be made to the binlog keeping the size of the table on disk as 0 bytes.
 
If one wishes, one can reconstruct the table on another box (having its engine as innodb) by applying the binlog.
 
or

you can simply have a slave where the table has innodb engine and the table gets reconstructed there.