Information: Forum is in read-only mode
For details and other support options see https://www.adiscon.com/news/support-forum-set-to-read-only-mode/

Partitioning SystemEvents table

This is the place for developers to discuss bugs, new features and everything else about code changes.

Moderator: alorbach

Google Ads


Partitioning SystemEvents table

Postby dbm » Thu Sep 15, 2011 9:59 am

Hi:

First of all, thanks for rsyslog, i really love it, it's a great work, sincerely. Now, my question:

I'm using rsyslog to log the remote mikrotiks logs in a mysql database. Now I am just in the test phase but I will have a lot of log lines to store ( big concurrence and storing for a long period ). To make it more easy to administer I am planning to recreate the table SystemEvents to make it partitioned by some type of function in the DeviceReportedTime field, something style "partition by the YYYYMM ( yearmonth ) number or label", with that, if needed, I will be able to query and extract log lines by the date of message creation in the remote device, if not, any query in the future can be a pain for the database engine ( I think about making the full scan of the table with the possible blocks at same time while rsyslog inserts new rows - I don't know exactly how mysql handles this- ). Then, my only fear is thinking in a future rsyslog sql/mysql code update that can break my solution. Can I apply this changes to the table "calmly" aka "we don't foresee a database structure change in the short-term"?

Could you evaluate a possible feature like this? the hability to offer the admin the possibility to create the SystemEvents table partitioned by DeviceReportedTime/Receivedate/Fromhost... or any other field susceptible to segregate data?

TIA

D.
dbm
New
 
Posts: 3
Joined: Thu Sep 15, 2011 9:27 am

Urgent Question?

  • Pulling out your Hair?
  • Wasting Time and Money?
  • Deadline Approaching?

Re: Partitioning SystemEvents table

Postby dbm » Thu Sep 15, 2011 1:06 pm

Unable to partition this table, i will create an historic one partitioned, and create some scripts for moving data from the "on-line" table to the partitioned on, and delete the moved records
dbm
New
 
Posts: 3
Joined: Thu Sep 15, 2011 9:27 am

Re: Partitioning SystemEvents table

Postby rgerhards » Thu Sep 15, 2011 1:59 pm

While the reply seems to be too late, I can say that there is no intend to change the default table structure.

Just our of interest: what prevents the partitioning?

Rainer
rgerhards
Site Admin
 
Posts: 3807
Joined: Thu Feb 13, 2003 11:57 am

Re: Partitioning SystemEvents table

Postby dbm » Thu Sep 15, 2011 4:01 pm

Hi:

The field ID is primary_key and then i should paritioning by primary_key and date ( the pk must be part of the partitioning clause ). I decided to create a historic table partitioned by message date and dropping the primary key, the autoincrement and index associated to the ID field. With that, i'm sure too that possible news changes in the original table will not break nothing "serious"
dbm
New
 
Posts: 3
Joined: Thu Sep 15, 2011 9:27 am

Re: Partitioning SystemEvents table

Postby luismg » Mon Dec 19, 2016 11:11 am

dbm wrote:Hi:

The field ID is primary_key and then i should paritioning by primary_key and date ( the pk must be part of the partitioning clause ). I decided to create a historic table partitioned by message date and dropping the primary key, the autoincrement and index associated to the ID field. With that, i'm sure too that possible news changes in the original table will not break nothing "serious"


I've been able to partition it using this command to recreate the primary key pair
ALTER TABLE SystemEvents, DROP PRIMARY KEY, ADD PRIMARY KEY (ID,DeviceReportedTime);
now I can partition by DeviceReportedTime

cheers
luismg
New
 
Posts: 9
Joined: Fri Nov 25, 2016 9:59 am

Re: Partitioning SystemEvents table

Postby luismg » Mon Dec 19, 2016 8:00 pm

Or you can just drop the table and create it, this is a logical partition every hour and the device reportedtime, id and fromhost as Indexes

CREATE TABLE `SystemEvents` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CustomerID` bigint(20) DEFAULT NULL,
`ReceivedAt` datetime DEFAULT NULL,
`DeviceReportedTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Facility` smallint(6) DEFAULT NULL,
`Priority` smallint(6) DEFAULT NULL,
`FromHost` varchar(60) DEFAULT NULL,
`Message` text,
`NTSeverity` int(11) DEFAULT NULL,
`Importance` int(11) DEFAULT NULL,
`EventSource` varchar(60) DEFAULT NULL,
`EventUser` varchar(60) DEFAULT NULL,
`EventCategory` int(11) DEFAULT NULL,
`EventID` int(11) DEFAULT NULL,
`EventBinaryData` text,
`MaxAvailable` int(11) DEFAULT NULL,
`CurrUsage` int(11) DEFAULT NULL,
`MinUsage` int(11) DEFAULT NULL,
`MaxUsage` int(11) DEFAULT NULL,
`InfoUnitID` int(11) DEFAULT NULL,
`SysLogTag` varchar(60) DEFAULT NULL,
`EventLogType` varchar(60) DEFAULT NULL,
`GenericFileName` varchar(60) DEFAULT NULL,
`SystemID` int(11) DEFAULT NULL,
`processid` varchar(60) NOT NULL DEFAULT '',
`checksum` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`,`DeviceReportedTime`),
KEY `FromHost` (`FromHost`),
KEY `DeviceReportedTime` (`DeviceReportedTime`)
) ENGINE=InnoDB AUTO_INCREMENT=52353 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (HOUR(DeviceReportedTime))
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
PARTITION p15 VALUES IN (15) ENGINE = InnoDB,
PARTITION p16 VALUES IN (16) ENGINE = InnoDB,
PARTITION p17 VALUES IN (17) ENGINE = InnoDB,
PARTITION p18 VALUES IN (18) ENGINE = InnoDB,
PARTITION p19 VALUES IN (19) ENGINE = InnoDB,
PARTITION p20 VALUES IN (20) ENGINE = InnoDB,
PARTITION p21 VALUES IN (21) ENGINE = InnoDB,
PARTITION p22 VALUES IN (22) ENGINE = InnoDB,
PARTITION p23 VALUES IN (23) ENGINE = InnoDB) */ |
luismg
New
 
Posts: 9
Joined: Fri Nov 25, 2016 9:59 am

Google Ads



Return to Developer's Corner

Who is online

Users browsing this forum: No registered users and 1 guest

cron