MySQL Cluster analysis for foodmart

Mon, 2010-03-15 20:19 — oli

This is an automated analysis of the DBI:mysql:database=foodmart;host=localhost database for migration into MySQL Cluster. No warranty is made to the accuracy of the information.

This information should be valid for MySQL 4.1 and 5.0. Since 5.1 is not a final release yet, the numbers should be used as a guide only.

5.1-dd is for tables stored on disk. The ndb_size.pl estimates are experimental and should not be trusted. Notably we don’t take into account indexed columns being in DataMemory versus non-indexed on disk.

Parameter Settings

NOTE the configuration parameters below do not take into account system tables and other requirements.

Parameter	4.1	5.0	5.1
DataMemory (kb)	38624	38624	37088
IndexMemory (kb)	53816	44840	44840
MaxNoOfTables	21	21	21
MaxNoOfAttributes	225	225	225
MaxNoOfOrderedIndexes	64	64	64
MaxNoOfUniqueHashIndexes	64	64	64
MaxNoOfTriggers	256	256	256

Memory usage because of parameters

Usage is in kilobytes. Actual usage will vary as you should set the parameters larger than those listed in the table above.

Parameter	4.1	5.0	5.1
Attributes	44	44	44
Tables	420	420	420
OrderedIndexes	640	640	640
UniqueHashIndexes	960	960	960

Table List

account

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
account_parent	smallint		6	MUL	4	4	4
account_rollup	varchar	YES	30		32	32	4
account_description	varchar	YES	30		32	32	16
account_type	varchar	YES	30		32	32	8
custom_members	varchar	YES	255		256	256	120
account_id	smallint		6	UNI	4	4	4
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_account_parent	BTREE	account_parent	29	25	25	10	10	10
i_account_id	BTREE	account_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	4
Row Overhead	16	16	16
Varsized Overhead	0	0	32
Column DataMemory/Row	360	360	156
Index DataMemory/Row	30	30	30
Total DataMemory/Row	406	406	234
Rows per 32kb page	80	80	139
Current number of rows	11	11	11
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	91	75	75
Rows per 8kb page	90	109	109
Current number of rows	11	11	11
Total IndexMemory (kb)	8	8	8

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
category_id	varchar	YES	30	PRI	32	32	12
category_parent	varchar	YES	30	MUL	32	32	0
category_description	varchar	YES	30		32	32	24
category_rollup	varchar	YES	255		256	256	0
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_category_parent	BTREE	category_parent	57	25	25	10	10	10
i_category_id	BTREE	category_id	57	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

	4.1	5.0	5.1
IndexMemory/Row	147	75	75
Rows per 8kb page	55	109	109
Current number of rows	4	4	4
Total IndexMemory (kb)	8	8	8

currency

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
currency	varchar	YES	30		32	32	12
currency_id	smallint		6	MUL	4	4	4
date	date				4	4	4
conversion_ratio	decimal(10,2)				0	0	0
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_currency	BTREE	currency_id,date	33	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	1
Row Overhead	16	16	16
Varsized Overhead	0	0	16
Column DataMemory/Row	40	40	20
Index DataMemory/Row	20	20	20
Total DataMemory/Row	76	76	72
Rows per 32kb page	429	429	453
Current number of rows	72	72	72
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	66	50	50
Rows per 8kb page	124	163	163
Current number of rows	72	72	72
Total IndexMemory (kb)	8	8	8

customer

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
customer_region_id	int		11	MUL	4	4	4
marital_status	varchar	YES	30		32	32	4
address3	varchar	YES	30		32	32	0
num_cars_owned	int		11		4	4	4
date_accnt_opened	date				4	4	4
total_children	smallint		6		4	4	4
fname	varchar	YES	30	MUL	32	32	8
ordinal	int		11		4	4	4
city	varchar	YES	30		32	32	12
postal_code	varchar	YES	30	MUL	32	32	8
customer_id	int		11	PRI	4	4	4
num_children_at_home	smallint		6	MUL	4	4	4
gender	varchar	YES	30		32	32	4
mi	varchar	YES	30		32	32	4
houseowner	varchar	YES	30		32	32	4
country	varchar	YES	30		32	32	8
education	varchar	YES	30		32	32	20
address4	varchar	YES	30		32	32	0
birthdate	date				4	4	4
member_card	varchar	YES	30		32	32	8
occupation	varchar	YES	30		32	32	12
account_num	bigint		20	MUL	8	8	8
address2	varchar	YES	30		32	32	8
yearly_income	varchar	YES	30		32	32	16
lname	varchar	YES	30	MUL	32	32	8
phone1	varchar	YES	30		32	32	16
address1	varchar	YES	30		32	32	20
state_province	varchar	YES	30		32	32	4
phone2	varchar	YES	30		32	32	16
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_customer_id	BTREE	customer_id	29	25	25	10	10	10
i_customer_children_at_home	BTREE	num_children_at_home	29	25	25	10	10	10
i_customer_postal_code	BTREE	postal_code	57	25	25	10	10	10
i_customer_region_id	BTREE	customer_region_id	29	25	25	10	10	10
i_account_num	BTREE	account_num	33	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_customer_lname	BTREE	lname	57	25	25	10	10	10
i_customer_fname	BTREE	fname	57	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	20
Row Overhead	16	16	16
Varsized Overhead	0	0	128
Column DataMemory/Row	680	680	220
Index DataMemory/Row	80	80	80
Total DataMemory/Row	776	776	444
Rows per 32kb page	42	42	73
Current number of rows	10281	10281	10281
Total DataMemory (kb)	7840	7840	4512

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	324	200	200
Rows per 8kb page	25	40	40
Current number of rows	10281	10281	10281
Total IndexMemory (kb)	3296	2064	2064

days

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
week_day	varchar	YES	30		32	32	12
day	int		11		4	4	4
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	1
Row Overhead	16	16	16
Varsized Overhead	0	0	16
Column DataMemory/Row	36	36	16
Index DataMemory/Row	10	10	10
Total DataMemory/Row	62	62	58
Rows per 32kb page	526	526	562
Current number of rows	7	7	7
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	7	7	7
Total IndexMemory (kb)	8	8	8

department

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
department_id	int		11	PRI	4	4	4
department_description	varchar	YES	30		32	32	24
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_department_id	BTREE	department_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	1
Row Overhead	16	16	16
Varsized Overhead	0	0	16
Column DataMemory/Row	36	36	28
Index DataMemory/Row	20	20	20
Total DataMemory/Row	72	72	80
Rows per 32kb page	453	453	408
Current number of rows	12	12	12
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	62	50	50
Rows per 8kb page	132	163	163
Current number of rows	12	12	12
Total IndexMemory (kb)	8	8	8

employee

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
store_id	int		11	MUL	4	4	4
marital_status	varchar	YES	30		32	32	4
position_id	int		11		4	4	4
last_name	varchar	YES	30		32	32	8
education_level	varchar	YES	30		32	32	20
supervisor_id	int		11	MUL	4	4	4
hire_date	timestamp				4	4	4
employee_id	int		11	PRI	4	4	4
department_id	int		11	MUL	4	4	4
management_role	varchar	YES	255		256	256	20
gender	varchar	YES	30		32	32	4
full_name	varchar	YES	30		32	32	16
birth_date	date				4	4	4
salary	decimal(10,2)				0	0	0
position_title	varchar	YES	30		32	32	24
end_date	timestamp				4	4	4
first_name	varchar	YES	30		32	32	8
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_employee_store_id	BTREE	store_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_employee_id	BTREE	employee_id	29	25	25	10	10	10
i_employee_department_id	BTREE	department_id	29	25	25	10	10	10
i_employee_supervisor_id	BTREE	supervisor_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	8
Row Overhead	16	16	16
Varsized Overhead	0	0	56
Column DataMemory/Row	512	512	136
Index DataMemory/Row	50	50	50
Total DataMemory/Row	578	578	258
Rows per 32kb page	56	56	126
Current number of rows	1155	1155	1155
Total DataMemory (kb)	672	672	320

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	149	125	125
Rows per 8kb page	54	65	65
Current number of rows	1155	1155	1155
Total IndexMemory (kb)	176	144	144

expense_fact

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
time_id	int		11	MUL	4	4	4
amount	decimal(10,2)				0	0	0
currency_id	int		11		4	4	4
category_id	varchar	YES	30		32	32	8
store_id	int		11	MUL	4	4	4
exp_date	date				4	4	4
account_id	int		11	MUL	4	4	4
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_expense_time_id	BTREE	time_id	29	25	25	10	10	10
i_expense_account_id	BTREE	account_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_expense_store_id	BTREE	store_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	1
Row Overhead	16	16	16
Varsized Overhead	0	0	16
Column DataMemory/Row	52	52	28
Index DataMemory/Row	40	40	40
Total DataMemory/Row	108	108	100
Rows per 32kb page	302	302	326
Current number of rows	2400	2400	2400
Total DataMemory (kb)	256	256	256

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	120	100	100
Rows per 8kb page	68	81	81
Current number of rows	2400	2400	2400
Total IndexMemory (kb)	288	240	240

inventory_fact

Column	Type	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
store_id	int	11	MUL	4	4	4
units_shipped	int	11		4	4	4
supply_time	smallint	6		4	4	4
store_invoice	decimal(10,4)			0	0	0
time_id	int	11	MUL	4	4	4
warehouse_cost	decimal(10,4)			0	0	0
warehouse_id	int	11	MUL	4	4	4
units_ordered	int	11		4	4	4
product_id	int	11	MUL	4	4	4
warehouse_sales	decimal(10,4)			0	0	0
HIDDEN_NDB_PKEY	bigint	8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_inv_1997_warehouse_id	BTREE	warehouse_id	29	25	25	10	10	10
i_inv_1997_product_id	BTREE	product_id	29	25	25	10	10	10
i_inv_1997_store_id	BTREE	store_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_inv_1997_time_id	BTREE	time_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	0
Row Overhead	16	16	16
Varsized Overhead	0	0	8
Column DataMemory/Row	28	28	28
Index DataMemory/Row	50	50	50
Total DataMemory/Row	94	94	102
Rows per 32kb page	347	347	320
Current number of rows	11352	11352	11352
Total DataMemory (kb)	1056	1056	1152

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	149	125	125
Rows per 8kb page	54	65	65
Current number of rows	11352	11352	11352
Total IndexMemory (kb)	1688	1400	1400

position

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
max_scale	decimal(10,2)				0	0	0
position_title	varchar	YES	30		32	32	24
min_scale	decimal(10,2)				0	0	0
pay_type	varchar	YES	30		32	32	8
position_id	int		11	PRI	4	4	4
management_role	varchar	YES	30		32	32	20
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_position_id	BTREE	position_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	3
Row Overhead	16	16	16
Varsized Overhead	0	0	28
Column DataMemory/Row	100	100	56
Index DataMemory/Row	20	20	20
Total DataMemory/Row	136	136	120
Rows per 32kb page	240	240	272
Current number of rows	18	18	18
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	62	50	50
Rows per 8kb page	132	163	163
Current number of rows	18	18	18
Total IndexMemory (kb)	8	8	8

product

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
recyclable_package	char		1		4	4	4
cases_per_pallet	smallint		6		4	4	4
gross_weight	double				8	8	8
product_class_id	int		11	MUL	4	4	4
brand_name	varchar	YES	60	MUL	64	64	12
shelf_width	double				8	8	8
units_per_case	smallint		6		4	4	4
SRP	decimal(10,2)				0	0	0
shelf_height	double				8	8	8
product_id	int		11	PRI	4	4	4
net_weight	double				8	8	8
shelf_depth	double				8	8	8
low_fat	char		1		4	4	4
product_name	varchar	YES	60	MUL	64	64	24
SKU	bigint		20	MUL	8	8	8
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_inv_1998_brand_name	BTREE	brand_name	89	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_product_SKU	BTREE	SKU	33	25	25	10	10	10
i_product_class_id	BTREE	product_class_id	29	25	25	10	10	10
i_product_name	BTREE	product_name	89	25	25	10	10	10
i_product_id	BTREE	product_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	2
Row Overhead	16	16	16
Varsized Overhead	0	0	20
Column DataMemory/Row	200	200	108
Index DataMemory/Row	60	60	60
Total DataMemory/Row	276	276	204
Rows per 32kb page	118	118	160
Current number of rows	1560	1560	1560
Total DataMemory (kb)	448	448	320

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	302	150	150
Rows per 8kb page	27	54	54
Current number of rows	1560	1560	1560
Total IndexMemory (kb)	464	232	232

product_class

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
product_class_id	int		11		4	4	4
product_family	varchar	YES	30		32	32	12
product_department	varchar	YES	30		32	32	12
product_category	varchar	YES	30		32	32	16
product_subcategory	varchar	YES	30		32	32	12
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	4
Row Overhead	16	16	16
Varsized Overhead	0	0	32
Column DataMemory/Row	132	132	56
Index DataMemory/Row	10	10	10
Total DataMemory/Row	158	158	114
Rows per 32kb page	206	206	286
Current number of rows	110	110	110
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	110	110	110
Total IndexMemory (kb)	8	8	8

promotion

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
cost	double				8	8	8
end_date	date				4	4	4
promotion_name	varchar	YES	30		32	32	16
promotion_district_id	int		11	MUL	4	4	4
media_type	varchar	YES	30		32	32	16
start_date	date				4	4	4
promotion_id	int		11	PRI	4	4	4
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_promotion_id	BTREE	promotion_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_promotion_district_id	BTREE	promotion_district_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	2
Row Overhead	16	16	16
Varsized Overhead	0	0	20
Column DataMemory/Row	88	88	56
Index DataMemory/Row	30	30	30
Total DataMemory/Row	134	134	122
Rows per 32kb page	243	243	267
Current number of rows	1864	1864	1864
Total DataMemory (kb)	256	256	224

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	91	75	75
Rows per 8kb page	90	109	109
Current number of rows	1864	1864	1864
Total IndexMemory (kb)	168	144	144

region

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
sales_region	varchar	YES	30		32	32	12
region_id	int		11		4	4	4
sales_district_id	int		11		4	4	4
sales_country	varchar	YES	30		32	32	8
sales_district	varchar	YES	30		32	32	12
sales_state_province	varchar	YES	30		32	32	4
sales_city	varchar	YES	30		32	32	12
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	5
Row Overhead	16	16	16
Varsized Overhead	0	0	40
Column DataMemory/Row	168	168	56
Index DataMemory/Row	10	10	10
Total DataMemory/Row	194	194	122
Rows per 32kb page	168	168	267
Current number of rows	110	110	110
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	110	110	110
Total IndexMemory (kb)	8	8	8

reserve_employee

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
store_id	int		11	MUL	4	4	4
marital_status	varchar	YES	30		32	32	4
position_id	int		11		4	4	4
last_name	varchar	YES	30		32	32	8
education_level	varchar	YES	30		32	32	20
supervisor_id	int		11	MUL	4	4	4
hire_date	timestamp				4	4	4
employee_id	int		11	PRI	4	4	4
department_id	int		11	MUL	4	4	4
gender	varchar	YES	30		32	32	4
full_name	varchar	YES	30		32	32	16
birth_date	date				4	4	4
salary	decimal(10,2)				0	0	0
position_title	varchar	YES	30		32	32	0
end_date	timestamp				4	4	4
first_name	varchar	YES	30		32	32	8
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_reserve_employee_id	BTREE	employee_id	29	25	25	10	10	10
i_reserve_employee_dept_id	BTREE	department_id	29	25	25	10	10	10
i_reserve_employee_super_id	BTREE	supervisor_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_reserve_employee_store_id	BTREE	store_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	7
Row Overhead	16	16	16
Varsized Overhead	0	0	52
Column DataMemory/Row	256	256	92
Index DataMemory/Row	50	50	50
Total DataMemory/Row	322	322	210
Rows per 32kb page	101	101	155
Current number of rows	143	143	143
Total DataMemory (kb)	64	64	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	149	125	125
Rows per 8kb page	54	65	65
Current number of rows	143	143	143
Total IndexMemory (kb)	24	24	24

salary

Column	Type	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
vacation_used	int	11		4	4	4
currency_id	int	11		4	4	4
employee_id	int	11		4	4	4
pay_date	date			4	4	4
vacation_accrued	int	11		4	4	4
salary_paid	decimal(10,4)			0	0	0
overtime_paid	decimal(10,4)			0	0	0
department_id	int	11		4	4	4
HIDDEN_NDB_PKEY	bigint	8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	0
Row Overhead	16	16	16
Varsized Overhead	0	0	8
Column DataMemory/Row	24	24	24
Index DataMemory/Row	10	10	10
Total DataMemory/Row	50	50	58
Rows per 32kb page	652	652	562
Current number of rows	21252	21252	21252
Total DataMemory (kb)	1056	1056	1216

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	21252	21252	21252
Total IndexMemory (kb)	688	520	520

sales_fact

Column	Type	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
store_id	int	11	MUL	4	4	4
store_cost	decimal(10,4)			0	0	0
time_id	int	11	MUL	4	4	4
product_id	int	11	MUL	4	4	4
customer_id	int	11	MUL	4	4	4
unit_sales	int	11		4	4	4
store_sales	decimal(10,2)			0	0	0
promotion_id	int	11	MUL	4	4	4
HIDDEN_NDB_PKEY	bigint	8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_sales_1997_product_id	BTREE	product_id	29	25	25	10	10	10
i_sales_1997_time_id	BTREE	time_id	29	25	25	10	10	10
i_sales_1997_store_id	BTREE	store_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10
i_sales_1997_promotion_id	BTREE	promotion_id	29	25	25	10	10	10
i_sales_1997_customer_id	BTREE	customer_id	29	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	0
Row Overhead	16	16	16
Varsized Overhead	0	0	8
Column DataMemory/Row	24	24	24
Index DataMemory/Row	60	60	60
Total DataMemory/Row	100	100	108
Rows per 32kb page	326	326	302
Current number of rows	269720	269720	269720
Total DataMemory (kb)	26496	26496	28608

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	178	150	150
Rows per 8kb page	46	54	54
Current number of rows	269720	269720	269720
Total IndexMemory (kb)	46912	39960	39960

store

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
meat_sqft	double				8	8	8
store_id	int		11	PRI	4	4	4
store_fax	varchar	YES	30		32	32	16
last_remodel_date	date				4	4	4
coffee_bar	char		1		4	4	4
store_type	varchar	YES	30		32	32	16
florist	char		1		4	4	4
region_id	int		11	MUL	4	4	4
store_phone	varchar	YES	30		32	32	16
store_name	varchar	YES	30		32	32	12
store_street_address	varchar	YES	30		32	32	20
store_city	varchar	YES	30		32	32	12
store_postal_code	varchar	YES	30		32	32	8
store_sqft	double				8	8	8
grocery_sqft	double				8	8	8
frozen_sqft	double				8	8	8
store_state	varchar	YES	30		32	32	8
store_manager	varchar	YES	30		32	32	8
salad_bar	char		1		4	4	4
video_store	char		1		4	4	4
store_number	int		11		4	4	4
first_opened_date	date				4	4	4
prepared_food	char		1		4	4	4
store_country	varchar	YES	30		32	32	8
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
i_store_region_id	BTREE	region_id	29	25	25	10	10	10
i_store_id	BTREE	store_id	29	25	25	10	10	10
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	10
Row Overhead	16	16	16
Varsized Overhead	0	0	68
Column DataMemory/Row	392	392	196
Index DataMemory/Row	30	30	30
Total DataMemory/Row	438	438	310
Rows per 32kb page	74	74	105
Current number of rows	25	25	25
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	91	75	75
Rows per 8kb page	90	109	109
Current number of rows	25	25	25
Total IndexMemory (kb)	8	8	8

time_by_day

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
the_day	varchar	YES	30		32	32	12
month_of_year	smallint		6		4	4	4
the_month	varchar	YES	30		32	32	8
week_of_year	int		11		4	4	4
the_date	date				4	4	4
quarter	varchar	YES	30		32	32	4
the_year	smallint		6		4	4	4
time_id	int		11		4	4	4
fiscal_period	varchar	YES	30		32	32	0
day_of_month	smallint		6		4	4	4
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	4
Row Overhead	16	16	16
Varsized Overhead	0	0	32
Column DataMemory/Row	152	152	48
Index DataMemory/Row	10	10	10
Total DataMemory/Row	178	178	106
Rows per 32kb page	183	183	307
Current number of rows	730	730	730
Total DataMemory (kb)	128	128	96

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	730	730	730
Total IndexMemory (kb)	24	24	24

warehouse

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
warehouse_city	varchar	YES	30		32	32	12
wa_address4	varchar	YES	30		32	32	0
wa_address3	varchar	YES	30		32	32	0
wa_address1	varchar	YES	30		32	32	20
warehouse_name	varchar	YES	60		64	64	24
warehouse_phone	varchar	YES	30		32	32	16
warehouse_state_province	varchar	YES	30		32	32	8
warehouse_class_id	int		11		4	4	4
stores_id	int		11		4	4	4
warehouse_postal_code	varchar	YES	30		32	32	8
warehouse_id	int		11		4	4	4
warehouse_owner_name	varchar	YES	30		32	32	0
warehouse_fax	varchar	YES	30		32	32	16
warehouse_country	varchar	YES	30		32	32	8
wa_address2	varchar	YES	30		32	32	0
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	12
Row Overhead	16	16	16
Varsized Overhead	0	0	80
Column DataMemory/Row	428	428	124
Index DataMemory/Row	10	10	10
Total DataMemory/Row	454	454	230
Rows per 32kb page	71	71	141
Current number of rows	24	24	24
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	24	24	24
Total IndexMemory (kb)	8	8	8

warehouse_class

Column	Type	VARSIZE	Size	Key	4.1 NDB Size	5.0 NDB Size	5.1 NDB Size
description	varchar	YES	30		32	32	16
warehouse_class_id	int		11		4	4	4
HIDDEN_NDB_PKEY	bigint		8	PRI	8	8	8

Indexes

We assume that indexes are ORDERED (not created USING HASH). If order is not required, 10 bytes of data memory can be saved per row if the index is created USING HASH

Index	Type	Columns	4.1 IdxMem	5.0 IdxMem	5.1 IdxMem	4.1 DatMem	5.0 DatMem	5.1 DatMem
PRIMARY	BTREE	HIDDEN_NDB_PKEY	33	25	25	10	10	10

DataMemory Usage

	4.1	5.0	5.1
Nr Varsized Attributes	0	0	1
Row Overhead	16	16	16
Varsized Overhead	0	0	16
Column DataMemory/Row	36	36	20
Index DataMemory/Row	10	10	10
Total DataMemory/Row	62	62	62
Rows per 32kb page	526	526	526
Current number of rows	6	6	6
Total DataMemory (kb)	32	32	32

IndexMemory Usage

	4.1	5.0	5.1
IndexMemory/Row	33	25	25
Rows per 8kb page	248	327	327
Current number of rows	6	6	6
Total IndexMemory (kb)	8	8	8

tags:

mysql cluster memory sizing foodmart

Languages

Login

Search form

MySQL Cluster analysis for foodmart

Parameter Settings

Memory usage because of parameters

Table List

account

Indexes

DataMemory Usage

IndexMemory Usage

category

Indexes

DataMemory Usage

IndexMemory Usage

currency

Indexes

DataMemory Usage

IndexMemory Usage

customer

Indexes

DataMemory Usage

IndexMemory Usage

days

Indexes

DataMemory Usage

IndexMemory Usage

department

Indexes

DataMemory Usage

IndexMemory Usage

employee

Indexes

DataMemory Usage

IndexMemory Usage

expense_fact

Indexes

DataMemory Usage

IndexMemory Usage

inventory_fact

Indexes

DataMemory Usage

IndexMemory Usage

position

Indexes

DataMemory Usage

IndexMemory Usage

product

Indexes

DataMemory Usage

IndexMemory Usage

product_class

Indexes

DataMemory Usage

IndexMemory Usage

promotion

Indexes

DataMemory Usage

IndexMemory Usage

region

Indexes

DataMemory Usage

IndexMemory Usage

reserve_employee

Indexes

DataMemory Usage

IndexMemory Usage

salary

Indexes

DataMemory Usage

IndexMemory Usage

sales_fact

Indexes

DataMemory Usage

IndexMemory Usage

store

Indexes

DataMemory Usage

IndexMemory Usage

time_by_day