• 4
name Punditsdkoslkdosdkoskdo

Most efficient way to setup MySQL in Ubuntu Server 10 LTS?

This question was originally on stackoverflow.com, but I was told I may get a better answer on this site...

Very soon I will setting up a web server for a client who has a very busy site. He has a forum (run on Simple Machines Forum SMF written in PHP) that cannot really be changed. I want to move this to a separate virtual private server on it's own, as it's very busy at around 40,000 hits a day with 400+ posts a day. With the version of SMF it has (the best, most stable production version 1.1) it needs MySQL to run, so I can't change it to PostgreSQL (which I would maybe prefer)

When installing MySQL Server on Ubuntu Server 10 LTS, I'm curious to know if there is anything I can do to ensure it's the most efficient it can be. I'm wanting to run as fast as possible to keep resources down and to ensure we can stay under our resource limit for the server. With a virtual private server, I have the option of switching up the resources in a flash, but I'm wanting to ensure MySQL Server is setup as efficiently as possible as I'm not exactly an expert at that type of thing.

I was doing some research and found an article which suggests doing this to ensure it's efficient:

# open mysql conf and set these settings:
#    key_buffer = 16k
#    max_allowed_packet = 1M
#    thread_stack = 64K
nano /etc/mysql/my.cnf
# restart mysql
/etc/init.d/mysql restart

Can anyone provide me with some tips, hints, links, knowledge on this subject?

I've tried many approaches until I was successful.

The approach that worked

The only approach that worked was downloading the deb-bundble.tar from MySQL website and installing the packages one by one. I followed these steps:

  • Go to MySQL 5.6 download page: https://dev.mysql.com/downloads/mysql/5.6.html#downloads
  • In the OS options, select "Ubuntu 14.04 64-bit"
  • Download the deb-bundle, a file named: mysql-server_5.6.40-1ubuntu14.04_amd64.deb-bundle.tar, usually, it's the first option
  • Untar the bundle somewhere using: tar -xf mysql-server_5.6.40-1ubuntu14.04_amd64.deb-bundle.tar
    • The tar contains multiple deb packages, including mysql-servermysql-community-server, ... etc
    • Then I tried to install these using: sudo dpkg -i <package-name>one-by-one, but I had follow their order according to error messages that fired, e.g.: I first tried: sudo dpkg -i mysql-server_5.6.40-1ubuntu14.04_amd64.deb, it failed because it requires mysql-community-server_5.6.40-1ubuntu14.04_amd64.deb to be installed first and so on
    • Some of the package installed successfully, some produced errors, after I have tried sudo dpkg -i mysql-server_5.6.40-1ubuntu14.04_amd64.deb for the last time which gave some errors, I desperately tried: sudo apt-get install -f which grabbed the missing dependencies and voila, MySQL Server 5.6 is successfully installed, up and running

Approaches that didn't work

Using Trusty Universal Repo

It's worth mentioning that I first tried the same approach that used to work in Ubuntu 16.04, that is, to add the 'trusty universe' repo and try installing mysql-server-5.6, i.e. I followed this:

sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe'
sudo apt-get update
apt-get install mysql-server-5.6

This approach did NOT work. It failed because there's a dependency initscripts that cannot be installed. It seems that it have been replaced after Ubuntu 16.04. Installing it's replacements: util-util-linux sysvinit-utils didn't help either.

Using MySQL apt config package

I also tried using MySQL config package (mysql-apt-config_0.7.3-1_all.deb) I downloaded from this repo: https://repo.mysql.com/ and installed via: sudo dpkg -i mysql-apt-config_0.7.3-1_all.deb. It didn't recognize Ubuntu 18.04 (bionic) of course, so, I acted as if it's xenial and configured MySQL Server version to 5.6, then I tried to check which MySQL version is now available for installation using: apt-cache policy mysql-server but that also didn't work, I still have later versions than 5.6.

Final note

Although the last two approaches didn't work, they may have contributed to the success of the first approach by making some packages or configuration available, I honestly don't know.

When I became hopeless that any approach would work, I considered compiling and installing it from source, I started that endeavor (I installed the build essentials and created the user group and the user) and was about to grab the source code when I stumbled upon the deb-bundle that turned out to be fruitful and was the successful approach I described above.

  • 0
Reply Report

First of all, 40 000 hits a day and 400+ posts a day doesn't sound like something what would make MySQL sweat. 40 000 hits a day is about 2 hits a second if spread among the day, and 400+ posts means that there's a new post after every three minutes.

And that is not much. Really.

You didn't mention what storage engine is in use. With MyISAM tables you need to tune especially the key_buffer and table_cache values. With InnoDB tables innodb_buffer_pool_size is the most important one.

The key_buffer = 16k in your example sounds strange. A 16 kilobytes key_buffer? No way, make it at least 16M (megabytes), or if your server has a decent amount of memory, then it can be much more. Please be aware though that many MySQL variables are connectionspecific and not global values, so if your server load means thousands of simultaneous MySQL connections, then you'll need to tune the values down, but if you typically have only handful of connections but large datasets, then you need to tune values up.

  • 0
Reply Report

First of all; Janne is correct: 40k reads and 400 writes a day is zero load. You should have absolutely no performance drawbacks with nearly any kind of setup.

I'm running a similar setup on a VPS with SMF. I've found that it benefits alot from caching mechanisms, as it utilizes APC.

My setup (that I recommend to you):

  • Nginx
  • MySQL (with InnoDB-convertion of most of SMF's tables)
  • PHP-FPM (extremely scalable, can use very little resources if you tell it to start with few spawns)
  • APC cache
  • 4
Reply Report