My Octopress Blog

A blogging framework for hackers.

Mysql-profiler

| Comments

CentOS 5 - oprofiler

1
2
3
4

  yum install oprofiler
# run program
  opcontrol --stop;  opcontrol --reset; opcontrol --callgraph=8; opcontrol --start; \
    opcontrol --status; sleep 5; opcontrol --stop; opreport -l | less

example report

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

opreport -l --threshold 1
warning: /no-vmlinux could not be found.
CPU: Intel Core/i7, speed 2128.05 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 1000000
samples  %        app name                 symbol name
16787     6.6447  mysqld                   ut_delay
15902     6.2944  no-vmlinux               /no-vmlinux
14975     5.9275  mysqld                   insert_events_waits_history_long(PFS_events_waits*)
9605      3.8019  mysqld                   get_thread_mutex_locker_v1
8388      3.3202  libc-2.5.so              memcpy
8308      3.2885  mysqld                   buf_page_get_gen
7220      2.8579  mysqld                   rec_get_offsets_func
6662      2.6370  mysqld                   mutex_spin_wait
6409      2.5369  libc-2.5.so              _int_malloc
5714      2.2618  mysqld                   cmp_dtuple_rec_with_match
5173      2.0476  mysqld                   end_mutex_wait_v1
4083      1.6162  mysqld                   pfs_mutex_enter_func
3339      1.3217  mysqld                   btr_cur_search_to_nth_level
3268      1.2936  mysqld                   get_thread_rwlock_locker_v1
2961      1.1720  mysqld                   page_cur_search_with_match
2761      1.0929  mysqld                   sp_instr_stmt::execute(THD*, unsigned int*)
2647      1.0478  mysqld                   mtr_commit
  • http://heeen.de/blog/2011/06/how-to-generate-fancy-callgraphs-from-oprofile.html
  • Profiling CPU on Linux with oprofile http://www.performancewiki.com/oprofile.html
  • http://people.redhat.com/wcohen/
  • http://people.redhat.com/wcohen/Fedora12OprofileTutorial.txt
  • http://www.centos.org/docs/5/html/Deployment_Guide-en-US/ch-oprofile.html
  • http://www.pixelbeat.org/programming/profiling/
  • http://kcachegrind.sourceforge.net/html/Home.html
  • http://www.ibm.com/developerworks/library/l-gnuprof.html
  • https://bugs.archlinux.org/task/17122
1
2

for o in 0 1 2 3; do od -A d -t x1 -w4 -j$o /boot/vmlinuz | grep "1f 8b 08 00"; done
dd if=/boot/vmlinuz bs=1 skip=_the_offset_returned_by_od_| zcat > vmlinux

Archlinux - pref

Install - sudo pacman -S perf, Run - sudo perf top или sudo perf top -K -p 8774

other tools

vmstat

1
2
3
4
5
6
7

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free    buff  cache   si   so    bi    bo   in   cs   us sy id wa

 0  0 2119940 1414648  17980 754320    0    0     0     0  526  878   4  2 95  0
 1  0 2119932 1407440  17988 761436   44    0  4312    96  641 12503  9  7 83  2
 1  0 2119932 1408252  17988 760236    0    0     0     0  830 77192 22 34 44  0
 2  0 2119932 1408308  17988 760228    0    0     0     0  765 79648 22 33 45  0

gdb – http://poormansprofiler.org/

Масштабирование mysql

  1. мастер вставляет задания в таблицу-очередь

  2. скрипт смотрит и выполняет на каждом слейве можно управлять какие слейвы должны выпонлять задания http://www.slideshare.net/highload/scaling-web-sites-by-sharding-and-replication-hl2008-rus-presentation 13 слайд

  3. Database Setup 4: Vertical Partitioning / Replication Tree

  4. http://www.insight-it.ru/masshtabiruemost/segmentirovanie-bazy-dannykh/

  5. https://code.google.com/p/vitess/

Репликация однопоточна и происходит недостаточно быстро.
Читать данные с мастера при Master/Slave репликации = медленная смерть
http://www.insight-it.ru/masshtabiruemost/arkhitektura-twitter-dva-goda-spustya/


http://www.insight-it.ru/goto/https://github.com/twitter/flockdb


http://www.insight-it.ru/masshtabiruemost/10-izvestnykh-masshtabiruemykh-arkhitekturnykh-shablonov/


http://habrahabr.ru/post/117453/


https://cs.dx.com/Ticket/LoginCheckReply?FormKey=C352F748341B4AF394D6CFD718D8A62E

http://yoshinorimatsunobu.blogspot.ru/2010/10/using-mysql-as-nosql-story-for.html

https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/configuration-options.en.txt

http://poormansprofiler.org/

http://dev.mysql.com/doc/refman/5.0/en/compile-and-link-options.html
For a connection from a client to a server running on the same host, if you connect using TCP/IP rather than a Unix socket file, performance is 7.5% slower. (On Unix, if you connect to the host name localhost, MySQL uses a socket file by default.)


iostat disk utilization


LongAdder jsr166
AtomicLong[Random.nextInteger(count)].inc();

tryLock -> lock()

Производительность

http performance

httperf --hog --server=192.168.1.100 --wsess=100000,47,0 --rate 192

https://calomel.org/nginx.html

mysql as nosql

Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server

Comments