Perl - MySQL/MariaDB - slow with no identifiable bottleneck
1
vote
2
answers
2243
views
I am running a Perl script (using DBI) which reads from raw files from a hard disk, and updates MySQL database (which is on a separate SSD). My performance is rather slow (1000 files processed in 30-60 seconds), but I can not find the bottleneck.
CPU. Network, Disk and Memory are all rather unused. I am running Windows 7 64bit on an i7 machine (8 cores). MariaDB is version 10.0.10. My database is around 78G in size with 5M entries, all tables properly indexed.
Perfmon confirms this showing total CPU 6%, Network 0%, HD Disk 140 I/O/s, Memory 10%. None of the CPU cores are used more than 3-4%.
I have experimented with changing all these mysql variables with no success:
innodb_use_global_flush_log_at_trx_commit
innodb_buffer_pool_instances
global.max_connections
innodb_thread_sleep_delay
global.innodb_io_capacity
global.innodb_sync_spin_loops
innodb_flush_log_at_trx_commit
wait_timeout
Mysqltuner did not report anything of interest, except for:
Data in InnoDB tables: 78G (Tables: 12)
Total fragmented tables: 10
Query cache is disabled
Thread cache is disabled
InnoDB data size/buffer pool: 78.1G/2.0G
Perl profiling showed that majority of time is taken up by DBI::st::execute (invoking sql).
I have also tried disabling firewall and virus scanner too - no difference.
Asked by Shalabajzer
(31 rep)
Jul 18, 2014, 08:08 PM
Last activity: Nov 7, 2017, 04:17 PM
Last activity: Nov 7, 2017, 04:17 PM