MySQL Lists are EOL. Please join:

List:Cluster« Previous MessageNext Message »
From:Mikael Ronström Date:July 12 2005 8:56am
Subject:Re: Large Table maintainance
View as plain text  
Hi Adam,
MySQL Cluster handles deletions well but is a main memory solution 
still.
So it is quite ok to use it for an amount of data and then move it to 
another
table when you want it to go off-line.

In 5.1 that will arrive in a short time in first alpha releases you 
will have two
options.
1) MySQL Cluster will be able to store non-indexed fields on disk
2) Your MyISAM tables can be partitioned.

By partitioning you can delete a partition without affecting other 
partitions.
You will also be able to move a partition to a new table and this will 
be close
to immediate.

There is a forum for partitioning that you can check for latest status.

Rgrds Mikael
PS: Most disk-based indexes have problems with deletions, to reorganise 
indexes
immediately after a delete is complex and most DBMS's have opted to 
avoid that
complexity. So the index is still ok but not as efficient since the 
packing degree is
lower.

2005-07-12 kl. 07.17 skrev Adam Dixon:

> I have a large table containing 12 varchar, 19 int fields. This table
> consitantly grows at around 7000 records per hour. With the current
> MyISAM solution on a single server - if we delete older rows from the
> table, it causes queries to this table (which are done very
> frequently) to slowdown considerably - There is probably a technical
> explaination to this;
>
> Currently we move the old data in an offline state to another table,
> then we optimize the remaining recent data, which takes along time.
> So basically using DELETE on the table ruins its speed, can anyone
> comment as to if NDBCLUSTER handles the deletion of rows better then
> MyISAM? So that we can move older data to a seperate system.
>
>
> I would love to do a 'on the fly' routine to clear up old data, eg
> select and insert into a offline area so that we can maintain the size
> of the cluster.
>
>> From what I can gather the index's become ineffective if delete is
> used on a myisam table.
>
>
> Or if anyone has any suggestions for this comments would be great.
>
> Adam
>
> -- 
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    
> http://lists.mysql.com/cluster?unsub=1
>
>
>
Mikael Ronstrom, Senior Software Architect
MySQL AB, www.mysql.com

Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html

Thread
Large Table maintainanceAdam Dixon12 Jul
  • Re: Large Table maintainanceStewart Smith12 Jul
    • Re: Large Table maintainanceAdam Dixon12 Jul
      • Re: Large Table maintainanceMikael Ronström12 Jul
  • Re: Large Table maintainanceMikael Ronström12 Jul
    • RE: Large Table maintainanceJonathan Miller12 Jul