Mariadb - temp tables

Now I'm seeing;

TEMP TABLES
Current max_heap_table_size = 7.90 G
Current tmp_table_size = 7.90 G
Of 125478 temp tables, 41% were created on disk

1. disable query_cache
2. increase inodb-buffer-pool-size to cover your data or 2/3 of your total memory. (Considering this server is hosting only mariadb)
3. Your present stats looks good though let's improve this further to 1 sec.
> "You have 0 out of 26347304 that take longer than 2.000000 sec. to complete"
Change your long_query_time to 1 sec and review slow queries.
4. Please enable log_queries_not_using_indexes. Possibly that is what creating tmptbales, fast but using disk.
5. Also consider reviewing your table definitions and allocate least required datatype.

Hi, thanks for the reply.

MysqlTuner shows different stats then does tuning-primer.sh. I've settled on the later.
I'll include both just in case something pops out.

While this server handles a few dbs such as wordpress, the majority of its use are very small connections, mostly read.

Here is how things look now. I've gotten things under control other than the disk writes.
I'll make the changes you suggest and let it run for a while.

# ./tuning-primer.sh

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.52-MariaDB x86_64

Uptime = 4 days 17 hrs 33 min 45 sec
Avg. qps = 1128
Total Questions = 461307557
Threads Connected = 16

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 2.000000 sec.
You have 46 out of 461307717 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 512
Current threads_cached = 31
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 15
Historic max_used_connections = 46
The number of used connections is 46% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 162 M
Current InnoDB data space = 128 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 512 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 2.54 G
Configured Max Per-thread Buffers : 3.04 G
Configured Max Global Buffers : 1.14 G
Configured Max Memory Limit : 4.18 G
Physical Memory : 15.48 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 2 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 2
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 291 M
Current query_cache_limit = 5 M
Current Query cache Memory fill ratio = 56.85 %
Current query_cache_min_res_unit = 256 bytes
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 8 M
Current read_rnd_buffer_size = 16 M
Sort buffer seems to be fine

JOINS
./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 2.00 M
You have had 25 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4945 tables
Current table_definition_cache = 3000 tables
You have a total of 332 tables
You have 378 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 9.76 G
Current tmp_table_size = 5.95 G
Of 28605 temp tables, 44% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 5 M
Current table scan ratio = 0 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 5350813
Your table locking seems to be fine

Select allOpen in new window

# ./mysqltuner.pl
 >>  MySQLTuner 1.6.1 - 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
[OK] Currently running supported MySQL version 5.5.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 280K (Tables: 36)
[--] Data in InnoDB tables: 291M (Tables: 255)
[!!] Total fragmented tables: 20

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 17h 38m 44s (461M q [1K qps], 166K conn, TX: 373B, RX: 124B)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 7.2G global + 31.2M per thread (100 max threads)
[OK] Maximum reached memory usage: 8.6G (55.66% of installed RAM)
[OK] Maximum possible memory usage: 10.3G (66.27% of installed RAM)
[OK] Slow queries: 0% (46/461M)
[OK] Highest usage of available connections: 46% (46/100)
[OK] Aborted connections: 0.00%  (1/166906)
[OK] Query cache efficiency: 28.4% (182M cached / 643M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 2% (1K temp sorts / 49K sorts)
[!!] Temporary tables created on disk: 80% (22K on disk / 28K total)
[OK] Thread cache hit rate: 99% (46 created / 166K connections)
[!!] Table cache hit rate: 6% (378 open / 5K opened)
[OK] Open file limit used: 1% (145/10K)
[OK] Table locks acquired immediately: 99% (278M immediate / 278M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.5M
[!!] Read Key buffer hit rate: 57.9% (599 cached / 252 reads)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 512.0M/291.0M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 99.98% (32762 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 100.00% (136143425928 hits/ 136143435812 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 658477 writes)

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

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (10000) variable
    should be greater than table_open_cache ( 4945)
Variables to adjust:
    table_open_cache (> 4945)

Select allOpen in new window

July 24/17
Query Cache is 23 % fragmented (cleared by running --execute="FLUSH QUERY CACHE")
Of 23150 temp tables, 44% were created on disk

Changed query_cache_size from 512M to 0
Changed query_cache_type from 1 to 0
Changed inodb_buffer_pool_size from 512M to 5120M
Changed long_query_time from 2 to 1
Added log_queries_not_using_indexes to my.cnf

Where will this log be? I cannot find enough information on how to use it. Do I have to set the path?

I run FLUSH QUERY CACHE from the command line since there doesn't seem to be any way of automating this since it needs the root mysql passwd.

I also made this change on the centos 7 server.

Swappiness
A low swappiness setting is recommended for database workloads. For MariaDB databases, it is recommended to set swappiness to
a value of 1.
vm.swappiness = 1

Set the swappiness to zero:
/etc/sysctl.conf)
vm.swappiness = 0

Some 24hrs later since the changes above, not really enough time to tell but;
Of 8733 temp tables, 39% were created on disk

Also, now that query caching is disabled, do I really need to run FLUSH QUERY CACHE on a regular basis?

Of 25936 temp tables, 39% were created on disk

Slightly better. What else do you need?

Hello, have you abandoned the question?

Hi Mark!

Apologies for making it seem like abandoned but it is not.

Now that you said "query caching is disabled" then there is no need to do much about it. But if query cache is disabled, why it still say: "Query cache is enabled" in previous output?
- Make sure your query cache is disabled.
- Also if it is disabled there is nothing to be flushed!

- On Disk tables => are your indexes in place?  (This is first priority)
Please set long_query_time=1 and log-queries-not-using-indexes.
Review slow queries and indexing.


For later,
- Your database is tiny (<500M) not sure if max-heap-table-size/tmp-table-size of almost 8G is really needed!!

- Grab fragmented tables and optimize them.
select table_schema,table_name from information_schema.tables where data_free>0 order by data_free desc limit 20;

Hi, no issues.

This shows that query caching is disabled. Here is the full report so you can see it all for yourself. Oddly, still showing 39% on disk.
I recently had both  max-heap-table-size/tmp-table-size set to 8096.

I really don't understand how such powerful software doesn't simply auto tune itself based on it's own internal testing.

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.52-MariaDB x86_64

Uptime = 7 days 7 hrs 21 min 36 sec
Avg. qps = 751
Total Questions = 474732333
Threads Connected = 17

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 1.000000 sec.
You have 685792 out of 474732354 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 512
Current threads_cached = 28
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 17
Historic max_used_connections = 45
The number of used connections is 45% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 177 M
Current InnoDB data space = 135 M
Current InnoDB buffer pool free = 50 %
Current innodb_buffer_pool_size = 5.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 6.51 G
Configured Max Per-thread Buffers : 3.04 G
Configured Max Global Buffers : 5.14 G
Configured Max Memory Limit : 8.18 G
Physical Memory : 15.48 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 2 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 2
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 8 M
Current read_rnd_buffer_size = 16 M
Sort buffer seems to be fine

JOINS
./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 2.00 M
You have had 53 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4945 tables
Current table_definition_cache = 3000 tables
You have a total of 332 tables
You have 336 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 9.76 G
Current tmp_table_size = 5.95 G
Of 79711 temp tables, 39% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 5 M
Current table scan ratio = 0 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 8935027
Your table locking seems to be fine

Select allOpen in new window

[root@pwdb mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
user=mysql
skip-show-database
skip-name-resolve

collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
log_queries_not_using_indexes

expire_logs_days = 10
ft_min_word_len = 4
interactive_timeout=120
join_buffer_size = 2M
long_query_time = 1
low_priority_updates = 1
max_connections = 100
max_connect_errors = 10
max_allowed_packet = 16M
max_heap_table_size = 10000M
max_write_lock_count = 1
query_cache_limit = 5M
query_cache_min_res_unit = 256
query_cache_size = 0
query_cache_type = 0
read_buffer_size = 5M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
table_cache = 10000
table_definition_cache = 3000
table_open_cache = 5000
thread_cache_size = 512
thread_concurrency = 8
thread_stack = 192K
tmp_table_size = 6096M

connect_timeout = 30
wait_timeout=30

innodb_buffer_pool_size = 5120M
innodb_lock_wait_timeout=800
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON
default-storage-engine=INNODB

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

Select allOpen in new window

"why-no-auto-tune":  my wild guess would be the creator left some job for themselves until minimum wage gets lucrative!!! :)

Anyways, now those 685792 slow queries should tell you why so many tmp disk tables!

>Anyways, now those 685792 slow queries should tell you why so many tmp disk tables!

How do I look for that?

Check slow query log:

show global variables like '%slow%';

Select allOpen in new window

Use the file and read. You may use pt-query-digest tool.

I've enabled the slow query log.

MariaDB [(none)]> show global variables like '%slow%';
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                                        |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries    | ON                                                                                                           |
| log_slow_rate_limit | 1                                                                                                            |
| log_slow_verbosity  |                                                                                                              |
| slow_launch_time    | 2                                                                                                            |
| slow_query_log      | ON                                                                                                           |
| slow_query_log_file | /var/log/mariadb/mariadb_slow.log                                                                            |
+---------------------+--------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

Temp tables have risen since adding the logging.

Of 13017 temp tables, 42% were created on disk

So what slow queries do you see now?
Can you reduce your long_query_time (set global long_query_time=1) and review the slow log again in a day?

Ah, interesting. I never used the logging to find problems this way. Here is what I am seeing, many many entries. All similar to this.
Now I can better understand at least partly why this is happening. Something is connecting every second. I look on the sending server and there are 11 connections to the db server. There is nothing in that code that should be connecting every second unless something is tre-trying but that is not clear yet or at least, I cannot tell from this log information.

# Time: 170817  8:03:44
# User@Host: nmapsrv[nmapsrv] @  [10.0.0.9]
# Thread_id: 14  Schema: nmap_reports  QC_hit: No
# Query_time: 0.000122  Lock_time: 0.000032  Rows_sent: 7  Rows_examined: 7
SET timestamp=1502982224;
SELECT * FROM one_ip_list;
# Time: 170817  8:03:45
# User@Host: nmapsrv[nmapsrv] @  [10.0.0.9]
# Thread_id: 14  Schema: nmap_reports  QC_hit: No
# Query_time: 0.000144  Lock_time: 0.000033  Rows_sent: 7  Rows_examined: 7
SET timestamp=1502982225;
SELECT * FROM one_ip_list;
# Time: 170817  8:03:46
# User@Host: nmapsrv[nmapsrv] @  [10.0.0.9]
# Thread_id: 14  Schema: nmap_reports  QC_hit: No
# Query_time: 0.000120  Lock_time: 0.000032  Rows_sent: 7  Rows_examined: 7
SET timestamp=1502982226;
SELECT * FROM one_ip_list;
# Time: 170817  8:03:47
# User@Host: nmapsrv[nmapsrv] @  [10.0.0.9]
# Thread_id: 14  Schema: nmap_reports  QC_hit: No
# Query_time: 0.000128  Lock_time: 0.000034  Rows_sent: 7  Rows_examined: 7
SET timestamp=1502982227;
SELECT * FROM one_ip_list;

We made a change in the code related to the above which has helped slightly. It seems however that we could configure more memory to be used on this server.

MEMORY USAGE
Max Memory Ever Allocated : 6.08 G
Configured Max Per-thread Buffers : 3.04 G
Configured Max Global Buffers : 5.14 G
Configured Max Memory Limit : 8.18 G
Physical Memory : 15.48 G
Max memory limit seem to be within acceptable norms

TEMP TABLES
Current max_heap_table_size = 9.76 G
Current tmp_table_size = 5.95 G
Of 10083 temp tables, 37% were created on disk

Mark,

That was, as the name suggests, slow query log. Checking for slow queries and tuning them is mostly going to be the best optimization.
Since you pointed tmp-disk-tables, I'd guess they're bad queries.
Do following:

wget percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /path/to/slow.log > digest.out

Select allOpen in new window

Check first 5 queries.

Only 1 Q about your config for now --> Is that 3.4G = max_connections x Per-thread-buffer ? (Which I expect it should be)

We have been watching those, they aren't bad queries, they just seem to take longer than what mariadb considers to be fast.

# ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out
/var/log/mariadb/mariadb_slow.log:  46% 00:34 remain
/var/log/mariadb/mariadb_slow.log:  96% 00:02 remain
# ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out
/var/log/mariadb/mariadb_slow.log:  46% 00:34 remain
# ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out
/var/log/mariadb/mariadb_slow.log:  47% 00:33 remain
# ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out
/var/log/mariadb/mariadb_slow.log:  45% 00:35 remain
/var/log/mariadb/mariadb_slow.log:  96% 00:02 remain
# ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out
/var/log/mariadb/mariadb_slow.log:  45% 00:36 remain
/var/log/mariadb/mariadb_slow.log:  95% 00:02 remain

>Is that 3.4G = max_connections x Per-thread-buffer ?

MEMORY USAGE
Max Memory Ever Allocated : 6.60 G
Configured Max Per-thread Buffers : 3.04 G
Configured Max Global Buffers : 5.14 G
Configured Max Memory Limit : 8.18 G
Physical Memory : 15.48 G
Max memory limit seem to be within acceptable norms

This is the result that the tuning-primer script gives me. According to this, I could give more memory to DB since the server is dedicated to this only.

TEMP TABLES
Current max_heap_table_size = 9.76 G
Current tmp_table_size = 5.95 G
Of 32105 temp tables, 39% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables

This part is interesting because I've made a lot of tuning changes to get rid of the temp tables but no matter how much I set these two to, it barely makes any difference. What I need to find out is what is being written to disk no?

" Check first 5 queries."

I'm sorry, I'm not sure what this means, check first 5 queries?

The mysqltuner script offers very different suggestions including saying that memory is dangerously high while tuning-primer.sh says there is plenty more that could be used. Mind you, the service has only been up for 4 days since being last restarted to make the changes in testing.

# ./mysqltuner.pl
 >>  MySQLTuner 1.6.1 - 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
[OK] Currently running supported MySQL version 5.5.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 418K (Tables: 53)
[--] Data in InnoDB tables: 484M (Tables: 308)
[!!] Total fragmented tables: 24

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 0h 2m 29s (2M q [8.071 qps], 111K conn, TX: 10B, RX: 426M)
[--] Reads / Writes: 88% / 12%
[--] Binary logging is disabled
[--] Total buffers: 11.2G global + 31.2M per thread (100 max threads)
[OK] Maximum reached memory usage: 12.7G (81.88% of installed RAM)
[!!] Maximum possible memory usage: 14.3G (92.10% of installed RAM)
[OK] Slow queries: 2% (74K/2M)
[OK] Highest usage of available connections: 48% (48/100)
[OK] Aborted connections: 0.00%  (1/111239)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 53K sorts)
[!!] Temporary tables created on disk: 64% (22K on disk / 35K total)
[OK] Thread cache hit rate: 99% (48 created / 111K connections)
[!!] Table cache hit rate: 7% (434 open / 6K opened)
[OK] Open file limit used: 1% (180/10K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.6M
[!!] Read Key buffer hit rate: 63.9% (878 cached / 317 reads)
[!!] Write Key buffer hit rate: 76.7% (258 cached / 60 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 5.0G/484.2M
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 42.99% (140881 used/ 327679 total)
[OK] InnoDB Read buffer efficiency: 100.00% (680876917 hits/ 680887803 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 197306 writes)

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

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce your overall MySQL memory footprint for system stability
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (10000) variable
    should be greater than table_open_cache ( 4945)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (>= 8M)
    table_open_cache (> 4945)
    innodb_buffer_pool_instances(=5)
[/code]

" # ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out" << What did we do to this digest.out. That file contains your slow query log analysis. You need to check queries in there. Start with top 5.


About dangerously high!! Well your data size is small and you have sufficient buffers allocated to them, actually more than size.
Fix your queries & indexes and things should be fine.

Sorry, still not sure what this means;

The command we used;
# ./pt-query-digest /var/log/mariadb/mariadb_slow.log > digest.out"

Shows only percentages, it doesn't show any specific queries. However, I posted the queries above in another comment.

What you posted had only "SELECT * FROM one_ip_list;" which is tiny table.
When you run that command the output is redirected to digest.out. What I meant is to look inside that file or may be just see:

cat digest.out | head -200

Select allOpen in new window

I'm here emphasizing on query tuning than variables.

Oh, I'm sorry, I didn't even notice that file. I just cut/paste your command.
There is an awful lot of information in that which I am not sure about posting in public.

# 63.9s user time, 80ms system time, 101.61M rss, 295.62M vsz
# Current date: Tue Aug 22 17:16:11 2017
# Hostname: pwdb.domain.com
# Files: /var/log/mariadb/mariadb_slow.log
# Overall: 495.82k total, 470 unique, 0.58 QPS, 0.00x concurrency ________
# Time range: 2017-08-12 19:51:11 to 2017-08-22 17:16:05
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           236s    26us      3s   475us     3ms    11ms   119us
# Lock time            18s       0   233ms    36us    57us   451us    31us
# Rows sent         40.44M       0 722.65k   85.53  621.67   4.23k    6.98
# Rows examine      72.04M       0 722.65k  152.35   1.04k   5.82k    6.98
# Query size        21.99M      22 1017.87k   46.51   69.19   3.29k   24.84

# Profile
# Rank Query ID           Response time  Calls  R/Call V/M   Item
# ==== ================== ============== ====== ====== ===== =============
#    1 0x92F3B1B361FB0E5B 110.1173 46.7%  30050 0.0037  0.00 SELECT wp_options
#    2 0xF4A4D63B675CA943  55.1331 23.4% 440596 0.0001  0.00 SELECT one_ip_list
#    3 0x67A347A2812914DF  22.6120  9.6%   2534 0.0089  0.38 SELECT one_port_scan
#    4 0xF69A844307CC9868  15.7589  6.7%     15 1.0506  0.00 SELECT one_port_scan
#    5 0x9BB49BD0AAC85F64   4.7255  2.0%      2 2.3628  0.02 INSERT UPDATE SELECT UPDATE SELECT UPDATE SELECT wp_postmeta
#    6 0xC08AB40FE8019189   4.1420  1.8%      3 1.3807  0.05 INSERT
#    7 0xC446CA7B74F08DB7   3.4272  1.5%      2 1.7136  0.01 INSERT UPDATE SELECT wp_statpress
#    8 0xC17B5A2401034F1C   3.4071  1.4%      2 1.7036  0.00 UPDATE
#    9 0x16947434387AD9E0   2.8702  1.2%      2 1.4351  0.00 INSERT DELETE UPDATE DELETE INSERT DELETE UPDATE REPLACE UPDATE DELETE UPDATE DELETE UPDATE DELETE UPDATE REPLACE UPDATE DELETE UPDATE DELETE UPDATE DELETE UPDATE REPLACE UPDATE DELETE UPDATE DELETE UPDATE DELETE UPDATE REPLACE UPDATE DELETE UPDATE DELETE UPDATE DELETE wp_options
#   10 0x2EBDA09098F5E627   2.5919  1.1%      2 1.2960  0.00 INSERT SELECT REPLACE DELETE UPDATE REPLACE SELECT REPLACE DELETE UPDATE SELECT DELETE SELECT UPDATE SELECT UPDATE wp_posts
# MISC 0xMISC              10.8641  4.6%  22615 0.0005   0.0 <460 ITEMS>

# Query 1: 0.04 QPS, 0.00x concurrency, ID 0x92F3B1B361FB0E5B at byte 121403023
# Scores: V/M = 0.00
# Time range: 2017-08-12 19:53:01 to 2017-08-22 17:13:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   30050
# Exec time     46    110s   956us    45ms     4ms     5ms     2ms     3ms
# Lock time      9      2s    11us     2ms    56us    66us    25us    57us
# Rows sent     46  18.64M     649     675  650.59  621.67    1.50  621.67
# Rows examine  48  34.86M     948   1.35k   1.19k   1.26k  108.27   1.20k
# Query size     9   2.03M      71      71      71      71       0      71
# String:
# Databases    19004.com (21886/72%), 19004works (8164/27%)
# Hosts        10.0.0.5
# Users        19004.com (21886/72%), 19004works (8164/27%)
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `19004.com` LIKE 'wp_options'\G
#    SHOW CREATE TABLE `19004.com`.`wp_options`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'\G

# Query 2: 0.52 QPS, 0.00x concurrency, ID 0xF4A4D63B675CA943 at byte 47886596
# Scores: V/M = 0.00
# Time range: 2017-08-12 19:51:11 to 2017-08-22 17:16:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         88  440596
# Exec time     23     55s    52us   233ms   125us   144us   475us   119us
# Lock time     83     15s    14us   233ms    34us    44us   475us    30us
# Rows sent      7   2.90M       6       7    6.90    6.98    0.37    6.98
# Rows examine   4   2.90M       6       7    6.90    6.98    0.37    6.98
# Query size    47  10.50M      25      25      25      25       0      25
# String:
# Databases    nmap_reports
# Hosts        10.0.0.9
# Users        nmapsrv
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms
#  10ms  #
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `nmap_reports` LIKE 'one_ip_list'\G
#    SHOW CREATE TABLE `nmap_reports`.`one_ip_list`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM one_ip_list\G

# Query 3: 0.00 QPS, 0.00x concurrency, ID 0x67A347A2812914DF at byte 121157759
# Scores: V/M = 0.38
# Time range: 2017-08-13 00:00:09 to 2017-08-22 00:00:15
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2534
# Exec time      9     23s    26us   797ms     9ms    23ms    58ms    44us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     46  18.73M       0 722.65k   7.57k   2.89k  58.73k    3.89
# Rows examine  26  18.73M       0 722.65k   7.57k   2.89k  58.73k    3.89
# Query size     0 143.21k      45      88   57.87   69.19    6.90   56.92
# String:
# Databases    receiver (764/30%), forums (710/28%)... 5 more
# Hosts        localhost
# Users        all_back
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ###########
#   1ms  #####
#  10ms  #####
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `nmap_reports` LIKE 'one_port_scan'\G
#    SHOW CREATE TABLE `nmap_reports`.`one_port_scan`\G
SELECT /*!40001 SQL_NO_CACHE */ * FROM `one_port_scan`\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /*!40001 SQL_NO_CACHE */ * FROM `one_port_scan`\G

# Query 4: 0.00 QPS, 0.00x concurrency, ID 0xF69A844307CC9868 at byte 101298175
# Scores: V/M = 0.00
# Time range: 2017-08-12 19:51:12 to 2017-08-17 10:56:32
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      15
# Exec time      6     16s      1s      1s      1s      1s    40ms      1s
# Lock time      0    47ms    54us    23ms     3ms    22ms     7ms    60us
# Rows sent      0      15       1       1       1       1       0       1
# Rows examine  13   9.69M 603.04k 665.76k 661.36k 650.62k  15.09k 650.62k
# Query size     0   1.33k      91      91      91      91       0      91
# String:
# Databases    nmap_reports
# Hosts        10.0.0.9
# Users        nmapsrv
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `nmap_reports` LIKE 'one_port_scan'\G
#    SHOW CREATE TABLE `nmap_reports`.`one_port_scan`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT MAX(run) FROM one_port_scan WHERE ip_address='x.x.x.x' AND service_option_id=3\G

# Query 5: 0.00 QPS, 0.00x concurrency, ID 0x9BB49BD0AAC85F64 at byte 112110023
# Scores: V/M = 0.02
# Time range: 2017-08-18 10:32:36 to 2017-08-20 15:08:45
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       2
# Exec time      2      5s      2s      3s      2s      3s   226ms      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     7   1.58M 811.33k 811.48k 811.40k 811.48k  111.02 811.40k
# String:
# Databases    19004.com (1/50%), receiver (1/50%)
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `receiver` LIKE 'wp_postmeta'\G
#    SHOW CREATE TABLE `receiver`.`wp_postmeta`\G
INSERT INTO `wp_postmeta` VALUES (446,70,'_cc_page_slider_on','0') /*... omitted ...*/\G

# Query 6: 0.00 QPS, 0.00x concurrency, ID 0xC08AB40FE8019189 at byte 130363627
# Scores: V/M = 0.05
# Time range: 2017-08-18 00:03:16 to 2017-08-22 00:03:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       3
# Exec time      1      4s      1s      2s      1s      2s   254ms      1s
# Lock time      0   893us    64us   759us   297us   725us   310us    69us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0      12       4       4       4       4       0       4
# Query size     0     498     166     166     166     166       0     166
# String:
# Databases    receiver
# Hosts        10.0.0.5
# Users        recuser
# Query_time distribution
#   1us
#  10us
# 100us

Select allOpen in new window

Yes, I am mainly tuning based on the results of the mysql testing scripts and not because the server is slow for example.
However, I am trying to optimize the db because we've seen a lot of locks in the past and this is when I started doing this, to get rid of those, which for the most part I have.

The load is very low right now but will become very high in the near future which is why I am trying to get this now while I can.

This is what I have in my.cnf

log_queries_not_using_indexes
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/mariadb_slow.log
# long_query_time = 1

So it seems that log_queries_not_using_indexes is already enabled.

The long_query_time is not enabled in my config.
Mariadb defaults; Default Value: 10.000000 >= MariaDB 10.1.13, 10 <= MariaDB 10.1.12
Should I enable this?

In one of the first results above, I see the following
Rows examine  48  34.86M     948   1.35k   1.19k   1.26k  108.27   1.20k

Does it mean that this select has to read a 34.5MB table each time it is running?

# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   30050
# Exec time     46    110s   956us    45ms     4ms     5ms     2ms     3ms
# Lock time      9      2s    11us     2ms    56us    66us    25us    57us
# Rows sent     46  18.64M     649     675  650.59  621.67    1.50  621.67
# Rows examine  48  34.86M     948   1.35k   1.19k   1.26k  108.27   1.20k
# Query size     9   2.03M      71      71      71      71       0      71

Select allOpen in new window

In total 30050 queries total 34.86M(illion) rows were examined
Min rows examined for a query were 948
Max .................................................... 1.35K
Avg  .................................................... 1.19k
95% of time ....................................... 1.26k  
Standard Deviation .......................... 108.27  
Median .............................................. 1.20k
refer documentation here.

and yes do reduce the long_query_time=1 to grab queries taking longer than 1 second

I've made the change and will let it run a while then post.

I have installed another 16GB in this server. I'll run the test again once some time has passed. Overnight however, temp tables changed to 35%, the first change I've seen since it's been at 39% since we started this testing.

Don't make chasing tmp tables your goal. If no slow queries logged, no performance issues, no congestion or locked queries  you're good to leave it alone.

But I've read countless times that disk writes are a bad thing for databases as it can lead to swapping. That is mainly what I'm trying to avoid and would like to put most if not everything in memory in fact.

"disk writes are a bad thing " For sure...  hence.... make sure no slow queries... and no queries not using indexes...

I think you are saying that the programming is what needs to be changed. I think you mentioned it above but I lost track somewhere.

Programming?? I think I mean queries.

Yes but those queries would be how the programming is done no?

sort of yes...

It's run for days now, should I post another capture as you asked for or are we pretty certain it's the queries?
If it's the queries, you seem confused about my asking if this means the programming.

The queries are based on the programming done in php, which is the app that communicates with the db to begin with.
Isn't this what you are saying might need attention?

So yes .. go ahead and grab some slow queries... Run pt-slow-query-digest against your slow queries file and post it here.

Thank you very much for sticking to this question and getting to a point where I was able to give enough information that you can confirm we have no problems.

At some point, we'll have someone taking care of this and in the meantime, can rest more easily now.

Keep your eyes on slow-queries and you should be good. Have a good time ahead.