Facebook
Twitter
Servus
Basic Pleskian
Hello!
Only two variables I edited in /etc/mysql/my.cnf under mysql daemon
[mysqld]
bind-address=127.0.0.1
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#bind-address = ::ffff:127.0.0.1
local-infile=0
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G
First was after upgrade to Ubuntu 16.04 with Plesk 17 by provider in April. It makes it very fast to playback huge backups for database. I successfully set this value since around one decade.
innodb-flush-log-at-trx-commit[=2] ...default is [=1] strongly recommend by Woltlab Communty Software
Second change was today morning, MySQL tuning, also by Woltlab.
First I ran this awesome script for checking the recommend value for innodb_buffer_pool_size in local database with the result 1G. So I also paste the second value in my.cnf. Restarted service mysql like usual. No problems.
Script for recommended_innodb_innodb_buffer_pool_size
Then I wanted to to be sure and check if everything is correctly existing after reboot and wanted also check the default value of innodb_autoextend_increment to increase it for better performance to 1-5% of RAM. In my case with 16G Ram I always used smooth 512M.
But I stopped this for now because I saw strange formats for several values. SCREENSHOT on another server (too large for uploading here)
I have to say that there was no need to look in local database PHPMyAdmin after I reinstalled the new Ubuntu 16 image. And I can't remember to see such html-like values with brackets. For me it looks like impossible or a strange issue.
<abbr title="1,073,741,824">1 GiB</abbr>
Two questions:
1. Is this kind of format correct or do I have a misconfiguration in MySQL format?
2. If this format is not correct, and I have to resolve this problem, how could I replay only a automated created mysql backup perhaps daily by Plesk.
Or, if it is the only possible way replay a full Plesk backup which is also created minimum daily.
--- I must say I made a backup of /etc/mysql/my.cnf during the today work, but I didn't made the mysql backup before using the script and changing to 1G. For some stupid reasons I made it afterwards, to have one bfore changing the 3rd value innodb_autoextend_increment to 512M.
I know the support and forum articles about replaying backups. But it would be wonderful if you could point me to the best way. Only if it's necessary at all. Perhaps the values are okay as they are. I hope it soo much.
Plesk repair all -n was without any errors, warnings infos, everything seems like usual.
Greets
Only two variables I edited in /etc/mysql/my.cnf under mysql daemon
[mysqld]
bind-address=127.0.0.1
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#bind-address = ::ffff:127.0.0.1
local-infile=0
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G
First was after upgrade to Ubuntu 16.04 with Plesk 17 by provider in April. It makes it very fast to playback huge backups for database. I successfully set this value since around one decade.
innodb-flush-log-at-trx-commit[=2] ...default is [=1] strongly recommend by Woltlab Communty Software
Second change was today morning, MySQL tuning, also by Woltlab.
First I ran this awesome script for checking the recommend value for innodb_buffer_pool_size in local database with the result 1G. So I also paste the second value in my.cnf. Restarted service mysql like usual. No problems.
Script for recommended_innodb_innodb_buffer_pool_size
Code:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;Then I wanted to to be sure and check if everything is correctly existing after reboot and wanted also check the default value of innodb_autoextend_increment to increase it for better performance to 1-5% of RAM. In my case with 16G Ram I always used smooth 512M.
But I stopped this for now because I saw strange formats for several values. SCREENSHOT on another server (too large for uploading here)
I have to say that there was no need to look in local database PHPMyAdmin after I reinstalled the new Ubuntu 16 image. And I can't remember to see such html-like values with brackets. For me it looks like impossible or a strange issue.
<abbr title="1,073,741,824">1 GiB</abbr>
Two questions:
1. Is this kind of format correct or do I have a misconfiguration in MySQL format?
2. If this format is not correct, and I have to resolve this problem, how could I replay only a automated created mysql backup perhaps daily by Plesk.
Or, if it is the only possible way replay a full Plesk backup which is also created minimum daily.
--- I must say I made a backup of /etc/mysql/my.cnf during the today work, but I didn't made the mysql backup before using the script and changing to 1G. For some stupid reasons I made it afterwards, to have one bfore changing the 3rd value innodb_autoextend_increment to 512M.
I know the support and forum articles about replaying backups. But it would be wonderful if you could point me to the best way. Only if it's necessary at all. Perhaps the values are okay as they are. I hope it soo much.
Plesk repair all -n was without any errors, warnings infos, everything seems like usual.
Greets
Last edited:
