recommendations, myisam-innodb
I have a program that listens to ippackets and reads out and stores an ip address, two mac addresses and one timestamp. I also have a packetID.
I am using Java 6 and the latest JDBCdriver with the latest stable Mysql.
My first approach was to just make one table with all the information. The results were very fast writes, but of course, extremly much redundant information. So I drew an ER diagram and made a much better relational model with 4 entities, holding each its only field as a key. I made a last entity holding the packetid as a primary key and the rest of the info came from the others as foreign key. Good. no redundant information.
Now I had to have one preparedstatement (i use these bequase i reuse the same questions alot, and i dont need to close the stmts with this) for each of entities, rendering me 5 calls to the database with jdbc instead of just 1. This slows it down, and speed in this program is really crucial.
Now for the question.
Should I change to the InnoDB engine (i'm using MyIsam) and make all foreign keys as on update cascade, thus I only have to do the single insert in the big table, and all the others will automaticly be filled, or will the InnoDB engine slow it down so it wont make up for it? Space - is also crucial, but speed is no 1.
Other factors I should think about?
I hope anyone has an answer.
Thanks
[1410 byte] By [
_Yonder_a] at [2007-11-26 15:45:15]
# 1
I think you should go back to the one table model.
You don't have redundant data (it doesn't appear). If you were storing extra data like a hostname as well as IP well then the hostname is redundant (sort of).
I mean you sort of have two keys. One is the packet ID and the second is the combination of the rest of the fields.
I'd vote for going back to the one table model.
******************************
If you proceed with multiple tables you are going to have this performance issue. No matter how it happens you need multiple inserts. That's expensive. Particularly key inserts.
To answer your question as it stands I don't se that changing to InnoDB would help. It very well might make things worse.
The only suggestion I can make if you do stick with the multiple table model is to start dropping keys/indexes. Indexes/keys are relatively expensive for insert operations
If your adding of information happens in batches then you can drop the keys, do your inserts and re-add the keys.
************************
Actually dropping the keys maybe something you want to consider anyway. If you are doing alot of inserts, particularly in sort of a batch mode it will be better to drop the keys and recreate after the batch for two reasons.
1) It will make the inserts faster
2) The rebuilt keys will be optimized anyway. So will make for faster selects.
# 2
Hmmm, ok, well, I do have alot of redundancy, I get much of the same information at different timestamps. I analysed the data and saw this, if i wouldnt have, i wouldnt have recreated the database, and it really did get the size down, and alot.
Are you saying that these are equal in speed? They will produce the same results in the database.
1. innodb
table: ip mac mac2 time packetno [on update cascade]
insert into table (.....)
2. myisam
table1-4: ip, mac, mac2, time
table: ip mac mac2 time packetno
insert into table1 (.)
insert into table2 (.)
insert into table3 (.)
insert into table4 (.)
insert into table (.....)
# 3
Since all of that is happening on the database side, without any additional network traffic, I'd bet that the difference in performance would be immaterial to you.
I personally would always favor InnoDB as the default implementation. It enforces referential integrity for you. What good is a database that doesn't?
%
# 4
Allright. Since "InnoDB requires about three times as much disk space compared to MyISAM" ( http://dev.mysql.com/tech-resources/articles/storage-engine/part_2.html) I think I will go with my 2nd option.Thanks to both of you
# 5
> Allright. Since "InnoDB requires about three times as
> much disk space compared to MyISAM"
> (http://dev.mysql.com/tech-resources/articles/storage-
> engine/part_2.html) I think I will go with my 2nd
> option.
Disk is cheap; referential integrity is priceless. Foolish choice, IMO. I'd still recommend that you go with InnoDB. Or, better yet, a database like PostgreSQL that has always enforced referential integrity and included triggers and stored procs to boot.
%
# 6
I have made the changes now, but I'm not saving any space. Is it possibly so, that the whole foreign-key business doesnt work in MyIsam anyway?
Lets say I have these 5 fields of data, ip, mac1, mac2, time, packetid.
ip, mac1, mac2 repeats themselves at least 5 times, time repeats itself more (I get many packets per second), packetid is unique.
In my mind, it should work like this:
when using the old table, with all these elements just as they are, I'm getting loads of redundant information.
with the new table, making only packetid as primarykey and the rest as foreign keys from their own tables, all values unique, I though that the database would get this fielddata from the other tables, just making space for packetid, but this table still takes up as much space as the first table i made. So now Im not only getting the same amount of redundand data, Im also getting 4 new tables filled with data that i have no direct use for.
