How to get memory size used for a query in mysql?

Question

THe reason I am asking this question is that I want to make sure if some of my queries will use more memory than that of query_cache_limit. Some people suggusted using 'show table status like 'table_name'. I think it is not relevant to this question. Thanks.

when you say memory sized used, in my answer i assume you mean the impact on the server for poorly performing queries you want to examine. if it is just the size of the result set or MUCH higher level, ignore my answer by all means — Drew, May 15 '13 at 21:48

score 1 · Accepted Answer · answered May 15 '13 at 21:24

1

The size of the result set is not available in stock MySQL.

Percona Server does support this information, by adding a field to its extended slow-query log format. Look in the "Bytes_sent" field in this log.

See http://www.percona.com/doc/percona-server/5.5/diagnostics/slow_extended_55.html#memory-footprint

answered May 15 '13 at 21:24

Bill Karwin

538,548
86
673
828

Thanks Bill, but how does MySql judge if the result set is over a limit? There should be some way to do the work. – user1342336 May 16 '13 at 01:05
Yes, but not all internal measurements are exposed to the user. – Bill Karwin May 16 '13 at 01:10

score 1 · Answer 2 · answered May 15 '13 at 21:42

note this is not for the faint of heart. and do it on another box, not production !

stop mysql and start it under the view of valgrind.

do your thing for a while. for instance run a quick php that uses your routine/query.

maybe capture 20 seconds of data usage at most.

then stop mysql via same stop below.

then examine the log file in the /tmp dir ... examine data by timestamps

service mysql stop
apt-get install valgrind
valgrind --tool=massif --massif-out-file=/tmp/massif.out /etc/init.d/mysql start



    #-----------
    snapshot=32
    #-----------
    time=217073
    mem_heap_B=1140
    mem_heap_extra_B=324
    mem_stacks_B=0
    heap_tree=empty
    #-----------
    snapshot=33
    #-----------
    time=226099
    mem_heap_B=1156
    mem_heap_extra_B=332
    mem_stacks_B=0
    heap_tree=detailed
    n3: 1156 (heap allocation functions) malloc/new/new[], --alloc-fns, etc.
     n5: 944 0x40AA47: ??? (in /bin/dash)
      n3: 832 0x41184C: ??? (in /bin/dash)
       n1: 704 0x4120F5: ??? (in /bin/dash)
        n1: 704 0x4020E4: ??? (in /bin/dash)
         n0: 704 0x4E4E76B: (below main) (libc-start.c:226)
       n1: 96 0x411EC0: ??? (in /bin/dash)
        n1: 96 0x4048E3: ??? (in /bin/dash)
         n1: 96 0x403BD5: ??? (in /bin/dash)
          n1: 96 0x40A88C: ??? (in /bin/dash)
           n1: 96 0x402159: ??? (in /bin/dash)
            n0: 96 0x4E4E76B: (below main) (libc-start.c:226)
       n1: 32 0x41212F: ??? (in /bin/dash)
        n1: 32 0x4020E4: ??? (in /bin/dash)
         n0: 32 0x4E4E76B: (below main) (libc-start.c:226)

How to get memory size used for a query in mysql?

2 Answers2