Re: Hash and Linear Hash partition table not performing well
Hash Partition Table
====================
CREATE TABLE `document_HASH` (
`documentId` bigint(20) NOT NULL AUTO_INCREMENT,
`calaisId` char(43) COLLATE utf8_bin DEFAULT NULL,
`calaisResponse` mediumtext COLLATE utf8_bin,
`path` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`url` varchar(1023) COLLATE utf8_bin DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`insertedDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sourceId` bigint(20) NOT NULL,
`title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`ingress` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`documentId`),
KEY `title` (`title`),
KEY `calaisId` (`calaisId`),
KEY `sourceId` (`sourceId`)
) ENGINE=MyISAM AUTO_INCREMENT=303338321 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY HASH (`documentId`)
PARTITIONS 128 */
Show table status
==================
show table status like 'document_HASH%'\G
*************************** 1. row ***************************
Name: document_HASH
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 303338320
Avg_row_length: 180
Data_length: 54600897600
Max_data_length: 0
Index_length: 12205486080
Data_free: 0
Auto_increment: 303338321
Create_time: 2009-11-17 06:08:04
Update_time: 2009-11-24 13:19:25
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: partitioned
Comment:
Buffer Size
============
show variables like '%buffer%'\G;
*************************** 1. row ***************************
Variable_name: bulk_insert_buffer_size
Value: 8388608
*************************** 2. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 8388608
*************************** 3. row ***************************
Variable_name: innodb_log_buffer_size
Value: 1048576
*************************** 4. row ***************************
Variable_name: join_buffer_size
Value: 131072
*************************** 5. row ***************************
Variable_name: key_buffer_size
Value: 4294967296
*************************** 6. row ***************************
Variable_name: myisam_sort_buffer_size
Value: 8388608
*************************** 7. row ***************************
Variable_name: net_buffer_length
Value: 16384
*************************** 8. row ***************************
Variable_name: preload_buffer_size
Value: 32768
*************************** 9. row ***************************
Variable_name: read_buffer_size
Value: 4194304
*************************** 10. row ***************************
Variable_name: read_rnd_buffer_size
Value: 262144
*************************** 11. row ***************************
Variable_name: sort_buffer_size
Value: 1048576
*************************** 12. row ***************************
Variable_name: sql_buffer_result
Value: OFF
I'm using simple SELECT queries with IN clause and passing the # of arguments...
1. 10
2. 50
3. 100
4. 500
5. 1000
6. 2500
7. 5000
8. 10000
9. 50000
10. 100000
11. 500000
12. 1000000
SQL Exa: SELECT COUNT(*) FROM document_HASH WHERE id IN(.........);
Subject
Views
Written By
Posted
6176
November 27, 2009 03:04AM
2977
December 05, 2009 11:49PM
Re: Hash and Linear Hash partition table not performing well
3021
December 08, 2009 03:42AM
3472
December 09, 2009 08:08PM
2751
December 07, 2009 04:44AM
2615
December 08, 2009 01:05AM
3481
December 22, 2009 03:29AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.