2

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)
9
  • 1
    overall your server is lightly used. Your settings aren't too bad. Ignore query cache recommendations. Your mysql tuner stats are just for an hour so its hard to say because I assume no stalls happened in that time.
    – danblack
    May 9 '19 at 10:16
  • 1
    Thanks for looking into this. Perhaps the word 'stall' is a bad choise of word (I'll edit that). It actually crashes on me, Virtualmin restarts the service after a crash. Actions that make it crash are simple edits to Wordpress pages/stylesheet settings. I wanted to provide you with the output from /var/log/mysql/error.log, but after service restart it's empty :-(
    – bebobalula
    May 9 '19 at 10:55
  • 1
    Darn! Sorry, I'm rebooted the server and hence was working locally in terminal. What to look for? I cannot paste large portions in here... Last line reads 2019-05-09T08:22:25.844330Z 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 10000)
    – bebobalula
    May 9 '19 at 11:27
  • 1
    Thanks. Would 10000 do the trick? What would be a good number? Now I have applied: ulimit -n 10000 [Service] LimitNOFILE=10000 LimitMEMLOCK=10000 Let's see how this goes
    – bebobalula
    May 9 '19 at 12:28
  • 1
    Well there it goes again. systemctl status mysql.service gives Process: 16886 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE) Restarting mysql doesn't seem to work... will try system reboot.
    – bebobalula
    May 9 '19 at 15:09
0
key_buffer_size = 384M  --> 80M
max_connections         --> 20

If these changes are not sufficient to get things going, we will need to decrease some more things. 2GB of RAM is "tiny" today.

5
  • Isn’t key_buffer_size a myISAM setting? I only have innoDB tables. Is it correct to assume default mysql.cnf settings are targeted at myISAM primarily? So if I only make use of innoDB what entries can be commented out? Or does mySQL need myISAM as a fallback system?
    – bebobalula
    May 10 '19 at 8:01
  • This server will only host about 20 Wordpress sites. There are currently 2 public and 1 staging sites running. Expected database use would be moderate I think. The busiest site would generate 100 visits/500 database queries a day would be my guess. What would the minimum server requirements be RAM wise for this use profile? Also I have no swap configured since SSD is used. DigitalOcean (server runs here) advices against this to prevent hardware corruption. Would this be advisable nonetheless?
    – bebobalula
    May 10 '19 at 8:02
  • @bebobalula - Something is MyISAM: Key buffer size / total MyISAM indexes: 384.0M/43.0K. Don't set it to zero; 20M is about as low as is safe, even if using all InnoDB for your tables; there are system tables that are MyISAM.
    – Rick James
    May 10 '19 at 16:12
  • @bebobalula - 2G should be plenty for 100/500, so that is not the problem. The problem is overcommitting memory, hence my tuning recommendations.
    – Rick James
    May 10 '19 at 16:19
  • Edited the post
    – bebobalula
    May 11 '19 at 11:51
0

Rate Per Second=RPS - Suggestions to consider for your my.cnf [mysqld] section

read_rnd_buffer_size=256K  # from 8M to conserve RAM and reduce handler_read_rnd_next
read_buffer_size=512K  # from 2M to conserve RAM and reduce handler_read_next
key_buffer_size=80M  # from 402M to conserve RAM
innodb_buffer_pool_size=256M  # from default 128M to allow growth
max_connnections=20  # from 214 until you need more
table_open_cache=800  # from 400 to reduce opened_tables count
query_cache_type=OFF  # from ON to conserve RAM and CPU cycles
query_cache_size=0  # from 8M to conserve RAM and CPU cycles

Disclaimer: I am the author of web content at my profile, Network profile and could provide additional suggestions and FREE Utility Scripts.

8
  • Edited the post.
    – bebobalula
    May 11 '19 at 11:51
  • It went down on me again so I upped the innodb_buffer_pool_size=256M to 512M.
    – bebobalula
    May 13 '19 at 13:47
  • @bebobalula Please post last 400 lines of your error log before the failure. And first 400 lines of error log covering your last 'start' of the instance. And current MySQLTuner.pl report after 24 hours of UPTIME, if you can get there. May 13 '19 at 17:19
  • The log alone will get me way over the 30k characters allowed. How do I go about this? Can I delete previous logs posted in the body? Or does that mess up the post? Using pastebin is a no go I believe? I'm new to Stcackexchange...
    – bebobalula
    May 13 '19 at 17:34
  • @bebobalula pastebin.com is complimentary and allows up to 512,000 bytes per file, multiple files per day. Share the link(s) here, please. Or view profile, Network profile for contact information and get in touch. May 13 '19 at 22:39
0

All in all, assigning swap (although discouraged by Digital Ocean when using ssd) took the edge off and seemed to prevent the crashes. Further tuning with the help of Wilson Hauck was a pleasant process.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.