I have about 30 websites running Magento/Wordpress. Every 1-2 days I would need to reset mysql and then the CPU usage would be around 27% but then it'll slowly climbing up slowly over 1-2 days until it can't process any query. I've been trying to fine tune mysql with mysqltuner but I'm not getting anywhere. Any one have any idea to what's wrong with my mysql?
Server SPECS. 8CPU and 32GB of RAM. MYSQL 8 upgraded from 5. I'm thinking of increase these two settings to 20 and 20GB, will it help?
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
MYSQL Tuner:
[root@sydney MySQLTuner-perl]# perl mysqltuner.pl --host localhost
>> MySQLTuner 1.9.4
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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(67K)
[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 393 warning(s).
[!!] /var/log/mysqld.log contains 1 error(s).
[--] 8 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-10T01:51:09.380924Z 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-10T01:51:09.380822Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 3) 2022-03-08T11:48:53.804929Z 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-08T11:48:53.804736Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 5) 2022-03-08T10:05:07.901100Z 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-08T10:05:07.901013Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 7) 2022-03-08T09:53:37.490055Z 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.
[--] 8) 2022-03-08T09:53:37.489953Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 3 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-10T01:51:02.223500Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL.
[--] 2) 2022-03-08T11:48:20.577771Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL.
[--] 3) 2022-03-08T10:05:03.527527Z 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: 3824)
[--] 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: 7h 12m 25s (11M q [445.772 qps], 36K conn, TX: 35G, RX: 1G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 30.5G
[--] Max MySQL memory : 117.6G
[--] Other process memory: 0B
[--] Total buffers: 7.0G global 566.0M per thread (200 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 23.1G (75.58% of installed RAM)
[!!] Maximum possible memory usage: 117.6G (385.24% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/11M)
[OK] Highest usage of available connections: 14% (29/200)
[OK] Aborted connections: 0.02% (6/36482)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (20 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 40853
[OK] Temporary tables created on disk: 0% (100 on disk / 476K total)
[OK] Thread cache hit rate: 98% (619 created / 36K connections)
[OK] Table cache hit rate: 99% (13M hits / 13M requests)
[OK] table_definition_cache(40000) is upper than number of tables(6471)
[OK] Open file limit used: 15% (6K/40K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
[OK] Binlog cache memory access: 99.98% (49854 Memory / 49863 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
[OK] InnoDB buffer pool / data size: 7.0G/2.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/7.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 56 for 8 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.99% (569469465 hits/ 569505240 total)
[!!] InnoDB Write Log efficiency: 78.28% (500340 hits/ 639165 total)
[OK] InnoDB log waits: 0.00% (0 waits / 138825 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).
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 4.0M, or always use indexes with JOINs)
innodb_buffer_pool_instances(=7)
[mysqld]
skip-name-resolve
max_connections = 200
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
max_allowed_packet = 550M
default-authentication-plugin = mysql_native_password
sql-mode=""
event_scheduler = off
table_open_cache = 40000
innodb_open_files = 40000
table_definition_cache = 40000
open_files_limit = 60000
#https://blog.hostseo.com/optimized-my-cnf-configuration-for-mysql-8-on-cpanel-whm-servers/
max_connect_errors = 1000000
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 6 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 7G # Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_stats_on_metadata = 0
innodb_sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 3M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
Some warnings in the log
2022-03-10T01:51:03.826195Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 40000 (request: 80210)
2022-03-10T01:51:03.826208Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 19895 (requested 40000)
2022-03-10T01:51:04.135116Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider notusing this option as it' is deprecated and will be removed in a future release.
2022-03-10T01:51:04.137530Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
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
CodePudding user response：
Your server is running out of physical RAM after being up for a while and thrashing. See this:
[OK] Maximum reached memory usage: 23.1G (75.58% of installed RAM)
[!!] Maximum possible memory usage: 117.6G (385.24% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
What can you do about this? Allocate less memory in your MySQL server.
Some opportunities:
You have ludicrously high open file and table cache limits, exceeding your OS limits. You only have 6,471 tables.
[OK] table_definition_cache(40000) is upper than number of tables(6471) [OK] Open file limit used: 15% (6K/40K) [MY-010140] [Server] Could not increase number of max_open_files to more than 40000 (request: 80210) [MY-010142] [Server] Changed limits: table_open_cache: 19895 (requested 40000)Remove these config lines and take the defaults, which are good.
table_open_cache = 40000 innodb_open_files = 40000 table_definition_cache = 40000 open_files_limit = 60000You reported a high-water-mark of 29/200 active connections. Try reducing max_connections.
[OK] Highest usage of available connections: 14% (29/200)Do your tables contain large BLOBs or CLOBs (LONGBLOB, LONGTEXT)? You can reduce max_allowed_packet (presently about 0.5GiB) to match the length of your largest LOB.
Is your server machine dedicated to the database server software? If not, don't forget to leave room for other processes, like php. Reduce innodb_buffer_pool_size in this case.
From what I see, your server's workload doesn't need a bigger machine. But, you should investigate the unindexed JOIN operations in the queries coming from your applications. Maybe some indexes will help.
[!!] Joins performed without indexes: 40853
CodePudding user response：
InnoDB buffer pool / data size: 7.0G/2.9G
InnoDB Read buffer efficiency: 99.99%
Those says that the buffer_pool is probably not full. Raising to 20G will neither help nor hurt. Well, if the data grows a lot, raising may be important.
These are excessive; for starters, cut them down by 10x.
max_allowed_packet = 550M
table_open_cache = 40000
innodb_open_files = 40000
table_definition_cache = 40000
open_files_limit = 60000
For deeper dive into the settings: Analysis
For analyzing the "slow" queries: SlowLog -- this is likely to lead to an improvement.