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 this with
ne, 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 "
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
; 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
&
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. It
; 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
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
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 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
-27 01:12:01
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 TA
Not an expert, but:
2009/11/14 Krishna Chandra Prajapati :
> 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 TABLE va
;
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
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 database
At 14:08 -0500 11/2/05, [EMAIL PROTECTED] wrote:
Hello,
I've been looking for information related to best practice on how t
e? I don't
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: o
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 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 w
Hello,
I've been looking for information related to best practice on how to
OPTIMIZE TABLE 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 application,
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 look
;; <[EMAIL PROTECTED]>
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
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
s
ot;MySQL mailing list"
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/b
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 s
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" messa
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
>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
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
mys
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 (u
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 this:
"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
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 General
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
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 sla
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:2
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
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, OPTIMI
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 bina
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
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
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 do
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?
>
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.
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
let 1-st connection open.Open 2-nd connection to execute the next 2 statement.
> 4-delete all 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, otherwi
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 conn
> 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
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 goin
> 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
index 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 u
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
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 T
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
&
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 p
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 mom
;
> Afterwards, I run 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
> experienc
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
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
PROTECTED]
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 (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)
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 comman
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 comman
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, ..
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
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, please
I have several database tables which regularly near the operating system 4GB
file limit. Until now I have been using mysqldump/'delete from
'/'optimise table ' to backup a large chunk of the oldest
data and remove it from the table. I have been doing this every couple of
months.
Approximately 1
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 to
: 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
t;Optimize Table".
now I am not getting anymore all the rows. Moreover in some cases, I'm
getting the following error:
ERROR 1030: Got error 127 from table handler.
is there anyway to move around this, while I keep the call to Optimize
Table?
PS: myisamchk
- 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
k" <[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 it anymore.
>
> On Fri, May 10, 2002 at 11:00:56AM -0700, [E
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?
> >
>
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 XO
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
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 p
> 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 quest
fault options from any options file
--defaults-file=# Only read default options from the given 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 PROT
Hi,
I am writing a maintenance-deamon which is executing
CHECK TABLE, ANALYZE TABLE and OPTIMIZE TABLE once at
night.
If CHECK TABLE fails for at least one table, then the
rest will be skipped.
The question is if the order of the statements is ok
this way. A second question would be, if I should
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 r
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.zeosli
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
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 ag
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 forumcontpresence
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
EMENT 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)); // or INDEX(ID))
>
>
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
87 matches
Mail list logo