Opened 17 months ago
Last modified 17 months ago
#113 new defect
MySQL Performance
| Reported by: | chris | Owned by: | chris |
|---|---|---|---|
| Priority: | major | Milestone: | Maintenance |
| Component: | crin1 | Version: | |
| Keywords: | Cc: | russell, peter | |
| Estimated Number of Hours: | 0 | Add Hours to Ticket: | 0 |
| Billable?: | yes | Total Hours: | 0.75 |
Description (last modified by chris)
MySQL is set to use a lot of resources, see the RAM allocation here:
And also a lot of queries are cached via memcache, which also has a lot of RAM allocated, see:
What can we do to make it quicker?
See also the work done when the server was setup on ticket:9
Change History (8)
comment:1 Changed 17 months ago by chris
comment:2 Changed 17 months ago by chris
- Description modified (diff)
comment:3 Changed 17 months ago by chris
- Add Hours to Ticket changed from 0 to 0.25
- Total Hours set to 0.25
This shouldn't be the case:
[!!] User 'piwik@localhost@%' has no password set.
So that account has been removed:
mysql mysql mysql> delete from user where User="piwik@localhost"; Query OK, 1 row affected (0.30 sec)
Things we could tweak:
- Key buffer size / total MyISAM indexes: 1.0G/670.9M -- this could be dropped to 700M
- [!!] InnoDB buffer pool / data size: 6.0G/8.8G -- the InnoDB buffer pool should be bigger than the data size so can we reduce the data size?
Looking at the database sizes by the dump sizes:
cd /var/backups/mysql/databases 889M ./piwik 2.2G ./stage 2.2G ./newprod 1.3G ./drupal 4.0K ./example 46M ./mediawiki 1.3M ./owncloud 72K ./phpmyadmin 2.2G ./dev 8.8G .
I'm not sure that the ownCloud site is used -- can we delete it?
There are 4 Drupal databases, don't we only have 3 sites, can one be dropped?
2.2G ./stage 2.2G ./newprod 1.3G ./drupal 2.2G ./dev
Is there a reason not to drop the example database?
Would it be worth me looking at reducing the size of the Piwik database?
comment:4 Changed 17 months ago by chris
Could be move the development database onto the development server, Crin4? Getting the InnoDB datasize to be less than the InnoDB buffer pool could make quite a difference?
comment:5 Changed 17 months ago by russell
Hi Chris,
Thanks for looking into this.
I've just reviewed this ticket with Peter.
We feel that ./drupal can be dropped now. My understanding is that this was an old dev. now out of use.
I'm going to do some work on the dev database to try to reduce its size by loosing some search indexing and caching from it.
We don't feel that we can move the dev. DB onto crin4, crin4 doesn't have sufficient resources.
It would be great if we could get the data size down under 6G so we can stop thrashing the HD so much.
All the best,
Russell
comment:6 Changed 17 months ago by chris
- Add Hours to Ticket changed from 0 to 0.5
- Total Hours changed from 0.25 to 0.75
That's great, thanks. I see you have dropped the drupal database.
This is the latest output from mysql tuner:
mysqltuner
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.55-0+deb8u1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 8G (Tables: 1569)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MyISAM tables: 1G (Tables: 13)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 1571
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 15m 5s (31M q [128.897 qps], 589K conn, TX: 68B, RX: 7B)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 7.5G global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 7.9G (101% of installed RAM)
[OK] Slow queries: 0% (905/31M)
[OK] Highest usage of available connections: 75% (114/151)
[OK] Key buffer size / total MyISAM indexes: 1.0G/671.0M
[OK] Key buffer hit rate: 96.5% (37M cached / 1M reads)
[OK] Query cache efficiency: 86.6% (25M cached / 28M selects)
[!!] Query cache prunes per day: 604590
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 973K sorts)
[!!] Temporary tables created on disk: 31% (209K on disk / 663K total)
[OK] Thread cache hit rate: 99% (198 created / 589K connections)
[!!] Table cache hit rate: 0% (64 open / 451K opened)
[OK] Open file limit used: 0% (1/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
[!!] InnoDB buffer pool / data size: 6.0G/8.8G
[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
Increasing the query_cache size over 128M may reduce performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
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
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 512M) [see warning above]
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 64)
innodb_buffer_pool_size (>= 8G)
So we still could do with loosing 2.8G of innodb data to get it below 6G... Looking at the sizes of all the databases:
mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+---------------+ | Database name | Size (MB) | +--------------------+---------------+ | dev | 4402.23059082 | | information_schema | 0.00878906 | | mediawiki | 89.31251144 | | mysql | 0.68581772 | | newprod | 4710.50323105 | | owncloud | 3.71875000 | | performance_schema | 0.00000000 | | phpmyadmin | 0.34375000 | | piwik | 1199.14062500 | | stage | 4389.69979095 | +--------------------+---------------+ 10 rows in set (59.36 sec)
Looking at the size of the newprod database by tables:
mysql> SELECT
-> TABLE_NAME, table_rows, data_length, index_length,
-> round(((data_length + index_length) / 1024 / 1024),2) 'Size in MB'
-> FROM information_schema.TABLES
-> WHERE table_schema = 'newprod' and TABLE_TYPE='BASE TABLE'
-> ORDER BY data_length DESC;
+-------------------------------------------------------------+------------+-------------+--------------+------------+
| TABLE_NAME | table_rows | data_length | index_length | Size in MB |
+-------------------------------------------------------------+------------+-------------+--------------+------------+
| cache_entity_node | 49164 | 955023360 | 1589248 | 912.30 |
| search_api_db_customsearch_body_value | 11880076 | 555696128 | 572243968 | 1075.69 |
| field_data_body | 43157 | 418250752 | 20348928 | 418.28 |
| field_revision_body | 42323 | 410910720 | 20267008 | 411.20 |
| search_index | 9754304 | 273985116 | 227266560 | 478.03 |
| cache_menu | 64307 | 202211328 | 20561920 | 212.45 |
| search_dataset | 42624 | 171667888 | 867328 | 164.54 |
| watchdog | 170709 | 85016576 | 7389184 | 88.13 |
| cache_views | 3761 | 28917760 | 212992 | 27.78 |
| cache_token | 17 | 23609344 | 16384 | 22.53 |
| taxonomy_index | 366053 | 17350656 | 27279360 | 42.56 |
| search_api_db_customsearch_title | 362792 | 17350656 | 17350656 | 33.09 |
| field_revision_field_tags | 164007 | 11059200 | 47792128 | 56.13 |
| field_data_field_tags | 188353 | 11059200 | 41304064 | 49.94 |
| node | 43254 | 7880704 | 21053440 | 27.59 |
| search_api_db_customsearch | 45519 | 7880704 | 42221568 | 47.78 |
| node_access | 78865 | 7864320 | 0 | 7.50 |
| search_total | 352067 | 7730048 | 8334336 | 15.32 |
| url_alias | 40337 | 6832128 | 9519104 | 15.59 |
| cache | 35 | 6832128 | 16384 | 6.53 |
| node_revision | 38841 | 6832128 | 3162112 | 9.53 |
| search_api_db_customsearch_field_country | 41666 | 5783552 | 3506176 | 8.86 |
| locales_target | 40792 | 5783552 | 4767744 | 10.06 |
| search_api_db_customsearch_field_tags | 67876 | 5767168 | 2637824 | 8.02 |
| cache_form | 2 | 4734976 | 16384 | 4.53 |
| field_data_field_country | 67305 | 4734976 | 20529152 | 24.09 |
| twitter | 10054 | 4734976 | 311296 | 4.81 |
| field_revision_field_country | 67119 | 4734976 | 21528576 | 25.05 |
| field_data_field_crc | 42314 | 3686400 | 14024704 | 16.89 |
| field_revision_field_crc | 42895 | 3686400 | 13074432 | 15.98 |
| search_api_db_customsearch_body_summary | 59665 | 3686400 | 3620864 | 6.97 |
| domain_access | 82421 | 3686400 | 2637824 | 6.03 |
| search_api_db_customsearch_field_themes | 36233 | 3686400 | 1589248 | 5.03 |
| search_api_db_customsearch_field_crc | 43951 | 3653632 | 2637824 | 6.00 |
| variable | 6801 | 3637248 | 0 | 3.47 |
| field_revision_field_organisation | 38460 | 2637824 | 13189120 | 15.09 |
| field_revision_field_link_ | 24143 | 2637824 | 11108352 | 13.11 |
| cache_field | 338 | 2637824 | 49152 | 2.56 |
| apachesolr_index_entities_node | 43378 | 2637824 | 3244032 | 5.61 |
| search_api_db_customsearch_field_organisation | 27384 | 2637824 | 1572864 | 4.02 |
| field_revision_field_issues | 28635 | 2637824 | 12107776 | 14.06 |
| search_api_item | 38344 | 2637824 | 1572864 | 4.02 |
| field_data_field_import_table | 41962 | 2637824 | 12173312 | 14.13 |
| field_data_field_organisation | 37835 | 2637824 | 12156928 | 14.11 |
| search_api_db_customsearch_field_news | 13169 | 2637824 | 1589248 | 4.03 |
| simplenews_subscription | 43152 | 2637824 | 0 | 2.52 |
| field_data_field_themes | 35456 | 2637824 | 11042816 | 13.05 |
| field_data_field_import_id | 38522 | 2637824 | 9535488 | 11.61 |
| search_api_db_customsearch_search_api_access_node | 43052 | 2637824 | 2605056 | 5.00 |
| search_api_db_customsearch_field_address | 47972 | 2637824 | 2621440 | 5.02 |
| field_data_field_attachment_link | 17903 | 2637824 | 5128192 | 7.41 |
| locales_source | 21281 | 2637824 | 1589248 | 4.03 |
| field_revision_field_import_table | 41771 | 2637824 | 12173312 | 14.13 |
| field_revision_field_import_id | 39964 | 2637824 | 10584064 | 12.61 |
| field_data_field_link_ | 23852 | 2637824 | 11108352 | 13.11 |
| field_data_field_articles | 41553 | 2637824 | 13156352 | 15.06 |
| field_revision_field_attachment_link | 16067 | 2637824 | 9535488 | 11.61 |
| field_revision_field_themes | 38612 | 2637824 | 13107200 | 15.02 |
| field_revision_field_articles | 40276 | 2637824 | 13189120 | 15.09 |
| field_data_field_issues | 32903 | 2637824 | 11059200 | 13.06 |
| cache_entity_file | 2303 | 2621440 | 98304 | 2.59 |
| search_api_db_customsearch_field_issues | 32322 | 2605056 | 1556480 | 3.97 |
| search_api_db_customsearch_field_email | 56043 | 2588672 | 2621440 | 4.97 |
| cache_bootstrap | 9 | 2310144 | 16384 | 2.22 |
| field_revision_field_email | 15720 | 1589248 | 4734976 | 6.03 |
| field_data_field_publications | 14521 | 1589248 | 4046848 | 5.38 |
| cache_entity_taxonomy_term | 639 | 1589248 | 16384 | 1.53 |
| search_api_db_customsearch_field_event_info | 13060 | 1589248 | 1589248 | 3.03 |
| search_api_db_customsearch_field_publications | 13276 | 1589248 | 442368 | 1.94 |
| field_data_field_address | 8772 | 1589248 | 1835008 | 3.27 |
| field_revision_field_publications | 14507 | 1589248 | 5308416 | 6.58 |
| field_data_field_news | 27694 | 1589248 | 11108352 | 12.11 |
| menu_links | 2451 | 1589248 | 884736 | 2.36 |
| field_revision_field_address | 9117 | 1589248 | 2064384 | 3.48 |
| simplenews_subscriber | 12188 | 1589248 | 1802240 | 3.23 |
| search_api_db_customsearch_field_web | 28867 | 1589248 | 1589248 | 3.03 |
| field_revision_field_news | 27441 | 1589248 | 11124736 | 12.13 |
| field_data_field_email | 16004 | 1589248 | 4308992 | 5.63 |
| search_api_db_customsearch_field_tel | 13354 | 1589248 | 442368 | 1.94 |
| file_managed | 2814 | 1556480 | 475136 | 1.94 |
| search_api_db_customsearch_field_violence | 8444 | 1556480 | 229376 | 1.70 |
| views_display | 117 | 1507328 | 16384 | 1.45 |
| menu_router | 796 | 1458176 | 425984 | 1.80 |
| field_data_field_violence | 8429 | 540672 | 1966080 | 2.39 |
| system_bak | 409 | 540672 | 131072 | 0.64 |
| system | 166 | 540672 | 163840 | 0.67 |
| field_revision_field_violence | 8125 | 540672 | 2179072 | 2.59 |
| field_data_field_tel | 6845 | 491520 | 1441792 | 1.84 |
| field_revision_field_tel | 6243 | 491520 | 1654784 | 2.05 |
| field_data_field_external_url | 3301 | 458752 | 950272 | 1.34 |
| field_revision_field_external_url | 3963 | 458752 | 950272 | 1.34 |
| search_node_links | 6466 | 443824 | 261120 | 0.67 |
| search_api_db_customsearch_field_fax | 6696 | 442368 | 294912 | 0.70 |
| field_data_field_crin_member | 4957 | 442368 | 1425408 | 1.78 |
| field_revision_field_crin_member | 5568 | 442368 | 1540096 | 1.89 |
| field_data_field_fax | 5172 | 425984 | 1327104 | 1.67 |
| field_revision_field_web | 3785 | 425984 | 983040 | 1.34 |
| field_revision_field_fax | 6022 | 425984 | 1523712 | 1.86 |
| field_data_field_web | 3697 | 425984 | 868352 | 1.23 |
| field_data_field_affiliate | 5065 | 409600 | 1359872 | 1.69 |
| field_revision_field_affiliate | 6241 | 409600 | 1490944 | 1.81 |
| field_data_field_operational_level | 5341 | 409600 | 1359872 | 1.69 |
| field_revision_field_operational_level | 5926 | 409600 | 1556480 | 1.88 |
| registry | 1786 | 393216 | 98304 | 0.47 |
| field_config_instance | 463 | 344064 | 32768 | 0.36 |
| cache_variable | 5 | 344064 | 16384 | 0.34 |
| search_api_db_customsearch_field_mandate | 8476 | 344064 | 262144 | 0.58 |
| registry_bak | 934 | 327680 | 114688 | 0.42 |
| field_data_field_promotion | 3927 | 311296 | 1359872 | 1.59 |
| field_revision_field_cited_crcs | 4417 | 311296 | 1114112 | 1.36 |
| field_revision_field_promotion | 4244 | 311296 | 1425408 | 1.66 |
| field_data_field_cited_crcs | 3477 | 311296 | 1015808 | 1.27 |
| search_api_db_customsearch_field_persistent_violations | 6320 | 294912 | 278528 | 0.55 |
| field_data_field_event_info | 2725 | 278528 | 720896 | 0.95 |
| field_revision_field_event_info | 2745 | 278528 | 819200 | 1.05 |
| registry_file | 886 | 262144 | 0 | 0.25 |
| field_revision_field_date | 3777 | 262144 | 819200 | 1.03 |
| field_data_field_date | 3766 | 262144 | 753664 | 0.97 |
| search_api_db_customsearch_field_event_type | 3357 | 245760 | 147456 | 0.38 |
| field_data_field_organisation_mandate | 324 | 245760 | 114688 | 0.34 |
| field_revision_field_image | 2353 | 245760 | 983040 | 1.17 |
| cache_l10n_update | 1 | 245760 | 16384 | 0.25 |
| field_revision_field_organisation_mandate | 209 | 245760 | 114688 | 0.34 |
| field_revision_field_why | 204 | 229376 | 114688 | 0.33 |
| field_data_field_why | 410 | 229376 | 114688 | 0.33 |
| field_data_field_image | 3244 | 229376 | 851968 | 1.03 |
| file_usage | 4661 | 229376 | 491520 | 0.69 |
| field_revision_field_mandate | 1988 | 212992 | 622592 | 0.80 |
| field_data_field_organisation_typr | 2170 | 212992 | 770048 | 0.94 |
| field_data_field_mandate | 2326 | 212992 | 622592 | 0.80 |
| field_revision_field_organisation_typr | 2365 | 212992 | 770048 | 0.94 |
| field_revision_field_areas_of_expertise | 232 | 196608 | 114688 | 0.30 |
| users | 523 | 196608 | 114688 | 0.30 |
| field_revision_field_general_measures | 2796 | 196608 | 868352 | 1.02 |
| search_api_db_customsearch_field_cited_crcs | 3569 | 196608 | 163840 | 0.34 |
| field_data_field_event_type | 3246 | 196608 | 884736 | 1.03 |
| field_revision_field_event_type | 3042 | 196608 | 884736 | 1.03 |
| field_data_field_areas_of_expertise | 292 | 196608 | 114688 | 0.30 |
| search_api_db_customsearch_field_general_measures | 2290 | 196608 | 114688 | 0.30 |
| field_data_field_general_measures | 2979 | 196608 | 786432 | 0.94 |
| field_data_field_persistent_violations | 1512 | 180224 | 475136 | 0.63 |
| search_api_db_customsearch_field_organisation_typr | 3072 | 180224 | 114688 | 0.28 |
| field_revision_field_persistent_violations | 1384 | 180224 | 475136 | 0.63 |
| field_config | 125 | 163840 | 131072 | 0.28 |
| redirect | 563 | 163840 | 196608 | 0.34 |
| block | 541 | 163840 | 131072 | 0.28 |
| field_data_field_issue_number | 1749 | 147456 | 507904 | 0.63 |
| field_data_field_is_archive_newsletter | 1743 | 147456 | 589824 | 0.70 |
| field_revision_field_issue_number | 1883 | 147456 | 507904 | 0.63 |
| field_revision_field_is_archive_newsletter | 1893 | 147456 | 606208 | 0.72 |
| field_data_field_right_hand_column | 217 | 147456 | 114688 | 0.25 |
| field_revision_field_simplenews_term | 1685 | 147456 | 606208 | 0.72 |
| field_revision_field_right_hand_column | 158 | 147456 | 114688 | 0.25 |
| field_data_field_report | 2129 | 147456 | 655360 | 0.77 |
| field_revision_field_report | 2063 | 147456 | 720896 | 0.83 |
| field_data_field_simplenews_term | 1951 | 147456 | 573440 | 0.69 |
| field_data_field_un_subtype | 1151 | 131072 | 507904 | 0.61 |
| facetapi | 155 | 131072 | 0 | 0.13 |
| field_data_field_attachment | 848 | 131072 | 393216 | 0.50 |
| field_revision_field_un_subtype | 974 | 131072 | 524288 | 0.63 |
| field_revision_field_attachment | 1052 | 131072 | 491520 | 0.59 |
| field_revision_field_alternative_report_type | 1492 | 114688 | 507904 | 0.59 |
| i18n_string | 880 | 114688 | 65536 | 0.17 |
| field_data_field_treaty | 1243 | 114688 | 475136 | 0.56 |
| field_data_field_banner | 1285 | 114688 | 425984 | 0.52 |
| taxonomy_term_data | 745 | 114688 | 180224 | 0.28 |
| field_revision_field_banner | 1411 | 114688 | 425984 | 0.52 |
| field_revision_field_treaty | 1587 | 114688 | 491520 | 0.58 |
| search_api_db_customsearch_field_report | 1806 | 114688 | 81920 | 0.19 |
| field_data_field_alternative_report_type | 1368 | 114688 | 491520 | 0.58 |
| field_data_field_aims_and_activities | 109 | 98304 | 114688 | 0.20 |
| field_revision_field_aims_and_activities | 110 | 98304 | 114688 | 0.20 |
| field_data_field_instruments | 656 | 98304 | 393216 | 0.47 |
| cache_apachesolr | 5 | 98304 | 16384 | 0.11 |
| field_data_field_scope | 925 | 98304 | 393216 | 0.47 |
| field_revision_field_instruments | 1041 | 98304 | 491520 | 0.56 |
| field_revision_field_scope | 798 | 98304 | 458752 | 0.53 |
| simplenews_newsletter | 2119 | 98304 | 0 | 0.09 |
| l10n_update_file | 225 | 98304 | 0 | 0.09 |
| cache_entity_user | 29 | 98304 | 16384 | 0.11 |
| field_revision_field_related_crc | 846 | 81920 | 376832 | 0.44 |
| field_data_field_show_on_homepage | 536 | 81920 | 376832 | 0.44 |
| field_revision_field_show_on_homepage | 643 | 81920 | 458752 | 0.52 |
| field_data_field_related_crc | 843 | 81920 | 360448 | 0.42 |
| search_api_db_customsearch_field_scope | 1453 | 81920 | 49152 | 0.13 |
| search_api_db_customsearch_field_treaty | 1561 | 81920 | 65536 | 0.14 |
| field_revision_field_year | 685 | 65536 | 98304 | 0.16 |
| field_revision_field_summary | 37 | 65536 | 114688 | 0.17 |
| role_permission | 504 | 65536 | 49152 | 0.11 |
| field_data_field_countriy | 550 | 65536 | 114688 | 0.17 |
| field_data_field_organisation_name | 471 | 65536 | 114688 | 0.17 |
| field_data_field_year | 346 | 65536 | 98304 | 0.16 |
| menu_node | 1288 | 65536 | 49152 | 0.11 |
| field_revision_field_postal_address | 447 | 65536 | 147456 | 0.20 |
| field_collection_item | 478 | 65536 | 0 | 0.06 |
| taxonomy_term_hierarchy | 1507 | 65536 | 16384 | 0.08 |
| field_data_field_map | 411 | 65536 | 98304 | 0.16 |
| field_data_field_english_translation | 484 | 65536 | 114688 | 0.17 |
| cache_path | 39 | 65536 | 16384 | 0.08 |
| field_data_field_summary | 23 | 65536 | 114688 | 0.17 |
| legal_conditions | 7 | 65536 | 0 | 0.06 |
| search_api_db_customsearch_field_instruments | 1027 | 65536 | 49152 | 0.11 |
| field_data_field_postal_address | 484 | 65536 | 114688 | 0.17 |
| block_custom | 20 | 65536 | 16384 | 0.08 |
| field_revision_field_english_translation | 469 | 65536 | 147456 | 0.20 |
| field_revision_field_countriy | 679 | 65536 | 180224 | 0.23 |
| field_revision_field_organisation_name | 340 | 65536 | 147456 | 0.20 |
| search_api_db_customsearch_field_related_crc | 838 | 65536 | 49152 | 0.11 |
| field_revision_field_map | 497 | 65536 | 98304 | 0.16 |
| field_data_field_campaign | 561 | 49152 | 114688 | 0.16 |
| field_revision_field_wiki_link | 253 | 49152 | 98304 | 0.14 |
| search_api_db_customsearch_field_author_org | 795 | 49152 | 49152 | 0.09 |
| field_collection_item_revision | 1181 | 49152 | 16384 | 0.06 |
| search_api_db_customsearch_field_author | 407 | 49152 | 16384 | 0.06 |
| history | 631 | 49152 | 16384 | 0.06 |
| field_revision_field_campaign | 561 | 49152 | 114688 | 0.16 |
| field_data_field_wiki_link | 173 | 49152 | 98304 | 0.14 |
| legal_accepted | 367 | 49152 | 16384 | 0.06 |
| cache_page | 1 | 49152 | 16384 | 0.06 |
| l10n_update_project | 107 | 49152 | 0 | 0.05 |
| search_api_server | 1 | 32768 | 32768 | 0.06 |
| search_api_index | 1 | 32768 | 65536 | 0.09 |
| sequences | 1 | 16384 | 0 | 0.02 |
| field_revision_field_guide_download | 4 | 16384 | 114688 | 0.13 |
| cache_image | 0 | 16384 | 16384 | 0.03 |
| domain_editor | 547 | 16384 | 0 | 0.02 |
| date_format_type | 5 | 16384 | 16384 | 0.03 |
| field_data_field_force_banner_title | 1 | 16384 | 114688 | 0.13 |
| field_data_field_cited_crc | 50 | 16384 | 114688 | 0.13 |
| field_group | 17 | 16384 | 32768 | 0.05 |
| image_styles | 14 | 16384 | 16384 | 0.03 |
| search_api_db_customsearch_field_related_campaign | 3 | 16384 | 16384 | 0.03 |
| backup_migrate_schedules | 2 | 16384 | 0 | 0.02 |
| rules_config | 5 | 16384 | 32768 | 0.05 |
| field_revision_field_continental_gdp | 5 | 16384 | 98304 | 0.11 |
| field_data_field_age_group | 3 | 16384 | 114688 | 0.13 |
| field_revision_field_director | 106 | 16384 | 114688 | 0.13 |
| field_data_field_publication_start_date | 34 | 16384 | 98304 | 0.11 |
| field_revision_field_area | 44 | 16384 | 114688 | 0.13 |
| field_revision_field_or | 119 | 16384 | 114688 | 0.13 |
| field_revision_field_works_on | 0 | 16384 | 114688 | 0.13 |
| domain_conf | 1 | 16384 | 0 | 0.02 |
| field_revision_field_main_contact | 16 | 16384 | 114688 | 0.13 |
| date_format_locale | 0 | 16384 | 0 | 0.02 |
| field_data_field_campaign_type | 20 | 16384 | 114688 | 0.13 |
| field_data_field_p_world_pop | 5 | 16384 | 98304 | 0.11 |
| role | 6 | 16384 | 32768 | 0.05 |
| field_data_og_membership_request | 1 | 16384 | 114688 | 0.13 |
| field_revision_field_related_campaign | 3 | 16384 | 114688 | 0.13 |
| image_effects | 14 | 16384 | 32768 | 0.05 |
| search_api_db_customsearch_field_regions | 243 | 16384 | 16384 | 0.03 |
| backup_migrate_profiles | 1 | 16384 | 0 | 0.02 |
| users_roles | 62 | 16384 | 16384 | 0.03 |
| field_revision_field_contact_information | 24 | 16384 | 114688 | 0.13 |
| semaphore | 0 | 16384 | 32768 | 0.05 |
| field_revision_field_general_related_items | 1 | 16384 | 114688 | 0.13 |
| multiblock | 2 | 16384 | 0 | 0.02 |
| field_revision_field_working_in | 0 | 16384 | 114688 | 0.13 |
| domain_alias | 2 | 16384 | 32768 | 0.05 |
| field_revision_field_link_to_search_page | 12 | 16384 | 114688 | 0.13 |
| ctools_object_cache | 0 | 16384 | 16384 | 0.03 |
| field_data_field_campaign_news | 24 | 16384 | 114688 | 0.13 |
| field_data_field_other_languages | 116 | 16384 | 114688 | 0.13 |
| field_data_field_your_name | 34 | 16384 | 114688 | 0.13 |
| field_revision_field_regions | 243 | 16384 | 114688 | 0.13 |
| i18n_translation_set | 3 | 16384 | 16384 | 0.03 |
| search_api_db_customsearch_field_quick_links | 2 | 16384 | 16384 | 0.03 |
| backup_migrate_destinations | 1 | 16384 | 0 | 0.02 |
| field_revision_field_contact_details | 42 | 16384 | 114688 | 0.13 |
| cache_filter | 6 | 16384 | 16384 | 0.03 |
| field_revision_field_subtitle | 6 | 16384 | 114688 | 0.13 |
| ctools_css_cache | 0 | 16384 | 0 | 0.02 |
| field_data_field_organiser_author_reporter_ | 22 | 16384 | 114688 | 0.13 |
| field_data_field_user_another_banner | 1 | 16384 | 114688 | 0.13 |
| field_data_field_your_email | 34 | 16384 | 114688 | 0.13 |
| field_revision_field_quick_links | 2 | 16384 | 114688 | 0.13 |
| authmap | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_force_banner_title | 1 | 16384 | 114688 | 0.13 |
| field_revision_field_staff | 105 | 16384 | 98304 | 0.11 |
| webform_submitted_data | 0 | 16384 | 32768 | 0.05 |
| cache_entity_comment | 0 | 16384 | 16384 | 0.03 |
| contact | 1 | 16384 | 32768 | 0.05 |
| field_revision_field_publication_start_date | 34 | 16384 | 98304 | 0.11 |
| field_data_field_buy_tickets | 26 | 16384 | 98304 | 0.11 |
| field_data_field_url_for_further_informatio | 11 | 16384 | 98304 | 0.11 |
| field_data_field_year_established | 41 | 16384 | 114688 | 0.13 |
| i18n_path | 15 | 16384 | 32768 | 0.05 |
| apachesolr_search_page | 2 | 16384 | 16384 | 0.03 |
| twitter_account | 1 | 16384 | 16384 | 0.03 |
| field_data_field_number_of_internet_users | 219 | 16384 | 98304 | 0.11 |
| field_revision_field_cited_crc | 50 | 16384 | 114688 | 0.13 |
| menu_position_rules | 2 | 16384 | 32768 | 0.05 |
| field_revision_field_age_group | 3 | 16384 | 114688 | 0.13 |
| webform_submissions | 0 | 16384 | 49152 | 0.06 |
| field_revision_field_legal_item | 122 | 16384 | 114688 | 0.13 |
| field_data_field_body | 0 | 16384 | 114688 | 0.13 |
| field_data_field_url_for_document | 18 | 16384 | 98304 | 0.11 |
| i18n_block_language | 53 | 16384 | 16384 | 0.03 |
| field_revision_field_campaign_type | 20 | 16384 | 114688 | 0.13 |
| field_data_field_intruments | 13 | 16384 | 114688 | 0.13 |
| field_revision_field_website_link | 121 | 16384 | 98304 | 0.11 |
| webform_roles | 2 | 16384 | 0 | 0.02 |
| cache_block | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_lauguages | 0 | 16384 | 114688 | 0.13 |
| ckeditor_settings | 2 | 16384 | 0 | 0.02 |
| honeypot_user | 0 | 16384 | 32768 | 0.05 |
| apachesolr_index_entities | 0 | 16384 | 16384 | 0.03 |
| trigger_assignments | 0 | 16384 | 0 | 0.02 |
| field_data_field_monitoring_body | 40 | 16384 | 114688 | 0.13 |
| field_revision_field_campaign_news | 24 | 16384 | 114688 | 0.13 |
| webform_last_download | 0 | 16384 | 0 | 0.02 |
| ckeditor_input_format | 2 | 16384 | 0 | 0.02 |
| field_data_field_banner_text | 40 | 16384 | 114688 | 0.13 |
| rdf_mapping | 2 | 16384 | 0 | 0.02 |
| field_data_field_works_on | 0 | 16384 | 114688 | 0.13 |
| field_revision_field_special_campaign_sub_pages | 72 | 16384 | 114688 | 0.13 |
| menu_custom | 23 | 16384 | 0 | 0.02 |
| webform_emails | 0 | 16384 | 0 | 0.02 |
| cache_admin_menu | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_violence_settings | 272 | 16384 | 114688 | 0.13 |
| captcha_sessions | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_population_under_18 | 220 | 16384 | 98304 | 0.11 |
| search_api_db_customsearch_field_monitoring_body | 222 | 16384 | 16384 | 0.03 |
| field_data_field_banner_image | 74 | 16384 | 114688 | 0.13 |
| quicktabs | 0 | 16384 | 0 | 0.02 |
| field_data_field_working_in | 0 | 16384 | 114688 | 0.13 |
| apachesolr_index_bundles | 48 | 16384 | 0 | 0.02 |
| taxonomy_vocabulary | 28 | 16384 | 32768 | 0.05 |
| field_data_field_event_location | 10 | 16384 | 114688 | 0.13 |
| field_data_field_membership_status | 42 | 16384 | 114688 | 0.13 |
| search_api_db_customsearch_field_age_group | 9 | 16384 | 16384 | 0.03 |
| mailchimp_signup | 1 | 16384 | 0 | 0.02 |
| field_revision_field_intruments | 13 | 16384 | 114688 | 0.13 |
| webform_component | 10 | 16384 | 0 | 0.02 |
| field_data_field_author_org | 231 | 16384 | 114688 | 0.13 |
| field_data_field_resource_type | 34 | 16384 | 114688 | 0.13 |
| captcha_points | 32 | 16384 | 0 | 0.02 |
| field_revision_field_population | 224 | 16384 | 98304 | 0.11 |
| filter_format | 5 | 16384 | 32768 | 0.05 |
| field_data_field_or | 119 | 16384 | 114688 | 0.13 |
| queue | 1 | 16384 | 32768 | 0.05 |
| flood | 65 | 16384 | 32768 | 0.05 |
| apachesolr_environment_variable | 16 | 16384 | 0 | 0.02 |
| field_revision_field_buy_tickets | 26 | 16384 | 98304 | 0.11 |
| webform | 1 | 16384 | 0 | 0.02 |
| blocked_ips | 0 | 16384 | 16384 | 0.03 |
| cache_rules | 0 | 16384 | 16384 | 0.03 |
| field_data_field_author | 176 | 16384 | 114688 | 0.13 |
| field_data_field_resource_title | 33 | 16384 | 114688 | 0.13 |
| field_revision_field_user_another_banner | 1 | 16384 | 114688 | 0.13 |
| cache_views_data | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_banner_image | 74 | 16384 | 114688 | 0.13 |
| filter | 43 | 16384 | 16384 | 0.03 |
| search_api_db_customsearch_field_legal_item | 122 | 16384 | 16384 | 0.03 |
| entityform_type | 1 | 16384 | 16384 | 0.03 |
| oauth_common_token | 0 | 16384 | 16384 | 0.03 |
| apachesolr_environment | 2 | 16384 | 0 | 0.02 |
| field_data_field_event | 17 | 16384 | 114688 | 0.13 |
| field_revision_field_body | 0 | 16384 | 114688 | 0.13 |
| field_revision_field_event_location | 10 | 16384 | 114688 | 0.13 |
| field_data_field_resource_subtype | 34 | 16384 | 114688 | 0.13 |
| field_revision_field_url_for_further_informatio | 11 | 16384 | 98304 | 0.11 |
| field_revision_field_p_world_pop | 5 | 16384 | 98304 | 0.11 |
| field_data_field_operation_level | 124 | 16384 | 114688 | 0.13 |
| entityform | 34 | 16384 | 32768 | 0.05 |
| oauth_common_provider_token | 0 | 16384 | 16384 | 0.03 |
| field_data_field_website_link | 121 | 16384 | 98304 | 0.11 |
| field_revision_field_operation_level | 124 | 16384 | 114688 | 0.13 |
| actions | 17 | 16384 | 0 | 0.02 |
| taxonomy_menu | 161 | 16384 | 16384 | 0.03 |
| field_data_field_main_contact | 16 | 16384 | 114688 | 0.13 |
| field_revision_field_banner_text | 40 | 16384 | 114688 | 0.13 |
| field_data_field_human_development_index | 214 | 16384 | 98304 | 0.11 |
| block_role | 10 | 16384 | 16384 | 0.03 |
| field_revision_field_url_for_document | 18 | 16384 | 98304 | 0.11 |
| field_revision_field_author_org | 231 | 16384 | 114688 | 0.13 |
| field_revision_field_other_languages | 116 | 16384 | 114688 | 0.13 |
| search_api_db_customsearch_field_intruments | 13 | 16384 | 16384 | 0.03 |
| entity_translation_revision | 0 | 16384 | 16384 | 0.03 |
| oauth_common_provider_consumer | 0 | 16384 | 32768 | 0.05 |
| field_revision_field_number_of_internet_users | 219 | 16384 | 98304 | 0.11 |
| search_api_db_customsearch_field_violence_settings | 270 | 16384 | 16384 | 0.03 |
| field_data_field_end_date | 9 | 16384 | 98304 | 0.11 |
| field_data_field_link_to_search_page | 12 | 16384 | 114688 | 0.13 |
| field_data_field_highlights | 0 | 16384 | 114688 | 0.13 |
| field_data_field_countries_work | 156 | 16384 | 114688 | 0.13 |
| search_api_db_customsearch_field_subtitle | 15 | 16384 | 16384 | 0.03 |
| simplenews_mail_spool | 0 | 16384 | 49152 | 0.06 |
| views_view | 63 | 16384 | 16384 | 0.03 |
| block_node_type | 67 | 16384 | 16384 | 0.03 |
| field_data_field_subtitle | 6 | 16384 | 114688 | 0.13 |
| entity_translation | 0 | 16384 | 0 | 0.02 |
| oauth_common_nonce | 0 | 16384 | 16384 | 0.03 |
| field_data_field_violence_settings | 272 | 16384 | 114688 | 0.13 |
| field_data_field_email_address | 41 | 16384 | 114688 | 0.13 |
| field_data_field_happy_planet_ranking | 200 | 16384 | 98304 | 0.11 |
| field_data_field_continental_gdp | 5 | 16384 | 98304 | 0.11 |
| field_revision_field_resource_type | 34 | 16384 | 114688 | 0.13 |
| languages | 5 | 16384 | 16384 | 0.03 |
| search_api_db_customsearch_field_special_campaign_sub_pages | 63 | 16384 | 16384 | 0.03 |
| simplenews_category | 11 | 16384 | 0 | 0.02 |
| field_revision_field_event | 17 | 16384 | 114688 | 0.13 |
| field_revision_field_author | 176 | 16384 | 114688 | 0.13 |
| field_revision_field_organiser_author_reporter_ | 22 | 16384 | 114688 | 0.13 |
| field_revision_og_membership_request | 1 | 16384 | 114688 | 0.13 |
| domain | 2 | 16384 | 49152 | 0.06 |
| field_data_field_legal_item | 122 | 16384 | 114688 | 0.13 |
| field_data_field_guide_download | 4 | 16384 | 114688 | 0.13 |
| field_data_field_contact_information | 24 | 16384 | 114688 | 0.13 |
| field_revision_field_resource_title | 33 | 16384 | 114688 | 0.13 |
| views_data_export | 0 | 16384 | 0 | 0.02 |
| rules_trigger | 0 | 16384 | 0 | 0.02 |
| shortcut_set_users | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_human_development_index | 214 | 16384 | 98304 | 0.11 |
| views_data_export_object_cache | 0 | 16384 | 32768 | 0.05 |
| cache_mailchimp | 0 | 16384 | 16384 | 0.03 |
| field_data_field_related_campaign | 3 | 16384 | 114688 | 0.13 |
| field_revision_field_your_name | 34 | 16384 | 114688 | 0.13 |
| search_api_db_customsearch_field_highlights | 0 | 16384 | 16384 | 0.03 |
| field_data_field_staff | 105 | 16384 | 98304 | 0.11 |
| entity_rule_setting | 1 | 16384 | 16384 | 0.03 |
| oauth_common_context | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_monitoring_body | 40 | 16384 | 114688 | 0.13 |
| field_data_field_general_related_items | 1 | 16384 | 114688 | 0.13 |
| field_data_field_contact_details | 42 | 16384 | 114688 | 0.13 |
| field_data_field_population_under_18 | 220 | 16384 | 98304 | 0.11 |
| field_revision_field_resource_subtype | 34 | 16384 | 114688 | 0.13 |
| views_content_cache | 11 | 16384 | 147456 | 0.16 |
| rules_tags | 3 | 16384 | 0 | 0.02 |
| shortcut_set | 1 | 16384 | 0 | 0.02 |
| field_revision_field_highlights | 0 | 16384 | 114688 | 0.13 |
| cache_libraries | 0 | 16384 | 16384 | 0.03 |
| field_data_field_area | 44 | 16384 | 114688 | 0.13 |
| field_revision_field_end_date | 9 | 16384 | 98304 | 0.11 |
| field_data_field_regions | 243 | 16384 | 114688 | 0.13 |
| node_type | 24 | 16384 | 0 | 0.02 |
| field_revision_field_your_email | 34 | 16384 | 114688 | 0.13 |
| search_api_db_customsearch_field_general_related_items | 1 | 16384 | 16384 | 0.03 |
| field_data_field_special_campaign_sub_pages | 72 | 16384 | 114688 | 0.13 |
| domain_theme | 2 | 16384 | 0 | 0.02 |
| oauth_common_consumer | 0 | 16384 | 16384 | 0.03 |
| field_revision_field_membership_status | 42 | 16384 | 114688 | 0.13 |
| disqus | 2 | 16384 | 32768 | 0.05 |
| field_data_field_director | 106 | 16384 | 114688 | 0.13 |
| field_data_field_lauguages | 0 | 16384 | 114688 | 0.13 |
| imagefield_focus_file | 0 | 16384 | 0 | 0.02 |
| batch | 0 | 16384 | 16384 | 0.03 |
| variable_store | 4 | 16384 | 16384 | 0.03 |
| rules_dependencies | 5 | 16384 | 16384 | 0.03 |
| field_revision_field_countries_work | 156 | 16384 | 114688 | 0.13 |
| sessions | 10 | 16384 | 49152 | 0.06 |
| field_revision_field_happy_planet_ranking | 200 | 16384 | 98304 | 0.11 |
| field_revision_field_email_address | 41 | 16384 | 114688 | 0.13 |
| field_data_field_quick_links | 2 | 16384 | 114688 | 0.13 |
| field_revision_field_year_established | 41 | 16384 | 114688 | 0.13 |
| cache_entity_taxonomy_vocabulary | 28 | 16384 | 16384 | 0.03 |
| domain_export | 2 | 16384 | 16384 | 0.03 |
| date_formats | 38 | 16384 | 16384 | 0.03 |
| field_data_field_population | 224 | 16384 | 98304 | 0.11 |
+-------------------------------------------------------------+------------+-------------+--------------+------------+
459 rows in set (6.31 sec)
Is there any potential for some of the biggest tables to be reduced in size?
comment:7 Changed 17 months ago by russell
I'm about to get rid of a chunk of data from the dev. DB. I've disabled a bunch of modules on dev. which means I should be OK now truncating the biggest 3 tables on dev. the biggest 2 are InnoDB.
comment:8 Changed 17 months ago by russell
That's done. Generally looking good, I think ideally we could do with finding some more economies somewhere.
Note: See
TracTickets for help on using
tickets.
The results of mysqltuner:
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5.5.54-0+deb8u1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 1G (Tables: 13) [--] Data in MEMORY tables: 0B (Tables: 1) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in InnoDB tables: 8G (Tables: 1569) [!!] Total fragmented tables: 1572 -------- Security Recommendations ------------------------------------------- [!!] User 'piwik@localhost@%' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 95d 14h 6m 3s (1B q [152.380 qps], 15M conn, TX: 2729B, RX: 311B) [--] Reads / Writes: 94% / 6% [--] Total buffers: 7.5G global + 2.7M per thread (151 max threads) [!!] Maximum possible memory usage: 7.9G (101% of installed RAM) [OK] Slow queries: 0% (31K/1B) [!!] Highest connection usage: 100% (152/151) [OK] Key buffer size / total MyISAM indexes: 1.0G/670.9M [OK] Key buffer hit rate: 97.2% (1B cached / 48M reads) [OK] Query cache efficiency: 89.7% (1B cached / 1B selects) [!!] Query cache prunes per day: 686375 [OK] Sorts requiring temporary tables: 0% (214K temp sorts / 35M sorts) [!!] Joins performed without indexes: 140204 [OK] Temporary tables created on disk: 13% (3M on disk / 23M total) [OK] Thread cache hit rate: 99% (10K created / 15M connections) [!!] Table cache hit rate: 0% (64 open / 13M opened) [OK] Open file limit used: 0% (1/1K) [OK] Table locks acquired immediately: 99% (178M immediate / 178M locks) [!!] InnoDB buffer pool / data size: 6.0G/8.8G [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 Reduce or eliminate persistent connections to reduce connection usage Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** max_connections (> 151) wait_timeout (< 28800) interactive_timeout (< 28800) query_cache_size (> 512M) [see warning above] join_buffer_size (> 128.0K, or always use indexes with joins) table_open_cache (> 64) innodb_buffer_pool_size (>= 8G)