ݺߣ

ݺߣShare a Scribd company logo

????? ??? DB ???? ??? (w/graphite+grafana)

?
?
I Goo Lee
I Goo Lee

MySQL PowerGroup Tech Seminar (2015.2) - 2.????? ??? DB ???? ??? (w/graphite+grafana) (by ???) - URL : cafe.naver.com/mysqlpg

1 of 51
Downloaded 234 times
????? ??? ???? ??? (grafana, graphite) LOEN Entertainment MelOn ???? IT???? ???
?? ???? ?? ??? ?? ??? ?? ? ?? ?? ??? ???!
???? ?? ?? ???? ?? ??? ? ???? ?? ??? ????. ???? ??? ????? ?? ??? ?? ??. Client Agent ??? ?? Server ?? ???? ????. (?? ??) ??? ?? ??? ???? ?????.
??? ?? ?.. http://play.grafana.org/
??? ???? ???? DB? ??? ??? ???? ????? ????? ?? ?? ? ??. ??? ??? Graphite, InfluxDB, OpenTSDB ? ??? ? ??? ???? ?? ????. ?? ?? ?? ??!
??? ??? ??? ??? DB? ??? ?? API? ???? ??? ???? ??. ??? ??? ???? ??? ?? ?? ? ?? ??? ?? ?? ??? ?? ??. ?? ????? ???? ?? ???? ?? ???? ?? ??? ????? ?? ????. ?? ???? ??? ????? ???? ?? ????? ??? ?? ??. Python ?? ????? ???? ??? ?? ??? ????. Metric ?? ? ?? (?? ?? ?? ?? ? ??) Render Graph (??? ?? ?) Carbon Cache Whisper (DB) Graphite Web (API)
3?? Component ? ???? ??. 1. Carbon : ???? ???? ???? ?????? ?? ?? ?? ?? 2. Whisper : ???? ??? ??? ??? ??? ?? DB (RRD? ??) 3. Graphite Web : ???? ???? ??? API ?? ? ?? ???? ??? ?? Django ? ?? Metric ?? ? ?? (?? ?? ?? ?? ? ??) Render Graph (??? ?? ?) Carbon Cache Whisper (DB) Graphite Web (API)
Metric ?? ? ?? (?? ??) Render Graph Architecture Carbon Cache (???) Whisper (DB) Graphite Web (API) graphite_gather_dstat.sh graphite_gather_mariadb.sh graphite_gather_oracle.sh ???? (Alert) ???..
?? ?? ?? ??/?? ???? ELK Stack ???? ??? ??? ?.. ??? ??..
Graphite Installation C 1. dependency # python ? ?? ? ?? $ sudo yum install python # dependency ?? $ wget https://launchpad.net/graphite/0.9/0.9.10/+download/check-dependencies.py $ python check-dependencies.py [FATAL] Unable to import the 'whisper' module, please download this package from the Graphite project page and install it. [FATAL] Unable to import the 'django' module, do you have Django installed for python 2.6.6? [FATAL] Unable to import the 'tagging' module, do you have django-tagging installed for python 2.6.6? [WARNING] Unable to import the 'mod_python' module, do you have mod_python installed for python 2.6.6? mod_python is one of the most common ways to run graphite-web under apache. Without mod_python you will still be able to use the built in development server; which is not recommended for production use. wsgi or other approaches for production scale use are also possible without mod_python [WARNING] Unable to import the 'txamqp' module, this is required if you want to use AMQP. Note that txamqp requires python 2.5 or greater. 3 necessary dependencies not met. Graphite will not function until these dependencies are fulfilled. 2 optional dependencies not met. Please consider the warning messages before proceeding. # dependency ?? ?? $ yum install Django $ yum install Django-tagging $ yum install mod_python $ yum install python-memcached $ yum install python-ldap $ yum install Twisted $ easy_install txamqp $ easy_install whisper # ??? ?? ???? ??? OK $ python check-dependencies.py All necessary dependencies are met. All optional dependencies are met. ?? ??? Oracle Enterprise Linux 6 ???.
Graphite Installation C 2. Download and Installation # ???? $ cd /root/programs $ git clone https://github.com/graphite-project/graphite-web.git $ git clone https://github.com/graphite-project/carbon.git $ git clone https://github.com/graphite-project/whisper.git $ ls -al ?? 2152 drwxr-xr-x 5 root root 4096 2015-08-20 15:50 . dr-xr-x---. 13 root root 4096 2015-08-20 14:33 .. drwxr-xr-x 10 root root 4096 2015-08-20 15:53 carbon -rw-r--r-- 1 root root 42201 2012-06-01 05:38 carbon-0.9.10.tar.gz -rw-r--r-- 1 root root 5196 2012-05-03 23:14 check-dependencies.py drwxr-xr-x 12 root root 4096 2015-08-20 15:54 graphite-web -rw-r--r-- 1 root root 2117421 2012-06-01 05:38 graphite-web-0.9.10.tar.gz drwxr-xr-x 5 root root 4096 2015-08-20 15:50 whisper -rw-r--r-- 1 root root 10661 2012-06-01 05:37 whisper-0.9.10.tar.gz # Default ?? (/opt/graphite) ?? $ cd carbon $ python setup.py install $ cd .. $ $ cd graphite-web $ python setup.py install $ cd .. $ $ cd whisper $ python setup.py install $ cd .. # Custom ?? ?? $ cd carbon $ python setup.py install --prefix=/srv/graphite --install-lib=/srv/graphite/lib $ cd .. $ $ cd graphite-web $ python setup.py install --prefix=/srv/graphite --install-lib=/srv/graphite/webapp $ cd .. ?? ??? ?? DEFAULT ??? ???? ?? ?????.
Graphite Installation C 3. Graphite-web's local_settings.py # ????? ??? ?? ???? ????. $ ls Cl /opt/graphite ?? 28 drwxrwxrwx 2 root root 4096 2015-04-03 20:27 bin drwxrwxrwx 2 root root 4096 2015-04-24 15:30 conf drwxrwxrwx 2 root root 4096 2015-05-12 22:57 examples drwxrwxrwx 4 root root 4096 2015-04-03 20:27 lib drwxrwxrwx 7 root root 4096 2015-05-06 21:05 storage drwxrwxrwx 4 root root 4096 2015-04-03 20:28 webapp $ cd /opt/graphite/webapp/graphite $ cp local_settings.py.example local_settings.py $ vi local_settings.py # local_settings.py ??? ??? DEBUG=True ? ?? ?? ??. # ????? ?? ?? ??? ?? ?? ??? ?? ?? ? ??? ??? ????. GRAPHITE_ROOT = '/opt/graphite' STATIC_ROOT='/opt/graphite/static' CONF_DIR = '/opt/graphite/conf' STORAGE_DIR = '/opt/graphite/storage' STATIC_ROOT = '/opt/graphite/static'
Graphite Installation C 4. Configuring The Webapp # ????? ?????? manage.py ? ????? ?? $ cp /root/programs/graphite-web/webapp/manage.py /opt/graphite/webapp/graphite/ $ cd /opt/graphite/webapp/graphite # manage.py ??? ??? ????? ??? ??? ????. $ vi manage.py $ python manage.py collectstatic /opt/graphite/webapp/graphite/settings.py:233: UserWarning: SECRET_KEY is set to an unsafe default. This should be set in local_settings.py for better security warn('SECRET_KEY is set to an unsafe default. This should be set in local_settings.py for better security') You have requested to collect static files at the destination location as specified in your settings. This will overwrite existing files! Are you sure you want to do this? Type 'yes' to continue, or 'no' to cancel: yes # /opt/graphite ?? static ??? ?? ?? ??? ? ??. #!/usr/bin/env python import os import sys sys.path.append('/opt/graphite/webapp') if __name__ == "__main__": os.environ.setdefault("DJANGO_SETTINGS_MODULE", "graphite.settings") from django.core.management import execute_from_command_line execute_from_command_line(sys.argv)
Graphite Installation C 4. Configuring The Webapp # ??? ? ??? httpd.conf ? ????? ??? ??. (????? /etc/httpd/conf/httpd.conf) # /opt/graphite/examples/example-graphite-vhost.conf ? ??? copy ?? httpd.conf ? ????. # ???? ??? ?? ?? /opt/graphite ??? ??? ??? ?? ?? ??? ??. # ??? ??? ??? ??? ?? $ /etc/init.d/httpd configtest httpd: Syntax error on line 192 of /etc/httpd/conf/httpd.conf: Cannot load /etc/httpd/modules/mod_wsgi.so into server: /etc/httpd/modules/mod_wsgi.so: cannot open shared object file: No such file or directory # mod_wsgi.so ??? ??? ???? ???? ?? ????. $ yum install mod_wsgi $ /etc/init.d/httpd configtest Syntax OK # ??? ??? ? ??? ? ?? tail -f /opt/graphite/storage/log/webapp/error.log ??? ????? ??. $ /etc/init.d/httpd reload # ?? ?? ??? ?? /home/graphite ??? ??? ?? ?? ??? ??? ? ?? ??? ??? ????. $ chmod -R 777 /opt/graphite/ <IfModule !wsgi_module.c> LoadModule wsgi_module modules/mod_wsgi.so </IfModule> WSGISocketPrefix run/wsgi <VirtualHost *:80> ServerName graphite.melon.com DocumentRoot "/opt/graphite/webapp" ErrorLog /opt/graphite/storage/log/webapp/error.log CustomLog /opt/graphite/storage/log/webapp/access.log common WSGIDaemonProcess graphite processes=5 threads=5 display-name='%{GROUP}' inactivity-timeout=120 WSGIProcessGroup graphite WSGIApplicationGroup %{GLOBAL} WSGIImportScript /opt/graphite/conf/graphite.wsgi process-group=graphite application-group=%{GLOBAL} WSGIScriptAlias / /opt/graphite/conf/graphite.wsgi Alias /static/ /opt/graphite/static/ Alias /media/ "@DJANGO_ROOT@/contrib/admin/media/" </VirtualHost>
Graphite Installation C 4. Configuring The Webapp # ???? ??? ?? ?? ??? ??? ????. $ cd /opt/graphite/conf $ cp graphite.wsgi.example graphite.wsgi $ vi graphite.wsgi import sys sys.path.append('/opt/graphite/webapp') from graphite.wsgi import application # Django database ?? $ cd /opt/graphite/webapp/graphite $ python manage.py syncdb Creating tables ... Creating table account_profile Creating table account_variable Creating table account_view Creating table account_window Creating table account_mygraph Creating table dashboard_dashboard_owners Creating table dashboard_dashboard Creating table dashboard_template_owners Creating table dashboard_template Creating table events_event Creating table url_shortener_link Creating table auth_permission Creating table auth_group_permissions Creating table auth_group Creating table auth_user_user_permissions Creating table auth_user_groups Creating table auth_user Creating table django_session Creating table django_admin_log Creating table django_content_type Creating table tagging_tag Creating table tagging_taggeditem You just installed Django's auth system, which means you don't have any superusers defined. Would you like to create one now? (yes/no): yes Username (leave blank to use 'root'): E-mail address: smyoo@iloen.com Password: Password (again): Superuser created successfully. Installing custom SQL ... Installing indexes ... Installed 0 object(s) from 0 fixture(s) $ chown nobody:nobody /opt/graphite/storage/graphite.db # ??? PC? hosts ??? ?? ????. # ??? ?? ???? ????. 192.168.238.11 graphite.melon.com # <????> # Exception Value: No module named pyparsing # ??? ??? ???? ??. $ yum install pyparsing # Exception Value: attempt to write a readonly database # Django? ???? sqlite db? ??? ?? ??? ?? ??? ??? ???? ?? ??? ?? ??? ????. $ chmod 777 /home/graphite/storage/graphite.db
Graphite ?? ??
Configuring Carbon C carbon.conf $ cd /home/graphite/conf $ cp carbon.conf.example carbon.conf # <carbon.conf> #[cache] ??? carbon-cache ?? ???? ???? ???? port ? 2003 ? ?? ????. # LOG_LISTENER_CONN_SUCCESS ? False ? ???? ???? ??? ???? ???? ?? ????. # ???? ??? /opt/graphite/storage/log/carbon-cache/carbon-cache-a/listener.log ? ??? ?? ??? ??? ? ?? ??? ???. LINE_RECEIVER_PORT = 2003 UDP_RECEIVER_PORT = 2003 PICKLE_RECEIVER_PORT = 2004 CACHE_QUERY_PORT = 7002 LOG_LISTENER_CONN_SUCCESS = False 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20612 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20613 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20613 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20614 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20614 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20615 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20615 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20616 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20616 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20619 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20619 closed cleanly LOG_LISTENER_CONN_SUCCESS = False
Configuring Carbon - storage-schemas.conf $ cd /home/graphite/conf $ cp storage-schemas.conf.example storage-schemas.conf # <storage-schemas.conf> # storage-schemas.conf ? ??? ?? ?? ???? ?? ? ??? ??. # 1. ??? ??? ?? ??? ????. # 2. ??? ??? ?? ??? whisper data file (.wsp) ?? ???? ???. ????? whisper-resize.py ? ????. # carbon-cache(??? ???) ?? ??. # carbon.conf ? ??? ?? 2003, 2004 ??? ???? ????. $ cd /opt/graphite/bin $ ./carbon-cache.py start Starting carbon-cache (instance a) $ ps -ef | grep carbon root 13222 1 0 11:50 ? 00:00:00 /usr/bin/python ./carbon-cache.py start root 14068 8773 0 11:50 pts/2 00:00:00 grep carbon [carbon] pattern = ^carbon. ? ??? ?? ??? carbon. ?? ???? ?? (??? ?? ?? ??? ????.) retentions = 60:90d ? ??? ???? 60? ???? ???? ?? 90? ?? ?? [default] pattern = .* retentions = 5s:30d,10s:1y,60s:5y
Getting Your Data Into Graphite Graphite message format C Plaintext protocol ?? (2003 Port) <metric path> <metric value> <metric timestamp> nc (netcat) ???? ???? ?? echo depth1.depth2.depth3.metric_name 55 `date +%s` | nc GRAPHITE_SERVER 2003 Graphite message format C Pickle protocol ?? (2004 Port) [(path, (timestamp, value)), ...] ? ?? ?? ?? ?? ??? ?? ?? Python ??? ? ? ??? ??
Getting Your Data Into Graphite echo `hostname`.db.mariadb.qps.Com_select 55 `date +%s` | nc graphite.melon.com 2003 Send graphite message example [root@mlcp02]# pwd /opt/graphite/storage/whisper/mlcp02/db/mariadb/qps [root@mlcp02]# [root@mlcp02]# ls -al ?? 13252 drwxr-xr-x 2 root root 4096 2015-04-10 23:33 . drwxr-xr-x 4 root root 4096 2015-04-10 23:41 .. -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_delete.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_delete_multi.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_insert.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_insert_select.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_select.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_update.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_update_multi.wsp [root@mlcp02]#
?? ??? ?? ?? ?? ???.
Graphite ???? ??? ??? ?? ?? ???? ???. https://graphite.readthedocs.org/en/latest/tools.html
OS ???? ?? ?? dstat? ???? ? ????? ?? (version 0.7.0 ??) $ cd /root/graphite_gather/ $ cat .env.ini export SLEEP_TIME=59 ## (?? ??? 1?) $ $ vi graphite_gather_dstat.sh # ?????? ?? $ nohup graphite_gather_dstat.sh & $ ps -ef | grep graphite root 50607 45401 0 12:40 pts/1 00:00:00 grep graphite root 64321 1 0 May18 ? 02:33:51 /bin/sh ./graphite_gather_dstat.sh #!/bin/sh . /root/graphite_gather/.env.ini array_name=("cpu_user" "cpu_sys" "cpu_idle" "cpu_wait" "cpu_hiq" "cpu_siq" "mem_used" "mem_buff" "mem_cach" "mem_free" "disk_read" "disk_write" "io_read" "io_write" "net_recv" "net_send" "swap_used" "swap_free" "paging_in" "paging_out" "open_files" "inodes") while(true) do dstat -cmdrnsg --fs --output /root/graphite_gather/dstat.csv 1 1 stat=`cat /root/graphite_gather/dstat.csv | tail -1` array_value=(`echo $stat | tr ',' ' '`) for (( i = 0 ; i < ${#array_name[@]} ; i++ )) ; do echo "`hostname`.os.${array_name[$i]} ${array_value[$i]} `date +%s`" | nc graphite 2003 #echo "`hostname`.os.${array_name[$i]} ${array_value[$i]} `date +%s`" done cpu_idle=`echo $stat | awk -F, '{printf"%d", $3}'` cpu_usage=$((100-$cpu_idle)) echo "`hostname`.os.cpu_usage $cpu_usage `date +%s`" | nc graphite 2003 #echo "`hostname`.os.cpu_usage $cpu_usage `date +%s`" rm -rf /root/graphite_gather/dstat.csv sleep $SLEEP_TIME done
OS ???? ?? ?? $ $ pwd /opt/graphite/storage/whisper/mlcp02/os $ $ $ $ ls -l ?? 47300 -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_hiq.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_idle.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_siq.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_sys.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_user.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_wait.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 disk_read.wsp -rw-r--r-- 1 root root 1935412 2015-04-27 18:29 disk_wirte.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 disk_write.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 inodes.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 io_read.wsp -rw-r--r-- 1 root root 1935412 2015-04-27 18:29 io_wirte.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 io_write.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_buff.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_cach.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_free.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_used.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 net_recv.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 net_send.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 open_files.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 paging_in.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 paging_out.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 swap_free.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 swap_used.wsp $ $
MariaDB(MySQL) ???? ?? ?? (5.5??) $ cd /root/graphite_gather/ $ cat .env.ini export SLEEP_TIME=57 ## (????? 1?) export MYSQL_HOME=/home/mysql/MariaDB # mystat.txt ??? ?? ?? ?? ???? status ?? ?? $ cat mystat.txt Threads_cached Threads_connected Threads_running # mystat_com.txt ? ?? ?? ???? ?? ?? $ cat mystat_com.txt Com_delete Com_insert Com_select Com_update $ vi graphite_gather_mariadb.sh # ?????? ?? $ nohup graphite_gather_mariadb.sh & $ ps -ef | grep graphite_ root 22329 21891 0 13:53 pts/3 00:00:00 grep graphite_ root 45282 1 0 May14 ? 08:41:32 /bin/sh ./graphite_gather_dstat.sh root 62776 1 1 May06 ? 1-18:05:38 /bin/sh ./graphite_gather_mariadb.sh #!/bin/sh . /root/graphite_gather/.env.ini hostname=`hostname` while(true) do mystat= mystat_com= while read line; do mystat=$mystat'|'$line; done < /root/graphite_gather/mystat.txt $MYSQL_HOME/bin/mysqladmin -uroot -p`cat /home/mysql/.mysqlpw.ini` -S /tmp/mysql.sock extended-status | grep -E `echo ${mystat:1}` | tail -`cat /root/graphite_gather/mystat.txt | wc -l` | awk '{print $2 " " $4}' > /root/graphite_gather/mystat_result while read line; do mystat_com=$mystat_com'|'$line; done < /root/graphite_gather/mystat_com.txt $MYSQL_HOME/bin/mysqladmin -uroot -p`cat /home/mysql/.mysqlpw.ini` -S /tmp/mysql.sock extended-status -r -i 1 -c 2 | grep -E `echo ${mystat_com:1}` | tail -`cat /root/graphite_gather/mystat_com.txt | wc -l` | awk '{print $2 " " $4}' >> /root/graphite_gather/mystat_result while read line; do echo "real.${hostname:0:${#hostname}-1}.${hostname:${#hostname}-1:1}.dbstat.$line `date +%s`" | nc graphite 2003; done < /root/graphite_gather/mystat_result #while read line; do echo "real.${hostname:0:${#hostname}-1}.${hostname:${#hostname}-1:1}.dbstat.$line `date +%s`"; done < /root/graphite_gather/mystat_result sleep $SLEEP_TIME done
Oracle ???? ?? ?? (11.2.0.4 ??) # ???? (?? ??? ??? ??) SQL> create table mondba.sysstat as select * from v$sysstat where 1=0; SQL> alter table mondba.sysstat add no number not null; SQL> insert into mondba.sysstat select a.*, 1 from v$sysstat a; SQL> commit; $ vi graphite_gather_oracle.sql set heading off set feedback off set time off timing off set pages 0 col value for 9999999999999999999999999999 insert into mondba.sysstat1 select a.*, (select max(no)+1 from mondba.sysstat1) from v$sysstat a; select replace(replace(replace(name,' ','_'),'(',''),')','')||' '||to_char(value) from ( select pool as name, sum(bytes) as value from v$sgastat where pool is not null group by pool union all select name, bytes as value from v$sgastat where pool is null union all select lower(status)||'_session' as name, count(*) as value from v$session group by status union all select 'data_size' as name, sum(bytes) as value from dba_data_files union all select 'free_size' as name, sum(bytes) as value from dba_free_space union all select b.name, sum(a.value) as value from v$sesstat a, v$statname b where a.statistic#=b.statistic# and b.name in ('session pga memory', 'opened cursors current', 'session uga memory') group by b.name union all select now.name, now.value-bef.value as value from (select name, value from mondba.sysstat1 where no=(select max(no) from mondba.sysstat1)) now, (select name, value from mondba.sysstat1 where no=(select max(no)-1 from mondba.sysstat1)) bef where now.name=bef.name and now.name in ('user calls', 'execute count', 'user commits', 'user rollbacks', 'consistent changes', 'session logical reads', 'db block gets', 'db block changes', /* * ??? ?? ?? ?? * */ 'cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes' ) ); delete from mondba.sysstat1 where no=(select max(no)-2 from mondba.sysstat1); commit;
Oracle ???? ?? ?? (11.2.0.4 ??) $ cat .env.ini export SLEEP_TIME=60 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1 export ORACLE_SID=ORAMP1 # ??? ?? ? $ vi graphite_gather_oracle.sh # ?????? ?? $ chmod 744 graphite_gather_oracle.sh $ $ nohup graphite_gather_oracle.sh & $ $ ps -ef | grep graphite_gather root 16544 29835 0 18:15 pts/7 00:00:00 grep graphite_gather root 27760 1 2 10:54 ? 00:10:39 /bin/sh ./graphite_gather_dstat.sh root 45282 1 0 May14 ? 09:03:38 /bin/sh ./graphite_gather_oracle.sh root 62776 1 1 May06 ? 1-19:46:02 /bin/sh ./graphite_gather_mariadb.sh #!/bin/sh . /root/graphite_gather/.env.ini while(true) do $ORACLE_HOME/bin/sqlplus -s '/as sysdba' < /root/graphite_gather/graphite_gather_oracle.sql > /root/graphite_gather/orastat.txt while read line; do echo "`hostname`.$ORACLE_SID.$line `date +%s`" | nc graphite 2003; done < /root/graphite_gather/orastat.txt #while read line; do echo "`hostname`.$ORACLE_SID.$line `date +%s`"; done < /root/graphite_gather/orastat.txt sleep $SLEEP_TIME done
??? ??? ?????.. ?? ???? ?? ???.
Graphite?? ???? ???? ???? (Poor -_-) Grafana (Awesome!! +_+)
Graphite? ???? ??? ??? ? https://graphite.readthedocs.org/en/latest/tools.html
Grafana Installation http://docs.grafana.org/installation/ Graphite ?? ??? ??? ?? ????.
Grafana Installation
Grafana ?? ??
Grafana Data source ??
Grafana - OS ????
Grafana - MariaDB (MySQL) ??? ????
Grafana - Oracle ???? (1)
Grafana - Oracle ???? (2)
DEMOGrafana Dashboard design (10 min)
Performance Issue ?? ?? ?? 1. Carbon-Cache 2. Whisper file IO (??? ? = ?? ?) ?? ?? ?? 1. Multiple Carbon-Cache (1 CPU Core = 1 Daemon) 2. Clustering 3. SSD 4. InfluxDB
Multiple Carbon-Cache Daemons CLIENT (Send Metric) Render Graph Graphite Webapp (API) carbon-cache (2043)carbon-cache (2023)carbon-cache (2013) carbon-cache (2033) carbon-relay (2003) Whisper (DB)
Graphite Clustering CLIENT (Send Metric) Render Graph Whisper (DB) carbon-relay (2003) carbon-cache (2033)carbon-cache (2023) Graphite Webapp (API) Whisper (DB) carbon-cache (2033)carbon-cache (2023) Graphite Webapp (API) carbon-relay (2013) carbon-relay (2013)
???? ?? ??? ?? ??? ???? ?? ? InfluxDB? ?? ??? ??. ?? graphite ? ??? ???? InfluxDB? ???? Clustering ??? ????. ??? ??? ??? DATABASE GO ??? ?? ? ?? ???? ???? (?? ??? ??? ??) Dependency? ?? ??? ??? SQL-like query language Clustering ??
Graphite Clustering with InfluxDB CLIENT (Send Metric) Render Graph Whisper (DB) carbon-relay-ng (2003) carbon-cache (2033)carbon-cache (2023) Graphite Webapp (API) carbon-relay (2013)
??? DB???? ????
???? ???? ?? ?? ???. ? ? ?? ??? ?? ? ?? ???? ?? ??? ?? ? ???? ???? ???? ?? ?
SE? ???? ?? ?? ???.
???? ???? ??? , SE, DBA ? .. ?? ?? ??? ???? ????? ?????.. ?? ? ?? ?? ? ??? ???. ??? ? ?? ? ?? DevOps ????? ?? ????? ???.
??? ?? ??, ??? ??, ??? ??? ?? ?? ?? ? ??? ??? ????.
Grafana http://grafana.org/ Graphite https://graphite.readthedocs.org/en/latest/ http://www.aosabook.org/en/graphite.html Graphite Clustering https://grey-boundary.io/the-architecture-of-clustering-graphite/ http://dieter.plaetinck.be/post/on-graphite-whisper-and-influxdb/ http://dieter.plaetinck.be/post/influxdb-as-graphite-backend-part2/ Graphite Tuning https://grey-boundary.io/minimizing-datapoint-lag-in-graphite/ Carbon-relay-ng (GitHub) https://github.com/graphite-ng/carbon-relay-ng InfluxDB https://influxdb.com/ ?? ???
?????

More Related Content

????? ??? DB ???? ??? (w/graphite+grafana)

  • 1. ????? ??? ???? ??? (grafana, graphite) LOEN Entertainment MelOn ???? IT???? ???
  • 2. ?? ???? ?? ??? ?? ??? ?? ? ?? ?? ??? ???!
  • 3. ???? ?? ?? ???? ?? ??? ? ???? ?? ??? ????. ???? ??? ????? ?? ??? ?? ??. Client Agent ??? ?? Server ?? ???? ????. (?? ??) ??? ?? ??? ???? ?????.
  • 4. ??? ?? ?.. http://play.grafana.org/
  • 5. ??? ???? ???? DB? ??? ??? ???? ????? ????? ?? ?? ? ??. ??? ??? Graphite, InfluxDB, OpenTSDB ? ??? ? ??? ???? ?? ????. ?? ?? ?? ??!
  • 6. ??? ??? ??? ??? DB? ??? ?? API? ???? ??? ???? ??. ??? ??? ???? ??? ?? ?? ? ?? ??? ?? ?? ??? ?? ??. ?? ????? ???? ?? ???? ?? ???? ?? ??? ????? ?? ????. ?? ???? ??? ????? ???? ?? ????? ??? ?? ??. Python ?? ????? ???? ??? ?? ??? ????. Metric ?? ? ?? (?? ?? ?? ?? ? ??) Render Graph (??? ?? ?) Carbon Cache Whisper (DB) Graphite Web (API)
  • 7. 3?? Component ? ???? ??. 1. Carbon : ???? ???? ???? ?????? ?? ?? ?? ?? 2. Whisper : ???? ??? ??? ??? ??? ?? DB (RRD? ??) 3. Graphite Web : ???? ???? ??? API ?? ? ?? ???? ??? ?? Django ? ?? Metric ?? ? ?? (?? ?? ?? ?? ? ??) Render Graph (??? ?? ?) Carbon Cache Whisper (DB) Graphite Web (API)
  • 8. Metric ?? ? ?? (?? ??) Render Graph Architecture Carbon Cache (???) Whisper (DB) Graphite Web (API) graphite_gather_dstat.sh graphite_gather_mariadb.sh graphite_gather_oracle.sh ???? (Alert) ???..
  • 9. ?? ?? ?? ??/?? ???? ELK Stack ???? ??? ??? ?.. ??? ??..
  • 10. Graphite Installation C 1. dependency # python ? ?? ? ?? $ sudo yum install python # dependency ?? $ wget https://launchpad.net/graphite/0.9/0.9.10/+download/check-dependencies.py $ python check-dependencies.py [FATAL] Unable to import the 'whisper' module, please download this package from the Graphite project page and install it. [FATAL] Unable to import the 'django' module, do you have Django installed for python 2.6.6? [FATAL] Unable to import the 'tagging' module, do you have django-tagging installed for python 2.6.6? [WARNING] Unable to import the 'mod_python' module, do you have mod_python installed for python 2.6.6? mod_python is one of the most common ways to run graphite-web under apache. Without mod_python you will still be able to use the built in development server; which is not recommended for production use. wsgi or other approaches for production scale use are also possible without mod_python [WARNING] Unable to import the 'txamqp' module, this is required if you want to use AMQP. Note that txamqp requires python 2.5 or greater. 3 necessary dependencies not met. Graphite will not function until these dependencies are fulfilled. 2 optional dependencies not met. Please consider the warning messages before proceeding. # dependency ?? ?? $ yum install Django $ yum install Django-tagging $ yum install mod_python $ yum install python-memcached $ yum install python-ldap $ yum install Twisted $ easy_install txamqp $ easy_install whisper # ??? ?? ???? ??? OK $ python check-dependencies.py All necessary dependencies are met. All optional dependencies are met. ?? ??? Oracle Enterprise Linux 6 ???.
  • 11. Graphite Installation C 2. Download and Installation # ???? $ cd /root/programs $ git clone https://github.com/graphite-project/graphite-web.git $ git clone https://github.com/graphite-project/carbon.git $ git clone https://github.com/graphite-project/whisper.git $ ls -al ?? 2152 drwxr-xr-x 5 root root 4096 2015-08-20 15:50 . dr-xr-x---. 13 root root 4096 2015-08-20 14:33 .. drwxr-xr-x 10 root root 4096 2015-08-20 15:53 carbon -rw-r--r-- 1 root root 42201 2012-06-01 05:38 carbon-0.9.10.tar.gz -rw-r--r-- 1 root root 5196 2012-05-03 23:14 check-dependencies.py drwxr-xr-x 12 root root 4096 2015-08-20 15:54 graphite-web -rw-r--r-- 1 root root 2117421 2012-06-01 05:38 graphite-web-0.9.10.tar.gz drwxr-xr-x 5 root root 4096 2015-08-20 15:50 whisper -rw-r--r-- 1 root root 10661 2012-06-01 05:37 whisper-0.9.10.tar.gz # Default ?? (/opt/graphite) ?? $ cd carbon $ python setup.py install $ cd .. $ $ cd graphite-web $ python setup.py install $ cd .. $ $ cd whisper $ python setup.py install $ cd .. # Custom ?? ?? $ cd carbon $ python setup.py install --prefix=/srv/graphite --install-lib=/srv/graphite/lib $ cd .. $ $ cd graphite-web $ python setup.py install --prefix=/srv/graphite --install-lib=/srv/graphite/webapp $ cd .. ?? ??? ?? DEFAULT ??? ???? ?? ?????.
  • 12. Graphite Installation C 3. Graphite-web's local_settings.py # ????? ??? ?? ???? ????. $ ls Cl /opt/graphite ?? 28 drwxrwxrwx 2 root root 4096 2015-04-03 20:27 bin drwxrwxrwx 2 root root 4096 2015-04-24 15:30 conf drwxrwxrwx 2 root root 4096 2015-05-12 22:57 examples drwxrwxrwx 4 root root 4096 2015-04-03 20:27 lib drwxrwxrwx 7 root root 4096 2015-05-06 21:05 storage drwxrwxrwx 4 root root 4096 2015-04-03 20:28 webapp $ cd /opt/graphite/webapp/graphite $ cp local_settings.py.example local_settings.py $ vi local_settings.py # local_settings.py ??? ??? DEBUG=True ? ?? ?? ??. # ????? ?? ?? ??? ?? ?? ??? ?? ?? ? ??? ??? ????. GRAPHITE_ROOT = '/opt/graphite' STATIC_ROOT='/opt/graphite/static' CONF_DIR = '/opt/graphite/conf' STORAGE_DIR = '/opt/graphite/storage' STATIC_ROOT = '/opt/graphite/static'
  • 13. Graphite Installation C 4. Configuring The Webapp # ????? ?????? manage.py ? ????? ?? $ cp /root/programs/graphite-web/webapp/manage.py /opt/graphite/webapp/graphite/ $ cd /opt/graphite/webapp/graphite # manage.py ??? ??? ????? ??? ??? ????. $ vi manage.py $ python manage.py collectstatic /opt/graphite/webapp/graphite/settings.py:233: UserWarning: SECRET_KEY is set to an unsafe default. This should be set in local_settings.py for better security warn('SECRET_KEY is set to an unsafe default. This should be set in local_settings.py for better security') You have requested to collect static files at the destination location as specified in your settings. This will overwrite existing files! Are you sure you want to do this? Type 'yes' to continue, or 'no' to cancel: yes # /opt/graphite ?? static ??? ?? ?? ??? ? ??. #!/usr/bin/env python import os import sys sys.path.append('/opt/graphite/webapp') if __name__ == "__main__": os.environ.setdefault("DJANGO_SETTINGS_MODULE", "graphite.settings") from django.core.management import execute_from_command_line execute_from_command_line(sys.argv)
  • 14. Graphite Installation C 4. Configuring The Webapp # ??? ? ??? httpd.conf ? ????? ??? ??. (????? /etc/httpd/conf/httpd.conf) # /opt/graphite/examples/example-graphite-vhost.conf ? ??? copy ?? httpd.conf ? ????. # ???? ??? ?? ?? /opt/graphite ??? ??? ??? ?? ?? ??? ??. # ??? ??? ??? ??? ?? $ /etc/init.d/httpd configtest httpd: Syntax error on line 192 of /etc/httpd/conf/httpd.conf: Cannot load /etc/httpd/modules/mod_wsgi.so into server: /etc/httpd/modules/mod_wsgi.so: cannot open shared object file: No such file or directory # mod_wsgi.so ??? ??? ???? ???? ?? ????. $ yum install mod_wsgi $ /etc/init.d/httpd configtest Syntax OK # ??? ??? ? ??? ? ?? tail -f /opt/graphite/storage/log/webapp/error.log ??? ????? ??. $ /etc/init.d/httpd reload # ?? ?? ??? ?? /home/graphite ??? ??? ?? ?? ??? ??? ? ?? ??? ??? ????. $ chmod -R 777 /opt/graphite/ <IfModule !wsgi_module.c> LoadModule wsgi_module modules/mod_wsgi.so </IfModule> WSGISocketPrefix run/wsgi <VirtualHost *:80> ServerName graphite.melon.com DocumentRoot "/opt/graphite/webapp" ErrorLog /opt/graphite/storage/log/webapp/error.log CustomLog /opt/graphite/storage/log/webapp/access.log common WSGIDaemonProcess graphite processes=5 threads=5 display-name='%{GROUP}' inactivity-timeout=120 WSGIProcessGroup graphite WSGIApplicationGroup %{GLOBAL} WSGIImportScript /opt/graphite/conf/graphite.wsgi process-group=graphite application-group=%{GLOBAL} WSGIScriptAlias / /opt/graphite/conf/graphite.wsgi Alias /static/ /opt/graphite/static/ Alias /media/ "@DJANGO_ROOT@/contrib/admin/media/" </VirtualHost>
  • 15. Graphite Installation C 4. Configuring The Webapp # ???? ??? ?? ?? ??? ??? ????. $ cd /opt/graphite/conf $ cp graphite.wsgi.example graphite.wsgi $ vi graphite.wsgi import sys sys.path.append('/opt/graphite/webapp') from graphite.wsgi import application # Django database ?? $ cd /opt/graphite/webapp/graphite $ python manage.py syncdb Creating tables ... Creating table account_profile Creating table account_variable Creating table account_view Creating table account_window Creating table account_mygraph Creating table dashboard_dashboard_owners Creating table dashboard_dashboard Creating table dashboard_template_owners Creating table dashboard_template Creating table events_event Creating table url_shortener_link Creating table auth_permission Creating table auth_group_permissions Creating table auth_group Creating table auth_user_user_permissions Creating table auth_user_groups Creating table auth_user Creating table django_session Creating table django_admin_log Creating table django_content_type Creating table tagging_tag Creating table tagging_taggeditem You just installed Django's auth system, which means you don't have any superusers defined. Would you like to create one now? (yes/no): yes Username (leave blank to use 'root'): E-mail address: smyoo@iloen.com Password: Password (again): Superuser created successfully. Installing custom SQL ... Installing indexes ... Installed 0 object(s) from 0 fixture(s) $ chown nobody:nobody /opt/graphite/storage/graphite.db # ??? PC? hosts ??? ?? ????. # ??? ?? ???? ????. 192.168.238.11 graphite.melon.com # <????> # Exception Value: No module named pyparsing # ??? ??? ???? ??. $ yum install pyparsing # Exception Value: attempt to write a readonly database # Django? ???? sqlite db? ??? ?? ??? ?? ??? ??? ???? ?? ??? ?? ??? ????. $ chmod 777 /home/graphite/storage/graphite.db
  • 17. Configuring Carbon C carbon.conf $ cd /home/graphite/conf $ cp carbon.conf.example carbon.conf # <carbon.conf> #[cache] ??? carbon-cache ?? ???? ???? ???? port ? 2003 ? ?? ????. # LOG_LISTENER_CONN_SUCCESS ? False ? ???? ???? ??? ???? ???? ?? ????. # ???? ??? /opt/graphite/storage/log/carbon-cache/carbon-cache-a/listener.log ? ??? ?? ??? ??? ? ?? ??? ???. LINE_RECEIVER_PORT = 2003 UDP_RECEIVER_PORT = 2003 PICKLE_RECEIVER_PORT = 2004 CACHE_QUERY_PORT = 7002 LOG_LISTENER_CONN_SUCCESS = False 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20612 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20613 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20613 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20614 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20614 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20615 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20615 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20616 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20616 closed cleanly 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20619 established 15/04/2015 14:56:01 :: MetricLineReceiver connection with 192.168.238.100:20619 closed cleanly LOG_LISTENER_CONN_SUCCESS = False
  • 18. Configuring Carbon - storage-schemas.conf $ cd /home/graphite/conf $ cp storage-schemas.conf.example storage-schemas.conf # <storage-schemas.conf> # storage-schemas.conf ? ??? ?? ?? ???? ?? ? ??? ??. # 1. ??? ??? ?? ??? ????. # 2. ??? ??? ?? ??? whisper data file (.wsp) ?? ???? ???. ????? whisper-resize.py ? ????. # carbon-cache(??? ???) ?? ??. # carbon.conf ? ??? ?? 2003, 2004 ??? ???? ????. $ cd /opt/graphite/bin $ ./carbon-cache.py start Starting carbon-cache (instance a) $ ps -ef | grep carbon root 13222 1 0 11:50 ? 00:00:00 /usr/bin/python ./carbon-cache.py start root 14068 8773 0 11:50 pts/2 00:00:00 grep carbon [carbon] pattern = ^carbon. ? ??? ?? ??? carbon. ?? ???? ?? (??? ?? ?? ??? ????.) retentions = 60:90d ? ??? ???? 60? ???? ???? ?? 90? ?? ?? [default] pattern = .* retentions = 5s:30d,10s:1y,60s:5y
  • 19. Getting Your Data Into Graphite Graphite message format C Plaintext protocol ?? (2003 Port) <metric path> <metric value> <metric timestamp> nc (netcat) ???? ???? ?? echo depth1.depth2.depth3.metric_name 55 `date +%s` | nc GRAPHITE_SERVER 2003 Graphite message format C Pickle protocol ?? (2004 Port) [(path, (timestamp, value)), ...] ? ?? ?? ?? ?? ??? ?? ?? Python ??? ? ? ??? ??
  • 20. Getting Your Data Into Graphite echo `hostname`.db.mariadb.qps.Com_select 55 `date +%s` | nc graphite.melon.com 2003 Send graphite message example [root@mlcp02]# pwd /opt/graphite/storage/whisper/mlcp02/db/mariadb/qps [root@mlcp02]# [root@mlcp02]# ls -al ?? 13252 drwxr-xr-x 2 root root 4096 2015-04-10 23:33 . drwxr-xr-x 4 root root 4096 2015-04-10 23:41 .. -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_delete.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_delete_multi.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_insert.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_insert_select.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_select.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_update.wsp -rw-r--r-- 1 root root 1935412 2015-04-10 23:36 Com_update_multi.wsp [root@mlcp02]#
  • 21. ?? ??? ?? ?? ?? ???.
  • 22. Graphite ???? ??? ??? ?? ?? ???? ???. https://graphite.readthedocs.org/en/latest/tools.html
  • 23. OS ???? ?? ?? dstat? ???? ? ????? ?? (version 0.7.0 ??) $ cd /root/graphite_gather/ $ cat .env.ini export SLEEP_TIME=59 ## (?? ??? 1?) $ $ vi graphite_gather_dstat.sh # ?????? ?? $ nohup graphite_gather_dstat.sh & $ ps -ef | grep graphite root 50607 45401 0 12:40 pts/1 00:00:00 grep graphite root 64321 1 0 May18 ? 02:33:51 /bin/sh ./graphite_gather_dstat.sh #!/bin/sh . /root/graphite_gather/.env.ini array_name=("cpu_user" "cpu_sys" "cpu_idle" "cpu_wait" "cpu_hiq" "cpu_siq" "mem_used" "mem_buff" "mem_cach" "mem_free" "disk_read" "disk_write" "io_read" "io_write" "net_recv" "net_send" "swap_used" "swap_free" "paging_in" "paging_out" "open_files" "inodes") while(true) do dstat -cmdrnsg --fs --output /root/graphite_gather/dstat.csv 1 1 stat=`cat /root/graphite_gather/dstat.csv | tail -1` array_value=(`echo $stat | tr ',' ' '`) for (( i = 0 ; i < ${#array_name[@]} ; i++ )) ; do echo "`hostname`.os.${array_name[$i]} ${array_value[$i]} `date +%s`" | nc graphite 2003 #echo "`hostname`.os.${array_name[$i]} ${array_value[$i]} `date +%s`" done cpu_idle=`echo $stat | awk -F, '{printf"%d", $3}'` cpu_usage=$((100-$cpu_idle)) echo "`hostname`.os.cpu_usage $cpu_usage `date +%s`" | nc graphite 2003 #echo "`hostname`.os.cpu_usage $cpu_usage `date +%s`" rm -rf /root/graphite_gather/dstat.csv sleep $SLEEP_TIME done
  • 24. OS ???? ?? ?? $ $ pwd /opt/graphite/storage/whisper/mlcp02/os $ $ $ $ ls -l ?? 47300 -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_hiq.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_idle.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_siq.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_sys.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_user.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 cpu_wait.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 disk_read.wsp -rw-r--r-- 1 root root 1935412 2015-04-27 18:29 disk_wirte.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 disk_write.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 inodes.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 io_read.wsp -rw-r--r-- 1 root root 1935412 2015-04-27 18:29 io_wirte.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 io_write.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_buff.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_cach.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_free.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 mem_used.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 net_recv.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 net_send.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 open_files.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 paging_in.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 paging_out.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 swap_free.wsp -rw-r--r-- 1 root root 1935412 2015-08-25 10:58 swap_used.wsp $ $
  • 25. MariaDB(MySQL) ???? ?? ?? (5.5??) $ cd /root/graphite_gather/ $ cat .env.ini export SLEEP_TIME=57 ## (????? 1?) export MYSQL_HOME=/home/mysql/MariaDB # mystat.txt ??? ?? ?? ?? ???? status ?? ?? $ cat mystat.txt Threads_cached Threads_connected Threads_running # mystat_com.txt ? ?? ?? ???? ?? ?? $ cat mystat_com.txt Com_delete Com_insert Com_select Com_update $ vi graphite_gather_mariadb.sh # ?????? ?? $ nohup graphite_gather_mariadb.sh & $ ps -ef | grep graphite_ root 22329 21891 0 13:53 pts/3 00:00:00 grep graphite_ root 45282 1 0 May14 ? 08:41:32 /bin/sh ./graphite_gather_dstat.sh root 62776 1 1 May06 ? 1-18:05:38 /bin/sh ./graphite_gather_mariadb.sh #!/bin/sh . /root/graphite_gather/.env.ini hostname=`hostname` while(true) do mystat= mystat_com= while read line; do mystat=$mystat'|'$line; done < /root/graphite_gather/mystat.txt $MYSQL_HOME/bin/mysqladmin -uroot -p`cat /home/mysql/.mysqlpw.ini` -S /tmp/mysql.sock extended-status | grep -E `echo ${mystat:1}` | tail -`cat /root/graphite_gather/mystat.txt | wc -l` | awk '{print $2 " " $4}' > /root/graphite_gather/mystat_result while read line; do mystat_com=$mystat_com'|'$line; done < /root/graphite_gather/mystat_com.txt $MYSQL_HOME/bin/mysqladmin -uroot -p`cat /home/mysql/.mysqlpw.ini` -S /tmp/mysql.sock extended-status -r -i 1 -c 2 | grep -E `echo ${mystat_com:1}` | tail -`cat /root/graphite_gather/mystat_com.txt | wc -l` | awk '{print $2 " " $4}' >> /root/graphite_gather/mystat_result while read line; do echo "real.${hostname:0:${#hostname}-1}.${hostname:${#hostname}-1:1}.dbstat.$line `date +%s`" | nc graphite 2003; done < /root/graphite_gather/mystat_result #while read line; do echo "real.${hostname:0:${#hostname}-1}.${hostname:${#hostname}-1:1}.dbstat.$line `date +%s`"; done < /root/graphite_gather/mystat_result sleep $SLEEP_TIME done
  • 26. Oracle ???? ?? ?? (11.2.0.4 ??) # ???? (?? ??? ??? ??) SQL> create table mondba.sysstat as select * from v$sysstat where 1=0; SQL> alter table mondba.sysstat add no number not null; SQL> insert into mondba.sysstat select a.*, 1 from v$sysstat a; SQL> commit; $ vi graphite_gather_oracle.sql set heading off set feedback off set time off timing off set pages 0 col value for 9999999999999999999999999999 insert into mondba.sysstat1 select a.*, (select max(no)+1 from mondba.sysstat1) from v$sysstat a; select replace(replace(replace(name,' ','_'),'(',''),')','')||' '||to_char(value) from ( select pool as name, sum(bytes) as value from v$sgastat where pool is not null group by pool union all select name, bytes as value from v$sgastat where pool is null union all select lower(status)||'_session' as name, count(*) as value from v$session group by status union all select 'data_size' as name, sum(bytes) as value from dba_data_files union all select 'free_size' as name, sum(bytes) as value from dba_free_space union all select b.name, sum(a.value) as value from v$sesstat a, v$statname b where a.statistic#=b.statistic# and b.name in ('session pga memory', 'opened cursors current', 'session uga memory') group by b.name union all select now.name, now.value-bef.value as value from (select name, value from mondba.sysstat1 where no=(select max(no) from mondba.sysstat1)) now, (select name, value from mondba.sysstat1 where no=(select max(no)-1 from mondba.sysstat1)) bef where now.name=bef.name and now.name in ('user calls', 'execute count', 'user commits', 'user rollbacks', 'consistent changes', 'session logical reads', 'db block gets', 'db block changes', /* * ??? ?? ?? ?? * */ 'cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes' ) ); delete from mondba.sysstat1 where no=(select max(no)-2 from mondba.sysstat1); commit;
  • 27. Oracle ???? ?? ?? (11.2.0.4 ??) $ cat .env.ini export SLEEP_TIME=60 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1 export ORACLE_SID=ORAMP1 # ??? ?? ? $ vi graphite_gather_oracle.sh # ?????? ?? $ chmod 744 graphite_gather_oracle.sh $ $ nohup graphite_gather_oracle.sh & $ $ ps -ef | grep graphite_gather root 16544 29835 0 18:15 pts/7 00:00:00 grep graphite_gather root 27760 1 2 10:54 ? 00:10:39 /bin/sh ./graphite_gather_dstat.sh root 45282 1 0 May14 ? 09:03:38 /bin/sh ./graphite_gather_oracle.sh root 62776 1 1 May06 ? 1-19:46:02 /bin/sh ./graphite_gather_mariadb.sh #!/bin/sh . /root/graphite_gather/.env.ini while(true) do $ORACLE_HOME/bin/sqlplus -s '/as sysdba' < /root/graphite_gather/graphite_gather_oracle.sql > /root/graphite_gather/orastat.txt while read line; do echo "`hostname`.$ORACLE_SID.$line `date +%s`" | nc graphite 2003; done < /root/graphite_gather/orastat.txt #while read line; do echo "`hostname`.$ORACLE_SID.$line `date +%s`"; done < /root/graphite_gather/orastat.txt sleep $SLEEP_TIME done
  • 28. ??? ??? ?????.. ?? ???? ?? ???.
  • 29. Graphite?? ???? ???? ???? (Poor -_-) Grafana (Awesome!! +_+)
  • 30. Graphite? ???? ??? ??? ? https://graphite.readthedocs.org/en/latest/tools.html
  • 35. Grafana - OS ????
  • 36. Grafana - MariaDB (MySQL) ??? ????
  • 37. Grafana - Oracle ???? (1)
  • 38. Grafana - Oracle ???? (2)
  • 40. Performance Issue ?? ?? ?? 1. Carbon-Cache 2. Whisper file IO (??? ? = ?? ?) ?? ?? ?? 1. Multiple Carbon-Cache (1 CPU Core = 1 Daemon) 2. Clustering 3. SSD 4. InfluxDB
  • 41. Multiple Carbon-Cache Daemons CLIENT (Send Metric) Render Graph Graphite Webapp (API) carbon-cache (2043)carbon-cache (2023)carbon-cache (2013) carbon-cache (2033) carbon-relay (2003) Whisper (DB)
  • 42. Graphite Clustering CLIENT (Send Metric) Render Graph Whisper (DB) carbon-relay (2003) carbon-cache (2033)carbon-cache (2023) Graphite Webapp (API) Whisper (DB) carbon-cache (2033)carbon-cache (2023) Graphite Webapp (API) carbon-relay (2013) carbon-relay (2013)
  • 43. ???? ?? ??? ?? ??? ???? ?? ? InfluxDB? ?? ??? ??. ?? graphite ? ??? ???? InfluxDB? ???? Clustering ??? ????. ??? ??? ??? DATABASE GO ??? ?? ? ?? ???? ???? (?? ??? ??? ??) Dependency? ?? ??? ??? SQL-like query language Clustering ??
  • 44. Graphite Clustering with InfluxDB CLIENT (Send Metric) Render Graph Whisper (DB) carbon-relay-ng (2003) carbon-cache (2033)carbon-cache (2023) Graphite Webapp (API) carbon-relay (2013)
  • 46. ???? ???? ?? ?? ???. ? ? ?? ??? ?? ? ?? ???? ?? ??? ?? ? ???? ???? ???? ?? ?
  • 47. SE? ???? ?? ?? ???.
  • 48. ???? ???? ??? , SE, DBA ? .. ?? ?? ??? ???? ????? ?????.. ?? ? ?? ?? ? ??? ???. ??? ? ?? ? ?? DevOps ????? ?? ????? ???.
  • 49. ??? ?? ??, ??? ??, ??? ??? ?? ?? ?? ? ??? ??? ????.
  • 51. ?????