Configuring RAM for a workstation (InnoDB/SphinxSearch/Python)

Question

My server borked today. It is built around a Threadripped 3960x, with 126GB RAM. It runs MySQL, SphinxSearch and multi-threaded Python scripts, on Ubuntu.

I currently allocate 60GB to the InnoDB buffer pool, 10GB to MyIsam, SphinxSearch indexes are 46GB. Python will "probably" need something like 12G to run the tasks I need to perform.

MySQLTuner says that memory is OK (as if it is not bothered by Sphinx). As I write this post, Glances reports 39.6G of free memory (36.8G cached; 1.4GB out of 4GB of SWAP used). So all looks nice. Yet it crashed, and I saw segmentation faults. More so after increasing the size of the InnoDB pool to accommodate a new and largish table (now back to 60G and no problem since, but too soon to tell.

The usual suggestion is, for a MySQL server, to allocate 75%+ of available RAM to the pool (96GB in my case), which is not viable when other processes compete for RAM.

I couldn't find a comprehensive discussion wrt memory allocation when a platform is used in a data science context.

Useful sources?

[EDIT]

Even though this is largely irrelevant since this has nothing to do with my actual question (where to find a discussion wrt RAM allocation for a data science work station), below is a partially redacted MySQLTuner output.

  1 [!!] Currently running unsupported MySQL version 8.0.19-0ubuntu0.19.10.3
  2 [OK] Operating on 64-bit architecture
  3  
  4 -------- Log file Recommendations ------------------------------------------------------------------
  5 [OK] /var/log/mysql/error.log doesn't contain any error.
  6  
  7 -------- Storage Engine Statistics -----------------------------------------------------------------
  8 [--] Data in MRG_MYISAM tables: 351.0G (Tables: 2)
  9 [--] Data in MyISAM tables: 589.1G (Tables: 131)
 10 [--] Data in InnoDB tables: 103.9G (Tables: 5)
 11 [OK] Total fragmented tables: 0
 12  
 13 -------- Performance Metrics -----------------------------------------------------------------------
 14 [--] Up for: 1d 4h 42m 38s (4M q [42.365 qps], 869K conn, TX: 2G, RX: 8G)
 15 [--] Reads / Writes: 0% / 100% 
 16 [--] Binary logging is disabled
 17 [--] Physical Memory     : 125.7G
 18 [--] Max MySQL memory    : 81.6G
 19 [--] Other process memory: 9.1G
 20 [--] Total buffers: 74.0G global + 19.3M per thread (400 max threads)
 21 [OK] Maximum reached memory usage: 74.9G (59.57% of installed RAM)
 22 [OK] Maximum possible memory usage: 81.6G (64.87% of installed RAM)
 23 [OK] Overall possible memory usage with other process is compatible with memory available
 24 [OK] Slow queries: 0% (0/4M) 
 25 [OK] Highest usage of available connections: 11% (46/400)
 26 [OK] Aborted connections: 0.00%  (5/869580)
 27 [OK] Sorts requiring temporary tables: 3% (29 temp sorts / 766 sorts)
 28 [OK] No joins without indexes
 29 [OK] Temporary tables created on disk: 0% (0 on disk / 948 total)
 30 [OK] Thread cache hit rate: 99% (46 created / 869K connections)
 31 [!!] Table cache hit rate: 10% (628 open / 6K opened)
 32 [OK] Open file limit used: 2% (219/10K)
 33 [OK] Table locks acquired immediately: 99% (60K immediate / 60K locks)
 34  
 35 -------- MyISAM Metrics ----------------------------------------------------------------------------
 36 [!!] Key buffer used: 26.5% (2B used / 10B cache)
 37 [OK] Key buffer size / total MyISAM indexes: 10.0G/210.8G
 38 [OK] Read Key buffer hit rate: 99.2% (676M cached / 5M reads)
 39 [!!] Write Key buffer hit rate: 11.0% (96M cached / 10M writes)
 40  
 41 -------- InnoDB Metrics ----------------------------------------------------------------------------
 42 [--] InnoDB is enabled. 
 43 [--] InnoDB Thread Concurrency: 0
 44 [OK] InnoDB File per table is activated
 45 [!!] InnoDB buffer pool / data size: 64.0G/103.9G
 46 [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (18.75 %): 6.0G * 2/64.0G should be equal 25%
 47 [OK] InnoDB buffer pool instances: 64
 48 [--] Number of InnoDB Buffer Pool Chunk : 512 for 64 Buffer Pool Instance(s)
 49 [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
 50 [OK] InnoDB Read buffer efficiency: 99.91% (2063007809 hits/ 2064876225 total)
 51 [OK] InnoDB Write log efficiency: 97.25% (325355628 hits/ 334571627 total)
 52 [OK] InnoDB log waits: 0.00% (0 waits / 9215999 writes)
 53  
 54 -------- Recommendations ---------------------------------------------------------------------------
 55 General recommendations: 
 56     Control warning line(s) into /var/log/mysql/error.log file
 57     Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
 58     Increase table_open_cache gradually to avoid file descriptor limits
 59     Read this before increasing table_open_cache over 64:
 60     Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
 61     This is MyISAM only table_cache scalability problem, InnoDB not affected.
 62     See more details here: https://bugs.mysql.com/bug.php?id=49177
 63     This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
 64     Beware that open_files_limit (10000) variable 
 65     should be greater than table_open_cache (1000)
 66     Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
 67 Variables to adjust:
 68     table_open_cache (> 1000)
 69     innodb_buffer_pool_size (>= 103.9G) if possible.
 70     innodb_log_file_size should be (=8G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

[EDIT 2]

Added partially redacted MySQL globals & Glances, and SWAP/Paging stats culled by SYSSTAT. Notice, perhaps the relatively large SWAP allocation (68G). Most likely lots of "wasted" space but since I have a large amount of drive space, not a worry. Clearly excessive but will adjust based on SWAP/paging stats. Not glances "warning" wrt memory pressure. Probably sign that the warning assumes an 8GB machine. Note also that MyQQLtuner suggests a 100G+ innoDB buffer, which is unrealistic. But 'Id like to push it above the current 60G nonetheless. Storage is NVMe RAID0 (7+GB/s) (with regular rsync())

Paging stats

08:35:01 AM  pswpin/s pswpout/s
08:45:01 AM      0.05      0.04
08:55:01 AM      0.05      0.00
09:05:01 AM      0.04      0.00
09:15:01 AM      0.04      0.01
09:25:01 AM      0.04      0.01
09:35:01 AM      0.03      0.02

SWAP memory stats

08:35:01 AM kbswpfree kbswpused  %swpused  kbswpcad   %swpcad
08:45:01 AM     67.4G    652.5M      0.9%    137.2M     21.0%
08:55:01 AM     67.4G    652.5M      0.9%    137.2M     21.0%
09:05:01 AM     67.4G    652.5M      0.9%    137.2M     21.0%
09:15:01 AM     67.4G    652.2M      0.9%    137.1M     21.0%
09:25:01 AM     67.4G    652.2M      0.9%    137.2M     21.0%

Glances report

pop-os (Ubuntu 19.10 64bit / Linux 5.3.0-7642-generic) - IP 192.168.2.11/24 Pub xxx.xxx.xxx.xxx                                Uptime: 2 days, 22:40:52

CPU  [  0.5%]   CPU -     0.5%  nice:     0.0%  ctx_sw:    3K       GPU GeForce GTX 1       MEM -   71.6%       SWAP -    0.9%       LOAD    48-core
MEM  [ 71.6%]   user:     0.4%  irq:      0.0%  inter:   2371       proc:          0%       total:   126G       total:   68.0G       1 min:    0.37
SWAP [  0.9%]   system:   0.1%  iowait:   0.0%  sw_int:   879       mem:           4%       used:   90.1G       used:     637M       5 min:    0.33
                idle:    99.5%  steal:    0.0%                                              free:   35.6G       free:    67.4G       15 min:   0.29

NETWORK                  Rx/s   Tx/s   TASKS 581 (1007 thr), 1 run, 350 slp, 230 oth sorted automatically by memory consumption
enp68s0                   4Kb    1Kb
lo                       264b   264b   CPU%   MEM%  VIRT  RES      PID USER          TIME+ THR  NI S  R/s W/s  Command
                                       0.3    62.9  90.0G 79.1G   1661 mysql      12h14:52 161   0 S    ? ?    /usr/sbin/mysqld
DefaultGateway                  31ms   0.3    6.0   51.1G 7.50G  57191 analyst       10:04 81    0 S    0 0    /home/analyst/sphinx-3.2.1/bin/searchd
                                       22.0   0.5   2.55G 669M   39164 analyst     4h48:08 1     0 R    0 1K   /usr/bin/python3 /usr/bin/glances
DISK I/O                  R/s    W/s   0.0    0.1   2.79G 71.4M   1815 gdm            1:15 12    0 S    ? ?    /usr/bin/gnome-shell
dm-0                        0      0   0.3    0.0   2.50G 58.0M  59975 analyst        0:05 34    0 S    0 0    /usr/bin/python3 /home/analyst/.vim/bu
md126                       0      0   0.0    0.0   159M  27.9M    840 root           0:02 1    -1 S    ? ?    /lib/systemd/systemd-journald
md127                       0      0   0.0    0.0   175M  23.8M   1685 gdm            0:05 2     0 S    ? ?    /usr/lib/xorg/Xorg vt1 -displayfd 3 -a
nvme0n1                     0      0   0.0    0.0   2.23G 14.9M  59972 analyst        0:13 32    0 S    0 0    vim _getComments.py
nvme0n1p1                   0      0   0.0    0.0   348M  8.98M   2048 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-key
nvme1n1                     0      0   0.0    0.0   178M  8.95M  84003 root           0:00 3     0 S    ? ?    /usr/sbin/cups-browsed
nvme1n1p1                   0      0   0.0    0.0   32.6M 8.89M 109769 root           0:00 1     0 S    ? ?    mysql
nvme2n1                     0      0   0.0    0.0   40.2M 8.79M  60015 analyst        0:00 1     0 S    0 0    /usr/bin/python3 /home/analyst/.vim/bu
nvme2n1p1                   0      0   0.0    0.0   18.7M 8.72M 109332 root           0:00 1     0 S    ? ?    sshd: analyst [priv]
nvme3n1                     0      0   0.0    0.0   18.7M 8.10M  82827 root           0:00 1     0 S    ? ?    sshd: analyst [priv]
nvme3n1p1                   0      0   0.0    0.0   1.86G 7.64M   1497 root           0:03 1     0 S    ? ?    /usr/bin/python3 /usr/bin/glances -s
nvme4n1                     0     1K   0.0    0.0   18.7M 7.17M  76386 root           0:00 1     0 S    ? ?    sshd: analyst [priv]
nvme4n1p1                   0      0   0.0    0.0   47.2M 7.11M   1397 root           0:00 1     0 S    ? ?    /usr/bin/python3 /usr/bin/networkd-dis
nvme4n1p2                   0      0   0.0    0.0   341M  7.08M   1433 root           0:04 3     0 S    ? ?    /usr/sbin/NetworkManager --no-daemon
nvme4n1p3                   0     1K   0.0    0.0   165M  7.04M      1 root           0:13 1     0 S    ? ?    /sbin/init splash
nvme4n1p4                   0      0   0.0    0.0   385M  6.98M   1434 root           0:00 5     0 S    ? ?    /usr/lib/udisks2/udisksd
nvme5n1                     0      0   0.0    0.0   502M  6.88M   2032 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-col
nvme5n1p1                   0      0   0.0    0.0   18.7M 6.58M  59923 root           0:00 1     0 S    ? ?    sshd: analyst [priv]
nvme6n1                     0      0   0.0    0.0   111M  6.57M  84002 root           0:00 1     0 S    ? ?    /usr/sbin/cupsd -l
nvme6n1p1                   0      0   0.0    0.0   18.7M 6.50M  60098 root           0:00 1     0 S    ? ?    sshd: analyst [priv]
                                       0.0    0.0   348M  6.47M   2045 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-pow
FILE SYS                 Used  Total   0.0    0.0   19.1M 6.45M 109364 analyst        0:00 1     0 S    ? ?    3
/ (nvme4n1p3)            454G   908G   0.0    0.0   250M  6.40M   2103 colord         0:00 3     0 S    ? ?    /usr/lib/colord/colord
/BU (md127)              560G  1.83T   0.0    0.0   230M  6.35M   1517 root           0:02 3     0 S    ? ?    /usr/lib/policykit-1/polkitd --no-debu
/data (md126)            609G  1.79T   0.0    0.0   753M  6.20M   2053 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-med
/recovery (nvme4n1p2)   2.36G  3.99G   0.0    0.0   18.7M 6.00M  39114 root           0:00 1     0 S    ? ?    sshd: analyst [priv]
                                       0.0    0.0   19.1M 5.88M  82859 analyst        0:00 1     0 S    ? ?    5
RAID disks               Used  Avail   0.0    0.0   411M  5.37M   1805 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-session/gnome-session-b
RAID0 md126                 2      -   0.0    0.0   19.1M 5.33M  76419 analyst        0:00 1     0 S    ? ?    4
RAID0 md127                 4      -   0.0    0.0   306M  5.24M   1432 root           0:00 3     0 S    ? ?    /usr/sbin/ModemManager --filter-policy
                                       0.0    0.0   256M  5.24M   1965 root           0:00 3     0 S    ? ?    /usr/lib/upower/upowerd
SENSORS                                0.0    0.0   223M  5.20M  38840 analyst        0:00 3   -11 S    0 0    /usr/bin/pulseaudio --daemonize=no
SYSTIN                           28C   0.0    0.0   243M  5.12M   1576 root           0:00 3     0 S    ? ?    /usr/sbin/gdm3
CPUTIN                           33C   0.0    0.0   348M  5.11M   2033 gdm            0:01 4     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-xse
Tdie                             46C   0.0    0.0   20.1M 5.08M 109365 analyst        0:00 1     0 S    0 0    -bash
Tctl                             46C   0.0    0.0   345M  4.94M   2145 gdm            0:00 3     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-pri
                                       0.0    0.0   26.6M 4.89M 109768 root           0:00 1     0 S    ? ?    sudo mysql
                                       0.0    0.0   14.8M 4.87M  84036 lp             0:00 1     0 S    ? ?    /usr/lib/cups/notifier/dbus dbus:// 
                                       0.0    0.0   20.1M 4.86M  82860 analyst        0:00 1     0 S    0 0    -bash
                                       0.0    0.0   178M  4.83M   1653 root           0:00 3     0 S    ? ?    gdm-launch-environment]
                                       0.0    0.0   19.1M 4.83M  59958 analyst        0:00 1     0 S    ? ?    0
                                       0.0    0.0   347M  4.80M   1982 gdm            0:00 4     0 S    ? ?    /usr/lib/ibus/ibus-x11 --kill-daemon
                                       0.0    0.0   20.1M 4.74M  76420 analyst        0:00 1     0 S    0 0    -bash
                                       0.0    0.0   347M  4.73M   2035 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-wac
                                       0.0    0.0   19.1M 4.73M  60130 analyst        0:09 1     0 S    ? ?    1
                                       0.0    0.0   19.0M 4.52M  39152 analyst        0:07 1     0 S    ? ?    2
                                       0.0    0.0   9.68M 4.47M   1412 messagebu      0:04 1     0 S    ? ?    /usr/bin/dbus-daemon --system --addres
                                       0.0    0.0   243M  4.44M   1436 root           0:02 3     0 S    ? ?    /usr/lib/accountsservice/accounts-daem
                                       0.0    0.0   20.4M 4.43M   1383 systemd-r      0:53 1     0 S    ? ?    /lib/systemd/systemd-resolved
                                       0.0    0.0   252M  4.38M   2031 gdm            0:00 3     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-pri
                                       0.0    0.0   242M  4.20M   2339 root           0:00 3     0 S    ? ?    /usr/lib/bolt/boltd
                                       0.0    0.0   18.4M 3.82M   2310 analyst        0:00 1     0 S    0 0    /lib/systemd/systemd --user
                                       0.0    0.0   267M  3.74M   1774 gdm            0:00 4     0 S    ? ?    /usr/lib/gnome-session/gnome-session-b
                                       0.0    0.0   16.2M 3.68M   1431 root           0:00 1     0 S    ? ?    /lib/systemd/systemd-logind
                                       0.0    0.0   350M  3.65M   1388 root           0:03 6     0 S    ? ?    /usr/bin/pop-upgrade daemon
                                       0.0    0.0   317M  3.43M   2029 gdm            0:00 5     0 S    ? ?    /usr/lib/gnome-settings-daemon/gsd-sma

                                       High memory consumption
                                       2020-04-09 08:03:30 (ongoing) - MEM (71.7)

MySQL globals

------------------------------------------------------------------+
| Variable_name                                         | Value 
+-------------------------------------------------------+-----------------| Aborted_clients                                       | 9  
| Aborted_connects                                      | 7  
| Bytes_received                                        | 15128841585 
| Bytes_sent                                            | 3894567345  
| Com_admin_commands                                    | 2326733  
| Com_begin                                             | 5  
| Com_change_db                                         | 384
| Com_commit                                            | 64448 
| Com_create_db                                         | 1  
| Com_create_table                                      | 432
| Com_delete                                            | 202
| Com_drop_table                                        | 420
| Com_flush                                             | 7  
| Com_insert                                            | 2289982  
| Com_insert_select                                     | 116
| Com_load                                              | 109
| Com_lock_tables                                       | 18 
| Com_rename_table                                      | 1  
| Com_select                                            | 11127 
| Com_set_option                                        | 6955771  
| Com_show_create_func                                  | 3  
| Com_show_create_table                                 | 667
| Com_show_databases                                    | 28 
| Com_show_engine_status                                | 4  
| Com_show_fields                                       | 835
| Com_show_function_status                              | 26 
| Com_show_keys                                         | 17 
| Com_show_procedure_status                             | 23 
| Com_show_processlist                                  | 38 
| Com_show_slave_hosts                                  | 1  
| Com_show_slave_status                                 | 1  
| Com_show_status                                       | 4  
| Com_show_storage_engines                              | 1  
| Com_show_table_status                                 | 368
| Com_show_tables                                       | 46 
| Com_show_triggers                                     | 360
| Com_show_variables                                    | 28 
| Com_truncate                                          | 36 
| Com_unlock_tables                                     | 15 
| Com_update                                            | 25421 
| Com_update_multi                                      | 168
| Connections                                           | 2317785  
| Created_tmp_files                                     | 326
| Created_tmp_tables                                    | 1928  
| Current_tls_ca                                        | ca.pem
| Current_tls_cert                                      | server-cert.pem 
| Current_tls_key                                       | server-key.pem  
| Current_tls_version                                   | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
| Flush_commands                                        | 6  
| Handler_commit                                        | 2274457  
| Handler_delete                                        | 27812140 
| Handler_external_lock                                 | 67855375 
| Handler_read_first                                    | 12790 
| Handler_read_key                                      | 391325575
| Handler_read_last                                     | 373
| Handler_read_next                                     | 664372409
| Handler_read_prev                                     | 61672829 
| Handler_read_rnd                                      | 58877032 
| Handler_read_rnd_next                                 | 3293654196  
| Handler_update                                        | 253770790
| Handler_write                                         | 301001614
| Innodb_buffer_pool_dump_status                        | Dumping of buffer pool not started 
| Innodb_buffer_pool_load_status                        | Buffer pool(s) load completed at 200406 17:06:16 
| Innodb_buffer_pool_pages_data                         | 3678440  
| Innodb_buffer_pool_bytes_data                         | 60267560960 
| Innodb_buffer_pool_pages_flushed                      | 29131111 
| Innodb_buffer_pool_pages_free                         | 66018 
| Innodb_buffer_pool_pages_misc                         | 449846
| Innodb_buffer_pool_pages_total                        | 4194304  
| Innodb_buffer_pool_read_ahead                         | 468754
| Innodb_buffer_pool_read_ahead_evicted                 | 184556
| Innodb_buffer_pool_read_requests                      | 2964325318  
| Innodb_buffer_pool_reads                              | 6052218  
| Innodb_buffer_pool_wait_free                          | 1343  
| Innodb_buffer_pool_write_requests                     | 339070408
| Innodb_data_fsyncs                                    | 6189745  
| Innodb_data_pending_fsyncs                            | 47 
| Innodb_data_read                                      | 178272179200
| Innodb_data_reads                                     | 10881128 
| Innodb_data_writes                                    | 42038814 
| Innodb_data_written                                   | 998294239744
| Innodb_dblwr_pages_written                            | 29131192 
| Innodb_dblwr_writes                                   | 329933
| Innodb_log_write_requests                             | 289208990
| Innodb_log_writes                                     | 12317906 
| Innodb_os_log_fsyncs                                  | 54945 
| Innodb_os_log_written                                 | 29770651136 
| Innodb_page_size                                      | 16384 
| Innodb_pages_created                                  | 983218
| Innodb_pages_read                                     | 10881093 
| Innodb_pages_written                                  | 29131333 
| Innodb_rows_inserted                                  | 28771085 
| Innodb_rows_read                                      | 3341654628  
| Innodb_rows_updated                                   | 58409363 
| Innodb_system_rows_deleted                            | 5860  
| Innodb_system_rows_inserted                           | 6255  
| Innodb_system_rows_read                               | 154443
| Innodb_system_rows_updated                            | 1127  
| Innodb_num_open_files                                 | 152
| Innodb_undo_tablespaces_total                         | 2  
| Innodb_undo_tablespaces_implicit                      | 2  
| Innodb_undo_tablespaces_active                        | 2  
| Key_blocks_unused                                     | 8467954  
| Key_blocks_used                                       | 7529603  
| Key_read_requests                                     | 2466462788  
| Key_reads                                             | 16762725 
| Key_write_requests                                    | 268675258
| Key_writes                                            | 43521415 
| Max_used_connections                                  | 48 
| Max_used_connections_time                             | 2020-04-09 04:10:02
| Mysqlx_address                                        | :: 
| Mysqlx_port                                           | 33060 
| Mysqlx_socket                                         | /var/run/mysqld/mysqlx.sock  
| Mysqlx_ssl_ctx_verify_depth                           | 18446744073709551615  
| Mysqlx_ssl_ctx_verify_mode                            | 5  
| Mysqlx_ssl_server_not_after                           | Feb 23 07:05:50 2030 GMT 
| Mysqlx_ssl_server_not_before                          | Feb 26 07:05:50 2020 GMT 
| Mysqlx_worker_threads                                 | 2  
| Open_files                                            | 196
| Open_table_definitions                                | 262
| Open_tables                                           | 433
| Opened_files                                          | 196
| Opened_table_definitions                              | 1342  
| Opened_tables                                         | 14379 
| Performance_schema_session_connect_attrs_longest_seen | 206
| Queries                                               | 17248784 
| Questions                                             | 11669042 
| Select_full_join                                      | 145
| Select_range                                          | 316
| Select_scan                                           | 3634  
| Sort_merge_passes                                     | 130
| Sort_rows                                             | 4489141  
| Sort_scan                                             | 1862  
| Ssl_accepts                                           | 2317707  
| Ssl_ctx_verify_depth                                  | 18446744073709551615  
| Ssl_ctx_verify_mode                                   | 5  
| Ssl_finished_accepts                                  | 2317706  
| Ssl_server_not_after                                  | Feb 23 07:05:50 2030 GMT 
| Ssl_server_not_before                                 | Feb 26 07:05:50 2020 GMT 
| Ssl_session_cache_mode                                | SERVER
| Ssl_session_cache_size                                | 128
| Ssl_used_session_cache_entries                        | 18 
| Table_locks_immediate                                 | 67009 
| Table_locks_waited                                    | 132
| Table_open_cache_hits                                 | 33915254 
| Table_open_cache_misses                               | 14379 
| Table_open_cache_overflows                            | 10555 
| Threads_cached                                        | 45 
| Threads_connected                                     | 3  
| Threads_created                                       | 48 
| Threads_running                                       | 2  
| Uptime                                                | 251184
| Uptime_since_flush_status                             | 251184

___it crashed, and I saw segmentation faults___ So if you show us that, maybe someone will be able to help — RiggsFolly, Apr 02 '20 at 20:23
Be kind and post the complete MySQLTuner report (after 24 hours of uptime - it you can get there), please, as well as first two pages of TOP or HTOP. — Wilson Hauck, Apr 02 '20 at 22:28
Interesting -- I wasn't asking about fixes. All is well and the most likely reason for segmentation fault was the competition between MySQL and other processes. I was asking about sources that consider this type of problem. I frankly don't understand the reasons for downvoting. — user3127882, Apr 02 '20 at 23:46
Please, be kind and post the complete MySQLTuner report (after 24 hours of uptime - if you can get there), as well as first two pages of TOP or HTOP. The last 400 lines of your error log after an interruption of service, before the restart could be very helpful. — Wilson Hauck, Apr 03 '20 at 07:45
Thanks for posting your MySQLTuner - partial report. Additional information request. # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top first 2 pages, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. — Wilson Hauck, Apr 08 '20 at 17:56
@WilsonHauck -- added the details as per your suggestion. Just to reiterate: all is fine now. Borked while I tried to push innoDB buffer pool. Notice that the system still has plenty of free memory (30GB+) yet glances issues a warning... I've added plenty of SWAP and increased swappiness to 40. Will keep a close eye on performance. Thing is that on a workstation, massive amounts of anonymous pages a created by scripts/DBs. Lots of space + aggressive purging is potentially the way to go. — user3127882, Apr 09 '20 at 20:57
One more thing -- aborted connections are perfectly normal. It is me loosing patience on an exploratory request, figuring a (much) better way while waiting :) — user3127882, Apr 09 '20 at 20:59
@RiggsFolly -- segmentation faults in Python are resolved by allocating more memory to the script (resource.setrlimit(resource.RLIMIT_STACK, (largeNumber,largeNumber)) /// solves execution but doesn't answer question wrt RAM management on workstation. Bill Karwin, below, provides useful guidance. I've scaled back InnoDB pool, will push progressively keeping an eye on paging and performance. — user3127882, Apr 09 '20 at 22:40
@user3127882 I see from Glances you have NVME, that is good. Thanks for the SHOW GLOBAL STATUS even if not complete. Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available include - ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, to allow start of server workload tuning analysis to provide suggestions. — Wilson Hauck, Apr 09 '20 at 23:43

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Yes, the usual suggestion of dedicating 75% or 80% of RAM to the buffer pool is based on the assumption that the only process on that host that has high demands on RAM is mysqld. In many sites, the database is running on its own host, and apps run on a separate host, so they don't compete for the same RAM resource.

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size says:

On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.

https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/ says:

It’s expected to occupy most of the RAM on a dedicated MySQL/Innodb server, but of course, other local services may affect how it is tuned.

https://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_buffer_pool_size/ says:

On a dedicated MySQL server running all InnoDB, as a rule of thumb, recommendation is to set the innodb-buffer-pool-size to 80% of the total available memory on the server.

Why not 90% or 100%?

Because other things need memory too...

https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/ says:

The considerations in this blog post are for Linux systems that are dedicated for MySQL.

The assumption that this applies when mysqld is the only significant consumer of RAM is a pretty consistent qualifier, every time I've seen the advice given.

It's up to you to adjust this ratio depending on the RAM requirements of other services on the same host.

Re your comment:

"...guidance as to how to take competing processes into account"

Well, you have some of the numbers:

Sphinx Search - your index is 46GB. See http://sphinxsearch.com/blog/2011/11/11/sphinx-memory-consumption/ for some guidance on estimating memory usage. Subtract the size of the .spd and .spp files. Add rt_mem_limit. Add mem_limit for when you build indexes. But for purposes of this example, let's just call it 46GB.
Python - you estimate you will need 12GB of memory.
MyISAM - I wouldn't bother allocating any large amounts of memory to MyISAM. I always prefer to store data in InnoDB. InnoDB is better for performance than MyISAM, and also MyISAM does not support ACID. And you can concentrate the memory on the InnoDB buffer pool. So just leave the default MyISAM key buffer value of 8MB.
Ubuntu needs some memory for basic OS activity. Not a lot, maybe 4GB is plenty.
Filesystem cache is flexible. Linux will employ memory for file caching if it's not being used by other apps. That's why you often see figures like 36.8GB in cache. But if other apps need memory, the cache usage will automatically scale down. I'd expect it be of benefit to allow at least 2-4GB though.

Subtract the figures above from your system RAM. Let's say it comes out to about 60GB available for MySQL.

The InnoDB buffer pool needs about 10% extra beyond what you allocate, because it maintains some data structures like lists of free pages. Also MySQL uses some more memory besides the InnoDB buffer pool. This depends on the number of clients connected, complexity of queries, size of result sets. This is hard to predict, and it can grow quickly and without warning. There's no way to put a hard limit on MySQL memory usage.

I would choose about 32GB for the InnoDB buffer pool size under these conditions. But continue to monitor the size of resident memory (RSS) of the mysqld process in top or ps. If it looks like it regularly exceeds the 60GB we expected, you might have to reduce the buffer pool, or install more physical RAM on the server, or move Sphinx Search or Python to their own separate servers.

Server tuning depends a lot on continual monitoring. There's not usually a single correct answer that you can set and forget about it.

Thanks for your input. Very similar to what I am familiar with, but provides little guidance as to how to take competing processes into account — user3127882, Apr 02 '20 at 23:42
Thanks for your thoughts. (1) wrt sphinx -- I am OK with how it manages caches. Might be possible to terminate/flush/swap since I rarely run massive parallel queries on these indexes. I usually query manually or thru single threads. (2) I am very ambivalent wrt MyISAM vs InnoDB. My use cases (zero transaction/tons of inserts/fast BU/small footprint) favor MyISAM is most cases. I use InnoDB to parallel query partitions and on tables that require massive updates. (3) totally agree with your guidance. A bit more agressive (60GB for the pool + 68G SWAP). Will keep an eye on perf and update — user3127882, Apr 09 '20 at 22:25
It's up to you, but I'm a strong advocate of retiring MyISAM. Read the answer I linked to where I wrote MyISAM does not support ACID. That doesn't require transactions to show how MyISAM can mess up your data. — Bill Karwin, Apr 10 '20 at 13:17
yes, and I believe I've read your post some time ago. Thing is that for me, footprint and ease of backup win the day, by a wide margin. I do not UPDATE most MyISAM. Plain inserts. Much much easier to quickly move data across machines or backups (copy MYI, MYD and you are good to go). I hear the argument wrt corruption. But this is infrequent (had issues with MyISAMpack). Restore and move on works for me. This being said, thanks again for your insights — user3127882, Apr 10 '20 at 17:04

Configuring RAM for a workstation (InnoDB/SphinxSearch/Python)

1 Answers1