Problem with mysqltuner and suspicious of permission issue (centos 6-7)

nadav123 · Feb 2, 2021

Hello, I am struggling with my database

MariaDB 10.3.27-MariaDB

CENTOS 7.9 virtuozzo [server]
v92.0.9

First Problem - MySQL Tuner:
I try run mysqltuner script to diagnose and optimize the database and I get false data of:
[OK] Maximum reached memory usage: 2.4G (60.47% of installed RAM)
[!!] Maximum possible memory usage: 27.1G (677.32% of installed RAM)

Second Problem Sockets / Permissions:
another issue i tried connect socket in my cnf with
[MySQL]
socket=/var/lib/mysql/mysql.sock
[mysqld]
socket=/var/lib/mysql/mysql.sock

what actually the default via unix and port 3306.
but for some reason, the tuner got a block and the interface of:
whm>MySQL process

gut block from data as well and show black page.

(this because in centos07 systemctl running MySQL / maria db as a root)

So... i start to check permissions.

first, i check:
/etc/my.cnf (own by root)

after that i see the:
/etc/my.cnf.d (own by root as well)

then i check the last folder:
/var/lib/mysql (own by MySQL as a user and a group mysql:mysql )

now i thinking to myself.

how is even possible?
if root runs my.cnf, how "mysql:mysql" run all the folders alone?

When i connect the sockets as i told you before in my.cnf.
i get a permission problem, so I start to understand [mysql] is maybe a stand-alone socket?

and [mysqld] is stand alone as well?

or root permission actually can access MySQL folder? (/var/lib/mysql)

Please help, at least with the tuner and the fake usage.
(this fake usage, when you get inside process manager mysql PID is 12% - 20%)

WHM>SERVICE STATUS

shows have more than 2 and a half gigabit available.
This is means, the tuner shows fake results.
I am not able to optimize and use all the resources of the database server.

Please help

cPRex · Feb 2, 2021

Hey there! MySQL Tuner is a great tool, but it is recommend that your database server be running for at least 24 hours before you run it in order for it to provide accurate results.

In the memory section you mention, this means that MySQL has reached a high of 2.4G of RAM usage at some point since it has been running, and that with your current configuration it is possible for MySQL to use up to 27.1G of RAM. The "!!!" indicates this exceeds the amount of RAM you have on your system. In the output from the tuner script it should print a line that looks something like this, although your values will likely be different:

Code:

[--] Total buffers: 1.7G global + 20M per thread (800 max threads)

In that example I provided the MySQL system could use 16G of RAM (20M x 800 threads = 16000M of RAM), even if the server doesn't have that much installed. So this isn't an error from the tuner but an issue with the server's configuration.

For the permissions issue, /var/lib should be 755 root:root and /var/lib/mysql should be 750 mysql:mysql. Can you let me know what specific connection errors you're seeing? You may also want to check the MySQL error log, which by default is placed in /var/lib/mysql/your.servers.hostname.err

nadav123 · Feb 2, 2021

Hey, how are you?
I think you didn't get me.
I don't have any errors in the logs.
process manager in WHM shows different on MySQL usage even after 4 days don't have an overload as the tuner show.

The data you provide (800 threads) far away from what I have in the tuner
My server is very optimized without any errors in the tuner, is the only overload is not even possible.
my server optimizes to 100 treads and 1G pool when the default of MySQL MariaDB and cPanel is 8GB.

I have VPS with 4g Physical... with no swap on Virtuozzo system, is a lot of power for a server run only 3 websites.

now the database after a restart, in 2 3 hours he will be in overload (usually 114% and after full 24 hours 220%).
in the meantime, you have process manager and service status shows on 2.5g available and MySQL PID BETWEEN 12-20%

I EVEN RUN COMMAND LINE IN SSH:
free -m
as well and see have memory 2.3 g available...
the tuner does not say the truth, and because of that I use few resources and not all the power has to the VPS.

1. I even wait a week and 2 weeks for the same result in the tuner.
2. even with default, my.cnf generated file configuration from WHM/Cpanel.
3. cPanel tweaks the default configuration, the tuner still shows overload. (the same numbers as well).

Now a little bit of good news

I actually run database rpm repair throw WHM
WHM>Rebuild RPM Database
, and this allows me now connect the sockets between [MySQL] [mysqld] inside my.cnf
(maybe the DATABASE RPM fix a permission problem between the sockets)

with the time i work on the rpm, I notice another MySQL socket:
/tmp/mysql.sock

This is a UNIX socket.

in the start i try inside my.cnf:
[mysql]
port = 3306
socket = /tmp/mysql.sock # UNIX

[mysqld]
socket = /var/lib/mysql/mysql.sock

This work but then i run the rpm database AND tried to comment out the main UNIX socket from the configuration of my.cnf
match MySQL between mysqld as should be, like this:

[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
socket = /var/lib/mysql/mysql.sock

Now I didn't get a problem run the tuner or any permission problem in the interface when the sockets connects (this good).
I PRETTY SURE THE DATABASE RPM REBUILD, FIX THAT AND ALLOW ME CONNECT THE SOCKETS AS SHOULD BE.
But the MySQL tuner still shows me fake data.

About the permission you right:
/var/lib should :
root:root

and /var/lib/mysql
mysql:MySQL

This what i have right now, but i don't know how i can see the 755 and the 750.
if you can give me a command line to see it, i will update you about that as well.

cPRex · Feb 2, 2021

Hey there! The command to see the permissions would be "ls -ld" on both directories:

Code:

ls -ld /var/lib
ls -ld /var/lib/mysql

It's possible the tuner script isn't giving you good details for your system, especially if you are seeing low usage in real time. When you say you see the 220% overload, is that from actual usage or is that reported by the tuner script?

nadav123 · Feb 2, 2021

Yap... 220 in real-time and 667 something potential.
now i don't see 0755 but i talk with cPanel support they said is was 0755 and they return it to default centos-07 (*chmod 0751)

i did your commend line is give me this:
root@server [~]# ls -ld /var/lib
drwxr-xr-x 32 root root 4096 Feb 2 01:46 /var/lib/

but just in case the cpanel support give me this:
chmod -v 751 /var/lib/mysql

I did it, the usage still the same.
i am frustrated

cPRex · Feb 2, 2021

I would not expect a permission change to affect the usage on the system.

Can you tell me what specific usage stat you're trying to improve and how you are tracking that?

nadav123 · Feb 2, 2021

You right is will not affect the usage in the live progress.
but the Mysql tuner works with permissions.
( as i said with the sockets. when the permission was wrong, and i connected sockets with mysqld and MySQL inside my.cnf).
in the start, the tuner was got a permission problem, and as i said after the Rebuild DATABASE RPM
i was able to connect sockets, and the permission problem is gone...
I said maybe the same thing with the usage but i got wrong...

Currently, I fix the permission problem together with Cpanel support but it didn't fix the usage RAM issue.
I even try to reinstall the tuner couple of times but it didn't work as well.

Now, what i am trying to improve, and how i am tracking that?

generally, I improve in a lot of sections, like Apache, Nginx, MySQL, and more, I assure you, you will not find an optimized professional SERVER, follow cPanel docs recommendation, not only in MySQL.

Now i am using a lot of diagnostic tools on the server with a lot of external tools and diagnostic like Engintron/Nginx, Nginx Amplify Agent, Munin,
cPanel/WHM (himself is a useful tool as well), PHPMyAdmin (and this only the server has WordPress as well and his own tools).

More than that have a hosting company (i am supported by them as well) that has Virtuozzo as a system administrator for diagnostic.
They say the same as me.
The tuner is faking.

Believe me, I didn't make their life easy.
I didn't believe them in the start until they prof it.

Now the most important, What i have for the database is mysqltuner, and this the only tool that actually can help you optimize the DATABASE.
I already pass all the checks of the tuner and I don't have any errors in the logs,
or in the mysqltuner (except the fake RAM of course).

When you see it over a half year you have 2.5g available and your tuner show overload all the time is a boomer.
I paid on these resources, and i cant optimize and use them because of fake usage of the only real good tuner have for cPanel...

Have tuner premier, but is not good as mysqltuner.
mysqltuner more updated.
support more systems and more inputs.

Now i can put my.cnf here but again, as i said.
a regular default generates my.cnf of cPanel/WHM
do the same overload with the same numbers.

not make sense.
you go to:
WHM>PROCCESS MANAGER
you see MySQL is only 12% after a day maximum of 20%-22%

in WHM>SERVICE STATUS
37% - 43% AFTER A DAY 47% - 49%

Don't have a swap file, maybe is connected to our problem?
another suspicious is swap file not excites because Virtuozzo and generally a cloud VPS do not support swap files.
and today is become to be 4 gigs Physical, this much stronger and faster.

but have disadvantages as well.
MySQL cant writes on the disk:
#innodb_read_io_threads = 64
#innodb_write_io_threads = 64

if you uncomment this line in the my.cnf file and try to write on the disk, you will get an error you must deactivate native AIO or rise fs something.
(when you rise the fs is not working as well, so you must deactivate AIO)
If you deactivate AIO completely you not write on the disk at all...
So is better not to write on the disk and give the default configuration actully work with AIO.

this issue already explained to me by cPanel support and my hosting provider.

but is ok with 4 gig physical you not actully need
TO WRITE ON THE DISK.
Is better to use your physical RAM for performance... is a fact.

But in my case is a problem, because mysql tuner gives me a fake result.
and i don't have the option to use the full power i have from my VPS.

Maybe the tuner design to diagnostic with a swap file?
Maybe this result is because not have a swap file at all?

Sorry, it was so long.
I work on this for 30 minutes to write it and detailed everything,
hope it gives you all the indications to help me.

Thank you again,
Appreciate this.

cPRex · Feb 2, 2021

I see Zachary also helped you through a ticket, so let me know if you still have more questions about the tuner and the system!

nadav123 · Feb 2, 2021

He didn't fix the fake usage...
you are my last chance

cPRex · Feb 2, 2021

I don't believe there is any "fake" usage on the server, but it is possible the tuner script isn't reporting accurately on the system for some reason.

You can see real-time database usage with the following command:

Code:

mysqladmin proc status

and by watching the "top -c" command on the system to see the CPU and RAM usage as it is happening.

nadav123 · Feb 2, 2021

i tell you this, not the current ram in the tuner.

maybe some verbose in the mysqltuner.pl?

cPRex · Feb 2, 2021

That shows the MySQL server has only been running about 3 minutes. As mentioned in the documentation, it's preferable to have MySQL up and running for at least 24 hours before trying the tuner.

That output still does show things that could be concerning. With 82 slow queries in 194 seconds, that is almost one slow query every 2 seconds. You may want to enable the slow query log and check that log to see what specific queries are causing an issue to see if they can be further optimized.

nadav123 · Feb 2, 2021

This because Wordfance Firewall Plugin. is a firewall of WordPress... is nothing and you can't do anything about that because:
I am not using mod secure on my websites, and the secure mode is much worst with WordPress, mod secure not design for WordPress and causes much more problems.
I already open a Topic with wordfance support

BUT
in 5 minutes it will show you, it is ok with the data... (this called wf tables)
BTW
These very big 3 sites. 112 pages and have very high code (i use WordPress for SEO purposes and do everything manually).

don't worry about the slow log i already check it and this is the firewall that scanning AND creating reports.
in 10 minutes this is well be adjusted to the queries and the tuner will show is fine and pass the MySQL tuner test.

As i said clearly.
The Process manager and the top -c commend
show 12% RAM.
AND I ALREADY WAIT BEFORE I POST THIS MESSAGE, THE 24 -48 AND MORE AND THIS WILL REACH TO 20% IN THE top -c
and tuner shows you 220% (THIS WILL HAPPENED IN 24 - 48 HOURS.)

he has all time the same % at some point.

1. 1 - 24 hours 114%
2. 24 -48 hours 220%

and again as i said, even with the original default my.cnf generated when you install MySQL and MariaDB, get the same overload.
is cant be.

even if i run:
free -m
is shown now 3GB open and after 2 days it will show 2.5 as always.

cPRex · Feb 3, 2021

Thanks for the additional details. This sounds more like a complaint with the MySQL tuner tool and not an issue with the processes on the server. You might want to reach out to the developer of the tool to let them know about the odd results you are seeing.

It's normal for Linux to constantly use RAM, but the total of the "free" and "cached" columns is how much RAM you actually have available on the system. You can find more details on that here:

Help! Linux ate my RAM!

If you work through that guide and the RAM usage is still not what you expect, there could be additional issues.

nadav123 · Feb 5, 2021

Ok, I think I solve the issue.
First thanks to Zachary from cPanel Support

Really good support have to cPanel, people don't know they can
use it, and the Hosting providers use it and not tell the customers after the purchases.

This support helps me more times from what my crappy hosting provider did the whole 2 years.

Thanks to Zachary!
And all cPanel support and to you cPRex.

I recommended putting this in the Tuner docs of cPanel because I believe this happening to a lot of people (This be useful for others 100%):

How To Use The MySQLTuner Script?

Disclaimer: Please bear in mind that, it is extremely important for you to fully understand each change you make to a MySQL database server. If you don't understand portions of the script's outpu...

support.cpanel.net

First - You must tell in the docs about Clouds VPS ( must of the hosting today this VPS and Dedicated Servers)
The tuner still faking and will fake until the
MySQL tuner developer will support by default clouds.

BUT!
Thanks to Zachary.
The tuner gets a better result on the live input and we were able to understand something from the data he provides and how the tuner provides it.
The potential memory is still high, This beacuse the server is on a VPS node on a cloud.

First MySQL Tuner not compile with a cloud VPS and hosting companies should compile it for their customers.
This because any cloud have different systems ( Virtuozzo for example )

Now First we must say thanks to Zachary follow after my steps as i detailed here and in the support.
Zachary finds out a permission 0755 and not the default one 0751 of MySQL folder inside /var/lib/mysql.

Now usually 0755 work as well, but along they change in centos 07, with the system administration,
the sockets, PIDS, and the permissions of the folder in different places with permission of 0751 only.

Now the sockets connect me before Zachry finds the issue. but now after a day, the memory
USAGE STAY THE SAME! (GOD BLESS ZACHARY)

CURRENTLY, NOW MEMORY USAGE AFTER 24 HOURS IS 92% !! ( IS A BIG DIFFERENT FROM 114 GO UP TO 220 )
POTENTIAL IS STIL 667%

Now the database still gives false data, but! this because is a cloud, today must of the clouds not support swap file from cPanel
and they have their own swap file in the cloud system administration.
(most of the clouds even google cloud, you must say it in the docs guys)
The tuner will replicate your database...
is mean the live usage
and the potential.
so is still fake, but is a big improvement.

Now i have some indication of what to optimize.
another thing can help people chack the issue is the option inside my.cnf:

table_definition_cache = 16384
table_open_cache = 8192
open_files_limit=40000
( this setting is 92% RAM the tuner show - no jumping thanks to Zachary stay 92% )

now the open file limit belong to cPanel Twicks and you can't touch it better not,
but table open cache and table definition is important to the test.

Before Zachry fix the permission is was:
table_definition_cache = 19935
table_open_cache = 19935
(MySQL tuner recommended on this value - this is 120% RAM the tuner show - no jumping thanks to Zachary stay 120% )

Now usually for the test, we need to know we finished these tables (usually in 24 hours they got the maximum capability)
now it is too much big is takes time and a number of the process the RAM will jump again.

if database ram usage not jumps again after 24 hours and stuck on some normal % in my scenario 92% right now.
This what actually Zachry fix, with the permissions now database not do the second jump and stuck on some normal %

but still is not the real usage of RAM actually and the 92% is not 92%...
and the potential is not 667% as well...

BUT!
The server runs much faster, less abort connections, sockets get connected in my.cnf.
and the most important.

The tuner gets one time RAM usage memory after 24 hours and not get a second jump of RAM as before I meet Zachary.
THIS IS VERY BIG IMPROVEMENT FROM BEFORE EVEN IF IS STILL FAKE.

God bless you, Zachary

cPRex · Feb 5, 2021

I'm glad those details were helpful!

nadav123 · Feb 8, 2021

cPRex is looked much much better now,
Even stabilize! deleted tables and ram usage go down and up (shifting) this looks much much better.
Sorry, i didn't respond,
This because i was needed a couple of days more to be sure, now I am sure.

I HOPE THIS THEARD WILL HELP FOR ALL THE PEOPLE HAVE VPS CLOUD ON ADMINISTRATION
AND SUFFER FROM THE SAME ISIUE LIKE THIS PEOPLE:

MySQL memory usage exceeding "Maximum Possible Memory Usage" · Issue #378 · major/MySQLTuner-perl

Greetings, In mysqltuner, I have a maximum possible memory usage of: [OK] Maximum reached memory usage: 459.1M (46.27% of installed RAM) [OK] Maximum possible memory usage: 699.8M (70.52% of instal...

github.com

MariaDb per thread wrong value · Issue #502 · major/MySQLTuner-perl

MySQL Version 10.1.45-MariaDB x86_64 mysqltuner.pl - Version 1.7.19 -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 4m 30s (758 q [...

github.com

And have more...

as I said on cloud administration the result little bit fake but has a way to understand how it works.

BUT AS I SAID,
IF SOMEONE NOTICE THE TUNER NOT LOWER BY HIMSELF THE %
OR THE TUNER HAS A WIRED RAM USAGE JUMPS (EVEN WITH THE FAKE RAM USAGE).
THIS IS MEAN HAVE PROBLEMS... AND THE TUNER NOT GET WRONG...!
HE PROVIDES FAKE USAGE, BUT WITHOUT THE TUNER I NOT WAS ABLE TO KNOW I HAVE THE WRONG PREMISON...!!! (VERY VERY IMPORTENT)

In my case is was permission 0755 from old centOS + connection of sockets between mysqld to MySQL inside the my.cnf.

If you remember cPRex, you ask me, why MySQL Tuner and what your purpose?

So...
as you see is exactly why...
and the Tuner proof it in this thread, This tool is much more than diagnostic.

Without this tool, I went with the wrong database permission, and this because of the move from CentOS 06 - 07...
It means a lot of people have this problem and this not comes from the VPS Cloud administration.
(But I can assure you, the 666% potential usage is because the VPS and dont have anything we can do this is not the fault of cPanel):

The Right Way to Monitor Virtual Memory on Linux

Read: The right way to monitor virtual memory on Linux

www.logicmonitor.com

I hope this thread will help people understand the tuner on Cloud VPS System Administration.
(This is a must of the systems today cPRex, for example, the biggest: Google Cloud).

Hope this will help people,

Thread starter	Similar threads	Forum	Replies	Date
S	SOLVED Problem Creating Requests to PhP Files Stored on Server?	File Management	3	Jul 24, 2023
M	Problem with ZIP/Unzip	File Management	1	Feb 1, 2023
R	Problem with compressing files	File Management	4	Jan 1, 2023
G	CAGEFS PROBLEM	File Management	4	Feb 12, 2022
J	SOLVED Problem with default file permission. Something is overriding umask setting	File Management	3	Feb 16, 2021

Problem with mysqltuner and suspicious of permission issue (centos 6-7)

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member

Jurassic Moderator

Well-Known Member