Not the answer you need?
Register and ask your own question!

mysql tables crashing

rashmiranirashmirani EntrantInactive User Role Beginner
Hi All,
I developed DB of one portal in mysql 5.0.26.
since last 20 days, hosted site on server win2k3. I found problem with few maximum used tables of Database. Those tables getting crashed on daily basis. daily I need to repair tables of database. I would like to fix this problem asap.

If anyone can guide me what changes I can apply in mysql configuration to stop table crashing probelm?
Or if anyone have idea how to set query log to detect problemtic queries of my php scipts.

thanks n regards.
«1

Comments

  • jcn50jcn50 Contributor Inactive User Role Beginner
    What do you mean by "table crashing"??... What kind of error do you have?...

    The only way to get a mySQL daemon stalled when using scripts, is when you try to INSERT overflow data...
    For example, let's say that you have a TEXT field, but in your scripts you try to INSERT a variable, having a length > 65535 characters... So you will need to use the substr() function.

    Hope this helps~.
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    My main table has been crashing as well.

    I beleive it is due to a lot of INSERTS and I need
    to start tracking this down.

    It crashes about 2-3 a week so that's not to bad but
    really shouldn't be happening at all...

    The issue is that my table is indexed and I think
    all the inserts and possibly deletes is causing the index
    to be out of sync...
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Sat, 08 September 2007 17:56</td></tr><tr><td class="quote">
    The issue is that my table is indexed and I think
    all the inserts and possibly deletes is causing the index
    to be out of sync...
    </td></tr></table>

    How big is your index?..
    What kind of "crash error" do you have?.. I really think it has to do with some overflow INSERTs...

    You can use "DELETE QUICK" instead of "DELETE".
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    Oh and my MySQL version is 5.0.23
  • rashmiranirashmirani Entrant Inactive User Role Beginner
    Hello All,

    thanks for reply.

    I am using phpMyAdmin frontend to access mysql databases.
    Table crash mean when I select database and see the list of tables. In action with specific tables status displayed "in use" instead of operation command like browse/select/insert/properties..etc.

    I set manual restrictions while insert records into tables. trim the contact as per field size. though atleast one table i can see daily with status "in use".

    anyone have idea can i trace the problem using error log files??
    where and how can i read:
    -->The Slow Query Log
    -->The General Query Log

    Please help me.

    regards.

    -Rashmi
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">rashmirani wrote on Mon, 10 September 2007 05:18</td></tr><tr><td class="quote">
    anyone have idea can i trace the problem using error log files??
    where and how can i read:
    -->The Slow Query Log
    -->The General Query Log
    </td></tr></table>

    See my previous post for error logging. Apart from SHOW PROCESSLIST, you can make your own "slow query log" taking a timestamp before and after your query.

    For example:
    $query = "whatever you want to INSERT / DELETE";
    $time_before=date("U");
    $result = mysql_query($query);
    $time_after=date("U");
    // let's say you want to have all the queries that took more than a minute:
    if ($time_afer-$time_before>60) error("Slow query",$query,__LINE__);


    Now I'm gonna rest after I took on your job!
  • rashmiranirashmirani Entrant Inactive User Role Beginner
    HI All,
    Thanks for all reply.
    let me explain my problem again here.

    My site hosted on linux server before and run well.
    before a month site hosted on windows server.
    we are using pear class to execute query and using phpmyadmin as mysql frontend.

    Problem of mysql table "in use" started after the day when site hosted on windows server. daily more than 3 tables show status "in use" frequently.

    I applied required changes in my.ini file. after mysql configuration changes applied now 1 or 2 tables shows status "in use" when see table list in phpmyadmin.
    Once i repair table then all run well.

    now please guide me how to detect the problem area and solve this problem.

    -Rashmi
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">rashmirani wrote on Tue, 11 September 2007 06:32</td></tr><tr><td class="quote">
    HI now please guide me how to detect the problem area and solve this problem.

    </td></tr></table>

    Man, you're not into it: the fact that your tables are "in use" is because they crashed. And, like you said, you need to make a repair.
    Before doing a repair, you can use CHECK TABLE and open your mySQL error_log.

    Check the previous posts in this thread for error reporting and logging, otherwise you won't find the cause. How can you find the origin of something if you only know/see the consequence?? How do you want us to help you if you only tell us that you need to repair your tables??

    Update your scripts and post the error(s) you got here in this post. There is no way someone else does it for you, especially when I already wrote the code you need to add...

    As allworknoplay suggested, check your read_rnd_buffer value, you may need to lower it to 2MB; same for the wait_timeout value.

    :mad:

    Alternatively, you can tell us:
    - what is the version your Windows OS?
    - what is the filesystem used? (FAT32 or NTFS)
    - how big is your DB?
    - how big is your indexes?
    - the brand of your HD?
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    Well it was only a matter of time my table crashed..

    Here it is....

    mysql> check table data_day;
    +
    +
    +
    +

    +
    | Table | Op | Msg_type | Msg_text |
    +
    +
    +
    +

    +
    | collector.data_day | check | warning | Table is marked as crashed |
    | collector.data_day | check | error | Found 2434089 keys of 2434281 |
    | collector.data_day | check | error | Corrupt |
    +
    +
    +
    +

    +
    3 rows in set (2.24 sec)
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    Here is the repair and error log...


    mysql> repair table data_day;
    +
    +
    +
    +
    +
    | Table | Op | Msg_type | Msg_text |
    +
    +
    +
    +
    +
    | collector.data_day | repair | status | OK |
    +
    +
    +
    +
    +
    1 row in set (59.68 sec)




    070912 19:40:06 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './collector/data_day.MYI'; try to repair it
  • jcn50jcn50 Contributor Inactive User Role Beginner
    A- No error report from the scripts' side?
    B- Can you post an extract of one row of "data_day" with full types used and auto_increment or timestamp if any? (you can remove any confidential data if any).
    C- Did you use DELETE or DELETE QUICK?
    D- Can you confirm that you're using MyISAM ?
    E- Are you ready to sacrifice some disk space to gain a crash-free DB?

    After you answered that, we'll see which next step you could do.
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">jcn50 wrote on Wed, 12 September 2007 22:58</td></tr><tr><td class="quote">

    A- No error report from the scripts' side?
    B- Can you post an extract of one row of "data_day" with full types used and auto_increment or timestamp if any? (you can remove any confidential data if any).
    C- Did you use DELETE or DELETE QUICK?
    D- Can you confirm that you're using MyISAM ?
    E- Are you ready to sacrifice some disk space to gain a crash-free DB?

    After you answered that, we'll see which next step you could do.
    </td></tr></table>

    A - I began to implement the error output into a logfile, but I haven't had time to finish, I have over 4000 lines of code I need to go through...but rest assured it will be implemented as it is an excellent idea...

    B - There is nothing too sensitive with my data so here it is in it's full glory. This is AFTER I've already repaired the table.

    mysql> select * from data_day limit 1;
    +
    +
    +
    +
    +----+
    -+
    +
    +
    | device_id | timestamp | data_type | object | id | actual | gdata | gdata1 |
    +
    +
    +
    +
    +----+
    -+
    +
    +
    | 1175214041 | 1189629601 | disk | C | | 1 | 31 | 0 |
    +
    +
    +
    +
    +----+
    -+
    +
    +
    1 row in set (0.01 sec)



    C - I don't want to use the DELETE QUICK yet and the reason why, and I think you will agree, is that from a programming/troubleshooting standpoint, you want to do one method first. Find out if that has relieved or resolved the issue.

    If I go with DELETE QUICK and also the BULK INSERTS, I won't know which one was the actual fix. Since there are a lot more INSERTS, I think I want to try BULK INSERTS first...

    D - Yes my tables are MYISAM.

    E - If I can sleep better at night knowing my systems is crash-free, then bring it on!!!!


    Compared to what I've seen out there, others have a MYSQL DB of greater than 20GIGs and are running fine, so the fact that I am less than 1GIG, I feel it's not so much the size of the DB causing the issue, it's just improper or inefficient methods that I'm using to access the DB....
  • jcn50jcn50 Contributor Inactive User Role Beginner
    A- Ok, perfect. I bet it will be useful cool:.

    B- You forgot to put me the types of your fields. You can easily have them by exporting the your table without including the data.
    So I guess your only key is "device_id", type is INT and UNSIGNED? Is "device_id" generated in an AUTO_INCREMENT style?

    C- Sure, no problem. Just wanted to know if the DELETE where queued or not. So the script actually awaits for the DELETE query to finish before doing any other DELETE.


    As for D and E, I have a suggestion (see my next post). :D
  • jcn50jcn50 Contributor Inactive User Role Beginner
    OK guys,


    Let's try something.

    1- Be sure that NO script is running, so that your DB is not in use.
    2- We are going to convert your DB engine. You don't have to update your scripts, as it will only change how your DB server handles the data. The SQL command is:
    ALTER TABLE your_table_name TYPE=InnoDB;
    If you have more than a table, you need to do this for every table. I don't know how long the conversion will take, but you can check with SHOW PROCESSLIST when it's done.
    So, allworknoplay, let's try:
    ALTER TABLE data_day TYPE=InnoDB;
    3- InnoDB is a crash-free engine. The tables are repaired automatically if a crash occurs. You can revert to myISAM again anytime. As I'm new to InnoDB, I don't know how to tweak the options: if you want to do that, please open another topic.
  • jcn50jcn50 Contributor Inactive User Role Beginner
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    Thanks dude, here is the description..

    +
    +
    +
    +
    +
    +
    +
    | Field | Type | Null | Key | Default | Extra |
    +
    +
    +
    +
    +
    +
    +
    | device_id | int(10) | NO | PRI | | |
    | timestamp | int(10) | NO | PRI | | |
    | data_type | varchar(32) | NO | PRI | | |
    | object | varchar(120) | NO | PRI | | |
    | id | varchar(10) | NO | MUL | | |
    | actual | int(1) | NO | | 0 | |
    | gdata | bigint(20) | NO | | 0 | |
    | gdata1 | bigint(20) | NO | | 0 | |
    +
    +
    +
    +
    +
    +
    +


    I'd like to try out some of your other suggestions first before
    I do any conversion to InnoDB. I spent so much time learning all the in's and out's of MyISAM, I would first have to learn InnoDB too...

    But the idea of crash-free is great, I had no idea that that was a built in function of InnoDB..

    Plus this stuff is PRODUCTION. If I mess up at all in any which way, I am in TROUBLE!!!!
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 01:28</td></tr><tr><td class="quote">

    +
    +
    +
    +
    +
    +
    +
    | Field | Type | Null | Key | Default | Extra |
    +
    +
    +
    +
    +
    +
    +
    | device_id | int(10) | NO | PRI | | |
    | timestamp | int(10) | NO | PRI | | |
    | data_type | varchar(32) | NO | PRI | | |
    | object | varchar(120) | NO | PRI | | |
    | id | varchar(10) | NO | MUL | | |
    | actual | int(1) | NO | | 0 | |
    | gdata | bigint(20) | NO | | 0 | |
    | gdata1 | bigint(20) | NO | | 0 | |
    +
    +
    +
    +
    +
    +
    +

    </td></tr></table>
    Wait! eek: As per mySQL standards, you should have only ONE primary key per table!
    http://dev.mysql.com/doc/refman/5.0/en/create-table.html
    "A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY."

    So you should have only one PRI, the other indexes should be noted as MUL, just like the field "id". confused:
    That's why your table was crashing man! I think we found out! Because the "timestamp" may not be unique (as I said earlier in message #1777 in this topic), same for the "data_type" and "object" fields! So be sure that "device_id" is UNIQUE in your design! (i.e: using AUTO_INCREMENT, not being generated by your script)

    If you had had the scripting error_reporting, you would have had an error like "can't INSERT duplicate keys" or "can't DELETE duplicate keys". cool:


    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 01:28</td></tr><tr><td class="quote">

    Plus this stuff is PRODUCTION. If I mess up at all in any which way, I am in TROUBLE!!!!

    </td></tr></table>
    Well, it can't be worse than MyISAM )....at least if you design your tables & write scripts properly! D
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    Well I was hoping I didn't have to explain what the table was all about but here goes...

    The first 4 columns are unique as a WHOLE when I built the table, at the end I basically said,
    UNIQUE(device_id, timestamp,data_type,object) so data in all 4 columns have to be a unique mix. Below is the SHOW CREATE TABLE.

    CREATE TABLE `data_day` (
    `device_id` int(10) NOT NULL,
    `timestamp` int(10) NOT NULL,
    `data_type` varchar(32) NOT NULL,
    `object` varchar(120) NOT NULL,
    `id` varchar(10) NOT NULL default '',
    `actual` int(1) NOT NULL default '0',
    `gdata` bigint(20) NOT NULL default '0',
    `gdata1` bigint(20) NOT NULL default '0',
    UNIQUE KEY `device_id` (`device_id`,`timestamp`,`data_type`,`object`),
    KEY `id` (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1


    Let me ask you this, say I am inserting a lot of data that is already unique so mysql has to reject them, do you think that in itself could be causing the crash?
  • jcn50jcn50 Contributor Inactive User Role Beginner
    You can have an INDEX that is NOT UNIQUE. But you can't have more than ONE PRIMARY INDEX, and that PRIMARY INDEX should be UNIQUE.

    UNIQUE = there is no duplicate (in the same column). You can't consider 4 columns being unique as a whole for a PRIMARY INDEX.

    I don't know if I answered your question?...

    For example, if I do:
    SELECT * FROM data_day WHERE device_id = 1175214041;
    it should always return 0 or 1 result, even if the other columns are different as a whole.

    The idea behind a PRIMARY INDEX is that it identifies each row uniquely. Let's imagine you have a list of prices, and your items have a bar code. The bar code is a PRIMARY INDEX because you can only have one price per bar code (even if the packaging changes, or the name of the product changes, or where it comes from changes; it doesn't matter, because we're talking about the same product).

    What cause the crash is not the INSERT, it's the DELETE. I don't know how you make your query, but I guess MySQL wants to delete every row that as the same device_id (or any of the other 3 PRIMARY INDEXes being the same), resulting in a crash. When mySQL uses a PRIMARY INDEX for deleting, only ONE ROW can be deleted... whereas your table may contains more than a row with the same device_id (or more than a row with the same timestamp, etc).

    Imagine that I ask you: "delete the product which has the bar code 1175214041", then you start to search on your list "which product has the bar code 1175214041?" and you end up with more than a single result, which one would you delete? (as you can only delete one) eek: ... => crash :o

    UNIQUE(device_id, timestamp,data_type,object) means:
    - device_id contains no duplicate ;
    - timestamp contains no duplicate ;
    - data_type contains no duplicate ;
    - object contains no duplicate.

    UNIQUE(device_id, timestamp,data_type,object) doesn't mean: "there's only ONE UNIQUE row having a defined: device_id AND timestamp AND data_type AND object".
  • jcn50jcn50 Contributor Inactive User Role Beginner
    If you want the SQL commands to fix your indexes, ask me.
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">jcn50 wrote on Fri, 14 September 2007 10:28</td></tr><tr><td class="quote">


    UNIQUE(device_id, timestamp,data_type,object) means:
    - device_id contains no duplicate ;
    - timestamp contains no duplicate ;
    - data_type contains no duplicate ;
    - object contains no duplicate.

    UNIQUE(device_id, timestamp,data_type,object) doesn't mean: "there's only ONE UNIQUE row having a defined: device_id AND timestamp AND data_type AND object".
    </td></tr></table>


    HMMM!!!!!! Very very interesting....

    Let me find some of my DELETE commands to see exactly how I'm doing it. But what you said is not how I intended my scripts
    to work, that would explain a lot....

    I always thought that including 4 unique columns would mean that the combination needs to be unique which is what my intentions were. so to make it simpler, let's play with 2 columns..

    So I could insert a row with:
    device_id = 10
    timestamp = 12345677

    But if I wanted to insert again:

    device_id = 10
    timestamp = 12345677

    It shouldn't let me do that...atleast that is what I thought
    I was doing...

    I will confirm my DELETES, but usually the deletes are based
    on timestamp, not even the device_id..

    So if I have 10 records with device_id 10,12,13,14,15 etc..

    My cleanup script would just delete any record that has a timestamp older than say 24 hours...

    So I didn't think that I needed to be specific in that sense when deleting???

    Thoughts?
  • jcn50jcn50 Contributor Inactive User Role Beginner
    OK, if you didn't intend to do what I wrote... it simply means that you have NO PRIMARY INDEX! :D ...

    I'm not saying that what you want you to do is wrong, I'm just saying that the columns which are set to UNIQUE (or "PRIMARY INDEX") are wrong/misused.

    SQL commands are:
    ALTER TABLE data_day DROP PRIMARY KEY
    (should delete all primary keys)

    ALTER TABLE data_day DROP INDEX device_id
    ALTER TABLE data_day DROP INDEX timestamp
    ALTER TABLE data_day DROP INDEX data_type
    ALTER TABLE data_day DROP INDEX object
    (should remove the unique index)

    ALTER TABLE data_day ADD INDEX device_id
    ALTER TABLE data_day ADD INDEX timestamp
    ALTER TABLE data_day ADD INDEX data_type
    ALTER TABLE data_day ADD INDEX object
    (should add a simple index)

    And you're done!

    Copy/paste your fields' type again, to show 5 beautiful "MUL" and no more "PRI".
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    To answer your question, yes the data_day table should not have any column that is a primary key. The device_id is a primary key in another table and we use that in the data_day table for other purposes. Basically we dump a lot of data into the data_day table and I guess, the reason why we used Unique( 4 columns ) is that I didn't want any duplicate entry if any of the INSERTS all matched the 4 columns exactly..

    Does that make any sense? So..

    col1, col2, col3, col4 could have values 1,2,3,4 respectively
    and if I wanted to INSERT those again, it should reject it..

    But 1,2,2,3 or 1,1,1,1 any other combo would work...

    I think we're pretty much on the same page, just want to confirm..

    You have suggested other things to try earlier in this thread
    so I want to try those first....you've given me plenty of troubleshooting ideas!!!
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 19:18</td></tr><tr><td class="quote">
    To answer your question, yes the data_day table should not have any column that is a primary key. The device_id is a primary key in another table and we use that in the data_day table for other purposes. Basically we dump a lot of data into the data_day table and I guess, the reason why we used Unique( 4 columns ) is that I didn't want any duplicate entry if any of the INSERTS all matched the 4 columns exactly..

    Does that make any sense? So..
    </td></tr></table>
    Ok I got it... but it doesn't work like that. (see below)


    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 19:18</td></tr><tr><td class="quote">
    col1, col2, col3, col4 could have values 1,2,3,4 respectively
    and if I wanted to INSERT those again, it should reject it..

    But 1,2,2,3 or 1,1,1,1 any other combo would work...
    </td></tr></table>
    No it will not work:
    - if you already have 1,2,3,4
    AND
    - if you try to insert 1,1,1,1
    => mySQL won't proceed the INSERT (because the first value is not distinct in the column), and mySQL will simply dump 1,1,1,1.

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 19:18</td></tr><tr><td class="quote">

    You have suggested other things to try earlier in this thread
    so I want to try those first....you've given me plenty of troubleshooting ideas!!!

    </td></tr></table>
    Debugging is part of programming )... good luck!
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    Ok here is one of my codes...

    INSERT INTO data_day (device_id,timestamp,data_type,object,actual,gdata) VALUES ('".$device_id."','".$timestamp."','disk','".$VLetter."','1',' ".$PercentUsedDisk."')

    The first 4 obviously are the ones in question, and the last 2 (actual,gdata) could be of any value, that doesn't and shouldn't have any affect on the INSERTS....
  • jcn50jcn50 Contributor Inactive User Role Beginner
    If you don't really need to have 4 seperated columns, you could merge columns device_id,timestamp,data_type,object into a single UNIQUE column (let's call it "merged").
    You will separate the values by a separator (let's pick up the semicolon).
    So your "merged" column's data could look like this:
    1175214041;1189629601;disk;C

    I have no other suggestion for now (at the mySQL level) I could think of (yet).
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    This particular script doesn't have any DELETES.

    I have another script that loops through this table 2.5 million rows and just deletes anything with a timestamp older than a certain period like 24 hours...

    So I don't think I have any WHERE clauses in the DELETE statements because I assume I didn't have to be specific, all I care is that if any record with a timestamp older than say 24 hours, then DELETE....
  • allworknoplayallworknoplay Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">jcn50 wrote on Fri, 14 September 2007 15:48</td></tr><tr><td class="quote">
    If you don't really need to have 4 seperated columns, you could merge columns device_id,timestamp,data_type,object into a single UNIQUE column (let's call it "merged").
    You will separate the values by a separator (let's pick up the semicolon).
    So your "merged" column's data could look like this:
    1175214041;1189629601;disk;C

    I have no other suggestion for now (at the mySQL level) I could think of (yet).
    </td></tr></table>


    Well I can't merge them because I have other programs that call this table that look for a specific column and do something else with it, like the data_type and object.

    That's what makes this whole thing difficult, because everything is production and one change to one column or table could have affects on other scripts that I completely forgot uses it..

    I think my first two steps at this point are to drop the index on the ID column and start using DELETE QUICK everywhere I can think of!!!
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 19:45</td></tr><tr><td class="quote">
    Ok here is one of my codes...

    INSERT INTO data_day (device_id,timestamp,data_type,object,actual,gdata) VALUES ('".$device_id."','".$timestamp."','disk','".$VLetter."','1',' ".$PercentUsedDisk."')

    The first 4 obviously are the ones in question, and the last 2 (actual,gdata) could be of any value, that doesn't and shouldn't have any affect on the INSERTS....
    </td></tr></table>

    I bet you won't have a lot of completed INSERTs, because data_type is not UNIQUE on this line of the script!

    How many results do you have for this query?
    SELECT * FROM data_day WHERE data_type = "disk";
  • jcn50jcn50 Contributor Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">allworknoplay wrote on Fri, 14 September 2007 19:49</td></tr><tr><td class="quote">
    I have another script that loops through this table 2.5 million rows and just deletes anything with a timestamp older than a certain period like 24 hours...

    So I don't think I have any WHERE clauses in the DELETE statements because I assume I didn't have to be specific, all I care is that if any record with a timestamp older than say 24 hours, then DELETE....
    </td></tr></table>

    2.5 million rows?! Wow, DELETE QUICK is a must D... Because after each DELETE, your indexes are rebuilt! That's a waste of resources.
    I hope your mySQL server automatically locks the table "data_day", because if you have an INSERT when the INDEX is being rebuilt, you are likely to have a bunch of crashes!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.