|
The Second stress test involves, Transfering 6 millions of records from a Flat File, to a table in MySQL. |
|
SSIS made me sweat with a simple test. It was refusing to treat sweetly to MySQL. Really, the interaction between SSIS and MySQL was not love at first sight. There were many complications with the drivers, the data types, and trying to insert data.
One of the errors was: [ADO NET Destination]... ERROR [42000] [MySQL] [ODBC 5.1 Driver] Error in Syntax..
The solution came from Jayaram Krishnaswamy, after fighting with the drivers and the types of data because the error message was not entirely clear.
Once tackle this, "insert" began to dump the data, but bad news again. The data passed to the table with a frequency of 15 rows / sec. Modified the batch size, the BufferMaxRows, ODBC, continued to maintain some horrible timing, with an average of 40000 rows / hour.
Drivers were downloaded from the MySQL page, you can not blame the ODBC, because other technologies have also used it. The base and the network is the same as for the other tests.
In the final comparison, today, SSIS positioned as the last in this test. However, I am open to any comments to improve this, and different result to re order it.
Also keep in mind that in the second part of the test, resources will be minimally quadrupled, so that the results clearly be different.
CASE 1:
| Objective: | To measure elapsed time reading and writing 6 million rows, from Flat file, to a MySQL database. The DB runs in another machine. The benefit es "Clear", only TSSIS runs alone in the virtual machine. The negative point is the transfer of data through the network, using the generic drivers installed by VM. |
| Rows: | 6.024.000 M |
| Columns: | 37 Columns |
| Resources: | Virtual machine with: 2 GB RAM, SSIS like main process over the virtual plataform. The resources used are anecdotal, today, Any production environment has enough processing power for current and future requirements. The objective here, is to build, to execute and to measure with the same environment (regardless of the limited resources) |
| Structure (Metadata) |
|
| Design & Run |
|
| Elapsed time (s) |
??? sec. |
| Rows per sec (avg) |
xxx. |
| Summary of log |
|
