On Mon, 2005-12-26 at 09:27 +0800, Chenzhou Cui wrote: Hi,
Sorry for long delay with reply. I guess it is similar to http://bugs.mysql.com/bug.php?id=17229 The problem is basically confirmed and we're now working to find solution > Dear Peter, > > Thanks very much for your concern. Answers to your questions are listed > below. Here, I am facing another serious problem: Should I interrupt the > Indexing work, which has been running for about 19 days? I don't know > how long it will take to finish the job. The table contains > 1,045,175,762 rows and there is 3GB memory in my server. > > There are two important fields: `RAdeg` and `DEdeg` in the table. The > source data is ordered by `DEdeg`. It costed me 22 hours 14 min 37.27 > sec to add a index on `DEdeg`. The `RAdeg` values are random. I don't > know how many days will it cost to create the index on that field. > > Some information about my database and server are provided below. > > Happy New Year, > Chenzhou > =============== > > > show processlist; > +------+------+-----------+-------+---------+---------+-------------------+----------------------------------------+ > | Id | User | Host | db | Command | Time | > State | Info | > +------+------+-----------+-------+---------+---------+-------------------+----------------------------------------+ > | 1524 | cb | localhost | USNOB | Query | 1630664 | copy to tmp > table | alter table `main` add index (`RAdeg`) | > | 4486 | cb | localhost | USNOB | Query | 0 | > NULL | show processlist | > +------+------+-----------+-------+---------+---------+-------------------+----------------------------------------+ > 2 rows in set (0.00 sec) > > show table status from USNOB; > +----------+--------+---------+------------+------------+----------------+--------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | 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 | > +----------+--------+---------+------------+------------+----------------+--------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | main | MyISAM | 10 | Fixed | 1045175762 | 157 > | 164092594634 | 44191571343572991 | 6073899008 | 0 > | NULL | 2005-12-06 08:31:40 | 2005-12-07 06:41:01 | > NULL | latin1_swedish_ci | NULL | | | > +----------+--------+---------+------------+------------+----------------+--------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > > #>free > total used free shared buffers cached > Mem: 3116424 3110228 6196 0 41292 2528564 > -/+ buffers/cache: 540372 2576052 > Swap: 1020088 20548 999540 > > > Peter Zaitsev wrote: > > >Hi, > > > >I'm not on the MySQL list so let me write to you directly. > > > >Are you using MyISAM Tables ? > > > > > Yes. I am using the default format. > > >How does SHOW CREATE TABLE looks like ? > > > > > > > CREATE TABLE `main` ( > `USNO_B1_0` char(12) NOT NULL default '', > `Tycho_2` char(12) default NULL, > `RAdeg` double(10,6) default NULL, > `DEdeg` double(10,6) default NULL, > `e_RAdeg` smallint(3) default NULL, > `e_DEdeg` smallint(3) default NULL, > `Epoch` float(6,1) default NULL, > `pmRA` mediumint(6) default NULL, > `pmDE` mediumint(6) default NULL, > `muPr` tinyint(1) default NULL, > `e_pmRA` smallint(3) default NULL, > `e_pmDE` smallint(3) default NULL, > `fit_RA` tinyint(1) default NULL, > `fit_DE` tinyint(1) default NULL, > `Ndet` tinyint(1) default NULL, > `Flags` char(3) default NULL, > `B1mag` float(5,2) default NULL, > `B1C` tinyint(1) default NULL, > `B1S` tinyint(1) default NULL, > `B1f` smallint(3) default NULL, > `B1s_g` tinyint(2) default NULL, > `B1xi` float(6,2) default NULL, > `B1eta` float(6,2) default NULL, > `R1mag` float(5,2) default NULL, > `R1C` tinyint(1) default NULL, > `R1S` tinyint(1) default NULL, > `R1f` smallint(3) default NULL, > `R1s_g` tinyint(2) default NULL, > `R1xi` float(6,2) default NULL, > `R1eta` float(6,2) default NULL, > `B2mag` float(5,2) default NULL, > `B2C` tinyint(1) default NULL, > `B2S` tinyint(1) default NULL, > `B2f` smallint(3) default NULL, > `B2s_g` tinyint(2) default NULL, > `B2xi` float(6,2) default NULL, > `B2eta` float(6,2) default NULL, > `R2mag` float(5,2) default NULL, > `R2C` tinyint(1) default NULL, > `R2S` tinyint(1) default NULL, > `R2f` smallint(3) default NULL, > `R2s_g` tinyint(2) default NULL, > `R2xi` float(6,2) default NULL, > `R2eta` float(6,2) default NULL, > `Imag` float(5,2) default NULL, > `IC` tinyint(1) default NULL, > `IS_` tinyint(1) default NULL, > `If_` smallint(3) default NULL, > `Is_g` tinyint(2) default NULL, > `Ixi` float(6,2) default NULL, > `Ieta` float(6,2) default NULL, > KEY `DEdeg` (`DEdeg`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > >Did you use prepared statements. > > > > > >This should be enough to comment. In general using single insert > >statements is the most inefficient way to insert data. > > > > > > > No. I just use the single insert mode. I didn't know batch mode would > improve the performence at that time. :) > > > -- Peter Zaitsev, Senior Performance Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
