Ad Widget

Collapse

Average value lenght in database. Let's share statistics.

Collapse
X
Collapse
 
  • Page of 2
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
  • orxlnk
    Junior Member
    • Mar 2013
    • 4
    #16
    DB:
    Code:
    +--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history_uint |     947773 |             52 |                   86.4688 |    49889280 |     32063488 | 796917760 |                838.1563 | 2013-03-12 18:06:46 |
| history_text |     262761 |             53 |                  129.8818 |    14172160 |     19955712 |   4194304 |                 36.5469 | 2013-03-12 18:06:46 |
| history      |       9656 |            164 |                  201.9155 |     1589248 |       360448 |  25165824 |                 25.8594 | 2013-03-12 18:06:46 |
| trends_uint  |      13233 |            183 |                  183.2413 |     2424832 |            0 |   4194304 |                  6.3125 | 2013-03-12 18:06:46 |
| history_str  |        517 |            126 |                  158.4526 |       65536 |        16384 |   5242880 |                  5.0781 | 2013-03-12 18:06:46 |
| events       |       2839 |             80 |                  155.8182 |      229376 |       212992 |         0 |                  0.4219 | 2013-03-12 18:06:46 |
| history_log  |         70 |            936 |                 1404.3429 |       65536 |        32768 |         0 |                  0.0938 | 2013-03-12 18:06:46 |
| trends       |        379 |            129 |                  129.6887 |       49152 |            0 |         0 |                  0.0469 | 2013-03-12 18:06:46 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
8 rows in set (0.16 sec)

mysql>
    Files:
    Code:
    ls -lhS /var/lib/mysql/data/zabbix| head -n 15
total 974M
-rw-rw---- 1 mysql mysql 852M Mar 14 09:43 history_uint.ibd
-rw-rw---- 1 mysql mysql  40M Mar 14 09:43 history_text.ibd
-rw-rw---- 1 mysql mysql  29M Mar 14 09:43 history.ibd
-rw-rw---- 1 mysql mysql  10M Mar 14 09:43 items.ibd
-rw-rw---- 1 mysql mysql  10M Mar 14 09:14 trends_uint.ibd
-rw-rw---- 1 mysql mysql 9,0M Mar 14 09:43 history_str.ibd
-rw-rw---- 1 mysql mysql 9,0M Mar 12 18:09 images.ibd
-rw-rw---- 1 mysql mysql 768K Mar 14 09:43 history_log.ibd
-rw-rw---- 1 mysql mysql 496K Mar 14 09:29 events.ibd
-rw-rw---- 1 mysql mysql 352K Mar 13 13:05 items_applications.ibd
-rw-rw---- 1 mysql mysql 336K Mar 14 09:18 auditlog.ibd
-rw-rw---- 1 mysql mysql 336K Mar 14 09:14 trends.ibd
-rw-rw---- 1 mysql mysql 272K Mar 14 09:42 triggers.ibd
-rw-rw---- 1 mysql mysql 256K Mar 14 09:18 functions.ibd

      Comment

      • malevopfgm
        Junior Member
        • Mar 2013
        • 6
        #17
        Code:
        +--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history_uint |   61815591 |             54 |                   83.1689 |  3339681792 |   1801453568 |   9437184 |               4911.9688 | 2013-03-13 13:01:53 |
| history_text |     130428 |          29172 |                29253.1052 |  3804905472 |     10518528 |   9437184 |               3647.6719 | 2013-03-13 12:57:27 |
| history      |   27461377 |             53 |                   82.5143 |  1474281472 |    791674880 |   9437184 |               2169.9844 | 2013-03-13 12:51:51 |
| trends_uint  |    2667102 |            115 |                  115.1812 |   307200000 |            0 |   9437184 |                301.9688 | 2013-03-13 13:16:49 |
| trends       |    1447836 |             96 |                   96.1424 |   139198464 |            0 |   9437184 |                141.7500 | 2013-03-13 13:16:32 |
| events       |     991449 |             68 |                  124.5678 |    67731456 |     55771136 |   9437184 |                126.7813 | 2013-03-13 12:51:33 |
| history_str  |     231534 |             90 |                  128.8591 |    21020672 |      8814592 |   9437184 |                 37.4531 | 2013-03-13 12:57:22 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |   9437184 |                  9.0469 | 2013-03-13 12:57:22 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
        Regards, Pablo

          Comment

          • mkolomiets
            Senior Member
            • Jul 2009
            • 134
            #18
            Code:
            +--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history      |  220095722 |             72 |                  113.4866 | 15847112704 |   9130803200 |   6291456 |              23826.7969 | 2013-03-13 11:59:14 |
| history_uint |  169269705 |             83 |                  125.2909 | 14079082496 |   7128875008 |   7340032 |              20232.4844 | 2013-03-13 11:59:14 |
| trends_uint  |    9117721 |            112 |                  112.2081 |  1023082496 |            0 |  53477376 |               1026.6875 | 2013-03-13 11:59:14 |
| history_str  |    4057646 |             95 |                  131.0552 |   385515520 |    146259968 |   7340032 |                514.1406 | 2013-03-13 11:59:14 |
| trends       |    3136907 |             84 |                   84.1840 |   264077312 |            0 |  14680064 |                265.8438 | 2013-03-13 11:59:14 |
| history_text |    1183608 |            134 |                  210.0449 |   158679040 |     89931776 |   5242880 |                242.0938 | 2013-03-13 11:59:14 |
| events       |    1012014 |            115 |                  203.9553 |   116670464 |     89735168 |   5242880 |                201.8438 | 2013-03-13 11:59:14 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |         0 |                  0.0469 | 2013-03-13 11:59:14 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
            Code:
            -rw-rw---- 1 mysql mysql  24G Mar 22 00:19 history.ibd
-rw-rw---- 1 mysql mysql  21G Mar 22 00:19 history_uint.ibd
-rw-rw---- 1 mysql mysql 1.1G Mar 22 00:19 trends_uint.ibd
-rw-rw---- 1 mysql mysql 624M Mar 22 00:18 escalations.ibd
-rw-rw---- 1 mysql mysql 524M Mar 22 00:18 history_str.ibd
-rw-rw---- 1 mysql mysql 272M Mar 22 00:19 trends.ibd
-rw-rw---- 1 mysql mysql 248M Mar 22 00:18 history_text.ibd
-rw-rw---- 1 mysql mysql 208M Mar 22 00:18 events.ibd
-rw-rw---- 1 mysql mysql 144M Mar 21 23:15 auditlog.ibd
-rw-rw---- 1 mysql mysql 124M Mar 22 00:17 alerts.ibd
-rw-rw-rw- 1 mysql mysql 104M Mar  7 15:57 alerts.dmp
-rw-rw---- 1 mysql mysql  88M Mar 21 17:15 auditlog_details.ibd
-rw-rw---- 1 mysql mysql  60M Mar 22 00:17 items.ibd
-rw-rw---- 1 mysql mysql  30M Mar 22 00:17 triggers.ibd
            Code:
            Zabbix server v2.0.5 (revision 33558) (12 February 2013)
Compilation time: Mar  6 2013 00:15:00

              Comment

              • mcmyst
                Member
                • Feb 2012
                • 72
                #19
                Zabbix 2.0.3

                Code:
                +--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history_uint |  173562117 |             60 |                   94.6732 | 10564419584 |   5867257856 |   5242880 |              15675.4688 | 2012-12-13 11:33:03 |
| trends_uint  |  177494781 |             72 |                   72.4394 | 12857622528 |            0 |   6291456 |              12267.9844 | 2012-12-13 11:33:04 |
| history      |    1607577 |             56 |                   86.8947 |    91340800 |     48349184 |   9437184 |                142.2188 | 2012-12-13 11:33:03 |
| trends       |     444242 |            100 |                  100.5371 |    44662784 |            0 |   6291456 |                 48.5938 | 2012-12-13 11:33:04 |
| history_str  |       2865 |             80 |                  114.3735 |      229376 |        98304 |         0 |                  0.3125 | 2012-12-13 11:33:04 |
| events       |       1566 |             83 |                  167.3972 |      131072 |       131072 |         0 |                  0.2500 | 2012-12-13 11:33:04 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |         0 |                  0.0469 | 2012-12-13 11:33:04 |
| history_text |          0 |              0 |                      NULL |       16384 |        32768 |         0 |                  0.0469 | 2012-12-13 11:33:04 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
                Code:
                ls -lhS /var/lib/mysql/zabbix | head -n 15
total 29G
-rw-rw---- 1 mysql mysql  16G Mar 29 09:26 history_uint.ibd
-rw-rw---- 1 mysql mysql  13G Mar 29 09:24 trends_uint.ibd
-rw-rw---- 1 mysql mysql 148M Mar 29 09:26 history.ibd
-rw-rw---- 1 mysql mysql  68M Mar 29 09:26 items.ibd
-rw-rw---- 1 mysql mysql  52M Mar 29 09:24 trends.ibd
-rw-rw---- 1 mysql mysql  20M Mar 29 08:48 graphs_items.ibd
-rw-rw---- 1 mysql mysql  14M Mar 29 08:48 graphs.ibd
-rw-rw---- 1 mysql mysql  13M Mar 29 02:20 hosts_groups.ibd
-rw-rw---- 1 mysql mysql  10M Mar 29 09:14 hosts.ibd
-rw-rw---- 1 mysql mysql  10M Mar 29 02:20 interface.ibd
-rw-rw---- 1 mysql mysql 9.0M Dec 13 11:33 images.ibd
-rw-rw---- 1 mysql mysql 896K Mar 29 08:50 auditlog.ibd
-rw-rw---- 1 mysql mysql 448K Mar 29 09:08 sessions.ibd
-rw-rw---- 1 mysql mysql 416K Mar 29 08:35 history_str.ibd

                  Comment

                  • tob
                    Junior Member
                    Zabbix Certified Specialist
                    • Dec 2008
                    • 25
                    #20
                    Code:
                    +--------------+------------+----------------+---------------------------+-------------+--------------+------------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE  | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+------------+-------------------------+---------------------+
| history_uint |  166902371 |            154 |                  222.1839 | 25722634240 |  11360387072 |  103809024 |              35464.1250 | 2013-02-20 00:39:08 |
| trends_uint  |  181435682 |             95 |                   95.2248 | 17277173760 |            0 |  580911104 |              17030.7969 | 2012-12-04 02:31:22 |
| history      |   57776901 |            212 |                  287.8039 | 12291424256 |   4336992256 |   23068672 |              15880.0938 | 2012-11-01 23:02:16 |
| trends       |   54930450 |             80 |                   80.9210 |  4445028352 |            0 |  138412032 |               4371.1094 | 2012-12-04 01:29:42 |
| history_text |    9171487 |            215 |                  329.4026 |  1978679296 |   1042432000 |  454033408 |               3314.1563 | 2012-06-27 17:34:47 |
| history_str  |    4001789 |            369 |                  465.3678 |  1479557120 |    382746624 | 1282408448 |               2999.0313 | 2012-06-27 17:34:31 |
| events       |   17097667 |             68 |                  151.7183 |  1170440192 |   1423589376 |   53477376 |               2524.8594 | 2013-12-13 00:10:35 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |          0 |                  0.0469 | 2012-06-27 17:34:31 |
+--------------+------------+----------------+---------------------------+-------------+--------------+------------+-------------------------+---------------------+
8 rows in set (2.97 sec)

mysql> exit
Bye
                    Code:
                    # ls -lhS /opt/mysql/zabbix | head -n 15
total 84G
-rw-rw---- 1 mysql mysql  36G Feb 13 12:14 history_uint.ibd
-rw-rw---- 1 mysql mysql  17G Feb 13 12:14 trends_uint.ibd
-rw-rw---- 1 mysql mysql  16G Feb 13 12:14 history.ibd
-rw-rw---- 1 mysql mysql 4.4G Feb 13 12:14 trends.ibd
-rw-rw---- 1 mysql mysql 3.3G Feb 13 12:14 history_text.ibd
-rw-rw---- 1 mysql mysql 3.0G Feb 13 12:14 history_str.ibd
-rw-rw---- 1 mysql mysql 2.6G Feb 13 12:13 events.ibd
-rw-rw---- 1 mysql mysql 1.8G Jan 14 16:35 node_cksum.ibd
-rw-rw---- 1 mysql mysql 124M Feb 13 12:13 alerts.ibd
-rw-rw---- 1 mysql mysql  84M Feb 13 12:14 items.ibd
-rw-rw---- 1 mysql mysql  60M Feb 13 12:11 auditlog.ibd
-rw-rw---- 1 mysql mysql  31M Feb 13 11:37 auditlog_details.ibd
-rw-rw---- 1 mysql mysql  27M Feb 13 12:13 items_applications.ibd
-rw-rw---- 1 mysql mysql  21M Feb 13 12:08 triggers.ibd
                    Last edited by zalex_ua; 13-02-2014, 13:20. Reason: formating

                      Comment

                      • OKyHb
                        Senior Member
                        • Sep 2010
                        • 103
                        #21
                        ~1 year and 4 month months of operation (zabbix 2.2.1 now)

                        Code:
                        +--------------+------------+----------------+---------------------------+--------------+--------------+------------+-------------------------+-------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH  | INDEX_LENGTH | DATA_FREE  | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME |
+--------------+------------+----------------+---------------------------+--------------+--------------+------------+-------------------------+-------------+
| trends_uint  | 6432407265 |             92 |                   92.4494 | 594672386048 |            0 | 1721761792 |             568765.7813 | NULL        |
| history_uint | 1150002246 |             63 |                  103.0448 |  72816279552 |  45685424128 |   37748736 |             113048.0313 | NULL        |
| trends       |  123414905 |             96 |                   96.5488 |  11915558912 |            0 |   94371840 |              11453.5625 | NULL        |
| history      |   14758374 |             62 |                  103.5803 |    923631616 |    605044736 |   10485760 |               1467.8594 | NULL        |
| events       |    7089711 |             70 |                  178.9603 |    502808576 |    765968384 |    9437184 |               1219.0000 | NULL        |
| history_text |    3968759 |            112 |                  155.2715 |    446775296 |    169459712 |   40894464 |                626.6875 | NULL        |
| history_str  |      40604 |             77 |                  110.1574 |      3145728 |      1327104 |          0 |                  4.2656 | NULL        |
| history_log  |          9 |          16384 |                32768.0000 |       147456 |       147456 |          0 |                  0.2813 | NULL        |
+--------------+------------+----------------+---------------------------+--------------+--------------+------------+-------------------------+-------------+
                        Code:
                        # /var/lib/mysql/zabbix>ls -lhS | head -n15
total 669G
-rw-rw---- 1 mysql mysql  46G Feb 15 13:33 trends_uint#P#p2014_01.ibd
-rw-rw---- 1 mysql mysql  40G Feb 15 13:33 trends_uint#P#p2013_11.ibd
-rw-rw---- 1 mysql mysql  40G Feb 15 13:33 trends_uint#P#p2013_12.ibd
-rw-rw---- 1 mysql mysql  40G Feb 15 13:33 trends_uint#P#p2013_10.ibd
-rw-rw---- 1 mysql mysql  39G Feb 15 16:36 trends_uint#P#p2014_02.ibd
-rw-rw---- 1 mysql mysql  36G Feb 15 13:33 trends_uint#P#p2013_09.ibd
-rw-rw---- 1 mysql mysql  33G Feb 15 11:43 trends_uint#P#p2012_11.ibd
-rw-rw---- 1 mysql mysql  33G Feb 15 13:33 trends_uint#P#p2013_08.ibd
-rw-rw---- 1 mysql mysql  32G Feb 15 13:33 trends_uint#P#p2013_07.ibd
-rw-rw---- 1 mysql mysql  32G Feb 15 13:33 trends_uint#P#p2013_06.ibd
-rw-rw---- 1 mysql mysql  31G Feb 15 13:33 trends_uint#P#p2013_05.ibd
-rw-rw---- 1 mysql mysql  31G Feb 15 13:33 trends_uint#P#p2013_04.ibd
-rw-rw---- 1 mysql mysql  30G Feb 15 13:33 trends_uint#P#p2013_03.ibd
-rw-rw---- 1 mysql mysql  29G Feb 15 13:33 trends_uint#P#p2013_02.ibd

                          Comment

                          • aib
                            Senior Member
                            • Jan 2014
                            • 1615
                            #22
                            Code:
                            +--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history_uint |   17471299 |             78 |                  116.3918 |  1377878016 |    655638528 |  49283072 |               1986.3125 | 2014-02-08 20:03:16 |
| history      |    5277167 |             66 |                  103.0666 |   350552064 |    193347584 |  49283072 |                565.7031 | 2014-02-08 20:03:16 |
| history_str  |    2398025 |             60 |                  100.7216 |   145899520 |     95633408 |  49283072 |                277.3438 | 2014-02-08 20:03:16 |
| trends_uint  |    1810471 |             94 |                   94.2876 |   170704896 |            0 |  49283072 |                209.7969 | 2014-02-08 20:03:16 |
| trends       |     769686 |             73 |                   73.0130 |    56197120 |            0 |  49283072 |                100.5938 | 2014-02-08 20:03:16 |
| events       |      11754 |            135 |                  301.0842 |     1589248 |      1949696 |  49283072 |                 50.3750 | 2014-02-08 20:03:16 |
| history_text |       4547 |             57 |                  118.9074 |      262144 |       278528 |  49283072 |                 47.5156 | 2014-02-08 20:03:16 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |  49283072 |                 47.0469 | 2014-02-08 20:03:16 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
8 rows in set (0.14 sec)
                            Code:
                            total 3.0G
-rw-rw----. 1 mysql mysql 3.0G Feb 17 11:57 ibdata1
-rw-r-----  1 mysql mysql 5.0M Feb 17 11:57 ib_logfile0
-rw-r-----  1 mysql mysql 5.0M Feb 17 11:57 ib_logfile1
drwxr-xr-x  2 mysql mysql 4.0K Jan 30 14:51 data
drwx------. 2 mysql mysql 4.0K Jan 22 09:06 mysql
drwx------. 2 mysql mysql 4.0K Jan 22 09:06 test
drwx------. 2 mysql mysql 4.0K Jan 22 13:24 zabbix
                            Sincerely yours,
                            Aleksey

                              Comment

                              • zalex_ua
                                Senior Member
                                Zabbix Certified Trainer
                                Zabbix Certified SpecialistZabbix Certified Professional
                                • Oct 2009
                                • 1286
                                #23
                                Thank you all !

                                Based on gathered stat, zabbix documentation has been improved in https://support.zabbix.com/browse/ZBX-10131

                                This topic is considered to be closed now !

                                  Comment

                                  Previous 1 2 template Next
                                  Working...