0

We have a local Optiplex PC setup to run our shared MySQL MariaDB database with no way to access it from the outside world. It doesn't have any critical data that needs to be protected.

Basically, no security, encryption or administration needed. What is performance-wise the fastest possible configuration for our setup?

Here's our current config:

[mysqld]
max_connections=500
join_buffer_size = 2M
tmp_table_size = 24M
max_heap_table_size = 24M
query_cache_size = 256M
key_buffer=256M
key_buffer_size = 1332M
thread_cache_size = 4
table_cache = 500
table_open_cache = 96
innodb_buffer_pool_size = 27M
slow_query_log
local-infile=0
skip-name-resolve

I'm running Debian 10 on an Optiplex 780, core2duo E8500 with 4GB RAM. My specs can be found here! https://i.dell.com/sites/doccontent/shared-content/data-sheets/en/Documents/optiplex_780_tech_spec_sheet.pdf

Ben
  • 105
  • 4
  • Not clear to my mind : fastest in terms of performances ? or fastest in term of deployment ? – Chaoxiang N Nov 14 '19 at 14:11
  • In terms of performance! It does not have to be re-deployed – Ben Nov 14 '19 at 14:13
  • @Ben Agreed, Additional information request. Post on pastebin.com and share the links. RAM size, # cores of your MySQL Host server From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 14 '19 at 20:28
  • @WilsonHauck I'm running Debian 10 on an Optiplex 780, core2duo E8500 with 4GB RAM. My specs can be found here! https://i.dell.com/sites/doccontent/shared-content/data-sheets/en/Documents/optiplex_780_tech_spec_sheet.pdf – Ben Nov 15 '19 at 08:44
  • 4GB of RAM? If you have more than 1.5GB of data, then adding RAM would be the first thing to do to improve performance. – Rick James Nov 15 '19 at 17:58

3 Answers3

2

It depends on how much RAM you have.

query_cache_size is too big. Don't allocate more than 50M. In most servers it is better to turn it off.

key_buffer_size = 1332M
innodb_buffer_pool_size = 27M

Those imply you are using the slower ENGINE=MyISAM.

These are deprecated:

key_buffer=256M
table_cache = 500

Can't provide "fastest possible" without some clues of what your application is like.

Usually a "slow" server is slow because of lack of indexes and/or poorly formulated queries, not configuration.

Run for a month, then provide the data Wilson indicated. Also, keep the slowlog turned on so we can find the "slow" queries.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • 1
    Fair enough, I was hoping there was a general "max performance" configuration that would work for most systems. I've added my specifications to the original post. – Ben Nov 15 '19 at 08:46
  • @Ben Thanks for your specs link. There are 4 Chassis options with the 780, MT, DT, SFF, USSF. Which one did you purchase? There are Hard Drive Options as well. Do you have HDD or SSD available? When you post the complete report from MySQLTuner, we will know how many MyISAM and Innodb tables and the summary data size of each table type - and much more helpful information. Congratulations on applying some changes to improve your DESKTOP equipment performance as a SERVER. Looking forward to your data being posted in a few weeks for analysis. – Wilson Hauck Nov 15 '19 at 14:26
1

Most of the settings you have in there are either pointless or downright counter-productive.

Remove them all. Make sure you are only using InnoDB. Adjust innodb_buffer_pool size to 50-80% of your RAM (but no bigger than your actual data). Add innodb_file_per_table=1. All other settings you should leave alone unless you know exactly what you are doing and are changing a setting for a very specific reason.

Ignore everything MySQL tuner script tells you.

If you don't care about data integrity in case of a crash: innodb_doublewrite=0 innodb_flush_logs_on_trx_commit=0

If you don't care about data integrity in face of a failing hardware: innodb_checksum=none

Gordan Bobić
  • 971
  • 4
  • 11
1

There are a bunch of scripts that connects to your MySQL server, analyzes some variables and performance statistics of your running system and proposes configurations changes. The most famous are https://github.com/major/MySQLTuner-perl and https://github.com/BMDan/tuning-primer.sh

JucaPirama
  • 265
  • 1
  • 7