Importing the local .tsv.bz2 file via unark() to a remote AWS RDS MySQL Database is taking an unusual amount of time

[GitHunter0]

Importing the local .tsv.bz2 file via unark() to a remote AWS RDS MySQL Database is taking an unusual amount of time

[GitHunter0]

DBI::dbWriteTable() is now using LOAD DATA LOCAL INFILE as a backend and working perfectly for remote MySQL databases, as discussed here r-dbi/RMariaDB#265 .

And as I suspected, arkdb is now working flawlessly as well. The only requirement is to use this version r-dbi/RMariaDB#267, however they will probably soon merge to the main branch and upload to CRAN.

@cboettig , thanks again!

[cboettig]

I don't have an AWS RDS MySQL db handy to test unfortunately. You might simplify things further by (a) testing with an uncompressed file and (b) testing with a trivially small dataset such that you can run profvis() or equivalent profiling to see where the slowest steps are.

It looks like you alternative method may not be entirely analogous though, it seems you are just writing df[1,] there?

Also sounds like you suspect part of the issue is related specifically to RMariaDB -- is there a part that you think is specifically arkdb-related? (arkdb really doesn't do much beyond just calling dbWriteTable in chunks; and this data is small enough to write in a single chunk, so like you I suspect the performance issue must be something specific to MariaDB or the R driver thereof. Honestly I have almost never touched MySQL since discovering duckdb and arrow....).

[GitHunter0]

Thank you very much for your feedback, @cboettig .

Some very small datasets worked but way slower than LOAD LOCAL DATA INFILE.

I used df[1,] just to make a boilerplate for the table since LOAD LOCAL DATA INFILE requires a preexisting table.

I agree with you that the problem is probably solely related to RMariaDB , I will try to work the issue there.

[GitHunter0]

Hey @cboettig , r-dbi/RMariaDB#265 was solved.
So I will just leave you a suggestion if I may.
Adding an option to use LOAD LOCAL DATA INFILE method as unark() backend would solve this freezing and slowness issue when dealing with remote MySQL databases.
Thank you

[cboettig]

Thanks for the heads up! A PR to add this for MySQL-type remotes would be welcome.

[GitHunter0]

@cboettig, soon I will take a look in the inner workings of arkdb to see if I can do that, thank you!

[GitHunter0]

@cboettig, soon I will take a look in the inner workings of arkdb to see if I can do that, thank you!

For what've seen the rightful solution would be to add LOAD DATA LOCAL INFILE method to DBI::dbWriteTable() backend, so I will discuss this possibility with the DBI contributors.

[cboettig]

makes total sense, thanks!

[cboettig]

awesome, thanks for reporting this!