List:General Discussion« Previous MessageNext Message »
From:Jocelyn Fournier Date:December 18 2002 6:34pm
Subject:Re: Can MySQL handle 120 million records?
View as plain text  
Hi,

I assume you are speaking about this comment :

"+--------------------+----------+----------+------
----------------+
| Table | Op | Msg_type |
Msg_text |
+--------------------+----------+----------+------
----------------+
| database.table_name | optimize | error | 28
when fixing table |
| database.table_name| optimize | status |
Operation failed |
+--------------------+----------+----------+------
----------------+
2 rows in set (40.91 sec)

I typed it in again thinking it had some sort of
rollback or perhaps just a machine glitch but
then I typed it in again and got the folowing

mysql> optimize table table_name;
+--------------------+----------+----------+------
-------------------------------------------+
| Table | Op | Msg_type |
Msg_text |
+--------------------+----------+----------+------
-------------------------------------------+
| database.table_name | optimize | error |
Can't open file: 'table_name.MYD'. (errno: 144) |
+--------------------+----------+----------+------
-------------------------------------------+

and lo all my data is lost... thank god for mysql
dump."

Well error 28 means there is no space left on the device.
When you run and OPTIMIZE TABLE statement, MySQL locks the main table and
recreate in // the index file.
As MySQL failed to recreate the index file, the table was marked as crashed
(errno: 144), but in any case data were lost (data file is not altered
during an optimize) :

he just have to execute a REPAIR TABLE statement to have all his record
back.

Take a look at what happens during an optimize of the following :

Before OPTIMIZE TABLE searchmainhardwarefr8 :

[root@forum] /home/mysql/Hardwarefr> l searchmainhardwarefr8.*
<19:28:52
-rw-rw----    1 mysql    mysql    27589205 Dec 18 19:25
searchmainhardwarefr8.MYD
-rw-rw----    1 mysql    mysql    16257024 Dec 18 19:25
searchmainhardwarefr8.MYI
-rw-rw----    1 mysql    mysql        8596 Oct 18 17:03
searchmainhardwarefr8.frm

During OPTIMIZE TABLE searchmainhardwarefr8 :

[root@forum] /home/mysql/Hardwarefr> l searchmainhardwarefr8.*
<19:29:21
-rw-rw----    1 mysql    mysql    27589205 Dec 18 19:25
searchmainhardwarefr8.MYD
-rw-rw----    1 mysql    mysql    16257024 Dec 18 19:25
searchmainhardwarefr8.MYI
-rw-rw----    1 mysql    mysql     6696960 Dec 18 19:29
searchmainhardwarefr8.TMM
-rw-rw----    1 mysql    mysql        8596 Oct 18 17:03
searchmainhardwarefr8.frm

After OPTIMIZE TABLE searchmainhardwarefr8 :

[root@forum] /home/mysql/Hardwarefr> l searchmainhardwarefr8.*
<19:29:22
-rw-rw----    1 mysql    mysql    27589205 Dec 18 19:25
searchmainhardwarefr8.MYD
-rw-rw----    1 mysql    mysql    16257024 Dec 18 19:29
searchmainhardwarefr8.MYI
-rw-rw----    1 mysql    mysql        8596 Oct 18 17:03
searchmainhardwarefr8.frm


As you can see, only the MYI file (index file) has changed, the data file
remains untouched.
Since you can completly recreate the MYI using the MYD file, there is no
data lost possibility, even if the hard disk is full.


Regards,
  Jocelyn


----- Original Message -----
From: "Michael She" <michael.she@stripped>
To: <mysql@stripped>
Cc: <mysql@stripped>; "Muruganandam" <muruganandam_g@stripped>
Sent: Wednesday, December 18, 2002 5:16 PM
Subject: Re: Can MySQL handle 120 million records?


> At 08:06 AM 12/18/2002 -0800, Jeremy Zawodny wrote:
>
>
> > > I have no problems using MySQL as a lightweight database for simple
> > > chores, but I'm a bit weary about putting into a mission critical
> > > environment.
> >
> >Why, exactly?
>
>
> Mainly for 2 reasons:
>
> 1. MySQL hasn't been "proven" yet in the corporate environment
> 2. Some of the comments in the mySQL manual... people losing data doing
> routine stuff like table optimizations, adding keys, etc.  If a database
is
> reliable, things like that shouldn't happen.  Comments like those in the
> MySQL manual scared me.
> --
> Michael She  : michael.she@stripped
> Mobile       : (519) 589-7309
> WWW Homepage : http://www.binaryio.com/
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread127832@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-joc=presence-pc.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>

Thread
Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
  • Re: Can MySQL handle 120 million records?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
    • Re: Can MySQL handle 120 million records?Michael She18 Dec
      • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
        • RE: Can MySQL handle 120 million records?Adam Nelson18 Dec
      • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
        • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
          • Re: Can MySQL handle 120 million records?Gerald Jensen18 Dec
            • Re: Can MySQL handle 120 million records?Aaron Clausen18 Dec
    • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
        • Database type questionRichard E. Perlotto II19 Dec
          • Re: Database type questionIikka Meriläinen19 Dec
      • Re[2]: Can MySQL handle 120 million records?Dyego Souza do Carmo19 Dec
    • RE: Can MySQL handle 120 million records?Wayne Lewis18 Dec
      • Excluding records?Eric Anderson19 Dec
        • Re: Excluding records?Adolfo Bello20 Dec
    • Re: Re[2]: Can MySQL handle 120 million records?Harald Fuchs19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
  • Re: Can MySQL handle 120 million records?Muruganandam18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
      • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
            • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
              • Re: Can MySQL handle 120 million records?Michael She18 Dec
                • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
            • Re: Can MySQL handle 120 million records?Michael Bacarella18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Can I build a web page to extract data?Gary Hostetler18 Dec
            • Re: Can MySQL handle 120 million records?Csongor Fagyal19 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
Re: Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?David T-G18 Dec
    • RE: Can MySQL handle 120 million records?JamesD18 Dec
      • Re: Can MySQL handle 120 million records?David T-G18 Dec
RE: Can MySQL handle 120 million records?Peter Vertes18 Dec
  • RE: Can MySQL handle 120 million records? - Impressive! How do you guys do that?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How doyou guys do that?R. Hannes Niedner18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How do youguys do that?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Jocelyn Fournier19 Dec
          • Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too...Qunfeng Dong20 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Ryan Fox19 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Brent Baisley19 Dec
  • RE: Can MySQL handle 120 million records?JamesD19 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny21 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
        • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny22 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny23 Dec
            • DELETE with no WHERE clauseGordon23 Dec
              • Re: DELETE with no WHERE clauseDennis Salguero24 Dec
              • re: DELETE with no WHERE clauseVictoria Reznichenko24 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • RE: Can MySQL handle 120 million records?Dean Harding21 Dec
          • RE: Can MySQL handle 120 million records?JamesD21 Dec
RE: Can MySQL handle 120 million records?David Brodbeck18 Dec
RE: Can MySQL handle 120 million records?Greg_Cope18 Dec
Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
RE: Can MySQL handle 120 million records?RBRoa19 Dec
RE: Can MySQL handle 120 million records?Dana Diederich19 Dec
RE: Can MySQL handle 120 million records?John Griffin19 Dec
Re: Excluding records?King_Henree@yahoo.com20 Dec