On 6/20/2012 5:45 AM, Christian Koetteritzsch wrote:
Hi guys,
As the title says I'm trying to index 40 billion entries with two
indexes on a server with 16 cores and 128GB RAM. The table is the one
below and it is a myisam table. The *.myd file is about 640GB
DROP TABLE IF EXISTS `l4_link`;
CREATE TABLE `l4_link` (
`ruid1` int NOT NULL,
`ruid2` int NOT NULL,
`overlap` int NOT NULL
);
I need an index for ruid1 and for ruid2.
Actually, based on your proposed query, I believe you want an index on
(ruid1, ruid2) not separate indexes for each column.
The status for this table is the following:
Name: l4_link
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 39806500262
Avg_row_length: 17
Data_length: 676710504454
Max_data_length: 4785074604081151
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2012-06-19 14:51:29
Update_time: 2012-06-19 16:26:35
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
The variables for myisam are the following:
mysql> show global variables like '%myisam%';
+----------------------------------------+---------------------------------------+
| Variable_name |
Value |
+----------------------------------------+---------------------------------------+
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap |
OFF |
+---------------------------------------+----------------------------------------+
8 rows in set (0.00 sec)
The temp folder has about 16tb free space.
When I start the indexing process, it copies the 640Gb into a temp file
and then starts with "repair with keycache".
On the internet I found that if it says "repair with keycache" you shold
increase the "myisam_max_sort_file_size", but this didn't work.
It still says "repair with keycache" after it copied the data to the
temp dir.
I hope you have any idea how to fix this.
Try maxing out the following settings:
myisam_sort_buffer_size
key_buffer_size
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_sort_buffer_size
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_sort_buffer_size
You will need to experiment with how large the maximum value will be
permitted for your platform.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
