Gino: Inserting millions of rows. Much much slower than SQLite. Am I doing it wrong? How can I improve throughput?

[brizzbane]

• GINO version: 0.8.3
• Python version: 3.7.3
• asyncpg version: 0.18.3
• aiocontextvars version: 0.2.2
• PostgreSQL version: 9.6

Description

I am building something similar/based on: https://github.com/p3pperp0tts/leaks_parser.

Parsing GB of text files, and inputting to database. The difference between what I am doing, and p3pperp0tts/leaks_parser, is that I am only inserting emails/passwords, and I have a constraint for unique email/password combo (could that be what is causing such a slow down?).

p3pperp0tts/leaks_parser parser goes much much faster, parsing files very quickly. i.e. to parse a 500mb compressed .tar.gz archive of txt files, to a 7.9 GB SQLite databse.. ..maybe a few minutes.

What I Did

 for line in read_file.read().splitlines():
     email, password = parseline(line)
     if email:
          save_credentials = await Credentials.create(id_data_archive_file=data_archive_file.id, email=email, password=password)

Whereas a single text file in an archive inserting into postgresql... doing the above does maybe 10k rows in ...20-30 seconds (guess-timating). It's a really large difference though when I am trying to go through millions of rows.

_Just wondering why SQLite is so much faster_.. is there something I am doing wrong? Is there another way I should be trying to accomplish this?

I was expecting PostgreSQL to be on par with SQLite ... but I'm pretty newb to working with databases.

[Pentusha]

Each your query make network IO for transfer your data to postgresql server, planing time, execution time + IO for receive response from server. You can multiply this time by the number of your queries and then you will get your result. All your queries are similar to each other and their execution plan will be similar too.
A possible solution would be to insert records in batches, rather than one at a time. It will reduce planning, execution and IO cost. Its possible to adjust batch size to balance between insertion performance and memory consumption.
https://github.com/fantix/gino/blob/master/tests/test_executemany.py
or:

from sqlalchemy.dialects.postgresql import insert

save_credentials: List[Credentials] = await (
    insert(Credentials)
    .values([{
        'id_data_archive_file': credential.id_data_archive_file,
        'email': credential.email,
        'password': credential.password,
    } for credential in credentials])
    .on_conflict_do_nothing()
    .returning(Credentials.__table__)
    .gino
    .load(Credentials)
    .all()
)

Also, index may may cause slow insertion. Sometimes is faster to temporary remove table indexes during insertion of huge amount of rows.

[mikekeda]

for line in read_file.read().splitlines():
replace with:
for line in read_file:
to use less memory

Also could you provide profiler results?

[brizzbane]

for line in read_file.read().splitlines():
replace with:
for line in read_file:
to use less memory

Also could you provide profiler results?

I'm aware of what you are saying, right now just using read().splitlines() for convenience. I plan on switching to the buffered generator later on. But I can't imagine that this should effect inserting into PostgreSQL at all.., or if it does, it shouldn't in for the order of magnitude that the slow down is compared to SQLite... ?

I really was surprised that it was such a HUGE difference compared to using SQLite.

I'm not sure how to go about providing profiler results?

[mikekeda]

Check this package - https://github.com/what-studio/profiling
You could run your program with
profiling live-profile parser.py
And check where you are spending most of time (but probably it will be db).
https://github.com/what-studio/profiling#live-profiling

[brizzbane]

I've not yet tried what @Pentusha has suggested.

The live-profiling...it doesn't show much, it just shows asyncio.run_until_complete ...I'm guessing that if I pull that apart a bit it may show more?

CPU 18.2% (184ms/1.0sec)                                               parse.py
FUNCTION                            CALLS    OWN  /CALL %      DEEP  /CALL %   
+ run_until_complete (asyncio.base    n/a      0      0 0.00  137ms      0 74.5

[brizzbane]

From intial testing, it looks like what @Pentusha suggested will work. Kind of feel like I don't know what I'm doing ... but I think I'm headed in the right direction for increasing throughput substantially.

Thank you @Pentusha, and @mikekeda :)

[wwwjfy]

Monitor server metrics and DB metrics, and checking logs in postgresql, especially slow queries, may also help.

[wwwjfy]

Closing due to inactivity. Feel free to reopen.