I have a vps with 8 core cpu 3,5 ghz 16gb ram ubuntu 17.1 ispconfig 3.1 (standalone multisites) with about 40 medium wordpress sites (also someone with woocommerce). I install ispconfig with apache 7.1 fpm and mariadb 10.1.25 (howtoforge guide). I want to tune mysql for better performance...here the mysqltuner results: -------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL started within last 24 hours - recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 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 Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: Beware that open_files_limit (16364) variable should be greater than table_open_cache (2000) Performance should be activated for better diagnostics Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_size (> 16M) join_buffer_size (> 256.0K, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_open_cache (> 2000) performance_schema = ON enable PFS innodb_buffer_pool_size (>= 911M) if possible. innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible. innodb_buffer_pool_instances (=1) ------------------------------------------------------------------------------------------------------------------------------------------------ Is a default config..Could someone give me an idea to boost mysql? --- --- --- --- here the result of mysql engine MariaDB [(none)]> SHOW ENGINES\G *************************** 1. row *************************** Engine: MRG_MyISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: SEQUENCE Support: YES Comment: Generated tables filled with sequential values Transactions: YES XA: NO Savepoints: YES *************************** 6. row *************************** Engine: Aria Support: YES Comment: Crash-safe tables with MyISAM heritage Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: DEFAULT Comment: Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables Transactions: YES XA: YES Savepoints: YES
I'm sure mysqltuner gives better advice than I could. Have you already done the changes mysqltuner suggests?
Yes but some edit give me error on mysql service. I change only innodb_buffer_pool_size to 70% of ram..for the rest i would you give me some suggestions. here the others that I don't know how to change: query_cache_size (=0) query_cache_type (=0) query_cache_size (> 16M) join_buffer_size (> 256.0K, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_open_cache (> 2000) performance_schema = ON enable PFS innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible. innodb_buffer_pool_instances (=1
