(ruid1, ruid2) will help for AND, but not at all for OR. > -----Original Message----- > From: Shawn Green [mailto:shawn.l.gr...@oracle.com] > Sent: Wednesday, June 20, 2012 2:30 PM > To: mysql@lists.mysql.com > Subject: Re: Indexing about 40 Billion Entries > > 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
