Category Archives: Database

Startup memory difference Percona Server vs MariaDB

When browsing for FreeBSD ports on database section I stumbleupon percona server and decide to see whats the difference between pkg and ports :

update pkg

# pkg update
Updating FreeBSD repository catalogue…
FreeBSD repository is up-to-date.
All repositories are up-to-date.
#

update ports

#portsnap fetch
#portsnap extract

pkg and ports give same version :

# cat /usr/ports/databases/percona56-server/Makefile | grep DISTVERSION
DISTVERSION=    5.6.19-67.0
MASTER_SITES=    http://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-${DISTVERSION}/source/tarball/
DISTNAME=    percona-server-${DISTVERSION}

# pkg install percona56-server
Updating FreeBSD repository catalogue…
FreeBSD repository is up-to-date.
All repositories are up-to-date.
The following 2 packages will be affected (of 0 checked):

New packages to be INSTALLED:
percona56-server: 5.6.19.67.0
percona56-client: 5.6.19.67.0

The process will require 121 MB more space.
10 MB to be downloaded.

Proceed with this action? [y/N]: n

Install using pkg will only need 10MB and much faster than ports so I’ll use pkg :-)

Stop MariaDB

# /usr/local/etc/rc.d/mysql.server.sh stop
Shutting down MySQL.. SUCCESS!

Re run pkg install

# pkg install percona56-server
Updating FreeBSD repository catalogue…
FreeBSD repository is up-to-date.
All repositories are up-to-date.
The following 2 packages will be affected (of 0 checked):

New packages to be INSTALLED:
percona56-server: 5.6.19.67.0
percona56-client: 5.6.19.67.0

The process will require 121 MB more space.
10 MB to be downloaded.

Proceed with this action? [y/N]: y

Fetching percona56-server-5.6.19.67.0.txz: 100%    8 MB 117.5k/s    01:13
Fetching percona56-client-5.6.19.67.0.txz: 100%    2 MB 110.0k/s    00:17
Checking integrity… done (0 conflicting)
[1/2] Installing percona56-client-5.6.19.67.0: 100%
===> Creating users and/or groups.
Using existing group ‘mysql’.
Using existing user ‘mysql’.
[2/2] Installing percona56-server-5.6.19.67.0: 100%

Its idle FreeBSD 10 server. Since I don’t know yet equivalent command for ‘free -m’ in FreeBSD I use top and export it to a file (maria for top process list when running mariadb and percona for top process list when running percona).

The result is :

# cat maria

last pid: 48064;  load averages:  0.08,  0.06,  0.07  up 0+01:20:00    14:41:12
23 processes:  2 running, 21 sleeping

Mem: 63M Active, 22M Inact, 89M Wired, 2492K Cache, 59M Buf, 306M Free
Swap: 1024M Total, 10M Used, 1014M Free

PID USERNAME    THR PRI NICE   SIZE    RES STATE    TIME    WCPU COMMAND
48059 mysql        23  52    0   457M 65512K sigwai   0:01   8.98% mysqld
911 alam          1  20    0 17576K  1504K select   0:10   0.00% sshd
779 root          1  20    0 10132K   408K nanslp   0:01   0.00% cron
915 root          1  23    0 10864K  1588K RUN      0:00   0.00% csh
772 root          1  20    0 12864K  1416K select   0:00   0.00% sendmail
908 root          1  20    0 17576K  1340K select   0:00   0.00% sshd
914 alam          1  20    0 10588K     0K wait     0:00   0.00% <su>
591 root          1  20    0 10096K   784K select   0:00   0.00% syslogd
47991 root          1  52    0 10472K  1716K wait     0:00   0.00% sh
912 alam          1  21    0 10476K     0K wait     0:00   0.00% <sh>
48064 root          1  23    0 11236K  1860K RUN      0:00   0.00% top
475 root          1  20    0  9380K    48K select   0:00   0.00% devd
775 smmsp         1  20    0 12864K     0K pause    0:00   0.00% <sendmail>
900 root          1  20    0 10084K   664K ttyin    0:00   0.00% getty
761 root          1  20    0 14816K  1268K select   0:00   0.00% sshd
906 root          1  52    0 10084K   664K ttyin    0:00   0.00% getty
907 root          1  52    0 10084K   664K ttyin    0:00   0.00% getty
901 root          1  52    0 10084K   664K ttyin    0:00   0.00% getty

# cat percona

last pid: 47972;  load averages:  0.02,  0.05,  0.07  up 0+01:19:09    14:40:21
23 processes:  2 running, 21 sleeping

Mem: 308M Active, 74M Inact, 90M Wired, 10M Cache, 59M Buf, 604K Free
Swap: 1024M Total, 65M Used, 959M Free, 6% Inuse

PID USERNAME    THR PRI NICE   SIZE    RES STATE    TIME    WCPU COMMAND
911 alam          1  20    0 17576K  1504K select   0:10   0.00% sshd
779 root          1  20    0 10132K   400K nanslp   0:01   0.00% cron
47962 mysql        23  33    0   612M   379M sigwai   0:01   0.00% mysqld
915 root          1  20    0 10864K  1296K RUN      0:00   0.00% csh
772 root          1  20    0 12864K  1416K select   0:00   0.00% sendmail
908 root          1  20    0 17576K  1340K select   0:00   0.00% sshd
914 alam          1  20    0 10588K     0K wait     0:00   0.00% <su>
591 root          1  20    0 10096K   784K select   0:00   0.00% syslogd
47866 mysql         1  52    0 10472K     0K wait     0:00   0.00% <sh>
912 alam          1  21    0 10476K     0K wait     0:00   0.00% <sh>
47972 root          1  20    0 11236K  1704K RUN      0:00   0.00% top
475 root          1  20    0  9380K    48K select   0:00   0.00% devd
775 smmsp         1  20    0 12864K     0K pause    0:00   0.00% <sendmail>
900 root          1  20    0 10084K   664K ttyin    0:00   0.00% getty
761 root          1  20    0 14816K  1268K select   0:00   0.00% sshd
906 root          1  52    0 10084K   664K ttyin    0:00   0.00% getty
907 root          1  52    0 10084K   664K ttyin    0:00   0.00% getty
901 root          1  52    0 10084K   664K ttyin    0:00   0.00% getty

I see big difference between them specially for percona but I don’t know how they use memory.

p.s :

MariaDB installled from source (MariaDB 10) and Percona Server from pkg on FreeBSD 10 that run from VirtualBox. I think I’ll try it on FreeBSD 10 64 bit on vps.

mysql error : Can’t create/write to file ‘/tmp/#sql_1581_0.MYI’ (Errcode: 13)

Recent PHP downgrade to 5.2.10 also bring another problem with phpmyadmin. When I try to browse data it work but when I try to see table structure I have this error :

Can’t create/write to file ‘/tmp/#sql_1581_0.MYI’ (Errcode: 13)

Being curios if the problem caused by phpmyadmin I try shell way :

# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 106
Server version: 5.1.41-3ubuntu12.8 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_1581_0.MYI’ (Errcode: 13)
mysql>

Ups, seem same problem.

Looking log data

# tail -f /var/log/mysql/error.log

101217  0:10:02 [Note] /usr/sbin/mysqld: Shutdown complete

101217  0:10:02 [Note] Plugin ‘FEDERATED’ is disabled.
/usr/sbin/mysqld: Can’t create/write to file ‘/tmp/ibLwkhPZ’ (Errcode: 13)
101217  0:10:02  InnoDB: Error: unable to create temporary file; errno: 13
101217  0:10:02 [ERROR] Plugin ‘InnoDB’ init function returned error.
101217  0:10:02 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
101217  0:10:02 [Note] Event Scheduler: Loaded 0 events
101217  0:10:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.1.41-3ubuntu12.8’  socket: ‘/var/run/mysqld/mysqld.sock’  port: 3306  (Ubuntu)

I guess its permission problem and quick solution for the problem.

# ls -l / | grep tmp

drwxr-xr-x   4 root root  4096 2010-12-17 00:10 tmp
#

# chmod 777 /tmp

Recheck with shell

# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 112
Server version: 5.1.41-3ubuntu12.8 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
+———————–+———————————–+——+—–+———+——-+
| Field                 | Type                              | Null | Key | Default | Extra |
+———————–+———————————–+——+—–+———+——-+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum(‘N’,’Y’)                     | NO   |     | N       |       |
| Insert_priv           | enum(‘N’,’Y’)                     | NO   |     | N       |       |
| Update_priv           | enum(‘N’,’Y’)

more …………

great.

Can’t locate Compress/Zlib.pm

edp# perl /usr/local/sbin/mysqlblasy.pl
Can’t locate Compress/Zlib.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.8/BSDPAN

/usr/local/lib/perl5/site_perl/5.8.8/mach /usr/local/lib/perl5/site_perl/5.8.8

/usr/local/lib/perl5/site_perl /usr/local/lib/perl5/5.8.8/mach /usr/local/lib/perl5/5.8.8 .) at

/usr/local/lib/perl5/site_perl/5.8.8/Archive/Zip.pm line 11.

BEGIN failed–compilation aborted at /usr/local/lib/perl5/site_perl/5.8.8/Archive/Zip.pm line  11.
Compilation failed in require at /usr/local/sbin/mysqlblasy.pl line 1340.
BEGIN failed–compilation aborted at /usr/local/sbin/mysqlblasy.pl line 1340.

Solution :

edp# cd /usr/ports/archivers/

edp# cd p5-Compress-Zlib && make install
edp# rehash

Try again :

edp# perl /usr/local/sbin/mysqlblasy.pl

edp# cd /data/db2/
edp# ls
localhost_2008_10_06-16_15_30.tar.gz
edp#
Yes, it works :-)

MYSQL server has gone away

Its very rare case when I see message :

“MYSQL server has gone away” , a few things to consider are :

* Check variable max_timeout
* Check variable max_allowed_packet

These setting available in my.ini or my.cf file, edit the value and restart mysql daemon.

Example :

max_allowed_packet=16M

max_timeout=28800

Try again to import database using mysql command and see the different :-)

Unknown SMSD service type (“MYSQL”)

That message show up when I install gammu from source in Ubuntu server 8.04 :

root@dns:/home/edpteam# gammu –smsd MYSQL smsdrc
Unknown SMSD service type (“MYSQL”)

After dig in mailing list I have clue to install mysql library :

apt-get install libmysqlclient15-dev

apt-get install libmysqlclient15-dev
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed

and are no longer required:
debhelper intltool-debian po-debconf libgammu-common

gettext libgammu3
libbluetooth2 libmm14 html2text
Use ‘apt-get autoremove’ to remove them.
The following extra packages will be installed:
zlib1g-dev
Suggested packages:
mysql-doc-5.0
The following NEW packages will be installed:
libmysqlclient15-dev zlib1g-dev
0 upgraded, 2 newly installed, 0 to remove and 10 not

upgraded.
Need to get 7375kB of archives.
After this operation, 21.1MB of additional disk space

will be used.
Do you want to continue [Y/n]? y
Get:1 http://us.archive.ubuntu.com hardy/main

zlib1g-dev 1:1.2.3.3.dfsg-7ubuntu1 [160kB]
Get:2 http://us.archive.ubuntu.com hardy-updates/main

libmysqlclient15-dev 5.0.51a-3ubuntu5.1 [7214kB]
Get:3 http://us.archive.ubuntu.com hardy-updates/main

libmysqlclient15-dev 5.0.51a-3ubuntu5.1 [7214kB]
Fetched 2897kB in 50min1s (965B/s)
Selecting previously deselected package zlib1g-dev.
(Reading database … 39442 files and directories

currently installed.)
Unpacking zlib1g-dev (from

…/zlib1g-dev_1%3a1.2.3.3.dfsg-7ubuntu1_i386.deb) …
Selecting previously deselected package

libmysqlclient15-dev.
Unpacking libmysqlclient15-dev (from

…/libmysqlclient15-dev_5.0.51a-3ubuntu5.1_i386.deb)


Setting up zlib1g-dev (1:1.2.3.3.dfsg-7ubuntu1) …
Setting up libmysqlclient15-dev (5.0.51a-3ubuntu5.1)

Processing triggers for libc6 …
ldconfig deferred processing now taking place
root@dns:/home/edpteam#

Recompile gammu to detect libmysqlclient and try again :

root@dns:/etc# gammu –smsd MYSQL /etc/smsdrc

Yes, this time no more error message :-)