MySQL Forums
Forum List  »  Partitioning

Re: Hash and Linear Hash partition table not performing well
Posted by: Neeraj Khandelwal
Date: December 08, 2009 03:42AM

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(.........);

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Hash and Linear Hash partition table not performing well
3021
December 08, 2009 03:42AM


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.