Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call our custom script with some arguments like this:

  • when a user connects (our_script START user mac ip);
  • when a user disconnects (our_script STOP user mac ip in_bytes out_bytes more)

Sometimes we do not receive the disconnect message. So we need to adapt to this.

So far I came up with this structure for the accounting table:

CREATE TABLE `accounting` (
  `user` varchar(50) NOT NULL DEFAULT '',
  `mac` varchar(20) NOT NULL DEFAULT '',
  `ip` varchar(15) NOT NULL DEFAULT '',
  `ipv6` varchar(39) DEFAULT NULL,
  `start_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stop_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `in_bytes` bigint(32) unsigned DEFAULT '0',
  `out_bytes` bigint(32) unsigned DEFAULT '0',
  `more_columns` varchar(255) default NULL, 
  PRIMARY KEY (`user`,`mac`,`ip`,`start_datetime`,`stop_datetime`),
  KEY `prim_ipv6` (`user`,`mac`,`ipv6`,`start_datetime`,`stop_datetime`),
  KEY `user` (`user`) USING HASH,
  KEY `mac` (`mac`) USING HASH,
  KEY `ip` (`ip`) USING HASH,
  KEY `ipv6` (`ipv6`) USING HASH,
  KEY `start_datetime` (`start_datetime`),
  KEY `stop_datetime` (`stop_datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The query used for writing are (these needs to be real fast):

  • when a user connects a simple insert
  • when a user disconnects update accounting set stop_datetime=now(), in_bytes=$in_bytes, out_bytes=$out_bytes, more_columns=$more where user="$user" and ip="$ip" and stop_datetime="0000-00-00 00:00:00" order by start_datetime DESC limit 1;, this is a workaround in the case we have more then one row for start, we just update the last start.

A better workaround, is to update the stop_datetime for rows without it, at every start, and use another column for storing that this is not a normal stop, I think I will go with the second workaround.

We have 2 ways to select from this table (this could be slower max 1 - 2 sec):

  • select the last 50 connection for a user: select ... where user="$user" order by start_datetime DESC limit 50;, for this the user HASH key is handy, beacause AFAIK HASH is better than BTREE for equality.
  • select witch user was connected with ip=$ip on date=$date select ... where ip="$ip" and $date between start_datetime and stop_datetime;

This table will hold between 0.5 to 1 bilion rows, that's why I thought to partition it. The best option is to partition it by month, but I have 2 relevant dates, and at the begining/end of the month start will be in one partition, stop will be in another.

The questions:

  • Witch is the best way to do partitions, by start_datetime, by stop_datetime, by start_datetime with subpartitions for stop_datetime ?

  • Do I realy need to partitionate ?

  • Do you have any other suggestion on how to improve this ?

Thank you

share|improve this question
    
Use InnoDB engine - it provides row-level locks and is faster for insert/update intensive applications. You don't need index on user, since it's the left-most field in the primary key and primary key can be used to resolve any query filtered by user. I guess prim_ipv6 has to be UNIQUE, as the primary key is unique and this is the v6 version of pk –  Darhazer Mar 10 '12 at 15:41
    
@Darhazer Unfortunately I can't use InnoDB, but according to dev.mysql: "For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported", I was assigned to another project, now i'm back to this, I am redesigning and I will update the question in the following days. –  Radu Maris Mar 19 '12 at 9:09
add comment

migrated from stackoverflow.com Mar 19 '12 at 15:27

This question came from our site for professional and enthusiast programmers.

2 Answers

Since stop_datetime will not be known at insert time the partition must be done by start_datetime.

I think partitioning by day (date_format(start_datetime, '%Y-%m-%d')) would be adequate since 1 billion rows divided by (3 years * 365 days) ≃ 900,000 rows per partition

Updating indexes is expensive and it happens at inserts, updates and deletes. Keep the indexes to a minimum. If the main queries will be those selects then I would keep the primary key like the following and trash all the other keys:

PRIMARY KEY (`user`,`start_datetime`,`stop_datetime`,`mac`,`ip`)
share|improve this answer
    
I'm redesigning, I will drop the update, use a single "insert select from another_table (max 40K rows)", partition by date is not possible because mysql has a limit of 1024 partition per table while 3*365=1095. I also had a problem with primary key, stop_datetime did not fit in, it seems that the key had a limit, but documentation says 1000 bytes, my test had 65 bytes ... didn't find out why. I saw this in the experiment logs: duplicate entry "some_user-00:11:22:33:44:55-100.200.10.20-2012-03-05 08:12:58-2" for key primary –  Radu Maris Mar 19 '12 at 14:25
add comment

PRIMARY KEY (user,mac,ip,start_datetime,stop_datetime),

KEY prim_ipv6 (user,mac,ipv6,start_datetime,stop_datetime),

The second one is totally redundant, DROP it. (A PRIMARY KEY is a KEY!)

MyISAM does not have HASH indexing; you were probably given BTree, which is nearly as good.

where user="$user" and ip="$ip" and stop_datetime="0000-00-00 00:00:00" order by start_datetime DESC limit 1 -- That begs for this index:

INDEX(user, ip, stop_datetime, start_datetime)

where user="$user" order by start_datetime DESC limit 50 -- begs for INDEX(user, start_datetime)

This is very hard to optimize: where ip="$ip" and $date between start_datetime and stop_datetime

Suggest adding: INDEX(ip, start_datetime), INDEX(stop_datetime)

PARTITIONing will not help unless you can take advantage of "partition pruning".

Since most of these queries include ip="$ip", and then get into trouble with timestamps, it might be best to partition on ip. I would not do more than 64 partitions; there are inefficiencies otherwise.

Do not (usually) put the "partition key" first in the PRIMARY KEY.

Because of your "inside-out" use of start/end times, I don't think the timestamps would be useful for partitioning.

ip varchar(15) NOT NULL DEFAULT '', ipv6 varchar(39) DEFAULT NULL,

How are you dealing with that? I think the standard says that this is how to encode ipv4 in IPv6 format:

::ffff:123.123.123.123

Perhaps you should switch to that, and abandon the ip column.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.