KH Coder Speed on Linux: MySQL settings to boost performance

[ironfrown]

My Linux installation is incredibly slow compared with that on Windows. The main problem is either with the Stanford POS Tagger or with sending its results to the MySQL database. It means that KHC is just fine working on small tutorial documents but unusable for larger document sets. I suspect the reason is that the Windows version relies on named pipes to communicate with MySQL while the Linux version talks to MySQL via the network protocol. Has anybody been successful improving the KHC performance on Linux? (And yes, I have tried to load and export documents on Windows and then importing its on Linux takes forever.)

[ko-ichi-h]

Hi,

When you run "pre-processing", there are console outputs like below.

Connected to MySQL 5.6, khc2.
Checking icode (jp2)... utf8
Morpho1  1 wallclock secs ( 0.56 usr +  0.02 sys =  0.58 CPU)
        Single row: 236 bytes
        The HEAP table will eat approx. 26MB; We have 1547MB max.
Read     2 wallclock secs ( 0.02 usr +  0.02 sys =  0.03 CPU)
Format   1 wallclock secs ( 0.11 usr +  0.02 sys =  0.12 CPU)
Strat1   4 wallclock secs ( 0.55 usr +  0.14 sys =  0.69 CPU)
Strat2   1 wallclock secs ( 0.00 usr +  0.02 sys =  0.02 CPU)
RawTXT   1 wallclock secs ( 0.22 usr +  0.14 sys =  0.36 CPU)
 df: heap  df: heap  df: heap df         3 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
fc       0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
Check    0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
MySQL: FLUSH
Morpho File: C:/khcoder3/config/khc2/khc2_ch.txt

Can you perform pre-processing of the tutorial file on both Windows and Linux and post the both outputs?

[ko-ichi-h]

BTW, if you want to avoid TCP/IP connection to MySQL, you can try editing config/coder.ini as follows.

sql_type	SOCKET
sql_socket	/tmp/mysql.sock

[ironfrown]

I think the problem is more sinister. I was using exactly the same file on both. My Win10 run finishes in 9 minutes, while the Linux run terminated after 2.5 hours. I have improved my Linux installation, to give enough MySQL heap space (so that KHC does not switch to ISAM storage) and used the socket connection to increase the speed. This is the pre-processing dump you've asked for:

$ time perl kh_coder.pl 
Encoding of this Console: UTF-8
Encoding of this file system: UTF-8
Perl/Tk: 804.034
Locale: en_AU.UTF-8
This is KH Coder 3.Alpha.17j on linux.
CWD: /home/jacob/Apps/khcoder-3a17j
Warning in Sys.setlocale(category = "LC_ALL", locale = "ja_JP.UTF-8") :
  OS reports request to set locale to "ja_JP.UTF-8" cannot be honored
R Version: 3.6, x86_64
Using un-threaded functions...
new default extension: .txt *.csv *.tsv *.xls *.xlsx *.docx *.odt
Checking icode (en)... ascii
Checking icode (en)... ascii
Conv:	 0 wallclock secs ( 0.45 usr +  0.04 sys =  0.49 CPU)
Connected to MySQL 5.7, khc12.
Data dir: /home/jacob/Apps/khcoder-3a17j/config/khc12/
Connected to MySQL 5.7, khc12.
Checking icode (en)... ascii
MySQL: FLUSH
Checking icode (en)... ascii
Starting server, pid: 11486, Connecting.openjdk version "1.8.0_222"
OpenJDK Runtime Environment (build 1.8.0_222-8u222-b10-1ubuntu1~18.04.1-b10)
OpenJDK 64-Bit Server VM (build 25.222-b10, mixed mode)

Loading default properties from tagger /home/jacob/Apps/stanford-pos-tagger/models/english-left3words-distsim.tagger
Loading POS tagger from /home/jacob/Apps/stanford-pos-tagger/models/english-left3words-distsim.tagger ... done [0.6 sec].
.. ok. Tagging... ok.
Morpho1	116 wallclock secs (41.28 usr  7.29 sys + 78.87 cusr 10.58 csys = 138.02 CPU)
	Single row: 612 bytes
	The HEAP table will eat approx. 1197MB; We have 2048MB max.
Read	22 wallclock secs ( 0.06 usr  0.04 sys +  0.00 cusr  0.01 csys =  0.11 CPU)
Format	11 wallclock secs ( 0.52 usr +  0.02 sys =  0.54 CPU)
Strat1	158 wallclock secs (29.01 usr +  0.50 sys = 29.51 CPU)
Strat2	19 wallclock secs ( 0.09 usr +  0.04 sys =  0.13 CPU)
RawTXT	 9 wallclock secs ( 2.04 usr +  0.02 sys =  2.06 CPU)
 df: heap  df: heap  df: heap df	54 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
fc	27 wallclock secs ( 0.06 usr +  0.01 sys =  0.07 CPU)
Check	 1 wallclock secs ( 0.03 usr +  0.00 sys =  0.03 CPU)
^C
Execution halted

real	147m3.261s = 8823 sec

The real problem starts after all pre-processing is finished. KHC seems to be stuck saving data to its database. When I peeked at the MySQL connection, the following code is slowly being pumped into the database:

-- Connection Id: 9
-- User: xyz
-- Host: localhost
-- DB: khc12
-- Command: Query
-- Time: 536
-- State: Sending data
INSERT INTO bun_hb (hyosobun_id, tid)
		SELECT hyosobun.id, bun.id
		FROM hyosobun, bun
		WHERE
			hyosobun.bun_id = bun.bun_id
			AND hyosobun.dan_id = bun.dan_id
			AND hyosobun.h5_id = bun.h5_id
			AND hyosobun.h4_id = bun.h4_id
			AND hyosobun.h3_id = bun.h3_id
			AND hyosobun.h2_id = bun.h2_id
			AND hyosobun.h1_id = bun.h1_id
		ORDER BY hyosobun.id /* statement may be truncated */

I've checked the indeces are all there and should be used but it is all so much slower!
Any ideas what's next?

[ko-ichi-h]

Hmm, would you check your "innodb_flush_log_at_trx_commit" MySQL server setting?

If it's "1", can you try setting it to "2" or "0", restarting MySQL server, and running pre-processing of KH Coder?

[ironfrown]

I have tried this but no difference. I have also followed up the thread on "Slow mysql on Ubuntu" and changed various MySQL parameters, e.g.

tmp_table_size=2G
max_heap_table_size=2G # Also tried more
innodb_flush_log_at_trx_commit = 0
innodb_read_io_threads=4 # Usually commented it out
innodb_write_io_threads=8 #To stress the double write buffer 
innodb_buffer_pool_size=2G # 70-80% available Memory 
innodb_buffer_pool_load_at_startup=ON 
innodb_log_file_size = 32M #Small log files, more page flush 
innodb_log_files_in_group=2 
innodb_file_per_table=1 
innodb_log_buffer_size=8M 
innodb_flush_method=O_DIRECT 
innodb_buffer_pool_dump_at_shutdown=ON
skip-innodb_doublewrite #commented or not depending on test

However, none of these had any significant effect. While on Windows the file is processed in 10 minutes, on Linux it takes days.

By the way, I have tried it on two different computers, one with 64Gb RAM.

[ko-ichi-h]

Well, I think the "innodb_flush_log_at_trx_commit" setting should have enormous impact on performance. Would you check whether the setting is in effect?

1. Start KH Coder
2. Open the project
3. Go to "Tools" "Execute SQL Statements"
4. Type as follows, click execute, and tell us the result.

show variables like "innodb%"

Also, results of the "show table status like 'rowdata'" may help.

As a last resort, if you are not using your MySQL for other purposes, you can try setting default engine to MyISAM. (I assume your current default is INNODB.) Windows version of KH Coder comes with MySQL 5.6 with following settings (I think you already knew it but anyway...).

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM
myisam_sort_buffer_size=314M
key_buffer_size=384M
read_buffer_size=256K
read_rnd_buffer_size=2M

[ironfrown]

Hi, I have finally crunched the problem. I have switched to MyISAM with your parameters and KHC worked very fast. However, I have noticed that your key_buffer_size was 384Mb while in my settings only 16Mb, the read buffer settings were also missing. So I have gone back to Innodb storage engine, however I have included the last three variable settings, i.e.

key_buffer_size=384M
read_buffer_size=256K
read_rnd_buffer_size=2M

Restarted MySQL and KH Coder now works beautifully fast on Ubuntu 18.04!

Thank you for you help -- Jacob

[Nemowang2003]

Sorry, but are these three variables defined under [mysqld] section? I added these three lines there but it seems that nothing changed.

[ko-ichi-h]

Yes. You should add these lines under [mysqld].