On Feb 12, 2014, at 12:49 AM, Pierre <pi...@hotmail.it> wrote:
Ok I understand, but this doesn't resolve the initialization problem, I'm sure
there is a bug, or something which can be improved a lot. I can't use the
UNIQUE constraint when I have to much key which doesn't fit in RAM.
Because even If have memcache/bloom filter in front, I still need to create my
initial dataset with the unique constraint,and even if my initial dataset is
unique, I can't insert them in my table because of the bug explained before.
If mysql can do a sort so quickly (create index), it should be trivial to reuse
this sorted data, eliminate the duplicate and create the UNIQUE index.
Le 12/02/2014 09:24, Justin Swanhart a écrit :
Hi,
This is not a bug, but how b tree indexes work. For them to be efficient they
must fit in ram.
There are buffering mechanisms that can be used for secondary indexes in
some cases, because the
write can be done without a read, but ONLY when the index is not unique. It if
it unique, then the
index dive is necessary and a btree traversal can take multiple random IO to
get to the leaf node.
Faster IO can help, so you can look into flash storage.
Consider fronting your lookups with memcache (a hash index) or a bloom filter
(there are many
implementations on the net) to reduce lookups.
--Justin
Le 12/02/2014 09:04, Pierre a écrit :
Using this technique I have the same Issue. It's now running for severals
hours, I'm at a 40% and
looking at show full processlist, it's getting slower and slower. It will never
finish.
I think there is a bug here.
Firstly, regardly the memory usage, It doesn't correctly use the buffer I did
set, I think it's only
using the key_buffer_size. myisam_sort_buffer_size or bulk_insert_buffer_size
are not used on this
task.
So what's happening ? When the RAM is full, mariadb is doing random access on
the disk to sort and
filter Go of data ! That's why my cpu was only used a few percent, the process
was in i/o wait most
of the time.
So what I am saying here is : mariadb can't crate UNIQUE keys/index if the rows
doesn't fit in RAM.
However if I try to create a standard index (ie non unique), it works well and
it's done in less
than an hour.
ALTER IGNORE TABLE mytable ADD INDEX (c1), ADD INDEX(c2);
Query OK, 349086532 rows affected (44 min 25.21 sec)
Records: 349086532 Duplicates: 0 Warnings: 0
In this second usage case, Maria is doing a good work by using the
myisam_sort_buffer_size. I think
it's doing something like an external/merge sort, spliting the rows in part
that fit in RAM, sorting
them, merging them and creating index.
It was 100% cpu most of the time, when It was not it was because mysql was
loading the rows in RAM
from hard disk (and not doing a random access on the hard disk like in create
unique index). So why
UNIQUE index is not behaving the same way ?
It's easy to reproduce the bug, just create a binary file of 2 or 3 x size of
RAM, then load data
infile and try to create a UNIQUE index on it. It will never end.
Am 10.02.2014 13:45, schrieb Pierre:
Mariadb is getting slower and slower when I'm inserting a massive amout of
data. I'm trying to
insert 166 507 066
rows (12go) using load data infile '' into an empty table. I splitted my file
in 13 parts of the
same size because
it was too long to insert in one shot. When I inserted more than 100M rows, it
starts to be
extremely slow.
he reason are the permamently updated keys
in general for large inserts on a new table
UNIQUE KEY `p1` (`p1`),
UNIQUE KEY `p2` (`p2`),
* remove keyes
* insert data
* add kyes
https://dev.mysql.com/doc/refman/5.5/en/insert-speed.html
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
