sql optimization help needed
Forgive me for being a non-admin kind of guy.
It is a Intel Xeon 5506 (Quad Core) with 8 GB DDR2 Memory. Please suggest how to fix the above points as the database is really slow, when trying to search for any product.
>> MySQLTuner 1.3.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 85M (Tables: 26)
[--] Data in InnoDB tables: 5G (Tables: 443)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 975K (Tables: 17)
[!!] Total fragmented tables: 33
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 17m 34s (13M q [131.096 qps], 4K conn, TX: 8B, RX: 4B)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 1.2G global + 24.4M per thread (3056 max threads)
[!!] Maximum possible memory usage: 74.0G (970% of installed RAM)
[OK] Slow queries: 0% (624/13M)
[OK] Highest usage of available connections: 0% (19/3056)
[OK] Key buffer size / total MyISAM indexes: 1.0G/99.9M
[!!] Key buffer hit rate: 7.6% (110K cached / 102K reads)
[!!] Query cache efficiency: 2.3% (185K cached / 8M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 2% (566 temp sorts / 26K sorts)
[!!] Temporary tables created on disk: 28% (9K on disk / 33K total)
[OK] Thread cache hit rate: 99% (19 created / 4K connections)
[OK] Table cache hit rate: 24% (626 open / 2K opened)
[OK] Open file limit used: 0% (97/15K)
[OK] Table locks acquired immediately: 100% (13M immediate / 13M locks)
[!!] InnoDB buffer pool / data size: 128.0M/5.2G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 5G)
It is a Intel Xeon 5506 (Quad Core) with 8 GB DDR2 Memory. Please suggest how to fix the above points as the database is really slow, when trying to search for any product.
-
Could you provide some more details about the database itself? For instance, how large is the database, and what's your server load and disk I/O (iostat) when you search for products? Thank you. 0 -
the database contains 1 million products. May I know how to check server load & iostat in WHM ? Thanks in advance. 0 -
[quote="zkhanseo, post: 1651951">May I know how to check server load & iostat in WHM ?
This thread will help you with commands/utilities on checking the load and disk activity: Troubleshooting High Loads On Linux Systems Thank you.0 -
# sar -d Linux 2.6.32-431.17.1.el6.x86_64 (server1.cheapesale.com) 05/27/2014 _x86_64_ (4 CPU) 12:00:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 12:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12:10:01 AM dev8-0 3.27 8.65 177.57 57.01 0.03 10.63 5.53 1.81 12:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12:20:01 AM dev8-0 3.30 3.01 178.47 54.96 0.03 9.17 5.46 1.80 12:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12:30:01 AM dev8-0 3.40 2.52 172.46 51.54 0.03 10.20 5.68 1.93 12:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12:40:01 AM dev8-0 4.41 38.17 182.25 49.94 0.04 10.10 5.60 2.47 12:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 12:50:01 AM dev8-0 4.95 25.79 200.78 45.77 0.06 12.52 5.02 2.48 01:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01:00:01 AM dev8-0 7.25 24.09 182.12 28.44 0.12 16.84 5.80 4.21 01:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01:10:01 AM dev8-0 16.15 40.94 467.20 31.46 0.31 19.23 5.38 8.68 01:10:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 01:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01:20:01 AM dev8-0 6.01 12.51 160.93 28.84 0.10 16.03 5.29 3.18 01:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01:30:01 AM dev8-0 4.39 40.32 106.15 33.34 0.07 16.21 4.87 2.14 01:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01:40:01 AM dev8-0 2.67 2.25 80.61 30.98 0.04 14.31 3.96 1.06 01:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01:50:01 AM dev8-0 3.07 2.84 80.78 27.28 0.05 15.93 4.26 1.31 02:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:00:01 AM dev8-0 3.27 2.65 84.73 26.70 0.07 19.88 4.98 1.63 02:10:02 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:10:02 AM dev8-0 3.46 15.49 88.64 30.06 0.06 17.70 5.90 2.05 02:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:20:01 AM dev8-0 4.65 22.97 115.56 29.76 0.09 18.47 4.99 2.32 02:20:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 02:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:30:01 AM dev8-0 2.89 1.22 78.69 27.65 0.06 19.40 4.80 1.39 02:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:40:01 AM dev8-0 4.01 3.85 103.72 26.80 0.07 17.56 6.14 2.46 02:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 02:50:01 AM dev8-0 3.12 2.98 81.98 27.21 0.08 24.81 6.39 1.99 03:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:00:01 AM dev8-0 3.31 9.70 83.91 28.27 0.08 22.66 5.09 1.69 03:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:10:01 AM dev8-0 3.09 1.11 83.38 27.36 0.06 19.18 4.91 1.52 03:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:20:01 AM dev8-0 3.03 5.25 81.00 28.51 0.05 17.49 5.20 1.57 03:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:30:01 AM dev8-0 35.19 661.78 274.60 26.61 0.16 4.64 1.43 5.04 03:30:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 03:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:40:01 AM dev8-0 3.11 1.43 90.41 29.54 0.05 14.66 5.70 1.77 03:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 03:50:01 AM dev8-0 2.82 1.08 81.90 29.42 0.04 13.53 4.71 1.33 04:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 04:00:01 AM dev8-0 3.13 1.06 96.21 31.04 0.04 13.66 4.72 1.48 04:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 04:10:01 AM dev8-0 2.51 1.27 131.09 52.73 0.04 16.67 4.69 1.18 04:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 04:20:01 AM dev8-0 3.40 8.87 132.81 41.61 0.06 17.02 4.36 1.48 04:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 04:30:01 AM dev8-0 3.99 9.06 97.13 26.62 0.10 23.92 5.32 2.12 04:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 04:40:01 AM dev8-0 5.58 78.11 100.57 32.01 0.14 25.66 5.06 2.82 04:40:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 04:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 04:50:01 AM dev8-0 4.25 6.74 105.14 26.30 0.10 23.54 4.87 2.07 05:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 05:00:01 AM dev8-0 131.22 4097.33 289.79 33.43 0.41 3.09 1.17 15.32 05:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 05:10:01 AM dev8-0 4.63 74.57 97.75 37.21 0.13 28.17 4.97 2.30 05:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 05:20:01 AM dev8-0 3.88 10.50 101.85 28.93 0.11 28.48 5.19 2.02 05:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 05:30:01 AM dev8-0 2.88 35.99 169.02 71.29 0.03 11.92 4.67 1.34 05:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 05:40:01 AM dev8-0 19.58 3520.78 146.79 187.31 0.08 3.84 2.02 3.95 05:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 05:50:01 AM dev8-0 2.50 9.71 159.22 67.53 0.02 9.70 4.77 1.19 05:50:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 06:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:00:01 AM dev8-0 2.65 4.78 173.24 67.07 0.04 15.32 5.40 1.43 06:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:10:01 AM dev8-0 2.31 0.64 155.56 67.54 0.03 13.77 4.81 1.11 06:20:02 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:20:02 AM dev8-0 3.53 5.62 184.56 53.91 0.03 9.92 4.77 1.68 06:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:30:01 AM dev8-0 2.89 3.98 149.13 53.00 0.03 9.91 4.83 1.39 06:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:40:01 AM dev8-0 2.67 6.38 147.77 57.67 0.03 9.58 4.68 1.25 06:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:50:01 AM dev8-0 2.87 6.13 154.28 55.81 0.03 11.33 4.82 1.39 07:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:00:01 AM dev8-0 3.05 2.14 164.51 54.56 0.02 8.10 5.00 1.53 07:00:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 07:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:10:01 AM dev8-0 4.42 11.55 278.78 65.70 0.04 8.82 5.68 2.51 07:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:20:01 AM dev8-0 5.19 44.36 393.67 84.44 0.06 11.24 5.16 2.68 07:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:30:01 AM dev8-0 3.76 11.51 124.11 36.07 0.06 16.42 4.91 1.85 07:40:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:40:01 AM dev8-0 2.99 3.28 98.76 34.18 0.04 14.33 4.53 1.35 07:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 07:50:01 AM dev8-0 3.60 16.82 110.92 35.45 0.07 19.04 5.07 1.83 08:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:00:01 AM dev8-0 2.97 9.05 95.83 35.26 0.04 14.94 5.12 1.52 08:10:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:10:01 AM dev8-0 3.48 6.48 104.90 31.99 0.07 20.17 5.68 1.98 08:10:01 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 08:20:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:20:01 AM dev8-0 9.18 54.94 218.05 29.75 0.16 17.26 6.29 5.77 08:30:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:30:01 AM dev8-0 7.44 14.10 195.63 28.19 0.13 17.15 6.06 4.51 08:40:02 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:40:02 AM dev8-0 6.52 182.60 122.95 46.86 0.06 8.62 3.77 2.46 08:50:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:50:01 AM dev8-0 2.67 1.44 137.80 52.08 0.04 14.52 5.88 1.57 09:00:01 AM dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 09:00:01 AM dev8-0 3.26 7.40 110.00 36.04 0.06 17.30 5.91 1.92 Average: dev8-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: dev8-0 7.30 170.19 147.87 43.56 0.07 10.27 3.37 2.46 root@server1 [~]#0 -
Your database has one million products that means it should be large in size. While /searching/creating records it may use disk that lead to high disk I/O. I suggest you to use tuning-primer' a third party script to optimize your mysql database. tuning-primer has support for mysql 5.5 and 5.6 0
Please sign in to leave a comment.
Comments
5 comments