2015년 12월 17일 목요일

How to Install Mroonga with MySQL Ver. 5.6

1. Install Rpm Library 

1) Rpm 

You should install rpm.

gcc
gcc-c++
libgcc

libstdc++
libstdc++-devel

groonga-devel
groonga-libs
groonga-normalizer-mysql
groonga-tokenizer-mecab
mecab
mecab-ipadic

2) MySQL 

Installed MySQL
MySQL Source

3) Mroonga 

Mroonga Source

ex> http://packages.groonga.org

2. Install 

1) MySQL Install 

Install MySQL the way you want

2) MySQL source 


gunzip mysql-5.6.24.tar.gz
tar xf mysql-5.6.24.tar

3) Mroonga source

tar xf mroonga-5.10.tar

4) Compile Mroonga

cd mroonga-5.10
./configure --with-mysql-source=/db/mysql-5.6.24 --with-mysql-config=/usr/local/mysql/bin/mysql_config
make
make install

5) check 

cd /usr/local/mysql/lib/plugin/
ls -al | grep ha_m
-rw-r--r-- 1 root  root  10884114 Dec 16 15:54 ha_mroonga.a
-rwxr-xr-x 1 root  root      1005 Dec 16 15:54 ha_mroonga.la
lrwxrwxrwx 1 root  root        19 Dec 16 15:54 ha_mroonga.so -> ha_mroonga.so.0.0.0
lrwxrwxrwx 1 root  root        19 Dec 16 15:54 ha_mroonga.so.0 -> ha_mroonga.so.0.0.0

-rwxr-xr-x 1 root  root   3943353 Dec 16 15:54 ha_mroonga.so.0.0.0

3. Enable Mroonga plugin 

1) MySQL Restart 

The way you want

2) Execute Command 

INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';
CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname 'ha_mroonga.so';
CREATE FUNCTION mroonga_snippet RETURNS STRING soname 'ha_mroonga.so';

3) check mroonga

show plugins;
show engines;

4. Test 

The Way you want~~~~

http://mroonga.org/docs/install/others.html#build-from-source

2015년 11월 25일 수요일

MySQL Ver. 5.7 Time Zone New Variable log_timestamps

You can see new system varable log_timestamps in MySQL Ver. 5.7. It introduced MySQL Ver. 5.7.2 .

[log_timestamps] 

log_timestamps controls the timestamp time zone of error log, general query log and slow query log's time information.
You can set log_timestamps at runtime.
Value type is enumeration ( 'SYSTEM'.'UTC') .
Value format is YYYY-MM-DDhh:mm:ss.uuuuuu + z signifying Zulu time (UTC) or + hh:mm ( offset from UTC)

[Warnning]
If MySQL startup with --timezone to mysqld_safe, No effect log_timestamps.
You should use default-time-zone system variable instead of --timezone to mysqld_safe.

MySQL TimeZone Configuration

MySQL Time Zone.

1. 3 Level Time Zone. 

MySQL support 3 level time zone setting.

1). System Time Zone. 

System time zone is the highest level time zone. You can see system_time_zone system variable. If you don't setting anything, When the server starts, it attempts to determine the time zone of the machine.

When MySQL startup with the --timezone=XXX option to mysqld_safe, You can set system_time_zone variable. And, you can set system_time_zone system variable by setting the TZ environment variable.

2). Server Time Zone.

It means MySQL server's current time zone. System variables is time_zone. If system_time_zone doesn't set, the initial value of time_zone is SYSTEM.

You can set the global value of time_zone with --default-time-zone=XXX option to mysqld_safe. Or, You can write the following line in an option file

[mysqld]
default-time-zone='XXX'

You can set the global value of time_zone at runtime with this statement:

mysql> set global time_zone= XXX;

3). Connection Time Zone. 

Each client has its own time zone setting with this statement:

mysql> set time_zone = XXX; 

2. Configuration of Timezone. 

Value of Timezone can be given  several formats.

1) SYSTEM 

It means the time zone should be the same as the system's.

2) Offset from UTC 

such as '+00:00' , '+10:00' or '-6:00'

3) Named time zone 

such as 'Asia/Seoul' , 'US/Eastern' or 'Europe/Paris'
You must loading named time zone data in mysql schema.




Using Timestamp data with UTC type.

1. Using Function 


[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:41:50 |
+---------------------+
1 row in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> create table test ( ts timestamp not null) ;
Query OK, 0 rows affected (0.01 sec)

[root@oraclelinux6.localdomain][test]> insert into test( ts) values (utc_timestamp());
Query OK, 1 row affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select * from test;
+---------------------+
| ts                  |
+---------------------+
| 2015-06-25 06:41:39 |
+---------------------+
1 row in set (0.00 sec)


2. Setting time_zone value 

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> set session time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 06:44:20 |
+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> set session time_zone='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:45:39 |
+---------------------+
1 row in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> system date;
Wed Jun 24 23:45:47 PDT 2015
[root@oraclelinux6.localdomain][test]>


3. Loading Named Time Zone table 


You can use system's local's zoneinfo database. Or, You can use package that is available for download at the MySQL Developer Zone.

3.1 Using system's zoneinfo database. 

shell> cd /db/mysql/bin
shell> ./mysql_tzinfo_to_sql /usr/share/zoneino >> /tmp/timezone.sql
shell> ./mysql -p -u root
[root@oraclelinux6.localdomain][]> source /tmp/timezone.sql  

3.2 Using a package that is available for download at the MySQL Developer Zone. 

You Can see a package file list this url.
http://dev.mysql.com/downloads/timezones.html
Select one package for you. And then, Load MySQL's mysql schema.

[root@oraclelinux6.localdomain][]> use mysql;
[root@oraclelinux6.localdomain][mysql]> source /tmp/timezone_posix.sql



2015년 11월 22일 일요일

DATETIME and TIMESTAMP

1. DATA Type

DATETIME and TIMESTAMP datatype have year,month,day,hour,minute and seconds. Additionally, They can save fraction of second. They use data format with YYYY-MM-DD HH:MM:SS[.fraction]. DATETIME can save value from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' . TIMESTAMP can save value from '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.99999' UTC.

2. Different DATETIME and TIMESTAMP 

DATETIME have no timezone information. TIMESTAMP have timezone information with time_zone system  variables. In detail,  TIMESTAMP have date and time data in UTC timezone. So, Before saving date and time data, MySQL convert timezone to UTC.

3. Setting default value 

After MySQL Version 5.6, DATETIME setting default time value.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


2015년 10월 22일 목요일

The total number of locks exceeds the lock table size

What Problems? 

When you run an operation on large number of rows within a table that use the innodb storage engine, You may see errors.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

What for?

When an operation's row number is large and InnoDB's buffer pool size is small, You will see this error.

What can I do for fixing Problem?

You should increase innodb_buffer_pool_size's value.

or

You should decrease operation's size.



2015년 10월 19일 월요일

MySQL InnoDB Huge page Error

What Problems? 

When you configure large-pages system variables enable, You may see errors.

InnoDB: HugeTLB: Warning: Failed to allocate 4194304 bytes. errno 12
InnoDB HugeTLB: Warning: Using conventional memory pool

What for?

In MySQL, InnoDB support large pages to allocate memory for its buffer pool.  So, When you confugre large-pages variables enable for using large page, The system must be configure 4MB as Huge page.
If huge page's size is less than 4 MB, You must increase greater than 4MB.

What can I do for fixing Problem?

You should increase greater than 4MB.
You can find current page size.

grep -i huge /proc/meminfo

HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

You can see more detail this url.
http://dev.mysql.com/doc/refman/5.6/en/large-page-support.html