Month: May 2011

MariaDB vs MySQL short benchmark

silviu4 Comments

Considering switching from MySQL to MariaDB? Me too. I wanted to see if I can look towards any performance improvements (or drops). I don’t have high level needs, I’m sure more comprehensive tests exist. This is simple run-all-tests.pl from sql-bench suite. The test machine was a CentOS VM with 2Gb RAM and 2 CPU cores assigned.

Mysql:

[cce_bash]
Benchmark DBD suite: 2.15
Date of test: 2011-05-25 2:22:27
Running tests on: Linux 2.6.18-238.el5 x86_64
Arguments:
Comments:
Limits from:
Server version: MySQL 5.0.77/
Optimization: None
Hardware:

alter-table: Total time: 7 wallclock secs ( 0.05 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.07 CPU)
ATIS: Total time: 12 wallclock secs ( 3.66 usr 0.51 sys + 0.00 cusr 0.00 csys = 4.17 CPU)
big-tables: Total time: 9 wallclock secs ( 1.88 usr 0.48 sys + 0.00 cusr 0.00 csys = 2.36 CPU)
connect: Total time: 107 wallclock secs (25.93 usr 15.66 sys + 0.00 cusr 0.00 csys = 41.59 CPU)
create: Failed (output/create-mysql-Linux_2.6.18_238.el5_x86_64)
insert: Total time: 791 wallclock secs (257.43 usr 67.52 sys + 0.00 cusr 0.00 csys = 324.95 CPU)
select: Total time: 181 wallclock secs (21.49 usr 4.51 sys + 0.00 cusr 0.00 csys = 26.00 CPU)
transactions: Test skipped because the database doesn’t support transactions
wisconsin: Total time: 8 wallclock secs ( 1.56 usr 0.50 sys + 0.00 cusr 0.00 csys = 2.06 CPU)

Of 9 tests, 1 tests didn’t work

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 2.00 0.01 0.00 0.01 100
alter_table_drop 3.00 0.02 0.01 0.03 91
connect 9.00 3.34 2.35 5.69 10000
connect+select_1_row 11.00 3.45 2.38 5.83 10000
connect+select_simple 11.00 4.60 2.54 7.14 10000
count 3.00 0.03 0.01 0.04 100
count_distinct 5.00 0.08 0.06 0.14 1000
count_distinct_2 8.00 0.10 0.03 0.13 1000
count_distinct_big 10.00 2.55 0.04 2.59 120
count_distinct_group 7.00 0.50 0.06 0.56 1000
count_distinct_group_on_key 6.00 0.12 0.02 0.14 1000
count_distinct_group_on_key_parts 6.00 0.41 0.06 0.47 1000
count_distinct_key_prefix 3.00 0.15 0.05 0.20 1000
count_group_on_key_parts 6.00 0.37 0.05 0.42 1000
count_on_key 48.00 4.76 0.92 5.68 50100
create_index 1.00 0.00 0.00 0.00 8
create_table 0.00 0.01 0.01 0.02 31
delete_all_many_keys 13.00 0.01 0.00 0.01 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 13.00 0.01 0.00 0.01 128
delete_key 2.00 0.16 0.24 0.40 10000
delete_range 2.00 0.01 0.00 0.01 12
drop_index 0.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
insert 60.00 6.77 4.48 11.25 350768
insert_duplicates 10.00 2.44 1.05 3.49 100000
insert_key 30.00 3.36 1.45 4.81 100000
insert_many_fields 2.00 0.13 0.13 0.26 2000
insert_select_1_key 1.00 0.00 0.00 0.00 1
insert_select_2_keys 1.00 0.00 0.00 0.00 1
min_max 2.00 0.01 0.00 0.01 60
min_max_on_key 23.00 9.25 3.33 12.58 85000
multiple_value_insert 1.00 0.12 0.00 0.12 100000
once_prepared_select 18.00 4.98 1.60 6.58 100000
order_by_big 8.00 6.68 0.11 6.79 10
order_by_big_key 8.00 6.96 0.17 7.13 10
order_by_big_key2 7.00 6.80 0.15 6.95 10
order_by_big_key_desc 8.00 7.07 0.16 7.23 10
order_by_big_key_diff 8.00 6.73 0.12 6.85 10
order_by_big_key_prefix 8.00 6.84 0.13 6.97 10
order_by_key2_diff 2.00 0.79 0.04 0.83 500
order_by_key_prefix 1.00 0.45 0.02 0.47 500
order_by_range 1.00 0.45 0.03 0.48 500
outer_join 10.00 0.00 0.01 0.01 10
outer_join_found 10.00 0.01 0.00 0.01 10
outer_join_not_found 7.00 0.00 0.00 0.00 500
outer_join_on_key 7.00 0.00 0.00 0.00 10
prepared_select 31.00 12.21 3.62 15.83 100000
select_1_row 10.00 2.31 0.90 3.21 100000
select_1_row_cache 11.00 2.14 1.12 3.26 100000
select_2_rows 11.00 2.49 1.01 3.50 100000
select_big 9.00 6.78 0.16 6.94 80
select_big_str 5.00 2.69 0.40 3.09 10000
select_cache 23.00 0.88 0.15 1.03 10000
select_cache2 22.00 0.88 0.11 0.99 10000
select_column+column 14.00 1.84 1.04 2.88 100000
select_diff_key 0.00 0.05 0.05 0.10 500
select_distinct 3.00 0.70 0.06 0.76 800
select_group 8.00 0.56 0.17 0.73 2911
select_join 1.00 0.19 0.01 0.20 100
select_key 59.00 23.49 5.84 29.33 200000
select_key2 68.00 26.43 8.40 34.83 200000
select_key2_return_key 56.00 22.67 6.04 28.71 200000
select_key2_return_prim 68.00 25.13 8.13 33.26 200000
select_key_prefix 72.00 28.90 8.34 37.24 200000
select_key_prefix_join 3.00 1.68 0.02 1.70 100
select_key_return_key 65.00 30.92 8.43 39.35 200000
select_many_fields 7.00 1.75 0.35 2.10 2000
select_range 27.00 3.05 0.09 3.14 410
select_range_key2 8.00 2.66 0.76 3.42 25010
select_range_prefix 8.00 2.62 0.65 3.27 25010
select_simple 13.00 1.67 2.12 3.79 100000
select_simple_cache 12.00 1.40 1.80 3.20 100000
select_simple_join 0.00 0.28 0.03 0.31 500
update_big 9.00 0.00 0.00 0.00 10
update_of_key 7.00 1.24 0.52 1.76 50000
update_of_key_big 4.00 0.02 0.02 0.04 501
update_of_primary_key_many_keys 5.00 0.01 0.00 0.01 256
update_with_key 47.00 6.28 3.86 10.14 300000
update_with_key_prefix 22.00 2.51 2.91 5.42 100000
wisc_benchmark 1.00 0.99 0.07 1.06 114
TOTALS 1111.00 307.95 88.99 396.94 3375950
[/cce_bash]
MariaDB:

[cce_bash]
Benchmark DBD suite: 2.15
Date of test: 2011-05-25 3:26:59
Running tests on: Linux 2.6.18-238.el5 x86_64
Arguments:
Comments:
Limits from:
Server version: MySQL 5.2.6 MariaDB mariadb101/
Optimization: None
Hardware:

alter-table: Total time: 8 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.05 CPU)
ATIS: Total time: 12 wallclock secs ( 3.87 usr 0.42 sys + 0.00 cusr 0.00 csys = 4.29 CPU)
big-tables: Total time: 8 wallclock secs ( 2.11 usr 0.40 sys + 0.00 cusr 0.00 csys = 2.51 CPU)
connect: Total time: 101 wallclock secs (29.79 usr 15.16 sys + 0.00 cusr 0.00 csys = 44.95 CPU)
create: Failed (output/create-mysql-Linux_2.6.18_238.el5_x86_64)
insert: Total time: 736 wallclock secs (240.44 usr 50.84 sys + 0.00 cusr 0.00 csys = 291.28 CPU)
select: Total time: 216 wallclock secs (23.22 usr 4.85 sys + 0.00 cusr 0.00 csys = 28.07 CPU)
transactions: Test skipped because the database doesn’t support transactions
wisconsin: Total time: 6 wallclock secs ( 1.72 usr 0.37 sys + 0.00 cusr 0.00 csys = 2.09 CPU)

Of 9 tests, 1 tests didn’t work

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 3.00 0.02 0.00 0.02 100
alter_table_drop 3.00 0.01 0.00 0.01 91
connect 10.00 4.42 1.86 6.28 10000
connect+select_1_row 12.00 3.99 2.37 6.36 10000
connect+select_simple 11.00 3.84 2.35 6.19 10000
count 4.00 0.02 0.01 0.03 100
count_distinct 6.00 0.12 0.05 0.17 1000
count_distinct_2 9.00 0.16 0.06 0.22 1000
count_distinct_big 10.00 2.65 0.03 2.68 120
count_distinct_group 6.00 0.47 0.06 0.53 1000
count_distinct_group_on_key 7.00 0.12 0.02 0.14 1000
count_distinct_group_on_key_parts 7.00 0.47 0.09 0.56 1000
count_distinct_key_prefix 4.00 0.12 0.06 0.18 1000
count_group_on_key_parts 8.00 0.41 0.05 0.46 1000
count_on_key 68.00 5.19 0.99 6.18 50100
create_index 1.00 0.00 0.00 0.00 8
create_table 0.00 0.00 0.01 0.01 31
delete_all_many_keys 13.00 0.02 0.00 0.02 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 13.00 0.02 0.00 0.02 128
delete_key 2.00 0.43 0.30 0.73 10000
delete_range 3.00 0.00 0.00 0.00 12
drop_index 1.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
insert 48.00 8.87 4.79 13.66 350768
insert_duplicates 16.00 3.63 1.71 5.34 100000
insert_key 33.00 3.86 1.82 5.68 100000
insert_many_fields 2.00 0.07 0.05 0.12 2000
insert_select_1_key 1.00 0.00 0.00 0.00 1
insert_select_2_keys 2.00 0.00 0.00 0.00 1
min_max 4.00 0.01 0.01 0.02 60
min_max_on_key 23.00 9.33 2.83 12.16 85000
multiple_value_insert 1.00 0.12 0.00 0.12 100000
once_prepared_select 24.00 5.06 1.89 6.95 100000
order_by_big 8.00 6.79 0.12 6.91 10
order_by_big_key 8.00 7.03 0.17 7.20 10
order_by_big_key2 7.00 6.85 0.09 6.94 10
order_by_big_key_desc 9.00 7.05 0.16 7.21 10
order_by_big_key_diff 9.00 6.78 0.10 6.88 10
order_by_big_key_prefix 8.00 6.86 0.11 6.97 10
order_by_key2_diff 2.00 0.79 0.03 0.82 500
order_by_key_prefix 1.00 0.46 0.02 0.48 500
order_by_range 1.00 0.43 0.02 0.45 500
outer_join 11.00 0.00 0.00 0.00 10
outer_join_found 9.00 0.01 0.00 0.01 10
outer_join_not_found 8.00 0.00 0.00 0.00 500
outer_join_on_key 7.00 0.00 0.00 0.00 10
prepared_select 26.00 10.27 2.47 12.74 100000
select_1_row 11.00 3.02 1.34 4.36 100000
select_1_row_cache 10.00 2.31 1.10 3.41 100000
select_2_rows 10.00 2.65 0.94 3.59 100000
select_big 8.00 6.89 0.17 7.06 80
select_big_str 4.00 1.36 0.70 2.06 10000
select_cache 26.00 1.05 0.18 1.23 10000
select_cache2 25.00 1.03 0.19 1.22 10000
select_column+column 11.00 2.61 0.87 3.48 100000
select_diff_key 0.00 0.06 0.03 0.09 500
select_distinct 3.00 0.73 0.04 0.77 800
select_group 10.00 0.72 0.16 0.88 2911
select_join 1.00 0.22 0.01 0.23 100
select_key 70.00 26.28 7.84 34.12 200000
select_key2 48.00 21.32 3.43 24.75 200000
select_key2_return_key 60.00 24.12 5.96 30.08 200000
select_key2_return_prim 51.00 21.54 4.22 25.76 200000
select_key_prefix 47.00 20.75 3.57 24.32 200000
select_key_prefix_join 3.00 1.67 0.02 1.69 100
select_key_return_key 56.00 23.19 5.72 28.91 200000
select_many_fields 6.00 2.04 0.35 2.39 2000
select_range 31.00 3.13 0.08 3.21 410
select_range_key2 6.00 2.10 0.38 2.48 25010
select_range_prefix 8.00 2.31 0.58 2.89 25010
select_simple 11.00 2.70 1.92 4.62 100000
select_simple_cache 11.00 2.89 1.71 4.60 100000
select_simple_join 0.00 0.25 0.04 0.29 500
update_big 9.00 0.00 0.00 0.00 10
update_of_key 8.00 1.42 0.69 2.11 50000
update_of_key_big 6.00 0.02 0.01 0.03 501
update_of_primary_key_many_keys 5.00 0.01 0.01 0.02 256
update_with_key 44.00 7.05 3.74 10.79 300000
update_with_key_prefix 14.00 3.85 1.03 4.88 100000
wisc_benchmark 2.00 1.02 0.08 1.10 114
TOTALS 1084.00 297.06 71.81 368.87 3375950
[/cce_bash]

So as you can see not much incentive here. I know that for advanced use cases MariaDB offers some new features and improvements but for a normal use case as for example the web server hosting this site there’s no major advantage.

network-manager-applet on Slackware 13.37 (current too) compile error

silviuLeave a comment

I took the time today to bring Network Manager and network-manager-applet (both very useful on my laptop where I have VPN’s, 3G modems and various networks I connect to) First I brought all the dependencies up to date but network-manager-applet still refused to compile:
[cce_bash]
checking for GOBJECT… yes
checking for NMA… no
configure: error: Package requirements (dbus-glib-1 >= 0.74
glib-2.0 >= 2.16
NetworkManager >= 0.8.4
libnm-glib >= 0.8.4
libnm-util >= 0.8.4
libnm-glib-vpn >= 0.8.4
gtk+-2.0 >= 2.14
gmodule-export-2.0
gconf-2.0
gnome-keyring-1
libnotify >= 0.4.3) were not met:

No package ‘gnome-keyring-1’ found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.

Alternatively, you may set the environment variables NMA_CFLAGS
and NMA_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
[/cce_bash]
Annoying as I had gnome-keyring installed and up to date. After a bit of poking around I realized that the missing dependency is in fact libgnome-keyring (alsa available at slackbuilds so easy enough to install as well)

Update: as the network-manager-applet package mantainer was so kind to reply to me the dependency is mentioned in gnome-keyring’s README:

gnome-keyring’s README states that libgnome-keyring is required:
http://slackbuilds.org/repository/13.37/misc/gnome-keyring/

I’ll leave the post here in case you pay as much attention as I did.

Shrink (resize) a LVM volume

silviuLeave a comment

I found a lot of resources on resizing LVM volumes or volume groups. But resources on shrinking were a little scarce and I couldn’t easily find a simple step by step method listed, so here you go. Please note that the volume you intend to shrink must be unmounted. If you want to resize the root partition (because like me you didn’t noticed that the ubuntu server installer will alocate the full 500Gb HDD for the root) you will have to boot off a LiveCD. I used the ubuntu server install cd and booted in Rescue Mode.

After it boots press ALT+F2 to move to a console.

Let’s see first the layout:
[ccNe_bash]
lvdisplay
[/ccNe_bash]
Let’s try and see if the volumes are there:
[ccNe_bash]
ls /dev/vgname/volume
[/ccNe_bash]
On the first try I got nothing so we must enable them:
[cceN_bash]
vgchange -a y
ls /dev/vgname/volume
[/cceN_bash]
Yep, they are all there.

Before anything else we need to check the volume for errors (resize2fs will not work if you don’t)
[ccNe_bash]
e2fsck -f /dev/vgname/volume
[/ccNe_bash]
After that we resize the filesystem:
[ccNe_bash]
resize2fs /dev/vgname/volume 15G
[/ccNe_bash]
Note that if you resize the volume to a size smaller than the file system it will trim it (the file system) and probably destroy it
Above, I reduced the filesystem from 453 to 15gb. That is a 438Gb gain.  Just to be on the safe side I will resize the volume only with 435Gb (leaving play room of over 3Gb which is plenty enough, 1Gb should be sufficient).

E.g. if you want your final size to be 15Gb than resize the fs to 14Gb, shrink the logical volume to 15Gb and then grow the file system back.
[ccNe_bash]
lvreduce -L -435G /dev/vgname/volume
[/ccNe_bash]
Let’s put whatever was left for safety to use (grow back the file system to the full volume size):
[ccNe_bash]
resize2fs /dev/vgname/volume
[/ccNe_bash]
Just to be on the safe side let’s check the filesystem for errors.
[ccNe_bash]
e2fsck -f /dev/vgname/volume
[/ccNe_bash]
If you, like me, have resized the root than now you should reboot, otherwise simply mount back the volume and enjoy the free space by creating new volumes.

Remember! I resized a test environment, which didn’t matter if it went away. If you resize a production system (or anything else with useful data on it) remeber to BACKUP!