community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Writing Output to MySQL is incredibly slow

Meteoroid

Problem:

I am attempting to write ~100k records to a MariaDB database and it is incredibly slow.

Internet is 400d/150up

Transaction size is set to 10000

30 fields non-TEXT/BLOB datatypes

 

Hardware:

AWS Server with 1800 IOPS, 2 Cores, 15gb Ram

 

Actual Performance: 30-50 IOPS

 

I remember coming across some best practices for troubleshooting database performance but can't seem to find it now.

 

Any help would be greatly appreciated. I have more experience with postgresql but I am currently at a mysql shop.

 

Update: Attached a screenshot of my task manager. Network is only doing 0.1mbps

 

 

ACE Emeritus
ACE Emeritus

Hi @chudson

Can you describe the workflow in more detail? Writing inside a macro or anything like that?  Or in general, can you recreate the problem with a block-until-done tool where the first output does all the processing, and the second output does your database write in one big barrage?  That should hopefully isolate the output step from any prior processing.

Hope that helps!

John

 

 

Meteoroid

@JohnJPS

 

Thanks for the response!

 

So I ended up writing to a .csv and then writing to the database. It is still taking ~5min to write ~3600 records even after utilizing the 'Block-Until-Done' node.

 

Statistics:
Info: Output Data (158): ODBC Driver version: 03.51
Info: Output Data (158): 3423 records have been successfully committed.
Info: Output Data (158): 3423 records were written to odbc:DSN=Marketing (digital.adwords_campaign)
Info: Output Data (158): Profile Time: 287746.09ms, 99.97% <== Database Write
Info: Input Data (169): Profile Time: 86.31ms, 0.03%
Info: Block Until Done (170): Profile Time: 4.25ms, 0.00%

 

I'm fairly certain its a database issue because I have another workflow that connects to a wordpress database managed by wpengine and I push 1k - 4k records weekly and it takes seconds. Maybe I should just contact support and ask for their database config since they also use MySQL :D

ACE Emeritus
ACE Emeritus

Yep, I think you're right.  Sorry to say I'm not a MySQL guru either.  :-(

Bolide
Bolide

I can say that i've experienced this and my work around is to push all the data to a local database then sqldump to the remote server. I regularly upload 100MM + records to my remote servers and this is the only way i've been able to manage that transfer. I'm thinking it's something with the drivers, but i'm really not a database architect.

Labels