Performance tuning time

[Baylink]

Here's a month's worth of tuning on this machine, which is still 12 720p Cams at 6fps modect; I upgraded the ffmpeg to:

ffmpeg version 3.3.4-2~16.04.york0 Copyright (c) 2000-2017 the FFmpeg developers
built with gcc 5.4.0 (Ubuntu 5.4.0-6ubuntu1~16.04.4) 20160609
configuration: --prefix=/usr --extra-version='2~16.04.york0' --toolchain=hardened --libdir=/usr/lib/i386-linux-gnu --incdir=/usr/include/i386-linux-gnu --enable-gpl --disable-stripping --enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa --enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca --enable-libcdio --enable-libflite --enable-libfontconfig --enable-libfreetype --enable-libfribidi --enable-libgme --enable-libgsm --enable-libmp3lame --enable-libopenjpeg --enable-libopenmpt --enable-libopus --enable-libpulse --enable-librubberband --enable-libshine --enable-libsnappy --enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora --enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack --enable-libwebp --enable-libx265 --enable-libxvid --enable-libzmq --enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2 --enable-libdc1394 --enable-libiec61883 --enable-chromaprint --enable-frei0r --enable-libopencv --enable-libx264 --enable-shared
libavutil 55. 58.100 / 55. 58.100
libavcodec 57. 89.100 / 57. 89.100
libavformat 57. 71.100 / 57. 71.100
libavdevice 57. 6.100 / 57. 6.100
libavfilter 6. 82.100 / 6. 82.100
libavresample 3. 5. 0 / 3. 5. 0
libswscale 4. 6.100 / 4. 6.100
libswresample 2. 7.100 / 2. 7.100
libpostproc 54. 5.100 / 54. 5.100

but the load is still 9-12 idle recording, and jumps to 16-24 in Montage Live.

=====
root@pano:/var/log/mysql# cat /appl/downloads/mysqltuner-201709071334

jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(530B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 2 error(s).
==================== Those are: "aborted connection 1636427 to db: 'zm' user: 'zmuser' host: 'localhost' (Got an error reading communication packets)" and they're trivial in number
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 615M (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31d 1h 47m 44s (10M q [3.971 qps], 1M conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 27% / 73%
[--] Binary logging is disabled
============ Is that a problem?
[--] Physical Memory : 5.9G
[--] Max MySQL memory : 352.4M
[--] Other process memory: 5.4G
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 36B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 271.7M (4.48% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (5.81% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/10M)
[OK] Highest usage of available connections: 49% (75/151)
[OK] Aborted connections: 0.00% (3/1641072)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
=========== I get the impression this is a feature, not a bug...
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (122 temp sorts / 840K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (377 on disk / 4K total)
[OK] Thread cache hit rate: 87% (200K created / 1M connections)
[OK] Table cache hit rate: 72% (256 open / 352 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 36B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (1M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)
============== my my.cnf is below

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/615.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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.89% (1619644083 hits/ 1621480637 total)
[!!] InnoDB Write Log efficiency: 32.61% (1456527 hits/ 4466052 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5922579 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control error line(s) into /var/log/mysql/error.log file
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
innodb_buffer_pool_size (>= 615M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
=====

=====
131 ? S< 0:00 \_ [bioset]
175 ? S 0:00 \_ [scsi_eh_4]
176 ? S< 0:00 \_ [scsi_tmf_4]
187 ? S 0:00 \_ [aacraid]
201 ? S< 0:00 \_ [bioset]
214 ? S< 0:00 \_ [kpsmoused]
218 ? S< 0:03 \_ [kworker/3:1H]
237 ? S< 0:00 \_ [bioset]
239 ? S< 0:00 \_ [bioset]
304 ? S< 0:00 \_ [ttm_swap]
305 ? S< 0:00 \_ [radeon-crtc]
372 ? S< 0:00 \_ [raid5wq]
398 ? S< 0:00 \_ [bioset]
423 ? S< 0:16 \_ [kworker/5:1H]
424 ? S< 0:11 \_ [kworker/0:1H]
426 ? S 43:43 \_ [jbd2/sda1-8]
427 ? S< 0:00 \_ [ext4-rsv-conver]
455 ? S< 0:13 \_ [kworker/4:1H]
490 ? S< 0:00 \_ [iscsi_eh]
495 ? S 0:00 \_ [kauditd]
515 ? S< 0:00 \_ [ib_addr]
520 ? S< 0:00 \_ [ib_mcast]
521 ? S< 0:00 \_ [ib_nl_sa_wq]
522 ? S< 0:00 \_ [ib_cm]
523 ? S< 0:00 \_ [iw_cm_wq]
525 ? S< 0:00 \_ [rdma_cm]
555 ? S< 0:15 \_ [kworker/2:1H]
676 ? S< 0:00 \_ [edac-poller]
869 ? S< 0:04 \_ [kworker/7:1H]
880 ? S 6:19 \_ [jbd2/sda3-8]
881 ? S< 0:00 \_ [ext4-rsv-conver]
1420 ? S< 0:04 \_ [kworker/6:1H]
1942 ? S< 0:05 \_ [kworker/1:1H]
9261 ? S< 0:00 \_ [xfsalloc]
9262 ? S< 0:00 \_ [xfs_mru_cache]
9265 ? S 0:00 \_ [jfsIO]
9266 ? S 0:00 \_ [jfsCommit]
9267 ? S 0:00 \_ [jfsCommit]
9268 ? S 0:00 \_ [jfsCommit]
9269 ? S 0:00 \_ [jfsCommit]
9270 ? S 0:00 \_ [jfsCommit]
9271 ? S 0:00 \_ [jfsCommit]
9272 ? S 0:00 \_ [jfsCommit]
9273 ? S 0:00 \_ [jfsCommit]
9274 ? S 0:00 \_ [jfsSync]
3380 ? S 0:11 \_ [kworker/6:2]
24368 ? S 0:11 \_ [kworker/3:2]
16035 ? S 0:22 \_ [kworker/3:1]
16159 ? S 0:19 \_ [kworker/6:0]
24628 ? S 0:05 \_ [kworker/0:0]
24762 ? S 0:13 \_ [kworker/1:1]
1919 ? S 0:01 \_ [kworker/4:0]
2541 ? S 0:07 \_ [kworker/5:2]
5094 ? S 0:07 \_ [kworker/0:1]
5861 ? S 0:00 \_ [kworker/5:1]
5906 ? S 0:05 \_ [kworker/2:1]
7131 ? S 0:06 \_ [kworker/4:1]
13174 ? S 1:20 \_ [kworker/7:2]
13366 ? S 0:02 \_ [kworker/1:0]
14503 ? S 0:01 \_ [kworker/2:0]
15293 ? S 0:16 \_ [kworker/7:0]
15374 ? S 0:02 \_ [kworker/u16:3]
15473 ? S 0:01 \_ [kworker/u16:1]
15482 ? S 0:00 \_ [kworker/u16:0]
15493 ? S 0:00 \_ [kworker/u16:2]
1 ? Ss 1:57 /sbin/init
479 ? Ss 29:11 /lib/systemd/systemd-journald
524 ? Ss 0:00 /sbin/lvmetad -f
542 ? Ss 0:09 /lib/systemd/systemd-udevd
989 ? Ssl 0:10 /lib/systemd/systemd-timesyncd
1113 ? Ss 0:14 /usr/sbin/cron -f
1116 ? Ss 0:00 /usr/sbin/atd -f
1126 ? Ssl 8:40 /usr/lib/accountsservice/accounts-daemon
1128 ? Ssl 1:57 /usr/bin/lxcfs /var/lib/lxcfs/
1130 ? Ss 0:00 /usr/sbin/acpid
1132 ? Ssl 8:26 /usr/sbin/rsyslogd -n
1136 ? Ss 0:00 /sbin/cgmanager -m name=systemd
1138 ? Ss 0:14 /usr/bin/dbus-daemon --system --address=systemd: --nofork --nopidfile --systemd-activati
1150 ? Ss 0:13 /lib/systemd/systemd-logind
1246 ? Ss 0:00 /usr/sbin/sshd -D
16966 ? Ss 0:00 \_ sshd: jra [priv]
17158 ? S 0:00 \_ sshd: jra@pts/0
17159 pts/0 Ss 0:00 \_ -bash
17222 pts/0 R+ 0:00 \_ ps axf
1277 ? Ssl 0:00 /usr/lib/policykit-1/polkitd --no-debug
1279 ? Ss 3:52 /sbin/iscsid
1282 ? S<Ls 21:12 /sbin/iscsid
1286 ? Ss 0:00 /usr/sbin/dovecot
1314 ? S 0:00 \_ dovecot/anvil
1315 ? S 0:00 \_ dovecot/log
1317 ? S 0:00 \_ dovecot/config
1307 ? Ssl 788:28 /usr/sbin/mysqld
1415 tty1 Ss+ 0:00 /sbin/agetty --noclear tty1 linux
1416 ? Ss 7:32 /usr/sbin/irqbalance --pid=/var/run/irqbalance.pid
2187 ? Ss 0:26 /usr/lib/postfix/sbin/master
2190 ? S 0:06 \_ qmgr -l -t unix -u
15487 ? S 0:00 \_ pickup -l -t unix -u -c
2468 ? S 0:00 /usr/sbin/dnsmasq --conf-file=/var/lib/libvirt/dnsmasq/default.conf --leasefile-ro --dhc
2469 ? S 0:00 \_ /usr/sbin/dnsmasq --conf-file=/var/lib/libvirt/dnsmasq/default.conf --leasefile-ro -
14905 ? Ssl 5:06 /usr/lib/snapd/snapd
15147 ? Ssl 0:00 /usr/sbin/virtlogd
15201 ? Ssl 0:00 /usr/sbin/virtlockd
15255 ? Ssl 0:02 /usr/sbin/libvirtd
19537 ? S 0:02 /usr/bin/python3 /usr/lib/ubuntu-release-upgrader/check-new-release -q
1530 ? Ssl 0:02 /usr/sbin/named -f -u bind
15996 ? Ss 0:13 /usr/sbin/smbd -D
15997 ? S 0:00 \_ /usr/sbin/smbd -D
16001 ? S 0:13 \_ /usr/sbin/smbd -D
16142 ? Ss 0:57 /usr/sbin/nmbd -D
16402 ? Ss 0:50 /usr/sbin/winbindd
16404 ? S 0:10 \_ /usr/sbin/winbindd
17382 ? S 0:02 /usr/bin/python3 /usr/lib/ubuntu-release-upgrader/check-new-release -q
27193 ? Ss 0:00 /sbin/mdadm --monitor --pid-file /run/mdadm/monitor.pid --daemonise --scan --syslog
9176 ? S 24:40 /usr/bin/perl -wT /usr/bin/zmdc.pl startup
9219 ? Sl 3446:30 \_ /usr/bin/zmc -m 4
9226 ? R 2912:37 \_ /usr/bin/zma -m 4
9233 ? Rl 3271:03 \_ /usr/bin/zmc -m 5
9245 ? Sl 4460:19 \_ /usr/bin/zmc -m 6
9252 ? R 2759:38 \_ /usr/bin/zma -m 6
9259 ? Sl 2943:08 \_ /usr/bin/zmc -m 7
9284 ? Sl 2168:33 \_ /usr/bin/zmc -m 8
9290 ? R 2829:15 \_ /usr/bin/zma -m 8
9297 ? Sl 2054:10 \_ /usr/bin/zmc -m 9
9304 ? R 2826:52 \_ /usr/bin/zma -m 9
9311 ? Sl 4662:49 \_ /usr/bin/zmc -m 10
9324 ? Sl 1560:34 \_ /usr/bin/zmc -m 12
9331 ? R 2832:28 \_ /usr/bin/zma -m 5
9347 ? Sl 1478:01 \_ /usr/bin/zmc -m 13
9363 ? R 2799:48 \_ /usr/bin/zma -m 13
9370 ? Sl 2252:39 \_ /usr/bin/zmc -m 14
9387 ? R 2781:30 \_ /usr/bin/zma -m 7
9403 ? Sl 1688:56 \_ /usr/bin/zmc -m 15
9421 ? S 9:09 \_ /usr/bin/perl -wT /usr/bin/zmfilter.pl
9424 ? R 2754:46 \_ /usr/bin/zma -m 10
9436 ? S 54:22 \_ /usr/bin/perl -wT /usr/bin/zmaudit.pl -c
9449 ? R 2691:21 \_ /usr/bin/zma -m 12
9461 ? S 13:25 \_ /usr/bin/perl -wT /usr/bin/zmwatch.pl
9481 ? R 2676:04 \_ /usr/bin/zma -m 14
9489 ? R 2752:24 \_ /usr/bin/zma -m 15
8506 ? S 0:01 \_ /usr/bin/perl -w /usr/bin/zmupdate.pl -c
13416 ? S 0:00 \_ /usr/bin/perl -w /usr/bin/zmtelemetry.pl
14700 ? Rl 143:52 \_ /usr/bin/zmc -m 3
14706 ? S 7:09 \_ /usr/bin/zma -m 3
9130 ? Ss 0:59 /usr/sbin/apache2 -k start
13369 ? S 0:01 \_ /usr/sbin/apache2 -k start
17219 ? R 0:00 | \_ /usr/lib/zoneminder/cgi-bin/zms
13370 ? S 0:00 \_ /usr/sbin/apache2 -k start
17218 ? R 0:00 | \_ /usr/lib/zoneminder/cgi-bin/zms
13371 ? S 0:00 \_ /usr/sbin/apache2 -k start
17223 ? R 0:00 | \_ /usr/lib/zoneminder/cgi-bin/zms
13372 ? S 0:00 \_ /usr/sbin/apache2 -k start
13373 ? S 0:00 \_ /usr/sbin/apache2 -k start
17224 ? R 0:00 | \_ /usr/lib/zoneminder/cgi-bin/zms
15495 ? S 0:00 \_ /usr/sbin/apache2 -k start
15496 ? S 0:00 \_ /usr/sbin/apache2 -k start
15503 ? S 0:00 \_ /usr/sbin/apache2 -k start
17221 ? R 0:00 | \_ /usr/lib/zoneminder/cgi-bin/zms
15504 ? S 0:00 \_ /usr/sbin/apache2 -k start
15521 ? S 0:00 \_ /usr/sbin/apache2 -k start
15535 ? S 0:00 \_ /usr/sbin/apache2 -k start
15779 ? S 0:00 \_ /usr/sbin/apache2 -k start
15792 ? S 0:00 \_ /usr/sbin/apache2 -k start
17022 ? Ss 0:00 /lib/systemd/systemd --user
17025 ? S 0:00 \_ (sd-pam)
17058 ? S 0:02 /usr/bin/python3 /usr/lib/ubuntu-release-upgrader/check-new-release -q
17220 ? R 0:00 \_ /usr/bin/python3 -Es /usr/bin/lsb_release -r -s
jra@pano:~$ top
top - 13:31:19 up 31 days, 1:47, 1 user, load average: 19.83, 17.78, 14.02
Tasks: 233 total, 10 running, 223 sleeping, 0 stopped, 0 zombie
%Cpu(s): 75.1 us, 24.0 sy, 0.0 ni, 0.3 id, 0.0 wa, 0.0 hi, 0.6 si, 0.0 st
KiB Mem : 6214836 total, 911560 free, 2159596 used, 3143680 buff/cache
KiB Swap: 19530748 total, 19285644 free, 245104 used. 1657916 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9311 www-data 20 0 467204 228696 166384 S 50.0 3.7 4663:30 zmc
9245 www-data 20 0 467528 230656 166000 S 40.1 3.7 4460:52 zmc
14700 www-data 20 0 357684 189176 26376 S 35.2 3.0 144:20.89 zmc
9219 www-data 20 0 464940 208744 165564 R 31.6 3.4 3446:58 zmc
9233 www-data 20 0 464988 230172 165696 R 30.3 3.7 3271:29 zmc
9259 www-data 20 0 464384 229388 166432 S 29.6 3.7 2943:31 zmc
9331 www-data 20 0 403492 249212 167000 S 26.6 4.0 2832:47 zma
9252 www-data 20 0 403492 251364 167116 S 25.7 4.0 2759:58 zma
9290 www-data 20 0 406196 248844 166756 R 25.7 4.0 2829:36 zma
9363 www-data 20 0 403492 249148 166936 S 25.0 4.0 2800:08 zma
9387 www-data 20 0 403492 249280 167080 S 25.0 4.0 2781:50 zma
9424 www-data 20 0 406196 252692 166972 R 25.0 4.1 2755:06 zma
9481 www-data 20 0 403492 249288 167080 R 25.0 4.0 2676:23 zma
9489 www-data 20 0 403492 248884 167056 R 24.7 4.0 2752:44 zma
9226 www-data 20 0 403492 242396 166936 S 24.0 3.9 2912:57 zma
9304 www-data 20 0 403492 249128 166932 R 24.0 4.0 2827:12 zma
9297 www-data 20 0 464220 227892 166488 S 22.0 3.7 2054:27 zmc
9403 www-data 20 0 461900 231356 166060 S 20.1 3.7 1689:11 zmc
9449 www-data 20 0 403492 249024 166940 R 20.1 4.0 2691:40 zma
9284 www-data 20 0 463852 227320 165700 S 19.7 3.7 2168:48 zmc
9324 www-data 20 0 459600 226388 166304 S 17.4 3.6 1560:48 zmc
9370 www-data 20 0 462536 224872 165544 R 14.8 3.6 2252:51 zmc
9347 www-data 20 0 460080 225940 165680 S 14.1 3.6 1478:14 zmc
1307 mysql 20 0 624432 205980 7972 S 8.9 3.3 788:36.14 mysqld
14706 www-data 20 0 209784 61644 25884 S 2.6 1.0 7:11.59 zma
17420 jra 20 0 8104 3580 2984 R 1.6 0.1 0:00.68 top
7 root 20 0 0 0 0 S 1.0 0.0 103:59.62 rcu_sched
13373 www-data 20 0 159972 9056 3548 S 1.0 0.1 0:01.38 apache2
15374 root 20 0 0 0 0 S 0.7 0.0 0:02.39 kworker/u16:3
15504 www-data 20 0 159832 8596 3172 S 0.7 0.1 0:00.66 apache2
15535 www-data 20 0 159832 8596 3168 S 0.7 0.1 0:00.67 apache2
23 root 20 0 0 0 0 S 0.3 0.0 78:28.86 ksoftirqd/3
479 root 20 0 12640 6036 5916 S 0.3 0.1 29:11.06 systemd-journal
1416 root 20 0 4436 208 0 S 0.3 0.0 7:32.92 irqbalance
13370 www-data 20 0 159972 9176 3552 S 0.3 0.1 0:01.49 apache2
13372 www-data 20 0 159972 9060 3544 S 0.3 0.1 0:01.30 apache2
15495 www-data 20 0 159972 9080 3552 S 0.3 0.1 0:00.73 apache2
15521 www-data 20 0 159832 8592 3168 S 0.3 0.1 0:00.68 apache2
1 root 20 0 6808 4136 3052 S 0.0 0.1 1:57.23 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:01.14 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 2:35.64 ksoftirqd/0
5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
9 root rt 0 0 0 0 S 0.0 0.0 0:31.56 migration/0
10 root rt 0 0 0 0 S 0.0 0.0 0:32.20 watchdog/0
jra@pano:~$ lcoate mysqltuner
No command 'lcoate' found, did you mean:
Command 'locate' from package 'locate' (universe)
Command 'locate' from package 'mlocate' (main)
lcoate: command not found
jra@pano:~$ ^C
jra@pano:~$ locate mysqltuner
/appl/downloads/mysqltuner.pl
jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: Jazzy1304
Please enter your MySQL administrative password:
jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(530B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 2 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 615M (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31d 1h 47m 44s (10M q [3.971 qps], 1M conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 27% / 73%
[--] Binary logging is disabled
[--] Physical Memory : 5.9G
[--] Max MySQL memory : 352.4M
[--] Other process memory: 5.4G
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 36B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 271.7M (4.48% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (5.81% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/10M)
[OK] Highest usage of available connections: 49% (75/151)
[OK] Aborted connections: 0.00% (3/1641072)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (122 temp sorts / 840K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (377 on disk / 4K total)
[OK] Thread cache hit rate: 87% (200K created / 1M connections)
[OK] Table cache hit rate: 72% (256 open / 352 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 36B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (1M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/615.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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.89% (1619644083 hits/ 1621480637 total)
[!!] InnoDB Write Log efficiency: 32.61% (1456527 hits/ 4466052 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5922579 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control error line(s) into /var/log/mysql/error.log file
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
innodb_buffer_pool_size (>= 615M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
jra@pano:~$ cat >/appl/downloads/mysqltuner-201709071334
-bash: /appl/downloads/mysqltuner-201709071334: Permission denied
jra@pano:~$ sudo cat >/appl/downloads/mysqltuner-201709071334
-bash: /appl/downloads/mysqltuner-201709071334: Permission denied
jra@pano:~$ sudo so
[sudo] password for jra:
jra@pano:~$ sudo su
[sudo] password for jra:
root@pano:/home/jra# ^C
root@pano:/home/jra# cat >/appl/downloads/mysqltuner-201709071334

jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(530B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 2 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 615M (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31d 1h 47m 44s (10M q [3.971 qps], 1M conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 27% / 73%
[--] Binary logging is disabled
[--] Physical Memory : 5.9G
[--] Max MySQL memory : 352.4M
[--] Other process memory: 5.4G
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 36B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 271.7M (4.48% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (5.81% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/10M)
[OK] Highest usage of available connections: 49% (75/151)
[OK] Aborted connections: 0.00% (3/1641072)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (122 temp sorts / 840K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (377 on disk / 4K total)
[OK] Thread cache hit rate: 87% (200K created / 1M connections)
[OK] Table cache hit rate: 72% (256 open / 352 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 36B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (1M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)




















































-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.














[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/615.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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.89% (1619644083 hits/ 1621480637 total)
[!!] InnoDB Write Log efficiency: 32.61% (1456527 hits/ 4466052 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5922579 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
































[--] TokuDB is disabled.




















































jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(530B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 2 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 615M (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31d 1h 47m 44s (10M q [3.971 qps], 1M conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 27% / 73%
[--] Binary logging is disabled
[--] Physical Memory : 5.9G
[--] Max MySQL memory : 352.4M
[--] Other process memory: 5.4G
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 36B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 271.7M (4.48% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (5.81% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/10M)
[OK] Highest usage of available connections: 49% (75/151)
[OK] Aborted connections: 0.00% (3/1641072)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (122 temp sorts / 840K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (377 on disk / 4K total)
[OK] Thread cache hit rate: 87% (200K created / 1M connections)
[OK] Table cache hit rate: 72% (256 open / 352 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 36B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (1M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control error line(s) into /var/log/mysql/error.log file
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
innodb_buffer_pool_size (>= 615M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
root@pano:/home/jra# less /appl/downloads/mysqltuner-201709071334
root@pano:/home/jra# less /v
var/ vmlinuz vmlinuz.old
root@pano:/home/jra# less /var/log/mysql/error.log
root@pano:/home/jra# l
tuner
root@pano:/home/jra# unalias l
root@pano:/home/jra# cd /var/log/mysql/
root@pano:/var/log/mysql# l
total 32
-rw-r----- 1 mysql adm 1170 Oct 7 13:32 error.log
-rw-r----- 1 mysql adm 408 Oct 7 06:05 error.log.1.gz
-rw-r----- 1 mysql adm 475 Oct 6 06:00 error.log.2.gz
-rw-r----- 1 mysql adm 357 Oct 5 06:06 error.log.3.gz
-rw-r----- 1 mysql adm 393 Oct 4 03:07 error.log.4.gz
-rw-r----- 1 mysql adm 353 Oct 3 06:15 error.log.5.gz
-rw-r----- 1 mysql adm 1397 Oct 2 04:40 error.log.6.gz
-rw-r----- 1 mysql adm 380 Oct 1 06:23 error.log.7.gz
root@pano:/var/log/mysql# zless error.log.1.gz
root@pano:/var/log/mysql# less /appl/downloads/mysqltuner-201709071334
root@pano:/var/log/mysql# cat /appl/downloads/mysqltuner-201709071334

jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(530B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 2 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 615M (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31d 1h 47m 44s (10M q [3.971 qps], 1M conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 27% / 73%
[--] Binary logging is disabled
[--] Physical Memory : 5.9G
[--] Max MySQL memory : 352.4M
[--] Other process memory: 5.4G
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 36B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 271.7M (4.48% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (5.81% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/10M)
[OK] Highest usage of available connections: 49% (75/151)
[OK] Aborted connections: 0.00% (3/1641072)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (122 temp sorts / 840K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (377 on disk / 4K total)
[OK] Thread cache hit rate: 87% (200K created / 1M connections)
[OK] Table cache hit rate: 72% (256 open / 352 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 36B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (1M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/615.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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.89% (1619644083 hits/ 1621480637 total)
[!!] InnoDB Write Log efficiency: 32.61% (1456527 hits/ 4466052 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5922579 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control error line(s) into /var/log/mysql/error.log file
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
innodb_buffer_pool_size (>= 615M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
root@pano:/var/log/mysql# ffmpeg -v
ffmpeg version 3.3.4-2~16.04.york0 Copyright (c) 2000-2017 the FFmpeg developers
built with gcc 5.4.0 (Ubuntu 5.4.0-6ubuntu1~16.04.4) 20160609
configuration: --prefix=/usr --extra-version='2~16.04.york0' --toolchain=hardened --libdir=/usr/lib/i386-linux-gnu --incdir=/usr/include/i386-linux-gnu --enable-gpl --disable-stripping --enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa --enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca --enable-libcdio --enable-libflite --enable-libfontconfig --enable-libfreetype --enable-libfribidi --enable-libgme --enable-libgsm --enable-libmp3lame --enable-libopenjpeg --enable-libopenmpt --enable-libopus --enable-libpulse --enable-librubberband --enable-libshine --enable-libsnappy --enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora --enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack --enable-libwebp --enable-libx265 --enable-libxvid --enable-libzmq --enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2 --enable-libdc1394 --enable-libiec61883 --enable-chromaprint --enable-frei0r --enable-libopencv --enable-libx264 --enable-shared
libavutil 55. 58.100 / 55. 58.100
libavcodec 57. 89.100 / 57. 89.100
libavformat 57. 71.100 / 57. 71.100
libavdevice 57. 6.100 / 57. 6.100
libavfilter 6. 82.100 / 6. 82.100
libavresample 3. 5. 0 / 3. 5. 0
libswscale 4. 6.100 / 4. 6.100
libswresample 2. 7.100 / 2. 7.100
libpostproc 54. 5.100 / 54. 5.100
Missing argument for option 'v'.
Error splitting the argument list: Invalid argument
root@pano:/var/log/mysql# ffmpeg version 3.3.4-2~16.04.york0 Copyright (c) 2000-2017 the FFmpeg developers
bash: syntax error near unexpected token `('
root@pano:/var/log/mysql# built with gcc 5.4.0 (Ubuntu 5.4.0-6ubuntu1~16.04.4) 20160609
bash: syntax error near unexpected token `('
root@pano:/var/log/mysql# configuration: --prefix=/usr --extra-version='2~16.04.york0' --toolchain=hardened --libdir=/usr/lib/i386-linux-gnu --incdir=/usr/include/i386-linux-gnu --enable-gpl --disable-stripping --enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa --enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca --enable-libcdio --enable-libflite --enable-libfontconfig --enable-libfreetype --enable-libfribidi --enable-libgme --enable-libgsm --enable-libmp3lame --enable-libopenjpeg --enable-libopenmpt --enable-libopus --enable-libpulse --enable-librubberband --enable-libshine --enable-libsnappy --enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora --enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack --enable-libwebp --enable-libx265 --enable-libxvid --enable-libzmq --enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2 --enable-libdc1394 --enable-libiec61883 --enable-chromaprint --enable-frei0r --enable-libopencv --enable-libx264 --enable-shared
configuration:: command not found
root@pano:/var/log/mysql# libavutil 55. 58.100 / 55. 58.100
libavutil: command not found
root@pano:/var/log/mysql# libavcodec 57. 89.100 / 57. 89.100
libavcodec: command not found
root@pano:/var/log/mysql# libavformat 57. 71.100 / 57. 71.100
libavformat: command not found
root@pano:/var/log/mysql# libavdevice 57. 6.100 / 57. 6.100
^C
root@pano:/var/log/mysql# uptime
14:32:04 up 31 days, 2:47, 2 users, load average: 14.95, 14.14, 13.97
root@pano:/var/log/mysql# cat /appl/downloads/mysqltuner-201709071334

jra@pano:~$ /appl/downloads/mysqltuner.pl
>> MySQLTuner 1.7.3 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(530B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 2 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 615M (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31d 1h 47m 44s (10M q [3.971 qps], 1M conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 27% / 73%
[--] Binary logging is disabled
[--] Physical Memory : 5.9G
[--] Max MySQL memory : 352.4M
[--] Other process memory: 5.4G
[--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 36B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 271.7M (4.48% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (5.81% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/10M)
[OK] Highest usage of available connections: 49% (75/151)
[OK] Aborted connections: 0.00% (3/1641072)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (122 temp sorts / 840K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 8% (377 on disk / 4K total)
[OK] Thread cache hit rate: 87% (200K created / 1M connections)
[OK] Table cache hit rate: 72% (256 open / 352 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 36B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (1M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/615.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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.89% (1619644083 hits/ 1621480637 total)
[!!] InnoDB Write Log efficiency: 32.61% (1456527 hits/ 4466052 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5922579 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
=====

/etc/my/my.cnf:
=====
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/serve ... ables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
# for zoneminder
sql_mode = NO_ENGINE_SUBSTITUTION
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
max_allowed_packet = 16M
"/etc/mysql/my.cnf" 107L, 3104C 1,1 Top
=====

[bbunge]

You are still running a 32 bit OS. Did you try the other machine with a fresh install of Ubuntu 64? I suspect with the 32 bit OS you are getting a lot of buffering/swap that is slowing things down and possibly not using all the cores on your processors.

[Baylink]

I'm using about 60MB of swap atop 6GB of physical RAM; free says 1.8GB or so available.

And the other machine, alas, is having disk controller problems, and I haven't been able to get it to boot.

That said, I would expect *less* trouble with the 32-bit install, since the binaries are half the size. No?

[bbunge]

No. MYSQL will hit memory limits sooner. Search for yourself. 32 bit systems should be used for light loads. I set up a Ubuntu 32 bit system tonight on my one camera i3 with 8 gig of RAM and noticed it did not work as well as a 64 bit system. Ubuntu 32 bit OS will set up PAE to use memory above 4 GIG but programs will still be limited to 32 bit memory limits. You would be best to dump your zm database and save the zm.sql to another drive, load the machine with fresh 64 bit OS and set up ZM. Clean out the ZM database then import the saved sql. You will loose events but keep the camera settings.

[jbg70]

I setup 12 ipcam in ZM_1.30.4 on a debian9 vm into esxi6.5. VM dedicated RAM is 8GB. CPU is Xeon E3 1246V3. Disk controller is LSI megaraid 9271-8i 1GB cache. DB is MariaDB, with some changes in cnf file as well as suggested by mysqltuner.
10 cameras are running at 1280x720 @ 25fps and 2 are running at 1280x960 @ 10 fps. Totals fps is 270, so much havier than your setup!
All monitors are in mocord mode.
System load at idle is 4.5 and with several alarms does not go over 6.5.
I experienced high load if in the options->Images->JPEG_FILE_QUALITY and JPEG_STREAM_QUALITY, you set too high values. I got good results setting them at 80. After changing it, restart ZM.

[Baylink]

Ok, well, sure, bbunge, but MySQL says it's good up over 2GB, IIRC, and I'm peaking it at 500-something MB. Or did I misread the report?

I don't particularly mind the exercise, but the bigger the hill it is to climb, the more justification I like to assemble for doing it, and I don't know that I'm there yet.

Is there any way to do any system-level profiling to prove that "mysql is slow returning to ZM" is the place where the load is running up?


And to jbg70: you're in mocord, so in theory, at least, you're not doing any motion detection; that can be a big piece of the puzzle, I would think. I do appreciate the report, though, and I'll check the JPEG parameters.

[Baylink]

I think I'm also going to do the Maria swap-in.

[jbg70]

Mocord is continuos recording + motion detection.

[Baylink]

D'oh. Yep, was thinking nocord, obviously.

[Baylink]

I see I never followed up on this.

I ended up replacing the box with the 4-core i7 it should have been in the first place; I now idle at .8 and don't break 4 with a 12-camera montage review up.

My thanks to all the people who contributed.