Im developing an offline application with Visual Basic on Win7, 64bit. I use MS Visual Studio 2010 Express, MySQL Connector Net 6.3.5(32bit) and MySQL 5.1 (64bit).
I have to insert a lot of records (+20.000.000) given by a XML streamer. To increase speed I already lock tables and disable keys.
During testing I have recognized that MySQl doesn’t seem to use more than 2GB. I have installed 8GB. How can I pass through the 2GB limit? Is the connector the limitation? Does it make sense to switch tothe MySQL ODBC Connector (64bit) to increase the memory usage? As far as I have heard the NET connector is platform indepandant and performance should be (more or less) same.
Thank you very much for feedback on this…
How exactly do you mean MySQL is limited to a 2GB barrier? How big is your working dataset? What storage engine does your tables use?
Thanks for your feedback.
Well, currently I use MyISAM. I’ve tried also INNO, but not seen a difference. For the current application I query 25.000.000 recordsets in total, which are placed in 5 tables. Each table has 1-5 coloumns only. No blobs. Biggest table is 13.000.000 recordsets.
Does it help you?
This might help http://www.mysqlperformanceblog.com/2007/05/24/predicting-ho w-long-data-load-would-take/#comment-211056
How long does loading 25M rows on InnoDB take for you? I just tested loading 34M on stock my-medium.cnf using the sakila.film sample database and it too 19mins average on Linux if this gives you an idea.
I don’t know much about .NET, but have you tried loading your data manually if possible?