I got an "interesting" problem with creation of indexes on MyISAM
tables in MySQL 5.6.15 and MySQL 5.6.14 running on FreeBSD 8.4 for float
columns - I am not able to create indexes on these columns
Indexes on all other columns work just fine
The problem occur while I was loading data from MySQL
On 05/07/2012 12:30 PM, Zhangzhigang wrote:
Thanks, i thought about this answer in the past, and i appreciate your reply.
How about the omelet?
What's your method?
--
RMA.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/m
" that aggregate data to make
"reports" more efficient. (I have seen 10x to 1000x performance improvement.)
Should we discuss this?
> -Original Message-
> From: Karen Abgarian [mailto:a...@apple.com]
> Sent: Monday, May 07, 2012 8:37 PM
> To: mysql@lists.mysql.c
ck James
Cc: mysql@lists.mysql.com
Subject: 回复：Why is creating indexes faster after inserting massive data rows?
James...
>* By doing all the indexes after building the table (or at least all the
>non-UNIQUE indexes), "sort merge" can be used. This technique had been highly
>
enchmark _*your*_ case.
>
> ** **
>
> *From:* Claudio Nanni [mailto:claudio.na...@gmail.com]
> *Sent:* Wednesday, May 09, 2012 8:34 AM
> *To:* Rick James
> *Cc:* Zhangzhigang; mysql@lists.mysql.com
> *Subject:* Re: 回复： Why is creating indexes faster after inserting massive
> data rows?
e will have to hit disk.
> If you are using normal disks, that is on the order of 125 rows per second
> that you can insert �C Terrible! Sortmerge is likely to average over 10,000.
>
>
>
> From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn]
> Sent: Tuesday, May 08, 2012 9:13
rows per second that
you can insert – Terrible! Sortmerge is likely to average over 10,000.
From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn]
Sent: Tuesday, May 08, 2012 9:13 PM
To: Rick James
Cc: mysql@lists.mysql.com
Subject: 回复： Why is creating indexes faster after inserting massive data
dseparately. it wastes some performance.
Does it?
发件人： Rick James
收件人： Johan De Meersman ; Zhangzhigang
抄送： "mysql@lists.mysql.com"
发送日期： 2012年5月8日, 星期二, 上午 12:35
主题: RE: Why is creating indexes faster after inserting massive data rows?
* Batch INSERTs run fas
Oh... I thought that it uses it's own buffer cache as same as the InnoDB. I
have got a mistake for this, thanks!
发件人： Karen Abgarian
收件人： mysql@lists.mysql.com
发送日期： 2012年5月9日, 星期三, 上午 2:51
主题: Re: 回复： 回复： 回复： Why is creating indexes faster after inse
Hi,
If MyISAM tables were being written directly to disk, the MyISAM tables would
be so slow that nobody would ever use them.That's the cornerstone of their
performance, that the writes do not wait for the physical I/O to complete!
On May 8, 2012, at 3:07 AM, Johan De Meersman wrote:
>
Ok, thanks for your help.
发件人： Johan De Meersman
收件人： Zhangzhigang
抄送： mysql@lists.mysql.com; Karen Abgarian
发送日期： 2012年5月8日, 星期二, 下午 6:07
主题: Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data
rows?
- Original Message
- Original Message -
> From: "Zhangzhigang"
>
> As i known, the mysql writes the data to disk directly but does not
> use the Os cache when the table is updating.
If it were to use the OS cache for reading but not writing, then the OS cache
would be inconsistent with the underlying file
- Original Message -
> From: "Zhangzhigang"
>
> The mysql does not use this approach what you said which is
> complicated.
>
> I agree with ohan De Meersman.
Umm... It's not a matter of who you agree with :-) Karen's technical detail is
quite correct; I merely presented a simplified pic
： Why is creating indexes faster after inserting massive data
rows?
Honestly, I did not understand that. I did not say anything about being
complicated. What does mysql not use, caching??
Judging by experience, creating a unique index on say, a 200G table could be a
bitter one.
On 07
ysql does not use this approach what you said which is complicated.
>
> I agree with ohan De Meersman.
>
>
>
> 发件人： Karen Abgarian
> 收件人： mysql@lists.mysql.com
> 发送日期： 2012年5月8日, 星期二, 上午 1:30
> 主题: Re: 回复： Why is creating indexes fast
Karen...
The mysql does not use this approach what you said which is complicated.
I agree with ohan De Meersman.
发件人： Karen Abgarian
收件人： mysql@lists.mysql.com
发送日期： 2012年5月8日, 星期二, 上午 1:30
主题: Re: 回复： Why is creating indexes faster after inserting massive
e faster.
>
> Based on this discussion, you should note that "random" indexes, such as
> GUIDs, MD5s, etc, tend to
>
>
>> -Original Message-
>> From: Karen Abgarian [mailto:a...@apple.com]
>> Sent: Monday, May 07, 2012 10:31 AM
>> To: mysql@list
e and to sort all rows by the index key. The latter process will be the
most determining factor in answering the original question, because for the
large tables the sort will have to do a lot of disk I/O.The point I am
trying to make is there will be situations when creating indexes and
ce for the index info.
InnoDB does something similar, but it is limited to the size of the buffer_pool.
> -Original Message-
> From: Johan De Meersman [mailto:vegiv...@tuxera.be]
> Sent: Monday, May 07, 2012 8:06 AM
> To: Zhangzhigang
> Cc: mysql@lists.mysql.com
> Subj
an [mailto:vegiv...@tuxera.be]
> Sent: Monday, May 07, 2012 1:29 AM
> To: Zhangzhigang
> Cc: mysql@lists.mysql.com
> Subject: Re: Why is creating indexes faster after inserting massive
> data rows?
>
> - Original Message -----
> > From: "Zhangzhigang"
>
- Original Message -
> From: "Zhangzhigang"
> Ok, Creating the index *after* the inserts, the index gets created in
> a single operation.
> But the indexes has to be updating row by row after the data rows has
> all been inserted. Does it work in this way?
No, when you create an index on
发送日期： 2012年5月7日, 星期一, 下午 4:59
> 主题: Re: 回复： Why is creating indexes faster after inserting massive data
> rows?
>
> On 2012/05/07 10:53, Zhangzhigang wrote:
> > johan
> >> Plain and simple: the indices get updated after every insert statement,
> > whereas if y
Thanks, i thought about this answer in the past, and i appreciate your reply.
发件人： Alex Schaft
收件人： mysql@lists.mysql.com
发送日期： 2012年5月7日, 星期一, 下午 4:59
主题: Re: 回复： Why is creating indexes faster after inserting massive data rows?
On 2012/05/07 10:53
回复： Why is creating indexes faster after inserting massive data rows?
Creating the index in one time is one macro-sort operation,
updating the index at every row is doing the operation on and on again.
If you do not understand the difference I recommend you to read some basics
about sorting algori
___
> 发件人： Johan De Meersman
> 收件人： Zhangzhigang
> 抄送： mysql@lists.mysql.com
> 发送日期： 2012年5月7日, 星期一, 下午 4:28
> 主题: Re: Why is creating indexes faster after inserting massive data rows?
>
> - Original Message -
> > From: "Zhangzhigang"
> >
>
On 2012/05/07 10:53, Zhangzhigang wrote:
johan
Plain and simple: the indices get updated after every insert statement,
whereas if you only create the index *after* the inserts, the index gets
created in a single operation, which is a lot more efficient..
Ok, Creating the index *after* t
com
发送日期： 2012年5月7日, 星期一, 下午 4:28
主题: Re: Why is creating indexes faster after inserting massive data rows?
- Original Message -
> From: "Zhangzhigang"
>
> Creating indexes after inserting massive data rows is faster than
> before inserting data rows.
> Please te
- Original Message -
> From: "Zhangzhigang"
>
> Creating indexes after inserting massive data rows is faster than
> before inserting data rows.
> Please tell me why.
Plain and simple: the indices get updated after every insert statement, whereas
if you only cre
insert
all data rows firstly and then create indexes. Normally, the sum using
time(inserting data rows and creating indexes) of first way is longer than the
second way.
Please tell me why?
发件人： Ananda Kumar
收件人： Zhangzhigang
抄送： "mysql@lists.mysq
which version of mysql are you using.
Is this secondary index.?
On Mon, May 7, 2012 at 12:07 PM, Zhangzhigang wrote:
> hi all:
>
> I have a question:
>
> Creating indexes after inserting massive data rows is faster than before
> inserting data rows.
> Please tell me why.
>
Joris Kinable wrote:
Good evening,
I've got to create a very large table: 180GB of data has to be stored.
In order to to this I'm using the following steps:
1. Create database structure including keys.
2. Disable keys: ALTER TABLE ut_netflow_4 DISABLE KEYS
3. Load data into the database.
4. Gen
Good evening,
I've got to create a very large table: 180GB of data has to be stored.
In order to to this I'm using the following steps:
1. Create database structure including keys.
2. Disable keys: ALTER TABLE ut_netflow_4 DISABLE KEYS
3. Load data into the database.
4. Generate all index keys: m
At 05:42 PM 10/3/2006, you wrote:
Dan Nelson wrote:
In the last episode (Oct 03), Angelo Zanetti said:
I've got a database that has a few thousand rows, I've noticed that
some of the search queries (especially the large ones) are taking
some time. Im looking at adding indexes to my tables
Dan Nelson wrote:
In the last episode (Oct 03), Angelo Zanetti said:
I've got a database that has a few thousand rows, I've noticed that
some of the search queries (especially the large ones) are taking
some time. Im looking at adding indexes to my tables in order to
speed up the data retr
In the last episode (Oct 03), Angelo Zanetti said:
> I've got a database that has a few thousand rows, I've noticed that
> some of the search queries (especially the large ones) are taking
> some time. Im looking at adding indexes to my tables in order to
> speed up the data retrieval.
>
> My ques
Angelo, results should be (nearly) immediate. When you add an index,
MySQL creates an index for the existing data in your table. Later,
when data is added/updated/deleted, the index is updated
simultaneously.
With a few thousand rows, you should be able to get by adding a few
indexes where they
Hi all,
I've got a database that has a few thousand rows, I've noticed that some
of the search queries (especially the large ones) are taking some time.
Im looking at adding indexes to my tables in order to speed up the data
retrieval.
My question is as follows: At this point in time if I add
"Ângelo M. Rigo" <[EMAIL PROTECTED]> wrote on 01/27/2005 03:04:15
PM:
> Hi
>
> I have an aplication wich is opening to many connections even i am
> using persistent connectins and closing every connection i do open
>
> I have created indexes in all the fields i supose they are needed
>
> I
: Creating indexes
Thank´s again for sharing your mysql experience !
may you can point me if my numbers are too high?
best regards!!
records size
TABLE1 225,893 InnoDB 54.6 MB
TABLE2 611
Hi
I have an aplication wich is opening to many connections even i am using
persistent connectins and closing every connection i do open
I have created indexes in all the fields i supose they are needed
I would like to know if i can and how can i measure where indexes are needed or
where
Did we already talk about the log flush method you're using with
InnoDB? I don't recall...
Log flush method? As described by Mysql documentation:
If you can afford the loss of some latest committed transactions, you
can set the `my.cnf' parameter |innodb_flush_log_at_trx_commit| to 0.
|In
OK, I'll qualify the statement. Software RAID-5 on my adaptec SCSI
controller and external disk array logs a message "aic7xxx_abort returns
0x2003" to /var/log/messages and the whole array shuts down (and
anything else attached to the card, regardless of bus) for minutes at a
time before resta
On Fri, Nov 07, 2003 at 05:03:43PM -0600, William Baker wrote:
> Sorry for the slow reply. I was battling SCSI controller bugs as well
> as database issues. I have given up on the software raid for now
> because it is unstable.
Really? I've run Linux software RAID quite happily on several sys
Sorry for the slow reply. I was battling SCSI controller bugs as well
as database issues. I have given up on the software raid for now
because it is unstable.
Back to the subject at hand: performance.
You are right, the "load" is meaningless outside the context of a
specific machine...and of
Hi,
When creating indices, MyIsam needs a big sort buffer (See MySQL Manual:
" myisam_sort_buffer_size: The buffer that is allocated when sorting the
index when doing a REPAIR or when creating indexes with CREATE INDEX or
ALTER TABLE. ").
I don't know how works ISAM tables,
On Mon, Nov 03, 2003 at 01:35:26PM -0600, William Baker wrote:
> It's hard to tell. The CPU is under a reasonable load (uptime shows 1.0
> - 2.0), no swapping, and the hard drive is churning away continually.
The "load average" is relatively meaningless. What's the actualy CPU
utilization as sh
On Mon, Nov 03, 2003 at 01:37:26PM -0600, William Baker wrote:
> Now why didn't I think of a single alter tablethat should certainly
> improve things. I'll give it a try.
Oh, yeah. That will probably help A LOT.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
<
Now why didn't I think of a single alter tablethat should certainly
improve things. I'll give it a try.
bbaker
William Baker wrote:
I am using a pentium4-2GHz machine with Linux-RH9 installed and 1GB
RAM. The database is on a dedicated SCSI drive with an Adaptec
UltraScsi3 controller
It's hard to tell. The CPU is under a reasonable load (uptime shows 1.0
- 2.0), no swapping, and the hard drive is churning away continually.
One thing that makes me think I am doing something wrong is that if I
build the indexes on a 60MB file, it still takes a considerable amount
of time (6-
On Mon, Nov 03, 2003 at 11:18:55AM -0600, William Baker wrote:
> I am using a pentium4-2GHz machine with Linux-RH9 installed and 1GB
> RAM. The database is on a dedicated SCSI drive with an Adaptec
> UltraScsi3 controller which shows 40MHz bus connecting the 10K-RPM
> disks. (Fairly new, fairl
William Baker wrote:
I am using a pentium4-2GHz machine with Linux-RH9 installed and 1GB
RAM. The database is on a dedicated SCSI drive with an Adaptec
UltraScsi3 controller which shows 40MHz bus connecting the 10K-RPM
disks. (Fairly new, fairly capable, low-end server grade.)
I have a 2GB
I am using a pentium4-2GHz machine with Linux-RH9 installed and 1GB
RAM. The database is on a dedicated SCSI drive with an Adaptec
UltraScsi3 controller which shows 40MHz bus connecting the 10K-RPM
disks. (Fairly new, fairly capable, low-end server grade.)
I have a 2GB datafile with 10 indexe
Bonjour,
When I insert a lot of rows in a table from another table in MyISAM
format it seems that indexes in the destination table are not
updated.
For example in table A I have index 1. This table contains many
rows.
I have table B with same structure as table A but em
If i need to create / drop indexes from MyISAM tables how does the blocking
work?
I need to mess with some indexes on a production database, and dont want
downtime.
Should i do this in the middle of the night, when hardly anyone is using the
system, or will the blocking be negligable?
thanks
sea
Thomas,
Monday, September 09, 2002, 10:12:01 PM, you wrote:
TS> just wanting some headsup on the following question.
TS> How does the index_priv catch in at all?
TS> Does a missing index_priv also block me to create a table with indexes in the
create-statement?
TS> Or does it only affect the lat
Hi folks,
just wanting some headsup on the following question.
How does the index_priv catch in at all?
Does a missing index_priv also block me to create a table with indexes in the
create-statement?
Or does it only affect the later creating of indexes?
Thanks in advance,
Thomas
sql, query
Is there a way to change the directory used when mySQL
copies the table for creating indexes on large tables?
My tmp directory is partitioned for 509 megs and
adding an index via ATLER TABLE or CREATE TABLE yields
this:
ERROR 3: Error writing file '/tmp/STFgNG04' (Errcode:
28)
the
On Wed, Oct 03, 2001 at 05:39:09PM +, Mike Lucente wrote:
> I'm also unable to create files >2GB with mysqldump, even after a
> recompile with gcc 2.96, RH 7.1 w/2.4.2 kernel, glibc 2.2.2.
AFIAK, it is limitation of ext2 file sistem.
You can't create any file lager then 2GB on ext2.
[skip]
the tables with symlinks, MySQL will put the new file
> > > (most of the time?) in the configured data directory and not where the
> > > symlink points.
> > >
> > > --Bill
> > >
> > >
> > >
> > > Mike Lucente wrote:
&
> limit on 2.2.x kernels?
> >
> > Or, if you have moved the tables with symlinks, MySQL will put the new file
> > (most of the time?) in the configured data directory and not where the
> > symlink points.
> >
> > --Bill
> >
> >
> >
> >
Hi!
On Oct 03, Mike Lucente wrote:
> I'm running out of space while creating indexes on some fairly large (1.8
> GB) tables, even though I have quite a bit of space available in the
> partition (utilization is at 30%).
>
> I know that the create process works as foll
0
-Original Message-
From: Mike Lucente [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 03, 2001 1:25 PM
To: Adams, Bill TQO
Cc: [EMAIL PROTECTED]
Subject: Re: Creating indexes on large tables
I'm running 2.4.2 and I'm not using symlinks.
On Wed, 3 Oct 2001, Adams, Bill TQO
s?
>
> Or, if you have moved the tables with symlinks, MySQL will put the new file
> (most of the time?) in the configured data directory and not where the
> symlink points.
>
> --Bill
>
>
>
> Mike Lucente wrote:
>
> > I'm running out of space while
t; (most of the time?) in the configured data directory and not where the
> symlink points.
>
> --Bill
>
>
>
> Mike Lucente wrote:
>
> > I'm running out of space while creating indexes on some fairly large (1.8
> > GB) tables, even though I have quite a
David Turner wrote:
> If this is the case. What are the steps necessary for index rebuilds? Can
> I specify where the index file is rebuilt? Any idea when we can specify
> the location of datafiles and indexfiles?
For the kernel: You either need to upgrade to a later 2.4.x series kernel which,
I
> Mike Lucente wrote:
>
> > I'm running out of space while creating indexes on some fairly large (1.8
> > GB) tables, even though I have quite a bit of space available in the
> > partition (utilization is at 30%).
> >
> > I know that the create process works a
:
> I'm running out of space while creating indexes on some fairly large (1.8
> GB) tables, even though I have quite a bit of space available in the
> partition (utilization is at 30%).
>
> I know that the create process works as follows (from the manual):
>
> Create a new ta
I'm running out of space while creating indexes on some fairly large (1.8
GB) tables, even though I have quite a bit of space available in the
partition (utilization is at 30%).
I know that the create process works as follows (from the manual):
Create a new table named `A-xxx'
68 matches
Mail list logo
