2

my mysql server creates a tmp table to disk almost on 100% of cases.... and uses a lot of CPU!!

my server:

[--] Physical Memory     : 4.0G
[--] Max MySQL memory    : 976.4M
[--] Other process memory: 682.4M
[--] Total buffers: 816.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 836.2M (20.41% of installed RAM)
[OK] Maximum possible memory usage: 976.4M (23.84% of installed RAM)

here is my conf:

[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=1    # nou

slow_query_log                  = 1
long_query_time = 5

#
# 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         = 32M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
open_files_limit = 4096
table_open_cache = 2048

#
# * Query Cache Configuration
#
query_cache_limit       = 8M
query_cache_size        = 0
query_cache_type = 0
tmp_table_size = 256M
max_heap_table_size = 256M
join_buffer_size = 256K

#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size   = 100M

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
innodb_log_file_size = 64M
innodb_buffer_pool_size = 512M

Full mysql-tuner script:

 >>  MySQLTuner 1.7.4 - 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] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.7.20-0ubuntu0.16.04.1-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(659B)
[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.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 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: 352M (Tables: 2132)
[--] Data in MyISAM tables: 10M (Tables: 9)
[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 is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4d 18h 22m 30s (62M q [152.242 qps], 5M conn, TX: 76G, RX: 6G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 4.0G
[--] Max MySQL memory    : 976.4M
[--] Other process memory: 690.2M
[--] Total buffers: 816.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 836.2M (20.41% of installed RAM)
[OK] Maximum possible memory usage: 976.4M (23.84% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/62M)
[OK] Highest usage of available connections: 12% (19/151)
[OK] Aborted connections: 0.00%  (1/5281057)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 99% (10M on disk / 10M total)
[OK] Thread cache hit rate: 99% (19 created / 5M connections)
[!!] Table cache hit rate: 0% (416 open / 100K opened)
[OK] Open file limit used: 1% (20/1K)
[OK] Table locks acquired immediately: 100% (250 immediate / 250 locks)

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/2.3M
[!!] Read Key buffer hit rate: 76.7% (210 cached / 49 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 512.0M/352.6M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 64.0M * 2/512.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 4 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: 100.00% (6407219480 hits/ 6407228178 total)
[!!] InnoDB Write Log efficiency: 1.76% (848 hits/ 48166 total)
[OK] InnoDB log waits: 0.00% (0 waits / 47318 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:
    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: 
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (431)
Variables to adjust:
    table_open_cache (> 431)

anything I can do to improve performance?

Thanks

  • I'd start with the Recommendations you pasted. – yoonix Feb 2 '18 at 18:23
  • Please post results of ulimit -a so we know file limits imposed by Linux/UNIX. – Wilson Hauck Feb 2 '18 at 19:41
  • 1
    here is the ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 514920 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 – Peter Czask Feb 2 '18 at 20:27
  • 1
    next: pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) 86400 max user processes (-u) 514920 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited – Peter Czask Feb 2 '18 at 20:27
  • @peterczask Please adjust open files from 1024 to 100000, yes 100K. RAM consumption for this is next to nothing to OS. – Wilson Hauck Feb 2 '18 at 20:48
1

New my.cnf and mysql-tuner... not fully solved :)

my.cnf

[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=1    # nou

slow_query_log                  = 1
long_query_time = 5

#
# 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         = 12M
#max_allowed_packet     = 32M
thread_stack            = 192K
thread_cache_size       = 32
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 40 
#table_cache            = 64
#thread_concurrency     = 10
open_files_limit = 40000
table_open_cache = 10000
#
# * Query Cache Configuration
#
query_cache_limit       = 1K
query_cache_size        = 0
query_cache_type = 0
tmp_table_size = 256M
max_heap_table_size = 256M
#join_buffer_size = 256K


# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log


expire_logs_days        = 10
max_binlog_size   = 100M


#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
innodb_log_file_size = 128M     #mysql-tuner suggested 128M
innodb_buffer_pool_size = 1G
innodb_log_buffer_size = 32M
innodb_buffer_pool_instances = 1     #mysql-tuner suggestion

tuner

 >>  MySQLTuner 1.7.4 - 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] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.7.20-0ubuntu0.16.04.1-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(82K)
[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
[!!] /var/log/mysql/error.log contains 176 warning(s).
[!!] /var/log/mysql/error.log contains 40 error(s).
[--] 8 start(s) detected in /var/log/mysql/error.log
[--] 1) 2018-02-02T20:38:10.879518Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-02-02T20:33:44.217394Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-02-02T19:25:03.221166Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-02-02T19:22:53.849515Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-02-02T19:17:18.397386Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-02-02T19:13:53.334283Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-02-02T19:07:44.962318Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-02-02T19:05:32.577164Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2018-02-02T20:38:09.201942Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-02-02T20:33:34.614898Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-02-02T19:25:02.774836Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-02-02T19:22:53.387773Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-02-02T19:17:17.976080Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-02-02T19:13:52.856776Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-02-02T19:07:44.497171Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-02-02T19:05:31.989861Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 10M (Tables: 9)
[--] Data in InnoDB tables: 352M (Tables: 2132)
[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 is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 46m 30s (2M q [296.033 qps], 246K conn, TX: 3G, RX: 291M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 4.0G
[--] Max MySQL memory    : 1.4G
[--] Other process memory: 619.8M
[--] Total buffers: 1.3G global + 1.1M per thread (80 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (32.58% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (34.40% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 24% (10/40)
[OK] Aborted connections: 0.00%  (1/246395)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 737K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 99% (491K on disk / 495K total)
[OK] Thread cache hit rate: 99% (10 created / 246K connections)
[!!] Table cache hit rate: 0% (464 open / 48K opened)
[OK] Open file limit used: 1% (17/1K)
[OK] Table locks acquired immediately: 100% (294 immediate / 294 locks)

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (2M used / 12M cache)
[OK] Key buffer size / total MyISAM indexes: 12.0M/2.3M
[!!] Read Key buffer hit rate: 89.9% (366 cached / 37 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/352.6M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 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: 100.00% (249639541 hits/ 249644879 total)
[!!] InnoDB Write Log efficiency: 29.95% (227 hits/ 758 total)
[OK] InnoDB log waits: 0.00% (0 waits / 531 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 warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    MySQL started within last 24 hours - recommendations may be inaccurate
    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: 

    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (467)
Variables to adjust:
    table_open_cache (> 467)
| improve this answer | |
  • since it is not fully solved, what area remains open? You must guide us. And upvote when you can. You are probably still limited. – Wilson Hauck Feb 22 '18 at 17:06
1

I found the culprit of the temp tables on disk... there was a query that used order by rand()... I modified to not use it and the temp table to disk reduced to 5%

| improve this answer | |
0

Things to do in my.cnf/ini [mysqld] section

thread_cache_size=30  #from 16 to minimize thread creation for 5M connections
#max_allowed_packet=32  # lead with # for default (per connection RAM reduction)
key_buffer_size=12M  #from 32 6M used 2/2/2018
innodb_buffer_pool_size=1G  #from 512M  352M used 2/2/2018 to allow growth
max_connections=40  #from 151 max_used_connection was 19 on 2/2/2018
#join_buffer_size=256K  or remove to allow default to work for you
query_cache_limit=1K  #from 8M because QC not used
innodb_log_buffer_size=32M  #not sure what you have today
innodb_log_file_size=1G  #not sure what you have today
table_open_cache=10000  #to overcome 431 limit after ulimit is raised

100,000 tables opened in 4 days per MySQLTuner

typically only 1 change per working day, MONITOR - in your situation, go for the gusto.

When you NEED max_packet_size > default, within your SESSION, SET @max_allowed_packet=nnn #of bytes upto 1000000000. can not use 1G here! you ARE limited to <=1G file size - workaround SPLIT into multiple files SET @local_infile=1 # to enable use of LOAD DATA LOCAL INFILE using max_allowed_packet=1G in your my.cnf/ini nearly always will CAUSE OOM - if busy.

| improve this answer | |
  • thanks. Done! now Temporary tables created on disk: is 75% – Peter Czask Feb 2 '18 at 20:37
  • Please post complete new MySQLTuner report when you have a few minutes. – Wilson Hauck Feb 2 '18 at 20:51
  • @PeterCzask This must be a RECORD to implement my 11 suggestions in 26 minutes. Thanks for your quick positive feedback. Wish you had enough REP to accept my answer. That's life. – Wilson Hauck Feb 2 '18 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy