0

I have been wondering this for long time

let's say I run a server where I hosted 2 PHP sites , they are both heavy on database query

server is multi-core , let's say 4 core 8 thread with bunch of RAM spare

frequently, specially during busy hour , I have been observing that in htop or monitor tools the database process mariadb was running on high CPU , while overall server load is still low , LA is usually less than 3 and like half of CPU bar is not raise as high as other half do, but both PHP site have been slowed down (like average TTFB jump from 0.x second to 1.x even 2.x seconds) , my guess is database , as both of them are competing for queries

so there comes the wondering , since it is possible to start up multiple database instance or even different database either directly install binary files or indirect means like Docker or Podman.

so I wonder , does it bring any benefit or speed up the query and also site speed ?

like 1 site ties to MariaDB , 1 site ties to MySQL

so they don't compete the query on same DB

I know it might be dumb question , as I think modern software like database should have full multi-core and multi-process support , but just can't stop wondering

qtwrk
  • 176
  • 1
  • Why not try and benchmark it yourself? – Nikita Kipriyanov Oct 18 '22 at 04:47
  • Additional DB information request, please. # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; H) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server workload tuning analysis to provide suggestions to smooth CPU use. – Wilson Hauck Oct 18 '22 at 14:59
  • Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions. – Wilson Hauck Oct 18 '22 at 15:00
  • @WilsonHauck thanks for the reply , please check https://justpaste.it/5resf – qtwrk Oct 19 '22 at 14:49
  • @NikitaKipriyanov yeah ... I am waiting for black friend to grab a similar spec server to run some test, but since it's still like a month way , so just post a question here if I can get some advice – qtwrk Oct 19 '22 at 14:49
  • @qtwrk Any SSD or NVME on your system? Thanks for posting your additional info. Only thing missing is SHOW GLOBAL VARIABLES; Please post in paste.it and share the link. Workload Analysis can begin after SGV posted. Thank you, Wilson – Wilson Hauck Oct 19 '22 at 16:36
  • @WilsonHauck it's 2 SSD with soft raid 1 , please check this https://justpaste.it/5frop – qtwrk Oct 19 '22 at 19:02

1 Answers1

0

Rate Per Second = RPS

Suggestions to consider for your environment.

For OS command prompt, ulimit -n 24000 and press Enter to enable more than 1024 table/file handles

Review this url https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ for making this change persistent across OS stop/start. Please use 24000 where the example is using 500000 for your system.

Suggetions to consider for your my.cnf [mysqld] section,

read_rnd_buffer_size=32768  # from more than 1G to reduce handler_read_rnd_next RPS of 3,331
binlog_cache_size=98304  # from 32K to reduce binlog_cache_use RPS of 5
key_buffer_size=16777216  # from 500M+ because less than 1% is in use.
key_cache_division_limit=50  # from 100 for Hot/Warm cache to reduce key_reads RPS of 16,333

Please view profile for contact info. Many more global variables could be improved and reduce system overhead and ram requirements.

Wilson Hauck
  • 472
  • 5
  • 11