(다양한 DB를 쓰다보니, 알게되는 디폴트 isolation level은 저 멀리..., mysql 을 그렇게 써놓고도 또 실수하는 것이 나다.. 나같은 실수를 하는 이를 위해서 정리)


play2의 db 설정의 isolation 레벨은 read_committed로 되어 있다. 


https://www.playframework.com/documentation/2.0.x/SettingsJDBC



oracle이나 ms sql은 default isolation level이 read committed 라 그냥 사용했다

http://www.dba-oracle.com/t_oracle_isolation_level.htm

http://msdn.microsoft.com/en-us/library/ms175909.aspx



사용하고 있는 DB는 mariadb(mysql) 인데, 아래와 같은 에러가 발생했다. 



java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 



mysql default isolation level은 repeatable read 라서, 에러가 발생했다.

http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html



mariadb(mysql) play2의 db 설정을 repeatable_read로 수정하니. 정상적으로 작동된다. 




db.default.isolation=REPEATABLE_READ






Posted by 김용환 '김용환'

댓글을 달아 주세요

[Graph] Titan Graph DB

DB 2014. 9. 4. 12:03


Titan Graph DB  공부중.. 


Graph DB이면서 ACID, eventually consistency 를 제공합니다. 각 종 오픈소스를 활용한 Apache License 제품입니다. Storage를 Cassandra나 Hbase를 활용할 수 있고, An Open Source Graph Computing Framework  인 ThinkerPop 과 integration이 되며 Elastic search 연동도 된다. 


http://thinkaurelius.github.io/titan/ 

https://github.com/thinkaurelius/titan/ 


성능자료 

http://www.stingergraph.com/data/uploads/papers/ppaa2014.pdf 

http://blog.euranova.eu/wp-content/uploads/2013/09/an-empirical-comparison-of-graph-databases.pdf 

https://github.com/puneetar/Performance-Analysis-of-Neo4j-and-Titan-Cassandra


Titan쪽에서 읽어볼만하게 권하는 자료

http://markorodriguez.com/2013/01/09/on-graph-computing/ 

http://thinkaurelius.com/2013/11/24/boutique-graph-data-with-titan/ 

http://thinkaurelius.com/2013/03/30/titan-server-from-a-single-server-to-a-highly-available-cluster/ 

http://thinkaurelius.com/2012/08/06/titan-provides-real-time-big-graph-data/


Neo4J vs Titan 

http://www.linkedin.com/groups/Neo4J-vs-Titan-2552968.S.5795238914037927937  (이게 좋음)

https://groups.google.com/forum/#!topic/aureliusgraphs/7v4KySSCyJI


다른 엔진과의 비교 자료

http://architects.dzone.com/articles/16-graph-databases-compared 



Posted by 김용환 '김용환'

댓글을 달아 주세요


mysql 버전 가져오기 


#!/usr/bin/env ruby



mysql_version = `mysql --version | tr [,] ' ' | tr [:space:] '\n' | grep -E '([0-9]{1,3}[\.]){2}[0-9]{1,3}'`


puts mysql_version


결과


5.6.20


Posted by 김용환 '김용환'

댓글을 달아 주세요


shell 에서 mysql db접속시 패스워드를 입력하면 mysql 5.6 부터는 Warning 문구가 발생한다. 


$ mysql #{database} -u#{username} -p#{password}


Warning: Using a password on the command line interface can be insecure


이는 보안상 발생하는 것인데.. 이 문제를 해결하는 궁극적인 방법은 mysql_config_editor를 사용하는 것이다. 

 mysql 설치 디렉토리/bin/mysql_config_editor 를 이용한다. 



 $ mysql_config_editor set --login-path=localhost --host=localhost --user=www --password

 enter password: wwwteam


 

 

 ~/.mylogin.cnf  암호화 파일이 생성된다. 

 

그리고 mysql을 사용하려면 다음과 같이 사용하면 된다. 





$ mysql --login-path=localhost

  Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7964

Server version: 5.6.20 Homebrew


Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


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


mysql>quit

Bye


$  mysql --login-path=localhost -e 'use new_xxx; show tables'


 

 



Posted by 김용환 '김용환'

댓글을 달아 주세요

[altibase] 5.5.1.3.0 설치

DB 2014. 6. 30. 15:25



1. 5.5.1.3.0 다운로드



$ wget http://data.altibase.com/download_back/altibase/altibase5/5.5.1patch/5.5.1.3.0/LINUX/altibase-server-5.5.1.3-LINUX-X86-64bit-patch_0_0_3_0.run



$ sudo ./altibase-server-5.5.1.3-LINUX-X86-64bit-patch_0_0_3_0.run 

----------------------------------------------------------------------------

Welcome to the ALTIBASE HDB Server 5.5.1.3.0 Setup Wizard.


----------------------------------------------------------------------------

Installation Directory


Please specify the installation directory for ALTIBASE HDB Server 5.5.1.3.0


Installation Directory [/opt/altibase-server-5.5.1]: /app/altibase


Please select the installation type.


Install type


[1] Patch package: patch package install

[2] Full package: full package install

Please choose an option [1] : 2


----------------------------------------------------------------------------

Pre-Installation requirements for ALTIBASE HDB




It is first, necessary to set your system environment to ensure that ALTIBASE 

HDB will run properly. Before installing ALTIBASE HDB, the kernel parameter 

values must be set using the root user account. The kernel parameter values may 

be modified after installation; however, they must be set prior to operating the 

ALTIBASE HDB.


Please refer to the installation manual and pre_install.sh script file.

(pre_install.sh : '$ALTIBASE_HOME'/install/pre_install.sh)



================ LINUX ================

[ How to modify kernel parameter values ]


echo 512 32000 512 512 > /proc/sys/kernel/sem

echo 872415232 > /proc/sys/kernel/shmall


# shmall 

If it is desired to use the ALTIBASE HDB in shared memory mode, the value of 

'shmall' must be set. This value determines the maximum size of an ALTIBASE 

database.


Press [Enter] to continue :

These values must be set in order for the ALTIBASE HDB to operate properly.

They must be set such that they are suitable for the system configuration.


=====================================



Press [Enter] to continue :


----------------------------------------------------------------------------

ALTIBASE HDB Property Settings

Step 1: Basic Database Operation Properties


Database name [mydb]: idp




ALTIBASE HDB connection port number (1024-65535)      [20300]: 8095




If you want to use the ALTIBASE HDB evaluation license, 

set the 'Maximum size of memory database' property to 2GB or less.


Maximum size of memory database

-MIN value:16M (K,M,G)      [2G]: 16M




Buffer area size for caching disk-based database pages

-MIN value:1M (K,M,G)      [128M]: 1M




Do you want to create a database after the installation is complete?


[1] YES

[2] NO

Please choose an option [1] : 1


----------------------------------------------------------------------------

ALTIBASE HDB Property Settings

Step 2: Database Creation Properties


Initialization database size

-4M~16M (K,M,G)      [10M]: 4M




Database archive logging mode 


[1] NoArchivelog

[2] Archivelog

Please choose an option [1] : 2




Database character set


[1] MS949

[2] US7ASCII

[3] KO16KSC5601

[4] BIG5

[5] GB231280

[6] UTF-8

[7] SHIFT-JIS

[8] EUC-JP

Please choose an option [1] : 1




National character set


[1] UTF-16

[2] UTF-8

Please choose an option [1] : 1


----------------------------------------------------------------------------

ALTIBASE HDB Property Settings

Step 3: Database Directory Properties


Default disk database directory [/app/altibase/dbs]: 


Memory database directory [/app/altibase/dbs]: 


Archive log directory [/app/altibase/arch_logs]: 


Transaction log directory [/app/altibase/logs]: 


[Log Anchor file Directories ]

The ALTIBASE HDB maintains three sets of log anchor files. These files contain 

important information

about the database. By default, they are located in the "logs" folder.

The location can be changed here or by modifying the contents of the ALTIBASE 

properties file,

which is named "altibase.properties".


Directory 1. [/app/altibase/logs]: 


Directory 2. [/app/altibase/logs]: 


Directory 3. [/app/altibase/logs]: 


----------------------------------------------------------------------------

Property Review


Please check your property settings.



To change these properties after installation is complete, 

please modify the following file :

  /app/altibase/conf/altibase.properties.


1. ALTIBASE HDB Property Settings :

    Step 1: Basic Database Operation Properties


    1) Database name :

         [idp]


    2) ALTIBASE HDB connection port number (1024-65535) :

         [8095]


    3) Maximum size of memory database :

         [16M]


    4) Buffer area size for caching disk-based database pages :

         [1M]


2. ALTIBASE HDB Property Settings :

Press [Enter] to continue :

    Step 2: Database Creation Properties


    1) Initial database size

         [4M]


    2) Database archive logging mode

         [archivelog]


    3) Database character set

         [MS949]


    4) National character set

         [UTF16]


3. ALTIBASE HDB Property Settings :

    Step 3: Database Directory Properties


    The database will not operate properly if one or more of these drectories 

are removed.


    1) Disk database directory : 

         [/app/altibase/dbs] 


Press [Enter] to continue :

    2) Memory database directory :

         [/app/altibase/dbs] 


    3) Archive log directory :

         [/app/altibase/arch_logs] 


    4) Transaction log directory :

         [/app/altibase/logs] 


    5) Log Anchor file directories :

         Directory 1 : 

         [/app/altibase/logs] 


         Directory 2 : 

         [/app/altibase/logs] 


         Directory 3 : 

         [/app/altibase/logs] 




Press [Enter] to continue :


----------------------------------------------------------------------------

Setup is now ready to install ALTIBASE HDB Server 5.5.1.3.0.


Do you want to continue? [Y/n]: Y


----------------------------------------------------------------------------

Please wait until the setup wizard finishes installing ALTIBASE HDB Server 

5.5.1.3.0.


 Installing

 0% ______________ 50% ______________ 100%

 #########################################


----------------------------------------------------------------------------

ALTIBASE HDB License


If a license has not been issued, or if it has expired,

ALTIBASE HDB services will not start.


If this is the case, Please visit   http://atc.altibase.com

or contact ALTIBASE Technical support (license@altibase.com)


 - If you set 'Maximum size of memory database' to 2GB or less, 

   you can use the ALTIBASE HDB evaluation license.

   You can change the maximum size using the MEM_MAX_DB_SIZE property 

   in the altibase.properties file.

   ($ALTIBASE_HOME/conf/altibase.properties)


Choose an option for ALTIBASE license registration.


[1] Input a license key.

[2] Select a license file.

[3] I will use the ALTIBASE evaluation license.

[4] I do not have an ALTIBASE license.

Please choose an option [1] : 3


Info: Evaluation license registration is complete.


copy 

FROM : $ALTIBASE_HOME/conf/license_evaluation

TO : $ALTIBASE_HOME/conf/license

Press [Enter] to continue :

----------------------------------------------------------------------------

ALTIBASE Quick Setting Guide




[  Installation complete  ]

Please refer to the file listed below to verify the ALTIBASE HDB version.

    /app/altibase/APatch/patchinfo 



[ Quick Guide to Making Settings in ALTIBASE ] 


1. Set kernel variables using the root user account.

    run the '/app/altibase/install/pre_install.sh' file 

    - This script helps your kernel parameter settings.


================ LINUX ================

[ How to modify kernel parameter values ]


echo 512 32000 512 512 > /proc/sys/kernel/sem

echo 872415232 > /proc/sys/kernel/shmall


# shmall 

If it is desired to use the ALTIBASE HDB in shared memory mode, the value of 

'shmall' must be set. This value determines the maximum size of an ALTIBASE 

database.

Press [Enter] to continue :


These values must be set in order for the ALTIBASE HDB to operate properly.

They must be set such that they are suitable for the system configuration.


=====================================



 


2. Provide a license.

    Please rename and locate the license file as shown below.

    /app/altibase/conf/license 


    If no license file has been issued, or if the license file has expired,

    ALTIBASE HDB services will not start.

    In this case, please visit http://atc.altibase.com

    or contact ALTIBASE Technical Support (license@altibase.com)


    -  If you set 'Maximum size of memory database' to 2GB or less, 

       you can use the ALTIBASE HDB evaluation license.


       copy

       FROM : /app/altibase/conf/license_evaluation

Press [Enter] to continue :

       TO : /app/altibase/conf/license 


       You can change the maximum size using the MEM_MAX_DB_SIZE property 

       in the altibase.properties file.

       ($ALTIBASE_HOME/conf/altibase.properties)


3. Configure user environment variables (using the user account with which 

ALTIBASE was installed).

    Run the '/app/altibase/install/post_install.sh' file 

    under the account with which ALTIBASE was installed.


    This script performs necessary post-installation configuration. 


    1) Create the ALTIBASE user environment file and apply to the user profile.

         (/app/altibase/conf/altibase_user.env)

    2) Create a database.


         If you selected 'YES' in response to the question about whether to 

create a database 

         after installation, at "ALTIBASE Property setting step 1",


         If you selected 'NO' in response to this question,

         you need  to create a database manually.

Press [Enter] to continue :


         shell> server create [DB Character Set] [National Character Set]


4. Start up and Shut down the server

    shell> server start

    shell> server stop


5. Connect to the database using iSQL

    shell> isql -s 127.0.0.1 -u SYS -p MANAGER



Press [Enter] to continue :




Would you like to launch 'post_install.sh' now ?

 - It will create the ALTIBASE user environment file and the database. [Y/n]: Y



-

/

Result

[Linux Env.]

Target : /app/altibase/conf/altibase_user.env


created  ----------------------- Altibase environment setup file.

added    ----------------------- ALTIBASE_HOME

added    ----------------------- PATH

added    ----------------------- LD_LIBRARY_PATH

added    ----------------------- CLASSPATH

        export ----------------------- 'altibase_user.env' into 

'/home/vagrant/.profile'




-----------------------------------------------------------------

     Altibase Client Query utility.

     Release Version 5.5.1.3.0

     Copyright 2000, ALTIBASE Corporation or its subsidiaries.

     All Rights Reserved.

-----------------------------------------------------------------

ISQL_CONNECTION = UNIX, SERVER = localhost, PORT_NO = 8095

[ERR-910FB : Connected to idle instance]

Connecting to the DB server.... Connected.

Press [Enter] to continue :


TRANSITION TO PHASE : PROCESS

Command executed successfully.


DB Info (Page Size     = 32768)

        (Page Count    = 129)

        (Total DB Size = 4227072)

        (DB File Size  = 1073741824)


Creating MMDB FILES     [SUCCESS]


Creating Catalog Tables [SUCCESS]


Creating DRDB FILES     [SUCCESS]


  [SM] Rebuilding Indices [Total Count:0]  [SUCCESS]


DB Writing Completed. All Done.


Create success.



Press [Enter] to continue :==============================================

Please perform [re-login] 

or [source /home/vagrant/.profile] 

or [. /home/vagrant/.profile]

==============================================

Press [Enter] to continue :

----------------------------------------------------------------------------

Setup has finished installing the ALTIBASE HDB Server 5.5.1.3.0 on your host.






2. DB 실행 

$ export ALTIBASE_HOME=/app/altibase

$ chown -R vagrant.vagrant /app/altibase/

$ /bin/isql  -s 127.0.0.1 -u sys -p manager -sysdba -port 8095


(sudo 계정으로 하면 db 실행이 안됨, 계정 단위 작업 중요)


Posted by 김용환 '김용환'

댓글을 달아 주세요


Altibase에서 아래의 쿼리를 사용할 때는 잘 주의해야 한다. 

(사실 이 Altibase뿐 아니라 모든 DB에도 해당되는 개념이다.)


select * from tbl_google limit 1,10000


table에 천만건의 데이터가 있었던 상황이고,

위의 select쿼리같은 것이 배치처럼 데이터를 가져오는 부분이었다.

select시, update가 일부 실행되면서 2건의 데이터를 가져오지못한 이슈가 발생했다.

당시 insert 쿼리는 하나도 실행이 되지 않았고, sequence에 상관없는 update만 일어났는데 불구하고..


아마도 Altibase는 메모리 DB이다 보니.. 메모리상에서 update쿼리로 인한 데이터 변화가 메모리상 순서를 일으키게 하여 일부 데이터의 순서가 바뀌어 모든 데이터를 가져오지 못하는 이슈가 생긴 것으로 추측한다. 

(Altibase는 limit에 대한 문서를 어디서 보나.... 별로 friendly하지 않은 느낌..)


이 문제를 해결하기 위해서는 둘 중의 해결법을 써야 한다. 

1. limit을 쓸 경우 Order by를 사용하여, 언제나 같은 데이터를 읽어올 수 있게 한다.  (update 쿼리가 들어온다 해도..)

2. limit을 쓰지 않는 다면, 모든 데이터를 모두 가져오게 한다. 이 방법은 App Client 메모리 리소스를 많이 할당함으로서 생기는 큰 문제가 있으니 패스..





limit은 데이터를 저장할 때 random하게 저장될 수 있다. 파일이 저장되는 위치가 다르기 때문이다. 따라서 order by를 써야 정상적으로 나오게 된다.

그렇다면 select * from table 의 결과 뷰는 파일이 저장되는 위치로 출력이 되거나, 보여줄 때 pk기반으로 출력되거나 하는 것으로 보일 것이다. 




일반적으로 mysql limit은 관련해서 설명이 나와 있나 보니. 내용에 대해서는 설명이 없다. 


https://dev.mysql.com/doc/refman/5.1/en/select.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).


With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):


SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:


SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:


SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.


For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table:


SET @a=1;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';

EXECUTE STMT USING @a;

The following statements will return the second to sixth row from the tbl table:


SET @skip=1; SET @numrows=5;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';

EXECUTE STMT USING @skip, @numrows;

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.


If LIMIT occurs within a subquery and also is applied in the outer query, the outermost LIMIT takes precedence. For example, the following statement produces two rows, not one:


(SELECT ... LIMIT 1) LIMIT 2;




Postgress는 그나마 설명이 되어 있다.  Limit을 쓸 때면 Order by 를 사용하라고 명시적으로 되어 있다. 


http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-LIMIT


LIMIT Clause


The LIMIT clause consists of two independent sub-clauses:


LIMIT { count | ALL }

OFFSET start

count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.


If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0.


SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is:


OFFSET start { ROW | ROWS }

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

In this syntax, to write anything except a simple integer constant for start or count, you must write parentheses around it. If count is omitted in a FETCH clause, it defaults to 1. ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses. According to the standard, the OFFSET clause must come before the FETCH clause if both are present; but PostgreSQL is laxer and allows either order.


When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.


The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.


It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case.



오라클은 순서적인 부분때문에 limit 과 같은 역할을 하는 rownum은 아래와 같이 사용한다.


select * from (select * from table order by seq) where rownum < 4




결국..

rownum 이든지. limit 이든지 쿼리의 맨나중에 실행이 되니.. order가 반드시 필요하게 된다.


limit을 쓸 것이면 반드시 order by 는 짝이 되어야 한다. performance 이슈가 중요한 것이 아니라, 정확도가 더 중요한 것임을 잊지 말아야 겠다. 



Posted by 김용환 '김용환'

댓글을 달아 주세요

  1. Favicon of https://blog.woniper.net BlogIcon woniper1 2014.08.21 09:49 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 감사합니다. 놓칠수 있었던 부분을 알게됐습니다.^^


<Partion table 연관 좋은 정보>


best 자료, partion table 성능 정보

http://www.slideshare.net/datacharmer/partitions-performance-with-mysql-51-and-55


http://blog.hibrainapps.net/m/post/view/id/131


http://www.rcy.co.kr/xeb/index.php?mid=study&page=7&document_srl=2369


http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=develop_05&wr_id=6


http://www.dbastuff.net/2014/01/partitioning-mysql-56-style.html


http://fbwotjq.tistory.com/298


http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=develop_05&wr_id=6

(영문) http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html


http://dev.kthcorp.com/2011/06/24/mysql-mass-data-dbms-tuning-case-study-on-statistics-and-analysis-server/







<유의할 점>

파티션 개수가 많아지지 않도록 backup&truncate 정책이 반드시 필요


http://blog.naver.com/PostView.nhn?blogId=seuis398&logNo=70105826106


http://bistros.tistory.com/entry/Mysql-partition-%EC%82%AC%EC%9A%A9%EC%9D%B4%EC%9C%A0%EC%A0%9C%ED%95%9C%EC%A3%BC%EC%9D%98%EC%82%AC%ED%95%AD%EB%93%B1




<대용량 데이터 저장시 사용할만 방법>

파티셔닝을 먼저 진행하면 추후 Sharding 에 유연하게 대처하기 어렵다.


1. DB Sharding을 먼저 진행하고..





Horizontal Parttition 을 진행하면 보다 효율적인 대용량 데이터 관리가 될 듯

Sharding된 Database를 바탕으로 partitioning을 진행하면 된다.




<예전 생각>

L 메신저 Noti center 서버 파트 개발할때...DB 샤딩을 먼저 진행했다. 계속 데이터는 쌓이는 구조이고 성능이 필요한 상태이다.  DB샤딩은 Application Architecture에 영향을 많이 주는 요소인 반면, Partitioning은 Query에 영향을 주는 구조이다.  따라서 DB샤딩 구조로 먼저 진행한 후, 운영하면서 부하가 많이 발생하고 이슈가 생긴다면 Partitioning을 진행하면 된다. 


메신저 또는 메신저 기반 플랫폼과 같은 것들은 계속 데이터가 쌓이는 시스템이라면 'DB 샤딩 + DB Partitioing' 을 이용하면 좋을 듯 하다. 







'DB' 카테고리의 다른 글

[altibase] 5.5.1.3.0 설치  (0) 2014.06.30
limit 사용시 항상 order by를 염두하기  (1) 2014.05.02
mysql partitioning 관련된 좋은 정보  (0) 2014.02.06
[mysql] replication leak  (0) 2014.02.04
[mysql] function 정보  (0) 2014.02.04
[mysql] Tip - group_concat, if/ifnull/case  (0) 2013.12.19
Posted by 김용환 '김용환'

댓글을 달아 주세요

[mysql] replication leak

DB 2014. 2. 4. 19:55



<현상>

memory leak by replication

stop slave;
set global slave_parallel_workers=8; 
start slave;

top

in a while , you will notice the memory problem, you can also use the script bellow to watch the memory used by mysqld;

while true
do
  date >> ps.log
  ps aux | grep mysqld >> ps.log
  sleep 60
done




<해결 방법>


[23 Jan 5:35] qinglin zhang
set the following three parameters may stop the leak:

master_info_repository=file
relay_log_info_repository=file
slave_parallel_workers=0




출처

http://bugs.mysql.com/bug.php?id=71197


http://bugs.mysql.com/bug.php?id=71425


https://www.evernote.com/shard/s157/sh/ee743edd-7c73-41b4-97dc-f69ab2284506/f5869c20adb6fe3636702678449a9bdb



'DB' 카테고리의 다른 글

limit 사용시 항상 order by를 염두하기  (1) 2014.05.02
mysql partitioning 관련된 좋은 정보  (0) 2014.02.06
[mysql] replication leak  (0) 2014.02.04
[mysql] function 정보  (0) 2014.02.04
[mysql] Tip - group_concat, if/ifnull/case  (0) 2013.12.19
Mysql HA  (0) 2013.04.23
Posted by 김용환 '김용환'

댓글을 달아 주세요

[mysql] function 정보

DB 2014. 2. 4. 17:39


1. function 데이터 읽어오기

show function status;


* 참고 

procedure 확인하기

show procedure status; 


2. function 상세 정보 확인

select name, body from mysql.proc where name like 'myFunction';



3. function 구체적인 정보

http://www.java2s.com/Tutorial/MySQL/0201__Procedure-Function/Selectinformationfromthemysqlproctableandgetalltheunderlyingdata.htm


http://www.java2s.com/Tutorial/MySQL/0201__Procedure-Function/UsetheSHOWCREATEFUNCTIONcommand.htm


http://blog.daum.net/_blog/BlogTypeView.do?blogid=0Frx9&articleno=7333084&_bloghome_menu=recenttext




4. function 쓰면 좋은 것

auto_increment류의 nextval, curval, setval을 구현시, ibatis에서 두번 쿼리 날리지 않고, 한번의 쿼리로 insert 데이터 & select auto_increment값을 얻어올 수 있다. 


http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/




5. mysqldump시 유의사항

function, procedure과 db dump를 아래와 같이 떠야 한다. 


 mysqldump --routines   -u 유저이름 -p DB명 > 백업파일



* 참고 

trigger까지 덤프를 떠야 안전. 


 mysqldump --routines --trigger  -u 유저이름 -p DB명 > 백업파일 



'DB' 카테고리의 다른 글

mysql partitioning 관련된 좋은 정보  (0) 2014.02.06
[mysql] replication leak  (0) 2014.02.04
[mysql] function 정보  (0) 2014.02.04
[mysql] Tip - group_concat, if/ifnull/case  (0) 2013.12.19
Mysql HA  (0) 2013.04.23
[mysql] "Impossible WHERE noticed after reading const tables"  (0) 2012.06.20
Posted by 김용환 '김용환'

댓글을 달아 주세요


1. group_concat(column_value)

mysql 에서 컬럼의 값을 , 단위로 수집하기


B_idx    job                shool          name 
------------------------------------------- 
  2        enginner          high          데이터1,데이터3



group_concat를 이용하면 된다. 


http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=190021



group_concat 안에서 order by와 seperator를 진행할 수 있다. 


http://blackbull.tistory.com/3



* 주의할점

mysql의 group_concat은 길이 제한과 관련된 설정정보가 따로 있다. (성능이슈가 있을수 있으니.. 대용량쪽에서는 유의해야 한다.)


http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html



  •  group_concat_max_len

    Command-Line Format--group_concat_max_len=#
    Option-File Formatgroup_concat_max_len
    System Variable Namegroup_concat_max_len
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default1024
    Range4 .. 4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default1024
    Range4 .. 18446744073709547520

    The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.






2. ifnull(column_value, default_value)

case

if


http://www.spatium.co.kr/languages/content.php?chno=5&bno=34





예)


select r.title, ifnull(l.hourly, -1) as hour, concat('\"', group_concat(l.errorcode ORDER BY l.errorcode SEPARATOR '","'), '\"') as errorstatus

From 

(select urlid, hour(FROM_UNIXTIME(date)) as hourly, errorcode 

from url_check_result 

where errorcode like 'e%' 

group by urlid, hourly, errorcode order by urlid, hourly, errorcode

) l

right join  

(SELECT  x.id, x.title, x.url 

FROM url_check_list as x, service_list as y

WHERE y.id = x.service_id and y.service_name = "googledoc" 

GROUP BY x.id) r

on l.urlid = r.id GROUP BY r.id, r.title, l.hourly  order by r.id,  l.hourly, l.errorcode




'DB' 카테고리의 다른 글

[mysql] replication leak  (0) 2014.02.04
[mysql] function 정보  (0) 2014.02.04
[mysql] Tip - group_concat, if/ifnull/case  (0) 2013.12.19
Mysql HA  (0) 2013.04.23
[mysql] "Impossible WHERE noticed after reading const tables"  (0) 2012.06.20
[Mysql] driver버전별 lServer 버전 권장  (0) 2012.04.27
Posted by 김용환 '김용환'

댓글을 달아 주세요