I get frequent database crashes… explainable since mysqltuner tells me mysql has an appetite for memory. Since I'm not a database expert I hope you can point me in the right direction where to look and what to edit.
I'm running:
- Ubuntu 16.04.6 64bit
- Intel(R) Xeon(R) CPU E5-2630L v2 @ 2.40GHz
- 2GB RAM
- MySQL version 5.7.26
- Managed by Virtualmin
I added the Speedemy settings to my.cnf to see if it would do anything for my problems. The server stills runs out of memory though.
mysqltuner output
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 147M (Tables: 203)
[!!] Total fragmented tables: 14
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 0m 2s (7K q [2.004 qps], 894 conn, TX: 22M, RX: 994K)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 592.0M global + 12.5M per thread (214 max threads)
[OK] Maximum reached memory usage: 642.0M (32.10% of installed RAM)
[!!] Maximum possible memory usage: 3.2G (163.34% of installed RAM)
[OK] Slow queries: 0% (0/7K)
[OK] Highest usage of available connections: 1% (4/214)
[OK] Aborted connections: 0.00% (0/894)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 355 sorts)
[OK] Temporary tables created on disk: 15% (262 on disk / 1K total)
[OK] Thread cache hit rate: 99% (4 created / 894 connections)
[!!] Table cache hit rate: 14% (383 open / 2K opened)
[OK] Open file limit used: 2% (22/1K)
[OK] Table locks acquired immediately: 100% (164 immediate / 164 locks)
[OK] Binlog cache memory access: 100.00% ( 153 Memory / 153 Total)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.7% (75M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/43.0K
[!!] Read Key buffer hit rate: 94.3% (122 cached / 7 reads)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/147.8M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.12% (1648 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 98.95% (142629 hits/ 144137 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 189 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
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
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 (1024) variable
should be greater than table_open_cache ( 400)
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 (> 400)
innodb_buffer_pool_size (>= 147M) if possible.
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 402653184 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 8388608 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
24 rows in set (0.00 sec)
/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
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
bind-address = 127.0.0.1
key_buffer_size = 384M
max_allowed_packet = 1M
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 32M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_file_per_table = 1
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_open_cache = 512
my.cnf
(I added the speedemy settings to the 'empty' default settings)
### This my.cnf file is based on the following speedemy.com blog post:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof
[client]
###
### client section - used for "mysql", "mysqladmin" and similar command line
### clients.
###
#port = 3306
#socket = /tmp/mysql.sock
### Just in case your current configuration is not using default values.
[mysqld]
###
### mysqld section - used by MySQL Server (also applies to Percona Server,
### MariaDB etc.)
###
#port = 3306
#socket = /tmp/mysql.sock
#user = mysql
### Just in case your current configuration is not using default values.
datadir = /var/lib/mysql
### This must point to the main MySQL data directory.
###
### General Server Options:
###
max_allowed_packet = 32M
### Default packet limit is almost always too small.
max_connections = 2000
### Max connections as well (sleeping threads are okay to have)
#table_open_cache = 2000
### Table open cache - defaults only good in MySQL 5.7 and 5.6
### On 5.1 or 5.5 set the above.
#table_open_cache_instances = 16
### Table open cache instances - be sure to set this on MySQL 5.6.
open_files_limit = 10000
### Increase the number of file descriptors, we're not in stone age now.
tmp_table_size = 64M
max_heap_table_size = 64M
### Incease limits for implicit and explicit temporary tables.
tmpdir = /tmp
### Don't use tmpfs for tmpdir, or AIO will be disabled. And make sure
### There's enough room for temporary tables and files (usually 2-5GB)
#thread_cache_size = 100
### Thread cache - this is now autosized in MySQL 5.6 and 5.7
### But on MySQL 5.1 and 5.5 do set the above
default_storage_engine = InnoDB
### Default storage engine in most cases should be InnoDB.
skip_name_resolve
### Disabling DNS resolution
query_cache_type=0
query_cache_size=0
#back_log = 512
### Consider increasing this if you have a high number of new connections
### (> 1000/sec) and you are running on MySQL 5.6 or older
#thread_concurrency = 0
### Do not tune this. This does nothing. And I have included it here only
### because I've seen too many people obsess over it.
#join_buffer_size = 256k
#sort_buffer_size = 256k
#read_buffer_size = 128k
#read_rnd_buffer_size = 256k
### Leave these at their defaults, do not change server-wide settings for them
### Instead, use session variable when you really need it like that:
### mysql> SET session read_buffer_size = 2 * 1024 * 1024;
### mysql> RUN YOUR QUERY;
###
### Binary logging section
###
log_bin
server_id = 1
### Enabling binlog as well as unique server_id for
### point in time recovery and, potentially, replication.
max_binlog_size = 100M
### Don't have large binary logs, with file systems like ext3 this could
### cause stalls.
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
### If you're on MySQL 5.6 or MySQL 5.7, store the binlog position
### to TABLE rather than FILE (that way position is in sync with db)
expire_logs_days = 7
### Control the amount of binary logs server is allowed to keep (in days)
sync_binlog = 0
### Disabling sync-binlog for better performance, but do consider the
### durability issues
binlog_format = MIXED
### For the binary log format, I recommend MIXED, but it's up to you.
###
### MyISAM Specific Options:
###
### Assuming MyISAM is not going to be used, therefore defaults used for MyISAM
### Still if you do plan to use it, this is probably the only variable you
### want to tune:
#key_buffer_size = 128M
###
### InnoDB Specific Options:
###
#innodb_buffer_pool_size = 96G
### Set the innodb buffer pool size to 75-80% of total memory (dedicated)
### The example above would be used on a dedicated 128GB MySQL server.
#innodb_log_file_size = 2047M
### Allow 1-2h worth of writes to be buffered in transaction logs:
### Helper command:
### mysql> pager grep seq
### mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
innodb_log_buffer_size = 16M
### Don't sweat about about this, just set it to 16M.
innodb_flush_log_at_trx_commit = 0
### This, OTOH is really important.
innodb_flush_method = O_DIRECT
### On Linux, just leave it set to O_DIRECT.
#innodb_buffer_pool_instances = 4
### Set this depending on MySQL version. Available since 5.6
### In 5.6, set to 4
### In 5.7, set to 8
innodb_thread_concurrency = 8
### Yes, this is not an error. Use 0 only for benchmarks and if you're nowhere
### near saturating your server.
#innodb_io_capacity = 1000
#innodb_io_capacity_max = 3000
### Base these on your server radom write IO capabilities
innodb_stats_on_metadata = 0
### On 5.6 and 5.7 this is already turned off by default.
#innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
#innodb_buffer_pool_load_at_startup = 1 # MySQL 5.6+
#innodb_buffer_pool_dump_pct = 75 # MySQL 5.7 only
### Enable these for faster warm-up
#innodb_adaptive_hash_index_parts = 16 # MySQL 5.7 only
#innodb_adaptive_hash_index_partitions = 16 # Percona Server only
### AHI is a common bottle-neck, however few versions of MySQL
#innodb_checksum_algorithm = crc32 # MySQL 5.6 or newer
### Hardware acceleratorion for checksums
innodb_read_io_threads = 16
innodb_write_io_threads = 16
### This isn't super important to fine tune, but it's good to aling
### it to number of availble read / write spindles
innodb_file_per_table
### There's a number of reasons to use file per table, but beware that
### it doesn't convert tables automatically, and ibdata1 will never shrink anyways
#innodb_open_files = 3000
### Set this on MySQL 5.1 and 5.5. On 5.6 and beyond it's autotuned
### specifies max number of .ibd files that MySQL can keep open at one time
#innodb_flush_neighbors = 0
### Set to 0 if you're using SSD. For magnetic drives, keep it at 1
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors
#innodb_flushing_avg_loops = 100
### Use this if you don't like how flushing activity behaves
### (e.g. if you get stalls)
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flushing_avg_loops
#innodb_page_cleaners = 8 # MySQL 5.7 only
### On a write-heavy environment, use this to gain even more control
### over the flushing activity. This is not yet tested too wildly.
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners
#innodb_old_blocks_time = 1000
### Use this only on 5.1 and 5.5 since 5.6 and 5.7 already have 1000 as a default
### This basically protects your buffer pool from occasional scans
### Although the way it works is quite complex, definitely not for my.cnf
#innodb_max_dirty_pages_pct
### If you're in a write-heavy environment, but want to limit how much of it
### you use for dirty pages, this is the variable to configure. Defaults are
### version specific, but range in 75-90 %
###
### Slow query log (mostly Percona XtraDB Specific)
###
long_query_time = 1.0
### Log queries that take > 1s to execute
slow_query_log = 1
### Enable the slow query logging
### Only to be used on Percona Server:
#log_slow_slave_statements = ON
#log_slow_verbosity = full
#slow_query_log_timestamp_always = ON
#slow_query_log_timestamp_precision = microsecond
#slow_query_log_use_global_control = all
### See here for more information:
### https://www.percona.com/doc/percona-server/5.6/diagnostics/slow_extended.html
I ran ulimit -n 10000
and added [Service] LimitNOFILE=infinity LimitMEMLOCK=infinity
to /etc/systemd/system/mysql.service
It still crashes on me. Here's the log:
2019-05-09T15:10:39.704727Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-09T15:10:39.711331Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-0ubuntu0.16.04.1-log) starting as process 18050 ...
2019-05-09T15:10:39.716998Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=server3-bin' to avoid this problem.
2019-05-09T15:10:39.745721Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-05-09T15:10:39.745762Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-05-09T15:10:39.745770Z 0 [Note] InnoDB: Uses event mutexes
2019-05-09T15:10:39.745776Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-05-09T15:10:39.745786Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-05-09T15:10:39.745793Z 0 [Note] InnoDB: Using Linux native AIO
2019-05-09T15:10:39.750629Z 0 [Note] InnoDB: Number of pools: 1
2019-05-09T15:10:39.758809Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-05-09T15:10:39.765671Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-05-09T15:10:39.766744Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-05-09T15:10:39.766761Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-05-09T15:10:39.766769Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-05-09T15:10:39.766782Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-05-09T15:10:39.766789Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-05-09T15:10:39.766796Z 0 [ERROR] Failed to initialize builtin plugins.
2019-05-09T15:10:39.766801Z 0 [ERROR] Aborting
2019-05-09T15:10:39.767196Z 0 [Note] Binlog end
2019-05-09T15:10:39.767262Z 0 [Note] Shutting down plugin 'CSV'
2019-05-09T15:10:39.769116Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2019-05-09T15:11:21.632996Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-09T15:11:21.643635Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-0ubuntu0.16.04.1-log) starting as process 1848 ...
2019-05-09T15:11:21.675629Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=server3-bin' to avoid this problem.
2019-05-09T15:11:22.173868Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-05-09T15:11:22.173905Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-05-09T15:11:22.173922Z 0 [Note] InnoDB: Uses event mutexes
2019-05-09T15:11:22.173929Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-05-09T15:11:22.173935Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2019-05-09T15:11:22.173941Z 0 [Note] InnoDB: Using Linux native AIO
2019-05-09T15:11:22.194558Z 0 [Note] InnoDB: Number of pools: 1
2019-05-09T15:11:22.197089Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-05-09T15:11:22.215371Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-05-09T15:11:22.309717Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-05-09T15:11:22.341933Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-05-09T15:11:22.372313Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-05-09T15:11:22.387121Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2329544419
2019-05-09T15:11:22.387148Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2329544428
2019-05-09T15:11:22.387926Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-05-09T15:11:22.387933Z 0 [Note] InnoDB: Starting crash recovery.
2019-05-09T15:11:22.536639Z 0 [Note] InnoDB: Last MySQL binlog file position 0 3849, file name server3-bin.000038
2019-05-09T15:11:23.145013Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-05-09T15:11:23.145044Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-05-09T15:11:23.145092Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-05-09T15:11:23.161018Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-05-09T15:11:23.161941Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-05-09T15:11:23.161956Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-05-09T15:11:23.162243Z 0 [Note] InnoDB: Waiting for purge to start
2019-05-09T15:11:23.212875Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2329544428
2019-05-09T15:11:23.213350Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-05-09T15:11:23.213959Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-05-09T15:11:23.273185Z 0 [Note] Recovering after a crash using server3-bin
2019-05-09T15:11:23.273251Z 0 [Note] Starting crash recovery...
2019-05-09T15:11:23.273283Z 0 [Note] Crash recovery finished.
2019-05-09T15:11:23.298008Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-05-09T15:11:23.298088Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2019-05-09T15:11:23.298102Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2019-05-09T15:11:23.298134Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2019-05-09T15:11:23.347833Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190509 17:11:23
2019-05-09T15:11:23.366913Z 0 [Note] Failed to start slave threads for channel ''
2019-05-09T15:11:23.443956Z 0 [Note] Event Scheduler: Loaded 0 events
2019-05-09T15:11:23.444130Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.26-0ubuntu0.16.04.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
[EDIT]
I took the advice of Rick James and Wilson Hauck. Max Memory plays nice now. Perhaps a little conservative, but let's see how it holds up.
[OK] Maximum reached memory usage: 419.2M (20.96% of installed RAM)
[OK] Maximum possible memory usage: 481.0M (24.05% of installed RAM)
Best Answer
If these changes are not sufficient to get things going, we will need to decrease some more things. 2GB of RAM is "tiny" today.