Zielony
Posts: 9
Joined: Thu Jan 03, 2013 4:20 pm

Migrating from MySQL to MariaDB

Tue Mar 26, 2013 10:34 am

As we can see, Arch Linux is migrating to MariaDB. The mysql package is going to be removed from official repository in a month. And how will it be about Arch Linux ARM? Do we have to migrate to MariaDB or mysql still will be supported? I'm asking about official repository, not AUR.

Maybe someone of you has migrated already and could share opinion, especially if it's going about performance on Raspberry?

pepedog
Posts: 1043
Joined: Fri Oct 07, 2011 9:55 am

Re: Migrating from MySQL to MariaDB

Tue Mar 26, 2013 2:50 pm

Thanks.
Same person wrote both, the x86 boys drive this change
Someone has commented on performance
http://archlinuxarm.org/forum/viewtopic ... =10#p28292

User avatar
KnightMB
Posts: 40
Joined: Sun Dec 23, 2012 4:31 pm
Contact: Website

Re: Migrating from MySQL to MariaDB

Wed Mar 27, 2013 6:47 am

I can understand having a DB preference to choose between the two but to just ditch mysql completely seems a little extreme. I have quite a few Pis that are mysql database only and they work great and fast.

I don't mind using another DB when time allows for testing and what not to work out any bugs, but to drop it "just because" does not seem to be a wise path to take. :?
Raspberry Pi + Timekoin = Next Generation Digital Currency

Lumpynose
Posts: 43
Joined: Thu Feb 28, 2013 6:07 am

Re: Migrating from MySQL to MariaDB

Wed Mar 27, 2013 7:17 am

It sounds like MariaDb is better than MySQL, but given my experience with MySQL that's not saying much. Sorry to be a snot but I won't shed any tears over people pulling the plug on MySQL. Before I retired my preferred database was PostgreSQL (no idea if it works on the raspi).

Zielony
Posts: 9
Joined: Thu Jan 03, 2013 4:20 pm

Re: Migrating from MySQL to MariaDB

Wed Mar 27, 2013 10:37 am

KnightMB wrote:I can understand having a DB preference to choose between the two but to just ditch mysql completely seems a little extreme. I have quite a few Pis that are mysql database only and they work great and fast.

I don't mind using another DB when time allows for testing and what not to work out any bugs, but to drop it "just because" does not seem to be a wise path to take. :?
The problem about MySQL is that it becomes more and more closed due to the fact it is bought by Oracle. It's the main reason that most of popular distros have already migrated to MariaDB. And if you dig the Internet a bit, you will see there are benchmarks, which show advantage of MariaDB in performance, although not always. I wouldn't tell that MySQL "works great and fast", because InnoDB table engine is quite slow on Raspberry. But the main reason I want to migrate is the matter of package support. I'm not going to build package from AUR by myself, when it's dropped out from the official repository. Moreover, migrating seems to be unproblematic.

User avatar
KnightMB
Posts: 40
Joined: Sun Dec 23, 2012 4:31 pm
Contact: Website

Re: Migrating from MySQL to MariaDB

Wed Mar 27, 2013 3:23 pm

I am all for open and faster, but it's a shame to pull it completely. At least they should freeze it at X.x version with some notes (move to MariaDB in the future) for example. :D

I've been using MySql since last century and performance has never been an issue, but I guess all the years of tweaks seem second nature.

But, as mentioned earlier, how extreme has someone pushed it on the Raspberry Pi (rev B)? I have several running mysql with 32GB SD cards using multi-million record databases for things like radius authentication, accounting software, website portals, support software, and digital currency all from one single device. MySQL is using close to 200 MB of RAM usage all the time, but the CPU load average 0.18, 0.23, 0.22.

So if the Maria DB is better performance that's great, just hope it is a drop in replacement like what was discussed over at the other forum or me thinks I'll be preserving that last pacman package for it later just in case. :mrgreen:

I am glad this topic was posted though, it would suck to be surprised when the do a device setup and find the package was just gone or missing during setup.
Raspberry Pi + Timekoin = Next Generation Digital Currency

Zielony
Posts: 9
Joined: Thu Jan 03, 2013 4:20 pm

Re: Migrating from MySQL to MariaDB

Wed Mar 27, 2013 4:40 pm

KnightMB wrote:I am all for open and faster, but it's a shame to pull it completely. At least they should freeze it at X.x version with some notes (move to MariaDB in the future) for example. :D
So Arch Linux ARM still has a chance to avoid shame, if they decide to support a package. ;-)
KnightMB wrote:But, as mentioned earlier, how extreme has someone pushed it on the Raspberry Pi (rev B)? I have several running mysql with 32GB SD cards using multi-million record databases for things like radius authentication, accounting software, website portals, support software, and digital currency all from one single device. MySQL is using close to 200 MB of RAM usage all the time, but the CPU load average 0.18, 0.23, 0.22.
I can't image this. :D Off topic: could you share the my.cnf, please? I tried to improve performance, also by modyfing config, but the best resolution was put /var/lib/mysql into tmpfs (RAM). I have few small databases, so I can afford this. Writing to card every commit was ineffective...
KnightMB wrote:So if the Maria DB is better performance that's great, just hope it is a drop in replacement like what was discussed over at the other forum or me thinks I'll be preserving that last pacman package for it later just in case. :mrgreen:
Yeah, it is a drop replacement. Those are the first words of description of this. :P

sdjf
Posts: 1397
Joined: Fri Mar 16, 2012 5:20 am
Location: California
Contact: Website

Re: Migrating from MySQL to MariaDB

Thu Mar 28, 2013 3:34 am

Hey, you guys better remember to save any unique dependency packages as well, if nothing else uses them, they likely will also get dropped.
FORUM TIP: To view someone's posting history, sign in, click on their user name, then on "Search User's Posts." || Running ArchLinuxArm on Model 2B and 512MB Model B

User avatar
KnightMB
Posts: 40
Joined: Sun Dec 23, 2012 4:31 pm
Contact: Website

Re: Migrating from MySQL to MariaDB

Thu Mar 28, 2013 9:57 am

Zielony wrote:
KnightMB wrote:I am all for open and faster, but it's a shame to pull it completely. At least they should freeze it at X.x version with some notes (move to MariaDB in the future) for example. :D
So Arch Linux ARM still has a chance to avoid shame, if they decide to support a package. ;-)
If mysql was some package that had code not updated since 1999 and no one even used it, I could see the reasoning for pulling it. But the concern seems to be Oracle (which I understand) so Maria DB is still a safe place to go if Oracle were to go insane. I won't argue with any concerns about this, but the move seems kind of out of place if mysql is still used a lot.
KnightMB wrote:But, as mentioned earlier, how extreme has someone pushed it on the Raspberry Pi (rev B)? I have several running mysql with 32GB SD cards using multi-million record databases for things like radius authentication, accounting software, website portals, support software, and digital currency all from one single device. MySQL is using close to 200 MB of RAM usage all the time, but the CPU load average 0.18, 0.23, 0.22.
I can't image this. :D Off topic: could you share the my.cnf, please? I tried to improve performance, also by modyfing config, but the best resolution was put /var/lib/mysql into tmpfs (RAM). I have few small databases, so I can afford this. Writing to card every commit was ineffective...
This is modified from the 512MB template config

Code: Select all

# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len = 3

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
KnightMB wrote:So if the Maria DB is better performance that's great, just hope it is a drop in replacement like what was discussed over at the other forum or me thinks I'll be preserving that last pacman package for it later just in case. :mrgreen:
Yeah, it is a drop replacement. Those are the first words of description of this. :P
Not the first time I've read those words from other software authors. :)
I hope it is as I've only read a few gotchas about the change so far in other topics in the arch forums. I look forward to even faster DB, just didn't plan on switching to quickly. :mrgreen:

I've attached screen-shots from the busiest pi DB server, sensitive stuff blurred out of course. This is a Pi Rev B with a 16GB SD card (Class 10), currently is running 3 accounting databases, a forum, radius authentication for a company with 1,000+ users, several website portals (like Joomla), support software (vTiger) database, and a digital currency database (Timekoin) all at the same time. All databases together are using 3GB of space, well over a million records, etc. I've used this as a replacement for the old DB server which had much better specs than the Pi did of course, I keep the old one on standby just in case something happens to the Pi, but so far so good. I'll probably just make another Pi DB server to serve as a backup and probably retire the old DB machine to something else. Mainly the move was a "let's see if it can do it" experiment, has not turned into a "hey, these things are cheap enough and fast enough to actually replace some production machines where massive CPU speed is not needed". :D
Attachments
screen_03.png
device space left on 16GB SD card
screen_03.png (21.16 KiB) Viewed 10639 times
screen_02.png
mysql top
screen_02.png (23.72 KiB) Viewed 10639 times
screen_01.jpg
Mass of live Databases
screen_01.jpg (61.12 KiB) Viewed 10639 times
Raspberry Pi + Timekoin = Next Generation Digital Currency

Zielony
Posts: 9
Joined: Thu Jan 03, 2013 4:20 pm

Re: Migrating from MySQL to MariaDB

Thu Mar 28, 2013 5:00 pm

@KnightMB, thanks for sharing. ;) I'm impressed with possibilities of Raspberry Pi after seeing your configuration, I mean services you host.

In meantime I migrated to MariaDB and everything works fine. ;-) If you put back your original my.cnf in its place, it should just run without any errors. I also run 2 popular tuning scripts for MySQL: mysqltuner.pl and tuning-primer.sh. I made small changes and now my my.cnf (without comments) running with MariaDB is:

Code: Select all

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query-cache-type = 1
query-cache-size = 20M
thread_cache = 16
join_buffer_size = 2M
max_connections = 50
aria-pagecache-buffer-size=20M
key-cache-segments=4

bind-address=127.0.0.1

log-bin=mysql-bin

binlog_format=mixed
expire_logs_days=15

server-id       = 1

innodb_data_file_path = ibdata1:10M:autoextend:max:64M
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 1M
innodb_log_file_size = 4M
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 1
sync_binlog=0
innodb_flush_method = O_DSYNC

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 16M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
If it's going about performance, it looks similiar, maybe a bit faster.

Return to “Arch”