Support » Plugin: PHP/MySQL CPU performance statistics » Test is Ok but mysql perfs are not
Test is Ok but mysql perfs are not
-
Hello,
I did the test and everything run without problems but I have bad performance for Query test (200 times). Result :
MySQL test:
Time to perform: Query test (200 times) : 5.77 seconds (35/sec)
Time to perform: select BENCHMARK(500000000, EXTRACT(YEAR FROM NOW())) : 4.32 seconds
Time to perform: select BENCHMARK(10000000,ENCODE(‘hello’,’goodbye’)) : 1.14 seconds
Time to perform: select BENCHMARK(25000000,1+1*2); : 0.54 seconds
Total time (all MySQL tests) : 11.77 secondsDo you have preconisation on how to optimize my MySQL server ?
Thank you in advance.
-
I am trying to do this as well.
I will post my results using MySQL Tuner.
Since my tests, I try to optimize the server without success.
Here is mysqltuner result on my MySQL server (executed today) :
>> MySQLTuner 1.6.2 – 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 5.5.44-MariaDB
[OK] Operating on 64-bit architecture——– Storage Engine Statistics ——————————————-
[–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 222M (Tables: 933)
[–] Data in InnoDB tables: 1G (Tables: 3245)
[–] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 153——– CVE Security Recommendations ——————————————-
[–] Skipped due to –cvefile option undefined——– Performance Metrics ————————————————-
[–] Up for: 28d 11h 39m 38s (120M q [48.901 qps], 1M conn, TX: 486B, RX: 24B)
[–] Reads / Writes: 96% / 4%
[–] Binary logging is disabled
[–] Total buffers: 8.5G global + 16.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 9.6G (30.78% of installed RAM)
[OK] Maximum possible memory usage: 10.9G (34.91% of installed RAM)
[OK] Slow queries: 0% (1K/120M)
[OK] Highest usage of available connections: 47% (72/151)
[OK] Aborted connections: 0.02% (214/1277118)
[OK] Query cache efficiency: 40.8% (75M cached / 185M selects)
[!!] Query cache prunes per day: 30918
[OK] Sorts requiring temporary tables: 0% (13 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 135332
[!!] Temporary tables created on disk: 60% (1M on disk / 2M total)
[OK] Thread cache hit rate: 96% (39K created / 1M connections)
[!!] Table cache hit rate: 0% (4K open / 694K opened)
[OK] Open file limit used: 7% (1K/24K)
[OK] Table locks acquired immediately: 99% (40M immediate / 40M locks)——– MyISAM Metrics —————————————————–
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/68.8M
[OK] Read Key buffer hit rate: 99.9% (94M cached / 99K reads)
[!!] Write Key buffer hit rate: 4.2% (2M cached / 2M writes)——– InnoDB Metrics —————————————————–
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 8.0G/1.4G
[!!] InnoDB buffer pool instances: 4
[OK] InnoDB Used buffer: 100.00% (524282 used/ 524284 total)
[OK] InnoDB Read buffer efficiency: 100.00% (31136707612 hits/ 31137314329 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5528856 writes)——– 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
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
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 (24000) variable
should be greater than table_open_cache ( 4096)
Variables to adjust:
query_cache_size (> 64M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 4096)
innodb_buffer_pool_instances(=8)@te-deum
Here is mine:>> MySQLTuner 1.6.2 - 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 5.5.47-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 282M (Tables: 140) [--] Data in InnoDB tables: 512K (Tables: 15) [!!] Total fragmented tables: 13 -------- Security Recommendations ------------------------------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] There is no basic password file list! -------- CVE Security Recommendations ------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ------------------------------------------------- [--] Up for: 6d 16h 39m 20s (25M q [43.922 qps], 187K conn, TX: 219B, RX: 4B) [--] Reads / Writes: 97% / 3% [--] Binary logging is disabled [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads) [OK] Maximum reached memory usage: 283.5M (5.88% of installed RAM) [OK] Maximum possible memory usage: 583.2M (12.10% of installed RAM) [OK] Slow queries: 0% (393/25M) [OK] Highest usage of available connections: 27% (42/151) [OK] Aborted connections: 0.00% (2/187313) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 7M sorts) [!!] Joins performed without indexes: 96783 [!!] Temporary tables created on disk: 50% (1M on disk / 3M total) [!!] Thread cache is disabled [!!] Table cache hit rate: 6% (400 open / 6K opened) [OK] Open file limit used: 5% (532/10K) [OK] Table locks acquired immediately: 99% (31M immediate / 31M locks) -------- MyISAM Metrics ----------------------------------------------------- [OK] Key buffer used: 100.0% (8M used / 8M cache) [OK] Key buffer size / total MyISAM indexes: 8.0M/75.7M [OK] Read Key buffer hit rate: 100.0% (21B cached / 1M reads) [!!] Write Key buffer hit rate: 81.4% (12M cached / 2M writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 128.0M/512.0K [OK] InnoDB buffer pool instances: 1 [OK] InnoDB Used buffer: 100.00% (8191 used/ 8191 total) [!!] InnoDB Read buffer efficiency: 88.88% (119055 hits/ 133955 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 5 writes) -------- 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 Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Set thread_cache_size to 4 as a starting value 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 (10000) variable should be greater than table_open_cache ( 400) Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4) table_open_cache (> 400)
my.cnf is really tiny right now:
GNU nano 2.0.9 File: /etc/my.cnf [mysqld] innodb_file_per_table=1 max_allowed_packet=268435456 open_files_limit=10000 default-storage-engine=MyISAM log_error=/var/log/mysql/mysql_error.log log_slow_queries = 1 slow_query_log_file = /var/log/mysql/mysql_slowqueries.log
My server specs are:
VPS running 8 core Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Memory: 4892664k/5242880k available
Running CentOS x64And my performance is about the same at 11.62 sec All MySQL.
We can work on this together and try out some settings if you want? I have a way to make custom configs close enough to match the server, but I am not a DBA.
I have also very low results for MySQL Query test:
Query test (200 times): 25.35 seconds (8/sec)
Any suggestions on how to improve these?
Running on Nginx, php7, with redis cache.
- The topic ‘Test is Ok but mysql perfs are not’ is closed to new replies.