How to fix a slow entry in MySQL database?
The server is dedicated, powerful enough. Base on SSD. Somehow lowest rates on record. The first indicators are indications Bitrix "panel performance". (about what Bitrix "miracle" product, I know).
Then ran some tests.
Prompt where to dig?
Then ran some tests.
./mysqltuner.pl
>> MySQLTuner 1.6.10 - Major Hayden
>> Bug reports, feature requests, and downloads at mysqltuner.com
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 81K (Tables: 12)
[--] Data in InnoDB tables: 6G (Tables: 3335)
[!!] Total fragmented tables: 149
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17h 56m 35s 82d (2 q [0.000 qps], 25M conn, TX: 157B, RX: 115B)
[--] Reads / Writes: 100% / 0%
[--] The Binary logging is disabled
[--] Physical Memory : 62.9 G
[--] MySQL Max memory : 9.9 G
[--] Other process memory: 13.8 G
[--] Total buffers: 8.2 G global + 24.9 M per thread (70 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] reached Maximum memory usage: 9.9 G (15.76% of installed RAM)
[OK] Maximum possible memory usage: 9.9 G (15.72% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2)
[!!] Highest connection usage: 100% (71/70)
[OK] Aborted connections: 0.51% (130070/25606836)
[OK] Query cache efficiency: 100.0% (457M cached / 457M selects)
[!!] Query cache prunes per day: 1965813
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[OK] Thread cache hit rate: 99% (10K created / 25M connections)
[OK] Table cache hit rate: 100% (9K open / opened 0)
[OK] Open file limit used: 0% (124/28K)
[OK] Table locks acquired immediately: 99% (immediate 3B / 3B locks)
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0 M/138.0 K
[OK] Key buffer Read hit rate: 100.0% (1B cached / 30 reads)
[OK] Key buffer Write hit rate: 100.0% (499M cached / writes 24)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 7.8 G/6.6 G
[OK] InnoDB buffer Used: 87.81% (449606 used/ 512000 total)
[OK] InnoDB buffer Read efficiency: 100.00% (4784039165461 hits/ 4784039252171 total)
[!!] InnoDB Log Write efficiency: 13.3% (16130866 hits/ 121277476 total)
[!!] InnoDB log waits: 0.00% (7 waits / 105146610 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
max_connections (> 70)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 128M) [see warning above]
innodb_log_buffer_size (>= 1M)
mysqlslap -u root-p --auto-generate-sql
Enter password:
Benchmark
Average number of seconds to run all queries: 0.481 seconds
Minimum number of seconds to run all queries: 0.481 seconds
Maximum number of seconds to run all queries: 0.481 seconds
Number of clients running queries: 1
Average number of queries per client: 0
mysqlslap -u root-p --auto-generate-sql --concurrency 20 --iterations 10 --engine=innodb -vvv
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 6.309 seconds
Minimum number of seconds to run all queries: 1.907 seconds
Maximum number of seconds to run all queries: 14.787 seconds
Number of clients running queries: 20
Average number of queries per client: 0
Prompt where to dig?
asked July 8th 19 at 11:44
5 answers
answered on
Solution
Found the solution: dmitro.pro/fixes/1%D1%81-%D0%B1%D0%B8%D1%82%D1%80%...
In the end, the fix in my.cnf:
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
And to improve the performance of CPU, set OPCache instead of APC:
In the end, the fix in my.cnf:
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
And to improve the performance of CPU, set OPCache instead of APC:
answered on July 8th 19 at 11:48
I would not look at such a standard.
First, numbers are just unrealistic for a single mysql server. Although it depends on the requests. But the record is 5K per second as it overdue.
Second, the tests from Bitrix to put it mildly , not very.
Well, actually he believes in your case this number is not clear at all. Can be a performance rating at the link above, then it's all complete nonsense. So few may not even be on a default setup on a VPS server.
First, numbers are just unrealistic for a single mysql server. Although it depends on the requests. But the record is 5K per second as it overdue.
Second, the tests from Bitrix to put it mildly , not very.
Well, actually he believes in your case this number is not clear at all. Can be a performance rating at the link above, then it's all complete nonsense. So few may not even be on a default setup on a VPS server.
answered on July 8th 19 at 11:50
Evaluation of processor 2.7 on a dedicated server... If the server is not on an atom, check the energy saver settings. You may want to translate the cpu governor to performance and test results will improve dramatically.
No idea how to check/do in CentO (( And the CPU is 2 x Intel(R) Xeon(R) CPU E5645 @2.40 GHz commented on July 8th 19 at 11:53
: CentOS commented on July 8th 19 at 11:56
yum install cpupowerutils
cpupower frequency-info # to know the current settings
cpupower frequency-set --governor performance # to put processor in performance
Well, for the sake of completeness, link to the documentation: https://access.redhat.com/documentation/en-US/Red_... commented on July 8th 19 at 11:59
cpupower frequency-info # to know the current settings
cpupower frequency-set --governor performance # to put processor in performance
Well, for the sake of completeness, link to the documentation: https://access.redhat.com/documentation/en-US/Red_... commented on July 8th 19 at 11:59
: Thank you. Here is what was given on the current:
# cpupower frequency-info
analyzing CPU 0:
no or unknown cpufreq driver is active on this CPU
CPUs which run at the same hardware frequency: Not Available
CPUs which need to have their frequency coordinated by software: Not Available
maximum transition latency: Cannot determine or is not supported.
hardware limits: Not Available
available cpufreq governors: Not Available
Unable to determine current policy
current CPU frequency: Unable to call hardware
current CPU frequency: Unable to call to kernel
boost state support:
Supported: yes
Active: yes
2000 MHz max turbo 4 active cores
2000 MHz max turbo 3 active cores
2100 MHz max turbo 2 active cores
2100 MHz max turbo 1 active cores commented on July 8th 19 at 12:02
# cpupower frequency-info
analyzing CPU 0:
no or unknown cpufreq driver is active on this CPU
CPUs which run at the same hardware frequency: Not Available
CPUs which need to have their frequency coordinated by software: Not Available
maximum transition latency: Cannot determine or is not supported.
hardware limits: Not Available
available cpufreq governors: Not Available
Unable to determine current policy
current CPU frequency: Unable to call hardware
current CPU frequency: Unable to call to kernel
boost state support:
Supported: yes
Active: yes
2000 MHz max turbo 4 active cores
2000 MHz max turbo 3 active cores
2100 MHz max turbo 2 active cores
2100 MHz max turbo 1 active cores commented on July 8th 19 at 12:02
:
cat /proc/cpuinfo | grep MHz
another view.
If there is a normal frequency, then the problem is definitely not that.
If lower, then read the Doc from RH (link given above), I CentOS is not very strong. commented on July 8th 19 at 12:05
cat /proc/cpuinfo | grep MHz
another view.
If there is a normal frequency, then the problem is definitely not that.
If lower, then read the Doc from RH (link given above), I CentOS is not very strong. commented on July 8th 19 at 12:05
: it's OK, all for 2400. commented on July 8th 19 at 12:08
answered on July 8th 19 at 11:52
base betrixaban, full-time, so is of little help ( there is apparently either is mysql or the server itself (drives for example) commented on July 8th 19 at 11:55
: don't. well, where does disks, you have the same kind of SSD? But indicators are more like a recording on a floppy disk. The fact that the base staff does not prevent you to tune it according to your needs. I would carefully cut methods of work with the database on multiple inserts, stuffed timestamp and looked at where the schools, maybe it generally opens 1 connection on the insert, Sori not familiar with Bitrix on the inside. In manami suggest to lock tables before multiple insert, then unlock back. Promise +40% speed. I am sure there is a place to columnate speed. commented on July 8th 19 at 11:58
: SSD Yes. OK, I will try to watch. The problem is that regular used classes of Bitrix. So everything should work as expected commented on July 8th 19 at 12:01
answered on July 8th 19 at 11:54
top/iotop?
: when you insert, Yes. there at nil all. commented on July 8th 19 at 12:00
: I do not understand You, explain plz commented on July 8th 19 at 12:03
Find more questions by tags MySQL
Reasonable for Bitrix depending on the quantity of goods and loading page information.
For the default store with thousands of items and less modern design.
With menus across hundreds of categories/subcategories.
It can be 1-4 seconds to load the main page.
https://poiuty.com/index.php?title=Bitrix_test
Here is more information on tests. - bailee_Stant commented on July 8th 19 at 11:54
There is only debit code, and perhaps add indexy in the database, or edit the code. - bailee_Stant commented on July 8th 19 at 12:00