effects of load data local on RSS

[Michael Widenius]

Hi!

>>>>> "Zardosht" == Zardosht Kasheff <zard...@gmail.com> writes:

Zardosht> Hello all,
Zardosht> We have noticed something peculiar to our storage engine, but could
Zardosht> not reproduce with InnoDB. We notice when a user runs load data local
Zardosht> infile, that a lot of memory that we cannot account for gets used by
Zardosht> the system, sometimes leading the system to crash from being low on
Zardosht> memory. However, when running load data infile ignore, we see no such
Zardosht> issues.

Zardosht> As far as I can tell, the settings for the handler are the same for
Zardosht> load data local infile and load data infile ignore. Both load many
Zardosht> rows within the context of a single statement, and both set the flag
Zardosht> HA_EXTRA_IGNORE_DUP_KEY in handler::extra. Otherwise, as far as the
Zardosht> handler is concerned, these two look identical. Yet one utilizes much
Zardosht> more RSS than the other, and we do not see this behavior with InnoDB.

table->file->extra(HA_EXTRA_IGNORE_DUP_KEY) is only called if IGNORE
or DUP_REPLACE (replace conflicting rows) is used.

Zardosht> Does anyone have any thoughts on what may be happening?

This is a speculation, but it's true for MyISAM and could be true for
InnoDB.

When one is not using 'IGNORE', one can optimize the LOAD DATA into an
empty file as follows:

- Disable all indexes (except primary key if the index is clustered)
- Insert all rows, not updating indexes
- Scan all rows and create the indexes by sorting keys.
- This can be either done all keys at once or key by key

The above will use more memory for creating the indexes, but it will
be notable faster than inserting rows one by one.

When using IGNORE the above can't be done as we need to skip all rows
that has a duplicate key.

MyISAM and Aria both optimize LOAD DATA the above way.
I think that newer InnoDB can do this too.

Regards,
Monty

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

[Michael Widenius]

Hi!

>>>>> "Zardosht" == Zardosht Kasheff <zard...@gmail.com> writes:

Zardosht> As I understand it, LOAD DATA LOCAL acts basically like ignore. It
Zardosht> sets table->file->extra(HA_EXTRA_IGNORE_DUP_KEY).

Where does it do that?
(LOCAL should not have anything to do with IGNORE).

The code we have in MariaDB 5.5 is:

if (ignore ||
handle_duplicates == DUP_REPLACE)
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);

This has nothing to do with LOCAL.

Zardosht> What I am wondering is why would LOAD DATA LOCAL use more RSS than
Zardosht> LOAD DATA IGNORE for us, but not for InnoDB. Does anyone have any
Zardosht> theories?

RSS?

As LOCAL and IGNORE are independent flags that have nothing to do with
each other, I can't answer your question.

My previous answer should cover most things about using or not using IGNORE.

[Zardosht Kasheff]

Here is a stack trace where for LOAD DATA LOCAL, we get a call to
handler::extra with operation set to HA_EXTRA_IGNORE_DUP_KEY

#0 ha_tokudb::extra (this=0x1aaf240,
operation=HA_EXTRA_IGNORE_DUP_KEY) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/storage/tokudb/ha_tokudb.cc:5949
#1 0x000000000084be8a in mysql_load (thd=0x1a14d20, ex=0x1acc190,
table_list=0x1acc218, fields_vars=..., set_fields=..., set_values=...,
handle_duplicates=DUP_ERROR, ignore=true,
read_file_from_client=true) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_load.cc:474
#2 0x00000000005dcfb5 in mysql_execute_command (thd=0x1a14d20) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_parse.cc:3173
#3 0x00000000005e32a6 in mysql_parse (thd=0x1a14d20,
rawbuf=0x1acbfd0 "load data local infile
'/nfs/tmcsrv/sysbench-mysqldump-50000000/sbtest.txt' into table
sbtest1 fields terminated by ',' enclosed by '\"'", length=135,
parser_state=0x536e0910)
at /home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_parse.cc:5627
#4 0x00000000005d7f0c in dispatch_command (command=COM_QUERY,
thd=0x1a14d20, packet=0x1ab1cd1 "", packet_length=135) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_parse.cc:1037
#5 0x00000000005d729d in do_command (thd=0x1a14d20) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_parse.cc:773
#6 0x00000000006b2b49 in do_handle_one_connection (thd_arg=0x1a14d20)
at /home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_connect.cc:840
#7 0x00000000006b2676 in handle_one_connection (arg=0x1a14d20) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_connect.cc:759
#8 0x000000310e60677d in start_thread () from /lib64/libpthread.so.0
#9 0x000000310dad325d in clone () from /lib64/libc.so.6
#10 0x0000000000000000 in ?? ()

[Michael Widenius]

hi!

>>>>> "Zardosht" == Zardosht Kasheff <zard...@gmail.com> writes:

Zardosht> Here is a stack trace where for LOAD DATA LOCAL, we get a call to
Zardosht> handler::extra with operation set to HA_EXTRA_IGNORE_DUP_KEY

<cut>

I missed the following code (was not totally obvious):

/* We can't give an error in the middle when using LOCAL files */
if (read_file_from_client && handle_duplicates == DUP_ERROR)
ignore= 1;

So yes, LOAD DATA LOCAL has an implicite IGNORE.