Skip to main content

MYSQL degrading over 24 hours period

Ray
Hi, MYSQL server slows down when running for more than 24 hours. I'm not sure if my SQL is getting full and why the speed is degrading\. Rebooting the server bring speed back to normal again. Below is what's showing after the last reboot which is about 8 hours. [root@sydney ~]# mysqladmin proc status +-------+------------------+-----------+--------------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------------------+-----------+--------------------+---------+------+-------+------------------+ | 98 | root | localhost | | Sleep | 27 | | | | 48672 | asxxer | localhost | asxxn_assess_db | Sleep | 1 | | | | 48673 | efxxnusr | localhost | exxdb | Sleep | 0 | | | | 48674 | root | localhost | | Query | 0 | init | show processlist | +-------+------------------+-----------+--------------------+---------+------+-------+------------------+ Uptime: 28800 Threads: 4 Questions: 22535205 Slow queries: 0 Opens: 126901 Flush tables: 3 Open tables: 4000 Queries per second avg: 782.472
top - 08:03:07 up 8:38, 0 users, load average: 2.26, 2.45, 2.53 Tasks: 219 total, 2 running, 215 sleeping, 0 stopped, 2 zombie %Cpu(s): 14.3 us, 2.8 sy, 0.3 ni, 82.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 32005396 total, 10531896 free, 3413288 used, 18060212 buff/cache KiB Swap: 1048572 total, 1048572 free, 0 used. 26101896 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 16014 mysql 20 0 4263236 1.1g 15104 S 34.2 3.5 405:23.63 mysqld 232049 xxxx+ 20 0 322940 75712 13264 S 31.9 0.2 0:00.96 php-cgi 2017 root 20 0 770376 13164 10344 S 1.0 0.0 11:58.37 db_governor 2209 root 30 10 383188 63724 4508 S 1.0 0.2 2:39.24 python3 2185 root 30 10 586180 47696 2020 S 0.7 0.1 2:24.06 python3 9 root 20 0 0 0 0 S 0.3 0.0 1:09.78 rcu_sched 1280 elastic+ 20 0 8175596 585412 11028 S 0.3 1.8 2:25.16 java 8707 root 20 0 1956176 11196 6752 S 0.3 0.0 0:27.92 PassengerAgent 230745 nobody 20 0 341588 20928 4200 S 0.3 0.1 0:00.10 httpd

Comments

14 comments

Date Votes
  • cPRex Jurassic Moderator
    Hey there! 4000 actively open tables seems high, so I'm wondering if it is hitting that limit and then experiencing the issue. Do you see that limit in place in your MySQL configuration?
    0
  • Ray
    So having Open tables: 4000 is bad? I only restart SQL an hour ago and it's now max at 4000 already. I'm not sure if this is normal. [root@sydney ~]# mysqladmin proc status +------+---------------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+---------------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+ | 95 | root | localhost | | Sleep | 50 | | | | 2671 | txxser | localhost | trxxres_db | Query | 0 | executing | SELECT `main_table`.* FROM `sm_megamenu_items` AS `main_table` WHERE (main_table.parent_id = 50) AND | | 2672 | root | localhost | | Query | 0 | init | show processlist | +------+---------------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+ Uptime: 1410 Threads: 3 Questions: 970644 Slow queries: 0 Opens: 8196 Flush tables: 3 Open tables: 4000 Queries per second avg: 688.400
    MySQL config. The install is standard and I only added the following into the config "skip-name-resolve", "event_scheduler=off" [mysqld] skip-name-resolve performance-schema = 0 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid innodb_buffer_pool_size = 52428800 max_allowed_packet = 268435456 open_files_limit = 40000 innodb_file_per_table = 1 default-authentication-plugin = mysql_native_password sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION event_scheduler=off
    mysql> SHOW GLOBAL STATUS LIKE 'Open_%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Open_files | 1609 | | Open_streams | 0 | | Open_table_definitions | 652 | | Open_tables | 4000 | | Opened_files | 1609 | | Opened_table_definitions | 1530 | | Opened_tables | 9874 | +--------------------------+-------+ 7 rows in set (0.00 sec)
    0
  • cPRex Jurassic Moderator
    Thanks for that. It's important to note that "opened_tables" is different from the "open" value on that page. The opened tables number, the "opens" in the "mysqladmin" output, is how many tables the MySQL server has accessed, while the "Open Tables" number is how many tables are actively opened. We can see more details about the mysqladmin command here:
    Can you see if that value is set to 4000 on your system? If so, that is likely where you're running into issues as you're hitting that limit. Details on adjusting that limit, and more details on how MySQL handles tables, can be found here:
    0
  • Ray
    Hi, yes. I noted that Open tables: 4000 is reached almost instantly after mysql restart. Is that normal? mysql> SHOW GLOBAL STATUS LIKE 'Open_%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Open_files | 1609 | | Open_streams | 0 | | Open_table_definitions | 652 | | Open_tables | 4000 | | Opened_files | 1609 | | Opened_table_definitions | 1530 | | Opened_tables | 9874 | +--------------------------+-------+ 7 rows in set (0.00 sec)
    0
  • cPRex Jurassic Moderator
    It may be normal for your machine since you are handling more than 700 requests per second. You may need to increase that table cache value, if you have spare CPU and RAM resources available to handle it.
    0
  • Ray
    I have 8 CPU Cores and 32GB of ram. When mysql degrading in speed I also noticed phpmyadmin was also slow at loading database tables. But low CPU and RAM utilisation is not high. Do I need to change:" open_files_limit = 40000
    to something higher? Like 8000?
    0
  • cPRex Jurassic Moderator
    PHMyAdmin would also be affected as that does use MySQL. If the CPU and RA< utilization is low, it would be safe to bump the values. You'll want to start by changing the table_open_cache value to 8000, or possibly even higher. Since that value isn't present in your current configuration you can just add it on a new line. Please be sure to restart MySQL after that change so the new value is activated in the system.
    0
  • Ray
    I'm very new to this script. Anything below that you suggest adjusting as priority? My latest config file, does it look OK? I've tuned. I have 8 CPU cores and 32GB RAM [mysqld] skip-name-resolve performance-schema = 1 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid innodb_buffer_pool_size = 52428800 max_allowed_packet = 268435456 innodb_file_per_table = 1 default-authentication-plugin = mysql_native_password sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION event_scheduler = off table_open_cache = 120000 innodb_open_files = 120000 table_definition_cache = 75000 open_files_limit = 256000
    >> MySQLTuner 1.9.4 * Jean-Marie Renouard * Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [--] Performing tests on localhost:3306 [OK] Currently running supported MySQL version 8.0.28-cll-lve [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/log/mysqld.log exists [--] Log file: /var/log/mysqld.log(122K) [OK] Log file /var/log/mysqld.log is not empty [OK] Log file /var/log/mysqld.log is smaller than 32 Mb [OK] Log file /var/log/mysqld.log is readable. [!!] /var/log/mysqld.log contains 753 warning(s). [!!] /var/log/mysqld.log contains 2 error(s). [--] 4 start(s) detected in /var/log/mysqld.log [--] 1) 2022-03-05T12:37:34.802998Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. [--] 2) 2022-03-05T12:37:34.802922Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock [--] 3) 2022-03-05T12:31:53.239162Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. [--] 4) 2022-03-05T12:31:53.239105Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock [--] 1 shutdown(s) detected in /var/log/mysqld.log [--] 1) 2022-03-05T12:37:17.661209Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL. -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 7.2G (Tables: 2270) [--] Data in InnoDB tables: 2.9G (Tables: 3815) [--] Data in MEMORY tables: 0B (Tables: 47) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Views Metrics ----------------------------------------------------------------------------- -------- Triggers Metrics -------------------------------------------------------------------------- -------- Routines Metrics -------------------------------------------------------------------------- -------- Security Recommendations ------------------------------------------------------------------ [--] Skipped due to unsupported feature for MySQL 8 -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 58s (14K q [249.810 qps], 75 conn, TX: 38M, RX: 2M) [--] Reads / Writes: 99% / 1% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 30.5G [--] Max MySQL memory : 38.1G [--] Other process memory: 0B [--] Total buffers: 90.0M global + 257.9M per thread (151 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 3.9G (12.66% of installed RAM) [!!] Maximum possible memory usage: 38.1G (124.87% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (0/14K) [OK] Highest usage of available connections: 9% (15/151) [OK] Aborted connections: 1.33% (1/75) [--] Query cache have been removed in MySQL 8 [OK] Sorts requiring temporary tables: 0% (5 temp sorts / 3K sorts) [!!] Joins performed without indexes: 52 [OK] Temporary tables created on disk: 0% (0 on disk / 220 total) [OK] Thread cache hit rate: 80% (15 created / 75 connections) [OK] Table cache hit rate: 98% (66K hits / 67K requests) [OK] table_definition_cache(75000) is upper than number of tables(6462) [OK] Open file limit used: 1% (429/40K) [OK] Table locks acquired immediately: 99% (2K immediate / 2K locks) [OK] Binlog cache memory access: 100.00% (14 Memory / 14 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Performance_schema is activated. [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [--] MyISAM Metrics are disabled on last MySQL versions. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 50.0M/2.9G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (192 %): 48.0M * 2/50.0M should be equal to 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 1 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: 99.53% (990333 hits/ 995018 total) [!!] InnoDB Write Log efficiency: 86.39% (762 hits/ 882 total) [OK] InnoDB log waits: 0.00% (0 waits / 120 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine not available. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Check warning line(s) in /var/log/mysqld.log file Check error line(s) in /var/log/mysqld.log file MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html (specially the conclusions at the bottom of the page). Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** join_buffer_size (> 256.0K, or always use indexes with JOINs) innodb_buffer_pool_size (>= 2.9G) if possible. innodb_log_file_size should be (=6M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    0
  • Ray
    I've noticed some warning with my new config 2022-03-05T12:31:32.851957Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 19919 (requested 120000) 2022-03-05T12:31:33.030089Z 0 [Warning] [MY-012364] [InnoDB] innodb_open_files should not be greater than the open_files_limit. 2022-03-05T12:37:19.260663Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 19919 (requested 120000) 2022-03-05T12:37:19.587425Z 0 [Warning] [MY-012364] [InnoDB] innodb_open_files should not be greater than the open_files_limit.
    0
  • cPRex Jurassic Moderator
    You are likely hitting the ulimits for the system as outlined here:
    0
  • Ray
    Below is our latest result. Could you recommend a good settings please? I have about 30 sites running magneto and woocommerce [mysqld] skip-name-resolve performance-schema = 1 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid innodb_buffer_pool_size = 52428800 max_allowed_packet = 268435456 innodb_file_per_table = 1 default-authentication-plugin = mysql_native_password sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION event_scheduler = off table_open_cache = 120000 innodb_open_files = 120000 table_definition_cache = 75000 open_files_limit = 256000
    >> MySQLTuner 1.9.4 * Jean-Marie Renouard * Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [--] Performing tests on localhost:3306 [OK] Currently running supported MySQL version 8.0.28-cll-lve [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/log/mysqld.log exists [--] Log file: /var/log/mysqld.log(416K) [OK] Log file /var/log/mysqld.log is not empty [OK] Log file /var/log/mysqld.log is smaller than 32 Mb [OK] Log file /var/log/mysqld.log is readable. [!!] /var/log/mysqld.log contains 2559 warning(s). [!!] /var/log/mysqld.log contains 4 error(s). [--] 6 start(s) detected in /var/log/mysqld.log [--] 1) 2022-03-06T20:16:37.359003Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. [--] 2) 2022-03-06T20:16:37.358952Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port:33060, socket: /var/run/mysqld/mysqlx.sock [--] 3) 2022-03-05T12:37:34.802998Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. [--] 4) 2022-03-05T12:37:34.802922Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port:33060, socket: /var/run/mysqld/mysqlx.sock [--] 5) 2022-03-05T12:31:53.239162Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. [--] 6) 2022-03-05T12:31:53.239105Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port:33060, socket: /var/run/mysqld/mysqlx.sock [--] 2 shutdown(s) detected in /var/log/mysqld.log [--] 1) 2022-03-06T20:16:04.924647Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL. [--] 2) 2022-03-05T12:37:17.661209Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL. -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 7.3G (Tables: 2270) [--] Data in InnoDB tables: 2.9G (Tables: 3815) [--] Data in MEMORY tables: 0B (Tables: 47) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Views Metrics ----------------------------------------------------------------------------- -------- Triggers Metrics -------------------------------------------------------------------------- -------- Routines Metrics -------------------------------------------------------------------------- -------- Security Recommendations ------------------------------------------------------------------ [--] Skipped due to unsupported feature for MySQL 8 -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1d 3h 45m 25s (57M q [571.947 qps], 163K conn, TX: 160G, RX: 10G) [--] Reads / Writes: 94% / 6% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 30.5G [--] Max MySQL memory : 38.1G [--] Other process memory: 0B [--] Total buffers: 90.0M global + 257.9M per thread (151 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 22.5G (73.72% of installed RAM) [!!] Maximum possible memory usage: 38.1G (124.87% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (0/57M) [OK] Highest usage of available connections: 58% (89/151) [OK] Aborted connections: 0.04% (60/163316) [--] Query cache have been removed in MySQL 8 [OK] Sorts requiring temporary tables: 0% (5K temp sorts / 16M sorts) [!!] Joins performed without indexes: 120758 [OK] Temporary tables created on disk: 0% (360 on disk / 2M total) [OK] Thread cache hit rate: 96% (5K created / 163K connections) [OK] Table cache hit rate: 99% (67M hits / 67M requests) [OK] table_definition_cache(75000) is upper than number of tables(6462) [OK] Open file limit used: 23% (9K/40K) [OK] Table locks acquired immediately: 99% (28M immediate / 28M locks) [OK] Binlog cache memory access: 99.96% (193118 Memory / 193194 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Performance_schema is activated. [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [--] MyISAM Metrics are disabled on last MySQL versions. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 50.0M/2.9G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (192 %): 48.0M * 2/50.0M should be equal to 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 1 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: 99.72% (2552515335 hits/ 2559632732 total) [!!] InnoDB Write Log efficiency: 71.33% (2172059 hits/ 3044976 total) [OK] InnoDB log waits: 0.00% (0 waits / 872917 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine not available. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Check warning line(s) in /var/log/mysqld.log file Check error line(s) in /var/log/mysqld.log file Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html (specially the conclusions at the bottom of the page). Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** join_buffer_size (> 256.0K, or always use indexes with JOINs) innodb_buffer_pool_size (>= 2.9G) if possible. innodb_log_file_size should be (=6M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    0
  • cPRex Jurassic Moderator
    That actually looks pretty good to me. I never make official recommendations for configurations, as you'll just have to see how the system behaves after a change and then adjust as necessary.
    0
  • Ray
    MYSQL slowed down again today and the query would not process or have a long delay. I feel like something is getting full. So the MSQL CPU starts from 27% then over 2 days it slowly go upto 100% CPU which then I need to restart mysql. Do you know why my MYSQL CPU goes up? I'm thinking of setting pool instant to 20 and pool size to 20GB. I have 32GB and 8 CPU. What do you think? innodb_buffer_pool_instances = 20 # Use 1 instance per 1GB of InnoDB pool size innodb_buffer_pool_size = 20G # Use up to 70-80% of RAM Anything below that you can tell? Below is after about 5 hours of mysql reset. mysql> SHOW ENGINE INNODB STATUS; | InnoDB | | ===================================== 2022-03-10 18:10:58 140503145830144 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 49 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4441 srv_active, 0 srv_shutdown, 14747 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1942 OS WAIT ARRAY INFO: signal count 10897 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 5266688 Purge done for trx's n:o < 5266687 undo n:o < 0 state: running but idle History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421986883538136, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421986883537328, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421986883536520, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0], aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 18 38257 OS file reads, 290562 OS file writes, 74018 OS fsyncs 0.06 reads/s, 16384 avg bytes/read, 33.70 writes/s, 9.29 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 48, seg size 50, 164 merges merged operations: insert 165, delete mark 7, delete 6 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 1859467, node heap has 216 buffer(s) Hash table size 1859467, node heap has 107 buffer(s) Hash table size 1859467, node heap has 177 buffer(s) Hash table size 1859467, node heap has 59 buffer(s) Hash table size 1859467, node heap has 33 buffer(s) Hash table size 1859467, node heap has 1827 buffer(s) Hash table size 1859467, node heap has 1655 buffer(s) Hash table size 1859467, node heap has 213 buffer(s) 1599.07 hash searches/s, 794.21 non-hash searches/s --- LOG --- Log sequence number 14174488557 Log buffer assigned up to 14174488557 Log buffer completed up to 14174488557 Log written up to 14174488557 Log flushed up to 14174488557 Added dirty pages up to 14174488557 Pages flushed up to 14174488557 Last checkpoint at 14174488557 102713 log i/o's done, 1.14 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 11121288 Buffer pool size 458712 Free buffers 416670 Database pages 37755 Old database pages 14081 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 48165, not young 1099968 2.25 youngs/s, 0.12 non-youngs/s Pages read 37008, created 251468, written 139490 0.06 reads/s, 61.69 creates/s, 26.52 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 37755, unzip_LRU len: 0 I/O sum[10600]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 57337 Free buffers 52515 Database pages 4287 Old database pages 1601 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4988, not young 118677 0.02 youngs/s, 0.00 non-youngs/s Pages read 4246, created 31419, written 18150 0.00 reads/s, 7.72 creates/s, 2.91 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4287, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 57341 Free buffers 52030 Database pages 4773 Old database pages 1781 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4887, not young 154239 0.00 youngs/s, 0.04 non-youngs/s Pages read 4687, created 31449, written 10899 0.02 reads/s, 7.72 creates/s, 2.13 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4773, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 57339 Free buffers 51722 Database pages 5075 Old database pages 1892 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 15466, not young 120714 2.03 youngs/s, 0.00 non-youngs/s Pages read 4963, created 31098, written 18110 0.00 reads/s, 7.62 creates/s, 3.43 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5075, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 57338 Free buffers 52091 Database pages 4713 Old database pages 1759 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 5388, not young 91894 0.00 youngs/s, 0.00 non-youngs/s Pages read 4569, created 31564, written 15680 0.00 reads/s, 7.72 creates/s, 3.81 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4713, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 57339 Free buffers 52767 Database pages 4041 Old database pages 1509 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3919, not young 78335 0.06 youngs/s, 0.04 non-youngs/s Pages read 4011, created 31445, written 12165 0.02 reads/s, 7.72 creates/s, 1.67 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4041, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 57338 Free buffers 51357 Database pages 5443 Old database pages 2026 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4639, not young 168080 0.06 youngs/s, 0.00 non-youngs/s Pages read 5309, created 31550, written 14784 0.00 reads/s, 7.72 creates/s, 2.87 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5443, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 57343 Free buffers 51961 Database pages 4852 Old database pages 1808 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4702, not young 177824 0.04 youngs/s, 0.00 non-youngs/s Pages read 4692, created 31550, written 23287 0.00 reads/s, 7.72 creates/s, 4.29 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4852, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 57337 Free buffers 52227 Database pages 4571 Old database pages 1705 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4176, not young 190205 0.04 youngs/s, 0.04 non-youngs/s Pages read 4531, created 31393, written 26415 0.02 reads/s, 7.72 creates/s, 5.42 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4571, unzip_LRU len: 0 I/O sum[1325]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=960283, Main thread ID=140503154222848, state=sleeping Number of rows inserted 2294966, updated 22627, deleted 9048, read 386680615 567.05 inserts/s, 0.14 updates/s, 0.00 deletes/s, 5342.50 reads/s Number of system rows inserted 971, updated 1277, deleted 966, read 1704723 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 27.82 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT
    0
  • cPRex Jurassic Moderator
    At this point it would likely be best to have an experienced MySQL administrator examine the machine directly. That person would be able to determine what specific issues are happening with the machine in real-time.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post