| Peter Zaitsev 2006-02-25, 9:43 am |
| 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:
>
> Yes. I am using the default format.
>
> 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
>
> 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/mysql? unsub...sie.nctu.edu.tw
|