Hi Alex,
with MySQL 4.1.22 there is not much you can do.
MySQL alter tables recreating a new temporary one and swapping then
afterwards.
In my opinion the effort to speed up the process is not worth for just this
alter table.
Cheers
Claudio
2011/6/21 Alex Schaft
> Hi,
>
> I'm busy creating
Hi,
I'm busy creating an index on a 518505 record table on a single column
which is now taking about 2 hours on the copy to tmp table process
The server is a 2gig ram Intel(R) Xeon(TM) CPU 3.00GHz running on a
hardware raid 5. The inno config was left as a standard install from
my-medium con
In the last episode (Aug 30), Kyong Kim said:
> I've been going through the 5.1 manual and exploring the new features.
>
> To add a secondary index to an existing table, InnoDB scans the table, and
> sorts the rows using memory buffers and temporary files in order by the
> value(s) of the secondar
I've been going through the 5.1 manual and exploring the new features.
To add a secondary index to an existing table, InnoDB scans the table,
and sorts the rows using memory buffers and temporary files in order
by the value(s) of the secondary index key column(s). The B-tree is
then built in key-v
NOT NULL,
> > > `date_modified` timestamp NULL default CURRENT_TIMESTAMP on update
> > > CURRENT_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8
> > >
> > >
> > > indexs are as below
> > >
> > > PRIMARY KEY (`id`),
> > > KEY `KI_IDX_0805090456
_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8
> >
> >
> > indexs are as below
> >
> > PRIMARY KEY (`id`),
> > KEY `KI_IDX_0805090456` (`words`,`id`),
> > KEY `CI_IDX_0805090456` (`lf_id`)
> >
> > we have 8 cpu, 8 gb ram.
> > We use se
>
> myisam_sort_buffer_size=300MB
> myisam_max_sort_file_size=10GB
>
> Each index creation is taking 10hrs, is there any way i can speed up index
> creation.
>
> regards
> anandkl
>
--
Krishna Chandra Prajapati
,Myisam_max_sort_file_size is a temporary file used by mysql during
index creation. This is not allocated from memory, if i am not wrong.
Mike,
Also my current setting of key_buffer_size is 2GB.
show variables like 'key%';
+--++
| Variable_name
parameters at session level
myisam_sort_buffer_size=300MB
myisam_max_sort_file_size=10GB
Each index creation is taking 10hrs, is there any way i can speed up index
creation.
regards
anandkl
Anandkl,
The reason it is taking so long is it is building the index using
the hard drive. You can speed
evel
>
> myisam_sort_buffer_size=300MB
> myisam_max_sort_file_size=10GB
>
> Each index creation is taking 10hrs, is there any way i can speed up index
> creation.
>
> regards
> anandkl
>
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-3
myisam_sort_buffer_size=300MB
myisam_max_sort_file_size=10GB
Each index creation is taking 10hrs, is there any way i can speed up index
creation.
regards
anandkl
Sounds like a very nice idea to me, but this could be a problem if temp
tables need to be bigger than RAM+Swap, which could easily be the case
in a table with 100,000,000 rows.
Gabriel PREDA schrieb:
For this table this is to late... leave it running...
If you want to do this on another table
For this table this is to late... leave it running...
If you want to do this on another table(s)... and in general on huge
loaded MySQL servers I recomend the following...
Create a directory let's say /mnt/mem_fs
Mount in it /dev/shm use "tmpfs" as filesystem...
Now you have a directory that sto
I have a table with ~100,000,000 rows. I recently discovered that I
need to start using one of the non-indexed columns in WHERE clauses. As
expected the performance is horrid. I decided to bite the bullet and
create an index (innodb):
mysql> show full processlist\G
*** 1
Gleb:
Aha. OK, we tried using OPTIMIZE instead of ANALYZE, and that does
indeed update the Cardinality of the index, the way you would expect.
Thanks very much for figuring this out! I really appreciate the help.
Thanks again!
Allan
"Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message new
Hello.
I've checked this and found that ANALYZE table really doesn't work, but
OPTIMIZE table made its work. In case it won't help you send to list
complete definitions of you tables and queries.
Allan Miller wrote:
> Hi Gleb,
>
> Thanks for the quick response. Unfortunately, ANALYZE
Hi Gleb,
Thanks for the quick response. Unfortunately, ANALYZE TABLE does not
appear to affect the Cardinality field of a temporary table (it is
still NULL, even with rows added). Only by creating the index itself
after the insert seems to make the query faster. I also tried FORCE
INDEX and it
Hello.
> Why is it that adding an index before or after the addition of data
>makes a difference on SELECT performance?
Check whether the problem still exists if you run ANALYZE table after
inserts in case you have already created indexes. FORCE_INDEX in the
SELECT clause should help as
In MySQL 4.1.11, if you create two temporary tables with non-unique indices as
part of the CREATE TEMPORARY TABLE statement, then
insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN
between the two tables is substantially slower than if
you create the indices after inserti
]
Subject: Speeding up index creation
We have MyISAM table with 150 million rows. The data is being laoded
from other system.
When I load data without indices, it is fast (30 minutes) but creating
index is taking 15 hours.
Load data with indices is taking 30 hours (worse than the other option)
I am
We have MyISAM table with 150 million rows. The data is being laoded from other system.
When I load data without indices, it is fast (30 minutes) but creating index is taking
15 hours.
Load data with indices is taking 30 hours (worse than the other option)
I am wondering if there is a way to spee
Hi again.
PROCESS LIST is:
Creating tmp file
Repair by sorting
The problem occurs while repair by sorting.
myisam parameters have been increased accordingly:
myisam max extra sort file size = 15000M
myisam max sort file size = 15000M
There must be a bug somewhere? What do you suggest?
Vincent
Hi!
Hi!
On Jul 06, Vincent Bouret wrote:
> Hi,
>
> I got the following values:
> key_buffer_size = 256M
> myisam max extra sort file size = 8000M
> myisam max sort file size = 8000M
> myisam sort buffer size = 128M
>
> But that big table (MYD = 2397 MB), rows = 5 355 866 still won't index
> in full
strange
problem, the fulltext index creation on very huge sets of data doesn't
seem to work.
I have switched from 4.0.14 to 4.0.20 and rebuilt the database from
scratch. I'm adding a lot of data on different tables (4 TEXT
fields/table). At the end, I issue a ALTER TABLE 'tablename
Frank,
We used the 64-bit source to compile 4.0.20, and we used the 32-bit
binaries.
The problem was tracked down at about 1am - it was the kernel (or the
SCSI drivers). We put a 3Ware SATA Raid-5 card in, and all the crashes
went away.
There are 64-bit binaries, but we had some problems with
David,
David Griffiths wrote:
We are in the process of setting up a new MySQL server. It's a
dual-Opteron (Tyan Thunder K8S motherboard) with 6 gig of DDR333 RAM
(registered) and an LSI SCSI card with 6 SCSI drives (5 in a RAID-5
array, with one hot-spare) running SuSE Enterprise 8.1 (64-bit).
We are in the process of setting up a new MySQL server. It's a
dual-Opteron (Tyan Thunder K8S motherboard) with 6 gig of DDR333 RAM
(registered) and an LSI SCSI card with 6 SCSI drives (5 in a RAID-5
array, with one hot-spare) running SuSE Enterprise 8.1 (64-bit).
I loaded all our data (about 2
Sasha,
Thanks for the reply. It's actually during index creation (while creating a
database). I am creating the tables, loading the data, then applying the
indexes and foreign keys.
There are five or six indexes on some tables, the first taking 5 minutes,
the second taking 5:45, etc.
In
David Griffiths wrote:
I was wondering what the bottleneck was. I'm adding a dozen indexes to
the same large-ish InnoDB table. Each successive index takes a bit
longer (45 seconds or so on a dual P3-933 with 2 gig of RAM).
Every time you add a new index or do any non-trivial modification to the sch
I was wondering what the bottleneck was. I'm adding a dozen indexes to
the same large-ish InnoDB table. Each successive index takes a bit
longer (45 seconds or so on a dual P3-933 with 2 gig of RAM).
Is it disk additional tables-space management that is taking the extra
time? Would faster disks h
sily manageable by splitting it out. Because the odds of
you ever needing every single bit of information at one time is very slim.
Donny
> -Original Message-
> From: Chris Fossenier [mailto:[EMAIL PROTECTED]
> Sent: Monday, January 26, 2004 12:23 PM
> To: [EMAIL PROTECTED
Mike,
Thanks for the input. I also received this tip from Peter of the MySQL team.
We'll see if it works.
Chris.
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Monday, January 26, 2004 3:03 PM
To: Chris Fossenier
Cc: MySQL List
Subject: Re: Index Creation Bottle
At 12:23 PM 1/26/2004, you wrote:
We have a large database that consists of 1 table with 400 fields. I know,
the first thing you are thinking is normalize, it's not an option right now
and the database gets reloaded every 3 months. The table contains marketing
data so it is not as easy to normalize
We have a large database that consists of 1 table with 400 fields. I know,
the first thing you are thinking is normalize, it's not an option right now
and the database gets reloaded every 3 months. The table contains marketing
data so it is not as easy to normalize as an employee database, or helpd
On Wed, Jul 16, 2003 at 03:49:51PM -0700, Cory Lamle wrote:
> Contents are Direct Alliance Corporation CONFIDENTIAL
> -
> I have a table with 10 cols. 8 of which all need to be searched on
> independently of each other.
>
> Does creating 8 separate indexes for that table affect the speed of how
>
Contents are Direct Alliance Corporation CONFIDENTIAL
-
I have a table with 10 cols. 8 of which all need to be searched on
independently of each other.
Does creating 8 separate indexes for that table affect the speed of how
mysql searches each index?
In other words would keeping my indexes to on
temporary table is only 640MB / 2300MB original table
> size.
MySQL 3.23, right ?
> I've changed myisam_sort_buffer to 128M in the hopes that that may have
> sped things up, but to no avail.
It does not apply here.
> Does anyone have a) an explanation for why the index creat
I've changed myisam_sort_buffer to 128M in the hopes that that may have sped
things up, but to no avail.
Does anyone have a) an explanation for why the index creation seems to go
slower and slower the more rows there are or b) any ideas on how to sped up
the index creation.
Regards, Peter Br
http://marc.theaimsgroup.com/?l=mysql&m=99257445520048&w=2 may help
> OK, another one here...
>
> On the very large table (at least 5 million rows) I need to index
> several columns. The mysqladmin -i60 processlist
>
> lists the state of the query as
>
> Repair with keycache
>
> for a ver
OK, another one here...
On the very large table (at least 5 million rows) I need to index several columns.
The mysqladmin -i60 processlist
lists the state of the query as
Repair with keycache
for a very long time. I imagne it's sorting the indexes somehow.
My question is what variable wil
Hi!
> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes:
Peter> Hello mysql,
Peter> Today I played a little bit with two different ways of backup -
Peter> first one is to use BACKUP TABLE (which works for myisam only) and
Peter> the second one is SAVE DATA/LOAD DATA.
Peter> In bo
Hi!
At 05:44 PM 6/1/01 +0400, you wrote:
>Hello mysql,
>
> Today I played a little bit with two different ways of backup -
> first one is to use BACKUP TABLE (which works for myisam only) and
> the second one is SAVE DATA/LOAD DATA.
>
> In both cases if I'm not mistaken the file is wrote by m
Peter Zaitsev writes:
> Hello mysql,
>
> Today I played a little bit with two different ways of backup -
> first one is to use BACKUP TABLE (which works for myisam only) and
> the second one is SAVE DATA/LOAD DATA.
>
> In both cases if I'm not mistaken the file is wrote by mysqld server
Hello mysql,
Today I played a little bit with two different ways of backup -
first one is to use BACKUP TABLE (which works for myisam only) and
the second one is SAVE DATA/LOAD DATA.
In both cases if I'm not mistaken the file is wrote by mysqld server
so there is no communication overh
have
imported the table.
You should also set Innobase log files very big, say 150 MB, to
reduce checkpointing and disk i/o during the import.
Delayed index creation is technically not very difficult to implement,
but on the TODO list it comes probably after several other items.
Innobase already
45 matches
Mail list logo
