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. :)
--
============================================================
Chenzhou Cui (China-VO Project)
National Astronomical Observatory | Tel: (8610)64841695
Chinese Academy of Sciences | FAX: (8610)64878240
Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED]
Beijing 100012, China | WWW: www.lamost.org/~cb
============================================================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
