Re: innodb or myisam for logging

[bharath venk]

Hi Rick ,
Thanks for the response,

thanks for info about disk subsystem ,secondary indexes and mysiam and InnoDB configurations it was very useful

there are few thinks I would want to clear and clarify

9M/day doesn't mean that traffic is equally distributed through out the day .. during peak times it may even go up to 1000/second

lack of reads on master was mentioned to clarify about locking issues in mysiam table locking happens for read when write happens and vice versa if I am not wrong but In Innodb row level locking happens which may be unnecessary over head in our case as only inserts happens


our Raw table (the fact table ) doesn't have a AUTO_INCREMENT field
eg of Raw table looks like
+-------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| viewer_id | varchar(32) | YES | MUL | NULL | |
| viewer_session_id | varchar(32) | YES | | NULL | |
| date_time | datetime | NO | MUL | NULL | |
| ad_id | int(10) unsigned | NO | MUL | NULL | |
| creative_id | int(10) unsigned | NO | | NULL | |
| zone_id | int(10) unsigned | NO | MUL | NULL | |
| channel | varchar(255) | YES | | NULL | |
| channel_ids | varchar(64) | YES | | NULL | |
| language | varchar(32) | YES | | NULL | |
| ip_address | varchar(16) | YES | | NULL | |
| host_name | varchar(255) | YES | | NULL | |
| country | char(2) | YES | | NULL | |
| https | tinyint(1) | YES | | NULL | |
| domain | varchar(255) | YES | | NULL | |
| page | varchar(255) | YES | | NULL | |
| query | varchar(255) | YES | | NULL | |
| referer | varchar(255) | YES | | NULL | |
| search_term | varchar(255) | YES | | NULL | |
| user_agent | varchar(255) | YES | | NULL | |
| os | varchar(32) | YES | | NULL | |
| browser | varchar(32) | YES | | NULL | |
| max_https | tinyint(1) | YES | | NULL | |
| geo_region | varchar(50) | YES | | NULL | |
| geo_city | varchar(50) | YES | | NULL | |
| geo_postal_code | varchar(10) | YES | | NULL | |
| geo_latitude | decimal(8,4) | YES | | NULL | |
| geo_longitude | decimal(8,4) | YES | | NULL | |
| geo_dma_code | varchar(50) | YES | | NULL | |
| geo_area_code | varchar(50) | YES | | NULL | |
| geo_organisation | varchar(50) | YES | | NULL | |
| geo_netspeed | varchar(20) | YES | | NULL | |
| geo_continent | varchar(13) | YES | | NULL | |
+-------------------+------------------+------+-----+---------+-------+

we summarise using date_time to pick from where left off . but the motto of this discussion was not for how summarising should happen .. sorry It was my mistake for not being clear enough . we have designed the architecture in such a way we have split central engine , summarising engine and delivery engine . delivery engine is where ad is delivered and logged . log information for that period is transfered from delivery engine slaves to summarising engine where the summarising is done and reflected in central engine.this happens every hour . so delivery engine master don't have to care about how summarising happens . so motto of this discussion is to which engine is better for delivery engine where logging happens for impression and clicks .sorry if I was not clear and had gone off the topic . the reason why I mentioned about summarising is to specify that no read happens at all at delivery engine master.

It may be happen that both innodb and mysiam may be able to handle but which is better for our requirement keeping in mind the kind of architecture and resources we are using . so that it will be able to scale up when our traffic increases ..

thanks in advance,
bharath