DataBase Server Performance - Any ideas ?

[gequiros]

Hello everyone, i ran: mysqltuner.pl during a busy time and the results are as follows:

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

DB-Server:~ # mysqltuner.pl
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: >> MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.20-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV -InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in MyISAM tables: 17G (Tables: 195)
[--] Data in MEMORY tables: 11M (Tables:
[!!] Total fragmented tables: 5

-------- Security Recommendations -------------------------------------------
[!!] User '@db-server' is an anonymous account.
[!!] User '@localhost' is an anonymous account.
[!!] User '@db-server' has no password set.
[!!] User '@localhost' has no password set.
[!!] User 'mysqltop@%' has no password set.
[!!] User 'mysqltop@localhost' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@db-server' has no password set.
[!!] User '@db-server' has user name as password.
[!!] User '@localhost' has user name as password.
[!!] User 'cron@%' hasn't specific host restriction.
[!!] User 'custom@%' hasn't specific host restriction.
[!!] User 'mysqltop@%' hasn't specific host restriction.
[!!] User 'slave@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 11h 38m 6s (721M q [880.198 qps], 54M conn, TX: 330B, RX: 96B)
[--] Reads / Writes: 90% / 10%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 4.2G global + 25.3M per thread (2048 max threads)
[!!] Maximum reached memory usage: 54.8G (174.34% of installed RAM)
[!!] Maximum possible memory usage: 54.7G (174.26% of installed RAM)
[OK] Slow queries: 0% (6K/721M)
[!!] Highest connection usage: 100% (2049/2048)
[OK] Aborted connections: 0.00% (2520/54469226)
[OK] Query cache efficiency: 39.2% (386M cached / 987M selects)
[!!] Query cache prunes per day: 1330498
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 22M sorts)
[!!] Joins performed without indexes: 2909
[OK] Temporary tables created on disk: 0% (6K on disk / 3M total)
[OK] Thread cache hit rate: 99% (17K created / 54M connections)
[!!] Table cache hit rate: 0% (360 open / 72K opened)
[OK] Open file limit used: 1% (446/24K)
[OK] Table locks acquired immediately: 98% (281M immediate / 284M locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 36.8% (1B used / 4B cache)
[OK] Key buffer size / total MyISAM indexes: 4.0G/5.9G
[OK] Read Key buffer hit rate: 99.3% (22B cached / 157M reads)
[OK] Write Key buffer hit rate: 96.4% (133M cached / 4M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Remove Anonymous User accounts - there are 2 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (24576) variable
should be greater than table_open_cache ( 512)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 2048)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 32M)
join_buffer_size (> 1.0M, or always use indexes with joins)
table_open_cache (> 512)
---------------------------------------------------------------------------------------------------------------------------------------

Does it need me to apply any changes? if so, can you guide me a bit ?, this is cause sometimes i have seen a major spikes on LOAD

I got 4 Dialers, one Web server and 1 DB server, only 30 agents logged in, i think we are "under-working" ( not even at a quarter of its capabilities ), but the spikes i have seen at times at DB, kinda scares me... ( sometimes at 4 or 5 on the load ), i run every night the mysqlcheck.... and sundays i run the deep and slow checks.... i do back it up everyday as well and keep last 7 days of backups

-rw-r--r-- 1 root root 1499814120 Aug 26 03:57 DB_BackUp-08-26-2017.sql.xz

DB server have 24 threads and 32gb and it is running on SSD

Thanks, any and all suggestions will be appreciated !!!

[williamconley]

Good job posting your specs!

but the spikes i have seen at times at DB, kinda scares me... ( sometimes at 4 or 5 on the load ),

Find out what queries are running that kick up the load and trace them back to the source. If it's report related, run reports at night. If they must be run during the day, consider a report/replication server if it really bothers you to have the spikes caused by reports.

Code: Select all

mysql asterisk -ucron -p1234 -e "select * from information_schema.processlist where command not in ('Sleep') "

i run every night the mysqlcheck.... and sundays i run the deep and slow checks....

The Vicibox installer will include a cron job that optimizes the DB nightly. So a mysqlcheck is only useful for detecting crashed tables and the "deep and slow checks" are already built-in. Runs around 1AM.

i do back it up everyday as well and keep last 7 days of backups

There is a backup script included in the Vicidial Perl folder. Automatically backs up the DB and anything/everything else you might need to rebuild the system. Keeps a copy for each day of the week and will also push to an external FTP server upon completion. Handy.

Run it at 11:45PM every night, so it has today's data on it and overwrites last week's ... and if you have a panic moment in the middle of the day tomorrow, you're overwriting last week's backup instead of last night's backup. With a single command. Handy some more.

Cron job (set the "# REPORT server connection information" in /etc/astguiclient.conf or omit the ftp option below):

Code: Select all

45 23 * * *  /usr/share/astguiclient/ADMIN_backup.pl  --ftp-transfer >/dev/null 2>&1

Just check options, doesn't do anything:

Code: Select all

perl /usr/share/astguiclient/ADMIN_backup.pl --help

Emergency DB Only Backup with FTP transfer:

Code: Select all

45 23 * * *  /usr/share/astguiclient/ADMIN_backup.pl --ftp-transfer --debugX --db-only

Emergency DB Only Backup without FTP transfer:

Code: Select all

45 23 * * *  /usr/share/astguiclient/ADMIN_backup.pl --debugX --db-only

Note that running the backup script while in operation *will* disrupt the cluster.