Shulou(Shulou.com)05/31 Report--

This article shows you how slow Oracle and MySQL JDBC is. It is concise and easy to understand. It will definitely make your eyes shine. I hope you can learn something from the detailed introduction of this article.

I often hear people say that the IO performance of the database is not good, but it is said that there is no perceptual knowledge. Let's now test the JDBC read performance of the commonly used Oracle and MySQL.

The reason for testing JDBC is that most applications are written in JAVA and can only use JDBC to access data. Here only test with JDBC read data, and generated into Java record object (after all, to this step can be used in the application), do not do any calculation.

1. data sources

Use the data generated by TPCH, select the customer table to do the test, the data record is 30 million rows, 8 fields. The original text file it generates is named customer.tbl and has a file size of 4.9G. Use the data import tool provided by the database to import the data from this file into Oracle and MySQL data tables.

2. test environment

The test was completed on an Intel server, 2 Intel2670 CPUs, main frequency 2.6G, a total of 16 cores, memory 64G. Database tables and text files are stored on the same SSD drive.

All tests were done natively on the server, consuming no network transmission time.

3. Database reading test

Data reads are performed with SQL statements through Oracle's JDBC interface.

Java is a hassle to write, and tests are executed with SPL scripts:

MySQL's test code is similar and will not be repeated.

Test Results (Time Unit: seconds)

The second time is probably faster because the operating system has a hard disk cache. Since we are primarily testing JDBC read times, we will take the second time as a rule, reducing the impact of the database itself reading from the hard disk. The number of rows read per second is also calculated according to the second time, that is, Oracle can read 100,000 rows of data per second, MySQL is about 80,000 rows. Of course, this value is related to the number and type of fields in the table (customer table has 8 fields), just a reference.

4. Text file comparison

Just from the above data volume is not much perceptual knowledge, let's read the text file to compare. The method is the same, read the data from the file and parse the record without any calculation.

Write the following SPL script to execute the test:

The test result was 42 seconds!

This means reading text is 281/42=6.69 times faster than Oracle and 381/42=9.07 times faster than MySQL!

We know that text parsing is a very cumbersome business, but even so, reading data from text files is much faster than reading data from databases. Oracle and MySQL IO is too slow!

5. binary fashion

Let's take a closer look at the read performance of binary storage formats and compare them to text.

For clarity, this time a larger table, the orders table in TPCH, has 300 million rows of data and 9 fields.

The text reading code is similar to the above, with a reading time test of 438 seconds.

Then, we convert this text file into SPL group tables and write code tests:

The test result was 164 seconds, roughly one-third of the time a text read is required.

This makes sense, because binary data no longer needs parsing and can be generated directly, with much less computation and therefore faster.

It should be noted that although the group table file uses column storage format, all columns are read here, and there is no less content than the text, and there is no cheaper column storage. In fact, column storage costs a bit because it reads all columns, and it's even faster if you use SPL set files, a row storage format.

6. parallel acceleration

It is also easy to parallelize data from files, and it is easy to write parallel programs for both text and table groups. Again, use the orders table above as an example to test, using 4 threads to fetch.

Text fetch code:

Group table fetch code:

Data segmentation and parallel computation are easy to implement with SPL.

The test results are:

Text 119 seconds

Group table 43 seconds

Compared with serial, it is close to linear promotion and makes full use of the multiple cores of CPU.

The data in the database is not easy to implement piecewise parallelism, and it is necessary to spell it with WHERE conditions. As a result, it is difficult to tell whether the parallelism is weak or the WHERE execution loss is too much. The reference significance of the test results is discounted, so we will not do it here.

7. conclusion

Database (Oracle and MySQL) JDBC performance is very, very poor! More than five times worse than text files. When binary data is used, it will improve the reading performance by 3 times compared with text. In other words, properly formatted binary files can have more than 15 times the advantage over databases. Taking into account the parallelism factor, it is entirely possible to be dozens or hundreds of times faster than the database.

When paying attention to performance and the amount of data is large, do not read the data out of the database calculation!

If you really need to read and then calculate (sometimes SQL is difficult to write complex process calculations), do not use the database to store (large data are history, basically no longer changed, you can read in advance), text is stronger than the database, binary is of course better (recommended to use SPL group table, haha). Don't waste time on non-computational tasks like reading.

That's how slow Oracle and MySQL JDBC is, and have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.