Re: Row size computation
Rick James Wrote:
-------------------------------------------------------
> > should not suffer from variability pointed in
> your message.
> COUNT(*) will be exact.
>
> > After another few days of heavy live use average
> length, counted as described above, is now at 118
> bytes from 73 after optimization. (~61% increase)
>
> It is not the size of the row that is changing. It
> is the number of rows per block that is changing.
Yes, that was my understanding (that blocks would be partially empty, but the real single row size stays the same). Thanks for the example.
> [ ... ]
>
> 73/118 = 62% -- this means that, on average, each
> block is only 62% full. (I would have predicted
> about 69%; 62 is close enough to be consistent
> with my theory.)
>
> Number_of_blocks = Data_length / 16384;
> Rows_per_block = COUNT / Number_of_blocks
>
> To further test my theory, let it run a few more
> days. Does it stay about 62%? (It might actually
> go either up and down.)
>
Sadly it is going still up, now average row length is at ~144 so that would mean that blocks are ~50% full on average. I think that it is likely that PRIMARY KEY order may be causing that, but maybe there is different reason.
To reiterate table schema.
CREATE TABLE `dev_apps` (
`dev_id` bigint(19) unsigned NOT NULL,
`app_id` int(10) unsigned NOT NULL,
`app_version` int(10) unsigned NOT NULL,
`launch_count` mediumint(8) unsigned NOT NULL default '0',
`crash_count` mediumint(8) unsigned NOT NULL default '0',
`ip` int(10) unsigned NOT NULL default '0',
`timezone` tinyint(4) default NULL,
`lang` char(3) character set ascii collate ascii_bin NOT NULL,
`country` char(3) character set ascii collate ascii_bin NOT NULL,
`os_id` int(10) unsigned default NULL,
`dev_type_id` int(10) unsigned default NULL,
`created_at` int(11) default NULL,
`updated_at` int(11) default NULL,
PRIMARY KEY (`dev_id`,`app_id`,`app_version`),
KEY `app_INDEX` (`app_id`,`app_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
dev_id denotes device
app_id denotes application
app_version denotes application version
With new release of the application some users will upgrade, i.e.
Users of app(1) version(1) are present:
1,1,1
2,1,1
3,1,1
4,1,1
5,1,1
6,1,1
...
Now user 2 updates to version(2):
1,1,1
2,1,1
2,1,2 <- now, will this cause block split and if it will what would be the contents of two created blocks?
4,1,1
5,1,1
6,1,1
...
Similar example can be done by with existing user adding another application.
Maybe better idea would be to organize PRIMARY KEY as (`app_id`, `app_version`, `dev_id`) (and do not use any additional index in table). It would probably better 'describe' how new rows are inserted into the table.
I think it would be extremely helpful for me to better understand the process of block splits. :)