Issue
I'm working on a Java / MySQL app and test runs of batch processing were taking 3-4seconds on Ubuntu 18.04.
I made the upgrade to Ubuntu 20.04 last night, and kept the same mysql conf file, and now the same app is taking 1min and 45-47 seconds!!!
6.27user 0.41system 1:45.53elapsed 6%CPU (0avgtext+0avgdata 168724maxresident)k
0inputs+384outputs (0major+42056minor)pagefaults 0swaps
I noticed with lscpu that the server is now running in min freq on average. I tried adding "acpi=ht" to /etc/default/grub, and restarted the machine, to turn off power management but the processors is still running at low frequency and presumably frequency boost isn't working.
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 44
Model name: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Stepping: 2
Frequency boost: enabled
CPU MHz: 1599.592
CPU max MHz: 2401.0000
CPU min MHz: 1600.0000
BogoMIPS: 4799.75
Virtualization: VT-x
L1d cache: 256 KiB
L1i cache: 256 KiB
L2 cache: 2 MiB
L3 cache: 24 MiB
Using mysqltuner to check the server's InnoDB metrics things look OK, so it seems like this is related to the CPU settings / OS throttling or a Java configuration / code issue.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 8
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 9.0G/2.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/9.0G should be equal 25%
[OK] InnoDB buffer pool instances: 9
[--] Number of InnoDB Buffer Pool Chunk : 72 for 9 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.90% (2901594 hits/ 2904524 total)
[!!] InnoDB Write Log efficiency: 75.47% (19033 hits/ 25219 total)
[OK] InnoDB log waits: 0.00% (0 waits / 6186 writes)
Running top while the app is running shows that Java is only using <1%cpu usage after startup. For a batch process ideally it'd use all available processing power.
2113 mysql 20 0 25.7g 2.7g 36264 S 4.3 11.6 0:12.35 mysqld
353 root 20 0 0 0 0 S 2.7 0.0 0:02.99 md0_raid5
244 root 0 -20 0 0 0 I 0.7 0.0 0:00.98 kworker/10:1H-kblockd
248 root 0 -20 0 0 0 I 0.7 0.0 0:00.07 kworker/8:1H-kblockd
399 root 20 0 0 0 0 D 0.7 0.0 0:00.61 jbd2/md0-8
2372 bias 20 0 5597164 180592 27784 S 0.7 0.7 0:07.54 java
Running stress I get full cpu usage
4654 user 20 0 3856 100 0 R 100.0 0.0 0:13.88 stress
4643 user 20 0 3856 100 0 R 100.0 0.0 0:13.82 stress
4645 user 20 0 134932 3272 272 R 100.0 0.0 0:13.84 stress
4646 user 20 0 3856 100 0 R 100.0 0.0 0:13.80 stress
The maven pom.xml file is using the same mysql connector version that the OS has installed for the mysql server 8.0.21
39 <dependency>
40 <groupId>mysql</groupId>
41 <artifactId>mysql-connector-java</artifactId>
42 <version>8.0.21</version>
43 </dependency>
Is the java app being throttled by the OS or by the JVM?
Are there known issues with 20.04 and mysql or java?
Solution
The MySQL JDBC driver default behavior apparently changed when updating the OS.
The previous driver turned off transaction auto commits for Java batch updates - manually adding/running setAutoCommit(false) on the data connection dropped the run time back to normal (apparently it was running atomic transactions for 1000 some inserts).
This is a good example of why people should defensively code and add intentional code even if the default behavior/configuration is what's used in code - the default can and will change!
https://coderanch.com/t/299833/databases/Batch-update-setAutoCommit-false
Answered By - bias
Answer Checked By - Senaida (JavaFixing Volunteer)
