MySQL Forums
Forum List  »  Partitioning

Re: Large Table Architecture
Posted by: Darren Mitchell
Date: May 25, 2011 09:33PM

Thanks for the reply.

Latest row count is 550+mil.

The data:
Not an mod/delete process, inserts new data daily; unchanged

The environment:
MySQL 5.5.10
Debian Linux
24 Core ( I'll double check, might be 48 )
99GB RAM

Its serving as a new warehouse design, the query listed below is from a client group using the new system. For this particular table; this is the partitioned version; I made the attempt before your reply. I will be trying your suggestions tomorrow.

SELECT COUNT('x'), COUNT(DISTINCT OPID), `Action`, Campaign 
FROM Mydb.mytablename
GROUP BY Campaign, `Action`;

EXPLAIN for query above:
"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
"1"	"SIMPLE"	"RESP_CA_MasterSent_List"	"ALL"	NULL	NULL	NULL	NULL	"556042827"	"Using filesort"

"Name"	"Engine"	"Version"	"Row_format"	"Rows"	"Avg_row_length"	"Data_length"	"Max_data_length"	"Index_length"	"Data_free"	"Auto_increment"	"Create_time"	"Update_time"	"Check_time"	"Collation"	"Checksum"	"Create_options"	"Comment"
"THETABLE"	"MyISAM"	"10"	"Dynamic"	"556042827"	"110"	"61420752072"	"0"	"3091817472"	"0"	NULL	"2011-05-24 20:36:02"	"2011-05-26 05:46:21"	"2011-05-25 18:34:31"	"latin1_general_ci"	NULL	"partitioned"	""

VARIABLES:
"Variable_name"	"Value"
"bulk_insert_buffer_size"	"8388608"
"innodb_buffer_pool_instances"	"1"
"innodb_buffer_pool_size"	"134217728"
"innodb_change_buffering"	"all"
"innodb_log_buffer_size"	"8388608"
"join_buffer_size"	"131072"
"key_buffer_size"	"8388608"
"myisam_sort_buffer_size"	"8388608"
"net_buffer_length"	"16384"
"preload_buffer_size"	"32768"
"read_buffer_size"	"131072"
"read_rnd_buffer_size"	"262144"
"sort_buffer_size"	"2097152"
"sql_buffer_result"	"OFF"

Again, many thanks for the reply and suggestions.

Options: ReplyQuote


Subject
Views
Written By
Posted
5134
May 24, 2011 12:55PM
2834
May 25, 2011 07:45PM
Re: Large Table Architecture
1709
May 25, 2011 09:33PM
1915
May 25, 2011 11:31PM
1535
May 26, 2011 09:49AM
1940
May 26, 2011 10:52AM


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.