1200% to 1800% mysql high load for few days ( PLease help guys )

[Jhon Tao]

Hi guys,

So i am running a website with very heavy traffic, mainly it serves static files, also videos uploading (Users will upload videos). I have streaming server/converting server hosted on other servers, this server only for video uploading by users, then nothing else.

So now issue with the server is onl mysql load, and its very high when users upload vidoes into our server( small files not high load but files like 300MB above or more it cause huge load), as well when there's heavy traffic like 3000-4000 concurrent users per hour, there's also heavy load on mysql.

I have spent almost $200 USD on mysql devs to fix but to no resolution and wasting more time to fix it so thinking maybe anyone knows any solution ? keep telling to upgrade server, and i have upgraded to a better server than my previous.

It is a Intel Core i9-13900K with 4x 1TB SSD, not nvme, and on Raid 10 array.
64GB Ram. 4x 1 TB SSD is brand new disk( Power on time less than 1 week old ).

daily vistiors about 100k vistors, hourly about 4-5k vistors.

using TOP command (i not a pro, but i used these commands )

top - 11:19:43 up 29 min, 1 user, load average: 17.34, 17.44, 14.66
Tasks: 543 total, 2 running, 541 sleeping, 0 stopped, 0 zombie
%Cpu(s): 45.8 us, 7.5 sy, 0.0 ni, 45.9 id, 0.6 wa, 0.0 hi, 0.2 si, 0.0 st
MiB Mem : 64059.0 total, 17095.9 free, 7438.7 used, 39524.4 buff/cache
MiB Swap: 32768.0 total, 32768.0 free, 0.0 used. 55785.9 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1422 mysql 20 0 36.6g 4.5g 38192 S 1333 7.1 326:26.19 mysqld
1334 root 20 0 715432 453520 23820 S 18.2 0.7 5:54.75 searchd
1400 www-data 20 0 18044 12212 6140 S 10.3 0.0 0:06.85 nginx
1 root 20 0 166692 12428 8644 S 2.3 0.0 0:32.83 systemd
1282 memcache 20 0 1176208 769648 3520 S 2.3 1.2 0:32.98 memcach+
1395 www-data 20 0 18328 12716 6140 S 1.3 0.0 0:07.73 nginx
1398 www-data 20 0 17252 11660 6140 S 1.0 0.0 0:07.95 nginx
4074 fastuser 20 0 112728 37436 26332 S 1.0 0.1 0:10.05 php
263876 www-data 20 0 249484 16900 8188 S 1.0 0.0 0:02.01 apache2
684 root 20 0 0 0 0 S 0.7 0.0 0:06.48 jbd2/md+
1374 www-data 20 0 17736 12304 6128 S 0.7 0.0 0:07.42 nginx
1376 www-data 20 0 18708 13212 6204 S 0.7 0.0 0:08.62 nginx
1377 www-data 20 0 18612 12984 6140 S 0.7 0.0 0:07.52 nginx
1378 www-data 20 0 17344 11664 6140 S 0.7 0.0 0:07.81 nginx
1380 www-data 20 0 17664 11928 6140 S 0.7 0.0 0:07.92 nginx
1386 www-data 20 0 18596 12896 6140 S 0.7 0.0 0:07.91 nginx
1387 www-data 20 0 17316 11896 6140 S 0.7 0.0 0:07.31 nginx

--------------------------
atop command ;

PRC | sys 12.69s | user 1m54s | #proc 558 | #trun 19 | #tslpi 523 | #tslpu 196 | #zombie 0 | clones 2244 | | #exit 2096 |
CPU | sys 211% | user 1206% | irq 8% | idle 1764% | wait 12% | steal 0% | guest 0% | ipc 2.33 | cycl 1.68GHz | curf 3.32GHz |
CPL | avg1 17.46 | avg5 17.63 | avg15 14.91 | | | csw 246372 | intr 236291 | | | numcpu 32 |
MEM | tot 62.6G | free 16.7G | cache 20.1G | dirty 229.5M | buff 9.3G | slab 9.9G | slrec 9.2G | shmem 110.2M | shrss 1.1M | numnode 1 |
SWP | tot 32.0G | free 32.0G | swcac 0.0M | | | | | vmcom 38.6G | | vmlim 63.3G |
PSI | cpusome 0% | memsome 0% | memfull 0% | iosome 0% | iofull 0% | cs 0/0/0 | ms 0/0/0 | mf 0/0/0 | is 1/4/4 | if 1/3/3 |
MDD | md127 | busy 34% | read 1495 | write 11164 | discrd 0 | KiB/r 7 | KiB/w 4 | MBr/s 1.1 | MBw/s 5.4 | avio 0.27 ms |
DSK | sda | busy 23% | read 716 | write 578 | discrd 0 | KiB/r 7 | KiB/w 46 | MBr/s 0.5 | MBw/s 2.6 | avio 1.75 ms |
DSK | sdd | busy 21% | read 625 | write 670 | discrd 0 | KiB/r 7 | KiB/w 42 | MBr/s 0.5 | MBw/s 2.8 | avio 1.66 ms |
DSK | sdc | busy 13% | read 68 | write 670 | discrd 0 | KiB/r 9 | KiB/w 42 | MBr/s 0.1 | MBw/s 2.8 | avio 1.83 ms |
NET | transport | tcpi 76041 | tcpo 114307 | udpi 242 | udpo 242 | tcpao 3501 | tcppo 3741 | tcprs 213 | tcpie 0 | udpie 0 |
NET | network | ipi 76286 | ipo 78383 | ipfrw 0 | deliv 76286 | | | | icmpi 0 | icmpo 0 |
NET | enp2s0 8% | pcki 36732 | pcko 74794 | sp 1000 Mbps | si 4713 Kbps | so 82 Mbps | erri 0 | erro 0 | drpi 0 | drpo 0 |
NET | lo ---- | pcki 39971 | pcko 39971 | sp 0 Mbps | si 58 Mbps | so 58 Mbps | erri 0 | erro 0 | drpi 0 | drpo 0 |

-------------

Please let me know what command to put to show you the exact logs :( so we can know the root cause and fix it, cause i am very sad now :( my vistors experiencing very high load time on the website due to this :(