a huge amount of
archiving by moving data to archiving servers and deleting from main
server.
We would like to however run optimize table in order to reclaim some
of the disk space , but we are facing the following issues :
- Running optimize on the full table not possible due to lack
I have a very large table (~50GB) and periodically rows are purged
from it and I want to run OPTIMIZE TABLE to recover the space. But I
do not have enough space to run it. If I do run it the server hangs
and must be killed and restarted and the table is damaged and must be
repaired. I do
functionally fine since about 6 years or so (catering to
millions of records per day).
However, since last few days, we were experiencing some elongated
slowness on both the instances.
So, we decided to OPTIMIZE TABLE slow_table on both the instances.
We first ran the command on one
machine, and they had
been running functionally fine since about 6 years or so (catering to
millions of records per day).
However, since last few days, we were experiencing some elongated
slowness on both the instances.
So, we decided to OPTIMIZE TABLE slow_table on both the instances.
We first
:
Analyze table :
Analyze table analyzes and stores the key distribution for a table.
For more details check the below URL
http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html
Optimize table :
OPTIMIZE TABLE is useful when we do more deleted operations on a table with
variable columns
hi all
what is the difference between
OPTIMIZE TABLE tablename;
and
ANALYZE TABLE tablename;
thank you
Cheers
Faizal S
GSM : 9840118673
Blog: http://oradbapro.blogspot.com
OPTIMIZE TABLE tablename;
analyze table ,stores the key distribution for a table ,reclaim the unused
space and to defragment the data file.
ANALYZE TABLE tablename;
This action only analyze table and stores the key distribution for a table.
2010-04-13
River wubx
Gtalk: wubin...@gmail.com
hi River
thanks for your mail.
could you please tel me the correct syntax for optimize table command. i
plan to put this in cronjob and execute every 8 hours in a day.
optimize table tab1; is that correct or anything need to add with his
statement.
my environment is solaria
Cheers
Faizal S
; mysql-help
Subject: Re: difference btw Analyze and Optimize table..
hi River
thanks for your mail.
could you please tel me the correct syntax for optimize table command. i
plan to put this in cronjob and execute every 8 hours in a day.
optimize table tab1; is that correct or anything need to add
Analyze table :
Analyze table analyzes and stores the key distribution for a table.
For more details check the below URL
http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html
Optimize table :
OPTIMIZE TABLE is useful when we do more deleted operations on a table with
variable columns
Check_time: 2010-02-21 05:17:27
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
Question I would like to ask is, what is an acceptable threshhold or
amount of freespace before I must run OPTIMIZE TABLE?
Kind Regards
Brent
Not an expert, but:
2009/11/14 Krishna Chandra Prajapati prajapat...@gmail.com:
I would like to know how optimize table work internally. A table contains
200 millions records. Whether query performance will be faster after
deletion of 30 million records from this table or not.
Use of OPTIMIZE
;
Regards
Theo
-Original Message-
From: Ananda Kumar [mailto:[EMAIL PROTECTED]
Sent: Monday, 15 September 2008 9:45 PM
To: mysql
Subject: optimize table
I have a table which adds on a daily basis around 3 million records.
We have a automated stored proc, which deletes 1 million reocrds.
Now
remember the particular tablesize at that time, but it could easily get
to be around 1 gig or more.
Any suggestions?
Your friendly neighborhood SA,
phiLLip
Paul DuBois [EMAIL PROTECTED]
11/03/2005 12:11 AM
To
[EMAIL PROTECTED], mysql@lists.mysql.com
cc
Subject
Re: optimize table on live
DuBois [EMAIL PROTECTED]
11/03/2005 12:11 AM
To
[EMAIL PROTECTED], mysql@lists.mysql.com
cc
Subject
Re: optimize table on live database
At 14:08 -0500 11/2/05, [EMAIL PROTECTED] wrote:
Hello,
I've been looking for information related to best practice on how to
OPTIMIZE TABLE table name
Hello,
I've been looking for information related to best practice on how to
OPTIMIZE TABLE table name with out taking the database offline. I
understand that it is not good to run an optimize while the database is
being used. So what is a good way of handling this?
In my particular
At 14:08 -0500 11/2/05, [EMAIL PROTECTED] wrote:
Hello,
I've been looking for information related to best practice on how to
OPTIMIZE TABLE table name with out taking the database offline. I
understand that it is not good to run an optimize while the database is
being used. So what is a good
On Mon, 1 Aug 2005, Heikki Tuuri wrote:
unfortunately, the bug fix was never made to 4.0. It is only in 4.1.12 and
later.
Hi Heikki,
Thanks for your response. The fix seems to consists only of a few lines:
http://mysql.bkbits.net:8080/mysql-4.1/[EMAIL PROTECTED]|[EMAIL PROTECTED]
It looks
Hi,
I am experiencing assertion failures described in the following bugreport:
http://bugs.mysql.com/bug.php?id=9670
The bug has been closed, but I'm running the most current 4.0.25 version
of MySQL. The report mentions downgrading to 4.0.23 or upgrading to 4.1.
Is this really neccessary or
@lists.mysql.com
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, August 01, 2005 4:08 PM
Subject: mysql bug 9670 OPTIMIZE TABLE crashes
Hi,
I am experiencing assertion failures described in the following bugreport:
http://bugs.mysql.com/bug.php?id=9670
The bug has been closed, but I'm
On Mon, 1 Aug 2005, Mikhail Entaltsev wrote:
in the bug report Heikki Tuuri wrote:
***
[13 Apr 20:59] Heikki Tuuri
The bug was introduced in 4.0.24 and 4.1.10. The fix will be in 4.1.12 and
5.0.5.
***
Hi Mikhail,
Thank you for your quick response. I had noticed myself that the fix
seemed
]
Lähetetty: Monday, August 01, 2005 5:08 PM
Aihe: mysql bug 9670 OPTIMIZE TABLE crashes
Hi,
I am experiencing assertion failures described in the following bugreport:
http://bugs.mysql.com/bug.php?id=9670
The bug has been closed, but I'm running the most current 4.0.25 version
of MySQL
Hello.
Use OPTIMIZE NO_WRITE_TO_BINLOG syntax. See:
http://dev.mysql.com/doc/mysql/en/optimize-table.html
Mike Debnam [EMAIL PROTECTED] wrote:
I issued a optimize table statement on my master which failed with a lock
wait timeout message due to some competing queries running
I issued a optimize table statement on my master which failed with a lock
wait timeout message due to some competing queries running at the same
time.
Now the statement has been replicated to my slaves, and it completes
successfully. However it's killing the slave input thread with the
following
Description:
OPTIMIZE TABLE on a table will sometimes destroy a table with non ascii
characters (e.g. Norwegian letters).
mysql optimize table feil;
crashtest.feil | optimize | error| 127 when fixing table
crashtest.feil | optimize | error| Can't copy datafile-header to tempfile,
error
the statement is taking 3 hours and counting.
optimize TABLE tablename
normally this takes 30 minutes or so.
boss wants to kill this thread.
does that mean the table will end up corrupt?
any input helpful.
david
hrdw: solaris 4cpu, running version 8
mysql version
Christopher M Bergeron [EMAIL PROTECTED] wrote:
Does anyone know of any guidelines or references that I can refer to
with regard to how long it takes to Optimize tables? I'm running on a
3GHz x86 [single] processor box with SCSI RAID and lots of ram. The
Mysql db files are approx. 35G
Does anyone know of any guidelines or references that I can refer to
with regard to how long it takes to Optimize tables? I'm running on a
3GHz x86 [single] processor box with SCSI RAID and lots of ram. The
Mysql db files are approx. 35G (uncompressed). The table in question is
described like
Scott Fletcher [EMAIL PROTECTED] wrote:
I wanna know is do I do the Analyze the table before the Optimize the
table or should I do it the other way around???
If you need to defragment the table, you can just run OPTIMIZE TABLE.
--
For technical support contracts, goto https
I wanna know is do I do the Analyze the table before the Optimize the
table or should I do it the other way around???
Thanks,
FletchSOD
A few days ago, Dennis T Cook asked this list if it is OK to abort a
OPTIMIZE that is taking a very long time. I am also wondering this.
Will it cause any damage to the half-optimized table, adnd is there a
right and a wrong way to stop this process?
Any help appreciated!
-Jim
--
MySQL
I've been running OPTIMIZE TABLE on a MyISAM table with an index on two
varchar type columns. The process status shows as Repair with
keycache. The table has 73 million rows. OPTIMIZE TABLE has
been running for several days with constant disk activity but low cpu
usage and no way to estimate
Hi List,
What are some issues relating to using OPTIMIZE TABLE and replication?
Does running OPTIMIZE TABLE on a master DB cause the optimizations to be
passed on to the slaves?
Thanks,
Jim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
In the last episode (May 14), Jim said:
What are some issues relating to using OPTIMIZE TABLE and
replication? Does running OPTIMIZE TABLE on a master DB cause the
optimizations to be passed on to the slaves?
Yes, unless you add the LOCAL flag to optimize.
http://dev.mysql.com/doc/mysql/en
In the last episode (May 14), Dan Nelson said:
In the last episode (May 14), Jim said:
What are some issues relating to using OPTIMIZE TABLE and
replication? Does running OPTIMIZE TABLE on a master DB cause the
optimizations to be passed on to the slaves?
Yes, unless you add the LOCAL
On Fri, May 14, 2004 at 03:10:05PM -0400, Jim wrote:
Hi List,
What are some issues relating to using OPTIMIZE TABLE and replication?
Does running OPTIMIZE TABLE on a master DB cause the optimizations to be
passed on to the slaves?
It does not. The command doesn't replicate.
--
Jeremy D
Actually, if you are using 4.1.1 optimize table does get passed to the
slave. This is from the 4.1.1 change log.
ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now
stored in the binary log and thus replicated to slaves. This logging does
not occur if the optional
Jeremy Zawodny wrote:
On Fri, May 14, 2004 at 03:10:05PM -0400, Jim wrote:
Hi List,
What are some issues relating to using OPTIMIZE TABLE and replication?
Does running OPTIMIZE TABLE on a master DB cause the optimizations to be
passed on to the slaves?
It does not. The command doesn't
On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote:
Actually, if you are using 4.1.1 optimize table does get passed to the
slave. This is from the 4.1.1 change log.
ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now
stored in the binary log and thus
In the last episode (May 14), Jeremy Zawodny said:
On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote:
Actually, if you are using 4.1.1 optimize table does get passed to
the slave. This is from the 4.1.1 change log.
ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH
It surprised me at first, but then I was actually happy about it.
Donny
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Friday, May 14, 2004 4:26 PM
To: Donny Simonton
Cc: [EMAIL PROTECTED]; 'Jim'
Subject: Re: OPTIMIZE TABLE and mySQL replication
On Fri
Yes.
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Friday, May 14, 2004 4:26 PM
To: Donny Simonton
Cc: [EMAIL PROTECTED]; 'Jim'
Subject: Re: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote:
Actually
On Fri, May 14, 2004 at 04:29:29PM -0500, Dan Nelson wrote:
In the last episode (May 14), Jeremy Zawodny said:
On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote:
Actually, if you are using 4.1.1 optimize table does get passed to
the slave. This is from the 4.1.1 change log
data from table :
delete from T1;
5-optimize the table :
optimize table T1;
MySQL daemon crash on this last statement if i use PERSISTENT
CONNECTION, otherwise is OK.
It doesn't crash for me when I use mysql command line client.
What MySQL client do you use - PHP ?
From the mysql daemon log
Title: Bug on MYSQL-5.0 in statement OPTIMIZE TABLE
Hi,
TABLE CREATE DESCRIPTION :
CREATE TABLE T1(IDAP INT(16),TVAL INT(2),CH_VAL_SEC INT(16),INDEX(IDAP),INDEX(TVAL),INDEX(CH_VAL_SEC));
RUNNING CONDITIONS:
-use persistent connection.
RUNNING SEQUENCE:
1-Open 1-st connection.
2
Hi all!
Running 4.0.17.
I hope this isn't a stupid question, but it appears that OPTIMIZE TABLE is
NOT equivalent to:
myisamchk --quick --check-only-changed --sort-index --analyze
Maybe I'm missing something, but OPTIMIZE TABLE rebuilds both the data file
and the index file (I see a TMD and TMM
In reference to my earlier message, I think I've figured out that the
equivalent command for OPTIMIZE TABLE is:
myisamchk -r --sort-index --analyze
That isn't documented anywhere... and in fact, the French language version
says something conflicting (I don't speak French but a Google search
or a data file with no
deleted rows in it?
I don't mind running either OPTIMIZE TABLE (which apparently rebuilds
everything and sorts it) or just the myisamchk to sort the index. Does
anyone know which one might get me more mileage?
What's the nature of your query?
If it's using an integer index
What's the nature of your query?
If it's using an integer index and that's what your searching on, then
having
it physically sorted is a Good Thing. If you're table-scanning your
main table, you're toast anyway. Finding ways of making that faster is
the
way to go, maybe partitioning your
On 8 Feb 2004, at 20:28, Mark Hazen wrote:
My tables are just 2 INT columns. I have unique indexes on them going
both
ways.
Sounds like you're sorted.
You know, this might sound strange, but does the performance drop off at
all if you lose the indices? A table scan of rows 8 bytes wide is
You know, this might sound strange, but does the performance drop off at
all if you lose the indices? A table scan of rows 8 bytes wide is going
to be pretty damn quick. Plus there's a lot less maintenance to do
without
indices and no risk of them getting corrupted.
A full table scan is
Hi.
I wonder if optimize table results are equal to dump such table and insert it back ?
Regards.
--
Grzegorz Paszka
sql,mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On Thu, Jun 19, 2003 at 02:24:52AM +0530, gamin wrote:
Hi,
What would happen to my_table (~70MB of indexes) and its indexes
if someone kills a an optimize table query - 'OPTIMIZE TABLE
my_table' . Im wondering if i should provide a cancel button in
my application during
Hi,
What would happen to my_table (~70MB of indexes) and its indexes if someone kills a
an optimize table query - 'OPTIMIZE TABLE my_table' . Im wondering if i should provide
a cancel button in my application during the optimization period. It is generally more
user friendly to provide one
Hi,
Im encountering something very funny with embedded server (libmysqld.dll) ver
4.0.12, im connecting to this using Delphi 7 statically on a windows 98 machine. If i
call mysql_real_query('OPTIMIZE TABLE my_db.my_table') in the main thread it works
perfectly fine, but the moment i call
an optimize table command and the table size decreases to
2KB's. Everything seems OK but the table_name.MYI and table_name.MYD file
sizes are still about 100MB's.
When I run all these on 3.23.49 everything works fine. Has anyonew
experienced the same problem ?
Hi Okan,
Could you submit a bug
Hi all,
First of all , the version of mysql is 3.23.52. I am using When I do a
truncate table on a MyIsam table, the show table command shows thatt there
are no records but the table size is about 100 MBs.
Afterwards, I run an optimize table command and the table size decreases to
2KB's
Hi all,
First of all , the version of mysql is 3.23.52. I am using When I do a
truncate table on a MyIsam table, the show table command shows thatt there
are no records but the table size is about 100 MBs.
Afterwards, I run an optimize table command and the table size decreases to
2KB's
On Sat, 4 Jan 2003 21:25:03 -0500, Dan Cumpian wrote:
1) Do I: Query.ExecSQL or Query.Open to execute the OPTIMIZE TABLE
TableName command?
From Delphi help: Use ExecSQL to execute queries that do not
return a cursor to data (such as INSERT, UPDATE, DELETE, and CREATE
TABLE).
2
]
Subject: Re: Optimize Table usage
On Sat, 4 Jan 2003 21:25:03 -0500, Dan Cumpian wrote:
1) Do I: Query.ExecSQL or Query.Open to execute the OPTIMIZE TABLE
TableName command?
From Delphi help: Use ExecSQL to execute queries that do not
return a cursor to data (such as INSERT, UPDATE, DELETE
Hello,
I am trying to write a process to optimize several tables in a database
by using the OPTIMIZE TABLE command in a query. I have a couple of
questions that I can't get an answer to in the documentation:
1) Do I: Query.ExecSQL or Query.Open to execute the OPTIMIZE TABLE
TableName command?
2
Hello,
I am trying to write a process to optimize several tables in a database
by using the OPTIMIZE TABLE command in a query. I have a couple of
questions that I can't get an answer to in the documentation:
1) Do I: Query.ExecSQL or Query.Open to execute the OPTIMIZE TABLE
TableName command?
2
Hello,
I am running MySQL 4.0.5 64 bit on a Sun Solaris and I am using MyISAM
Indices.
I would like to set replication (one master and one slave).
My question is:
When I OPTIMIZE TABLE table1, ... on the master will the slave be updated
or if I want to optimize the same tables on the slave I
Mariella,
Thursday, December 05, 2002, 4:15:52 AM, you wrote:
MDG I am running MySQL 4.0.5 64 bit on a Sun Solaris and I am using MyISAM
MDG Indices.
MDG I would like to set replication (one master and one slave).
MDG My question is:
MDG When I OPTIMIZE TABLE table1, ... on the master
On Mon, Nov 18, 2002 at 10:14:19PM -0700, Mark Stringham wrote:
Howdy -
I have heard that periodically optimizing tables can be helpful in the
overall maintenance of the table space in the db. Is this correct and how
often should it be done?
Depends how frequently the data in the table
Howdy -
I have heard that periodically optimizing tables can be helpful in the
overall maintenance of the table space in the db. Is this correct and how
often should it be done?
Thanks
Mark
sql,query
-
Before posting,
I have several database tables which regularly near the operating system 4GB
file limit. Until now I have been using mysqldump/'delete from
tablename'/'optimise table tablename' to backup a large chunk of the oldest
data and remove it from the table. I have been doing this every couple of
I have several large MyISAM tables that are frequently updated by certain
events in the application. I find that the queries get considerably slower
over time, and I run OPTIMIZE TABLE to defragment the datafiles. The user
comments on http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html seem
Hello,
I am using mysql 3.23.38 on winnt.
I have a table already filled with default value, that way we do only
updates during our processing.
One column is specified as not null in the table definition and it's also an
index.
everything was working fine, Until we decided to use Optimize Table
: ERROR 1030: Got error 127 from table handler / Optimize Table
Hello,
I am using mysql 3.23.38 on winnt.
I have a table already filled with default value, that way we do only
updates during our processing.
One column is specified as not null in the table definition and it's also an
index
Hi.
This is more intended to be an second answer to the original post, but
I don't have it anymore.
On Fri, May 10, 2002 at 11:00:56AM -0700, [EMAIL PROTECTED] wrote:
On Fri, May 10, 2002 at 12:55:39PM +0200, Mark wrote:
How dangerous is OPTIMIZE TABLE?
I have been reading through
, May 11, 2002 7:24 PM
Subject: Re: How dangerous is OPTIMIZE TABLE?
Hi.
This is more intended to be an second answer to the original post, but
I don't have it anymore.
On Fri, May 10, 2002 at 11:00:56AM -0700, [EMAIL PROTECTED] wrote:
On Fri, May 10, 2002 at 12:55:39PM +0200, Mark wrote
- Original Message -
From: Benjamin Pflugmann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Mark [EMAIL PROTECTED]
Sent: Saturday, May 11, 2002 7:24 PM
Subject: Re: How dangerous is OPTIMIZE TABLE?
Hi.
This is more intended to be an second answer to the original post, but I
don't have
How dangerous is OPTIMIZE TABLE?
I have been reading through the FAQ, and came to the section on OPTIMIZE
TABLE.
http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html
That has my interest, as my MySQL XOVER database will expire articles ever
so often. But then I read a comment there that OPTIMIZE
On Fri, May 10, 2002 at 12:55:39PM +0200, Mark wrote:
How dangerous is OPTIMIZE TABLE?
I have been reading through the FAQ, and came to the section on OPTIMIZE
TABLE.
http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html
That has my interest, as my MySQL XOVER database will expire
articles
file #
--defaults-extra-file=# Read this file after the global files are read
-Original Message-
From: Peter Romianowski [mailto:[EMAIL PROTECTED]]
Sent: 09 April 2002 12:08
To: [EMAIL PROTECTED]
Subject: check, analyze and optimize table
Hi,
I am writing a maintenance-deamon which
Yes you are right
We lock tables to run this but only when we have problems.
One more thing I would do is run 'show processlist' and mail it to my self
to see how thing are going.
Simon
PS have you tried mytop?
No, I did not try mytop, but I'll give it a try now :)
Another question:
Hi,
I use all this in the same order and i don't have any problems:
CHECK TABLE
IF (CHECK TABLE!=OK)
{
REPAIR TABLE
}
// i don't use analyze tables
OPTIMIZE TABLE
Before to perform this commands, you must be sure is not any users connected
(show full processlist
Has anyone reported such problem with MySQL 3.23.47 ?
I can report an example.
Marek
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To
Hello..
I need help in this:
When I use this query on my DB:
OPTIMIZE TABLE xxx... - all look fine... but when I want optimize next
table I get this error message from MySQL server: Command Out of Sync - You
cannot run this command now
When I disconnect and connect my DB again all is all
Milan Kajnar wrote:
Hello..
I need help in this:
When I use this query on my DB:
OPTIMIZE TABLE xxx... - all look fine... but when I want optimize next
table I get this error message from MySQL server: Command Out of Sync - You
cannot run this command now
When I disconnect and connect
Hi,
Milan Kajnar wrote:
[skipped]
I use WinZeosDB 3.3 Components in Delphi applications...
This definitely looks like a bug in Zeos components. Running this
command in mysql client or mysqlgui works just find.
May be, at least ZeosDBO-5.2.2 works for me fine. Check
http://www.zeoslib.org/
Hi,
I notice a strange behaviour with OPTIMIZE TABLE and ANALYZE TABLE (on MyISAM table) :
Before analysing my table, mysql show me :
mysql show index from forumcontpresencepc6
If OPTIMIZE TABLE appears to be writting to a temporary file
while it works (.TMM).. Why must it lock readers out of the original
table file?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual
Description:
If I use a table containing AUTO_INCREMENT field, and I have there
a 0 value, OPTIMIZE TABLE will fail.
How-To-Repeat:
CREATE TABLE test(
ID DECIMAL(5,0) NOT NULL AUTO_INCREMENT,
text CHAR(20) NOT NULL,
PRIMARY KEY(ID
85 matches
Mail list logo
