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

[zalex_ua]

Hi all, I would appreciate if you will share some statistics from your zabbix database.
I need it to be sure is a calculation in documentation correct. Here http://www.zabbix.com/documentation/...#database_size

So please perform this SQL query (it is very "light" and fast) and show its output.

SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, ((DATA_LENGTH + INDEX_LENGTH)/TABLE_ROWS) AS AVG_ROW_LENGTH_CALCULATED, 
DATA_LENGTH, INDEX_LENGTH, DATA_FREE, 
((DATA_LENGTH + INDEX_LENGTH + DATA_FREE)/(1024*1024)) AS SUMMARY_SPACE_IN_MBYTES, CREATE_TIME 
from information_schema.tables 
where TABLE_SCHEMA='zabbix' 
AND TABLE_NAME IN ('history', 'history_uint', 'history_log', 'history_str', 'history_text', 'trends', 'trends_uint', 'events') 
ORDER BY SUMMARY_SPACE_IN_MBYTES DESC;

If you are using innodb_file_per_table for MySQL it would be good to show also output of this command:

# ls -lhS /var/lib/mysql/zabbix | head -n 15

* adjust the path if yours is different

Check please also which zabbix server version you are using and mention here.

[zalex_ua]

Here is an example of outputs from my testing zabbix installation.

+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| events       |    1348173 |             68 |                  118.0153 |    91848704 |     67256320 |   8388608 |                159.7344 | 2012-06-19 20:47:13 |
| history      |    1347332 |             73 |                  102.1468 |    98729984 |     38895616 |   4194304 |                135.2500 | 2012-06-19 20:47:13 |
| history_uint |    1244707 |             72 |                   98.7352 |    90308608 |     32587776 |   6291456 |                123.2031 | 2012-06-19 20:47:13 |
| history_text |     321897 |             55 |                  185.9314 |    17874944 |     41975808 |  39845888 |                 95.0781 | 2012-06-19 20:47:13 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |  26214400 |                 25.0469 | 2012-06-19 20:47:13 |
| trends       |     213943 |             78 |                   78.6488 |    16826368 |            0 |   5242880 |                 21.0469 | 2012-06-19 20:47:13 |
| trends_uint  |     133317 |             87 |                   87.0097 |    11599872 |            0 |   6291456 |                 17.0625 | 2012-06-19 20:47:13 |
| history_str  |      45228 |             93 |                  139.8298 |     4210688 |      2113536 |   5242880 |                 11.0313 | 2012-06-19 20:47:13 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
8 rows in set (0.02 sec)

# ls -lhS /var/lib/mysql/zabbix | head -n 15
total 676M
-rw-rw---- 1 mysql mysql 164M Oct 22 00:39 events.ibd
-rw-rw---- 1 mysql mysql 140M Oct 22 00:39 history.ibd
-rw-rw---- 1 mysql mysql 128M Oct 22 00:40 history_uint.ibd
-rw-rw---- 1 mysql mysql 100M Oct 22 00:39 history_text.ibd
-rw-rw---- 1 mysql mysql  29M Oct 12 20:27 history_log.ibd
-rw-rw---- 1 mysql mysql  25M Oct 22 00:09 trends.ibd
-rw-rw---- 1 mysql mysql  21M Oct 22 00:09 trends_uint.ibd
-rw-rw---- 1 mysql mysql  15M Oct 22 00:39 history_str.ibd
-rw-rw---- 1 mysql mysql  10M Oct 20 20:58 housekeeper.ibd
-rw-rw---- 1 mysql mysql  10M May 17 15:22 images.ibd
-rw-rw---- 1 mysql mysql 9.0M Oct 20 17:55 auditlog.ibd
-rw-rw---- 1 mysql mysql 9.0M Oct 11 15:17 history_log_bkp.ibd
-rw-rw---- 1 mysql mysql 720K Oct 20 17:55 escalations.ibd
-rw-rw---- 1 mysql mysql 448K Oct 22 00:40 items.ibd

# zabbix_server -V
Zabbix server v2.1.0 (revision 30948) (21 May 2012)
Compilation time: Oct 19 2012 11:12:52

just FYI - v2.1.0 - is current trunk

[zalex_ua]

Hmm, after the tables optimization (recreate + analyze actually) I got other results:

+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| events       |    1362510 |             68 |                  116.0520 |    92930048 |     65191936 |   5242880 |                155.7969 | 2012-10-22 01:01:18 |
| history      |    1269224 |             52 |                   81.1181 |    66682880 |     36274176 |   5242880 |                103.1875 | 2012-10-22 00:57:20 |
| history_uint |    1096448 |             53 |                   81.4382 |    58277888 |     31014912 |   7340032 |                 92.1563 | 2012-10-22 01:00:23 |
| history_text |     324151 |             50 |                  105.3850 |    16302080 |     17858560 |   4194304 |                 36.5781 | 2012-10-22 01:06:41 |
| trends       |     197089 |             66 |                   66.7534 |    13156352 |            0 |   4194304 |                 16.5469 | 2012-10-22 01:01:43 |
| trends_uint  |     132347 |             67 |                   67.4687 |     8929280 |            0 |   4194304 |                 12.5156 | 2012-10-22 01:01:50 |
| history_str  |      45003 |             81 |                  117.2288 |     3686400 |      1589248 |   4194304 |                  9.0313 | 2012-10-22 01:06:51 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |         0 |                  0.0469 | 2012-10-22 01:06:45 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
8 rows in set (0.02 sec)

[kmradke]

(haven't upgraded to 2.x yet because of the downtime needed to upgrade the database...)

+--------------+------------+----------------+---------------------------+-------------+--------------+-------------+-------------------------+---------------------+
| 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 |   73896735 |            103 |                  149.0779 |  7647281152 |   3369091072 | 14126415872 |              23978.0313 | 2010-11-05 09:27:58 |
| history      |   93524355 |            110 |                  158.8391 | 10354704384 |   4500619264 |   658505728 |              14795.1406 | 2011-01-21 19:04:47 |
| trends_uint  |   42186595 |             72 |                   72.4232 |  3055288320 |            0 |     7340032 |               2920.7500 | 2011-01-21 15:02:50 |
| trends       |   31181199 |             72 |                   72.8944 |  2272935936 |            0 |     7340032 |               2174.6406 | 2011-01-21 15:00:24 |
| events       |     546097 |             69 |                  167.4711 |    37814272 |     53641216 |     7340032 |                 94.2188 | 2011-01-21 14:55:06 |
| history_text |     767483 |             57 |                  115.0214 |    44122112 |     44154880 |     4194304 |                 88.1875 | 2011-01-21 14:57:15 |
| history_log  |        105 |            780 |                 1092.2667 |       81920 |        32768 |    19922944 |                 19.1094 | 2011-01-21 14:57:12 |
| history_str  |      55598 |             94 |                  132.9038 |     5259264 |      2129920 |     7340032 |                 14.0469 | 2011-01-21 14:57:14 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-------------+-------------------------+---------------------+
8 rows in set (0.81 sec)

# ls -lhS zabbix | head -n 15
total 21G
-rw-rw---- 1 mysql mysql  15G Oct 29 13:02 history.ibd
-rw-rw---- 1 mysql mysql 2.9G Oct 29 12:21 trends_uint.ibd
-rw-rw---- 1 mysql mysql 2.2G Oct 29 12:23 trends.ibd
-rw-rw---- 1 mysql mysql 100M Oct 29 12:59 events.ibd
-rw-rw---- 1 mysql mysql  92M Oct 29 13:01 history_text.ibd
-rw-rw---- 1 mysql mysql  29M Oct 29 11:55 alerts.ibd
-rw-rw---- 1 mysql mysql  22M Oct 29 12:31 history_log.ibd
-rw-rw---- 1 mysql mysql  18M Oct 23 12:36 auditlog.ibd
-rw-rw---- 1 mysql mysql  17M Oct 29 12:59 history_str.ibd
-rw-rw---- 1 mysql mysql  15M Oct 19 16:14 auditlog_details.ibd
-rw-rw---- 1 mysql mysql  15M Oct 29 12:56 service_alarms.ibd
-rw-rw---- 1 mysql mysql  13M Oct 29 13:01 items.ibd
-rw-rw---- 1 mysql mysql 9.0M Oct 19 16:20 items_applications.ibd
-rw-rw---- 1 mysql mysql 9.0M Oct 26 16:33 profiles.ibd

# ./zabbix_server -V
Zabbix Server v1.8.6 (revision 20932) (4 August 2011)
Compilation time: Aug  8 2011 09:12:13

[zalex_ua]

kmradke, thank you for your results !

Just to let you know - it seems your history_uint was created (2010-11-05 09:27:58) before enabling the innodb_file_per_table option and actually data from this table located in shared innodb pool (ibdata1 file)

Maybe reason that other tables have been optimized/recreated (at ~ 2011-01-21 14:57:14) but the history_uint not.

[dotneft]

The first server:

+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+-------------+
| 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  |  769516304 |             99 |                   99.8150 | 76809244672 |            0 |  89128960 |              73336.0039 | NULL        |
| history_uint |  589706518 |             60 |                   96.8477 | 35659972608 |  21451767808 |  16777216 |              54482.0000 | NULL        |
| events       |   16356171 |             89 |                  224.4487 |  1458470912 |   2212651008 |  58720256 |               3557.0547 | NULL        |
| history_str  |     318676 |             59 |                  106.6043 |    19083264 |     14888960 |  16777216 |                 48.3984 | NULL        |
| history      |      40964 |             77 |                  128.8874 |     3178496 |      2101248 |   8388608 |                 13.0352 | NULL        |
| trends       |       5976 |            100 |                  100.0696 |      598016 |            0 |         0 |                  0.5703 | NULL        |
| history_text |          8 |           2560 |                 2560.0000 |       20480 |            0 |         0 |                  0.0195 | NULL        |
| history_log  |          5 |           4096 |                 4096.0000 |       20480 |            0 |         0 |                  0.0195 | NULL        |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+-------------+

 ls -lhS zabbix | head -n 15
Total 146G
-rw-rw---- 1 mysql mysql  32G Oct 30 00:20 history_uint#P#p2012_10_29.ibd
-rw-rw---- 1 mysql mysql  28G Oct 29 00:19 history_uint#P#p2012_10_28.ibd
-rw-rw---- 1 mysql mysql 5.0G Oct 28 04:42 trends_uint#P#p2012_10_26.ibd
-rw-rw---- 1 mysql mysql 5.0G Oct 26 05:35 trends_uint#P#p2012_10_22.ibd
-rw-rw---- 1 mysql mysql 5.0G Oct 26 05:35 trends_uint#P#p2012_10_25.ibd
-rw-rw---- 1 mysql mysql 5.0G Oct 30 00:49 trends_uint#P#p2012_10_29.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 28 04:42 trends_uint#P#p2012_10_27.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 26 05:35 trends_uint#P#p2012_10_24.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 26 05:35 trends_uint#P#p2012_10_23.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 21 08:42 trends_uint#P#p2012_10_19.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 21 08:42 trends_uint#P#p2012_10_20.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 21 08:42 trends_uint#P#p2012_10_17.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 21 08:42 trends_uint#P#p2012_10_18.ibd
-rw-rw---- 1 mysql mysql 4.9G Oct 16 03:35 trends_uint#P#p2012_10_15.ibd

# /usr/local/sbin/zabbix_server -V
Zabbix server v2.1.0 (revision {ZABBIX_REVISION}) (21 May 2012)
Compilation time: Aug  9 2012 18:01:08

The second server:

+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+-------------+
| 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  |  648952274 |             82 |                   82.9274 | 53815926784 |            0 | 140509184 |              51456.8672 | NULL        |
| history_uint |  266800126 |             61 |                  104.3481 | 16324890624 |  11515191296 | 102760448 |              26648.3711 | NULL        |
| trends       |  151585600 |             75 |                   75.7039 | 11475615744 |            0 | 158334976 |              11095.0000 | NULL        |
| history      |   60555441 |             58 |                  104.2088 |  3549990912 |   2760421376 |  95420416 |               6109.0781 | NULL        |
| history_str  |   28562303 |             57 |                  112.4092 |  1644953600 |   1565712384 | 189792256 |               3242.9297 | NULL        |
| events       |    4812692 |             75 |                  193.6775 |   361123840 |    570986496 |  47185920 |                933.9297 | NULL        |
| history_text |    1039088 |             90 |                   90.8809 |    94433280 |            0 | 134217728 |                218.0586 | NULL        |
| history_log  |       2554 |            248 |                  248.5826 |      634880 |            0 |         0 |                  0.6055 | NULL        |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+-------------+

# ls -lhS zabbix | head -n 15
итого 107G
-rw-r--r-- 1 mysql mysql 7.3G Сен 16 04:10 trends_uint#P#p2010_10.ibd
-rw-r--r-- 1 mysql mysql 2.9G Окт 30 00:52 trends_uint#P#p2012_10.ibd
-rw-r--r-- 1 mysql mysql 2.6G Окт 26 14:00 trends_uint#P#p2012_09.ibd
-rw-r--r-- 1 mysql mysql 2.5G Сен 16 04:05 trends_uint#P#p2012_08.ibd
-rw-r--r-- 1 mysql mysql 2.5G Сен 16 04:08 trends_uint#P#p2012_07.ibd
-rw-r--r-- 1 mysql mysql 2.5G Сен 16 04:07 trends_uint#P#p2012_06.ibd
-rw-r--r-- 1 mysql mysql 2.4G Сен 16 04:13 trends_uint#P#p2012_05.ibd
-rw-r--r-- 1 mysql mysql 2.4G Сен 16 04:05 trends_uint#P#p2012_04.ibd
-rw-r--r-- 1 mysql mysql 2.3G Сен 16 04:10 trends_uint#P#p2012_03.ibd
-rw-r--r-- 1 mysql mysql 2.2G Сен 16 04:03 trends_uint#P#p2011_10.ibd
-rw-r--r-- 1 mysql mysql 2.2G Сен 16 04:12 trends_uint#P#p2011_11.ibd
-rw-r--r-- 1 mysql mysql 2.2G Сен 16 04:14 trends_uint#P#p2012_01.ibd
-rw-r--r-- 1 mysql mysql 2.1G Сен 16 04:04 trends_uint#P#p2012_02.ibd
-rw-r--r-- 1 mysql mysql 2.0G Сен 16 04:01 trends_uint#P#p2011_12.ibd

$ /usr/local/sbin/zabbix_server -V
Zabbix server v2.1.0 (revision {ZABBIX_REVISION}) (21 May 2012)
Compilation time: Aug 19 2012 00:01:58

[f.koch]

Hm, is this only for mysql or do you interested also in postgresql stats?

[zalex_ua]

Hm, is this only for mysql or do you interested also in postgresql stats?

Would be very interesting not only for mysql, but I don't have similar SQL/approach ready for postgresql.
So please if you know how to correctly get similar stat from postgresql, would be very nice to see your results.

[dima_dm]

+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history_log  |  103893466 |            362 |                  418.8857 | 37662769152 |   5856722944 | 421527552 |              41905.4219 | 2012-10-24 11:37:09 |
| trends_uint  |  341812157 |             87 |                   87.6472 | 29958864896 |            0 | 153092096 |              28717.0000 | 2012-10-24 11:53:53 |
| history_uint |   61121955 |             66 |                  102.8122 |  4065820672 |   2218262528 |  12582912 |               6004.9688 | 2012-10-24 11:52:41 |
| trends       |   29111466 |             72 |                   72.8249 |  2120040448 |            0 |  32505856 |               2052.8281 | 2012-10-24 11:53:53 |
| history      |   11815446 |             53 |                   89.1650 |   633552896 |    419971072 | 340787200 |               1329.7188 | 2012-10-24 11:39:32 |
| events       |    4535847 |             67 |                  127.1428 |   306561024 |    270139392 |  14680064 |                563.9844 | 2012-10-24 11:56:26 |
| history_str  |     145435 |            108 |                  158.9564 |    15745024 |      7372800 |  23068672 |                 44.0469 | 2012-10-24 11:39:37 |
| history_text |     195711 |             98 |                  161.5705 |    19316736 |     12304384 |   6291456 |                 36.1563 | 2012-10-24 11:39:40 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
8 rows in set (0.60 sec)

# ls -lhS zabbix | head -n 15
total 81G
-rw-rw----  1 mysql mysql  42G Oct 30 18:22 history_log.ibd
-rw-rw----  1 mysql mysql  29G Oct 30 18:21 trends_uint.ibd
-rw-rw----  1 mysql mysql 6.0G Oct 30 18:23 history_uint.ibd
-rw-rw----  1 mysql mysql 2.1G Oct 30 18:22 trends.ibd
-rw-rw----  1 mysql mysql 1.4G Oct 30 18:23 history.ibd
-rw-rw----  1 mysql mysql 576M Oct 30 18:21 events.ibd
-rw-rw----  1 mysql mysql 188M Oct 30 14:53 auditlog.ibd
-rw-rw----  1 mysql mysql 108M Oct 30 18:00 alerts.ibd
-rw-rw----  1 mysql mysql  64M Oct 30 18:23 items.ibd
-rw-rw----  1 mysql mysql  52M Oct 24 17:28 auditlog_details.ibd
-rw-rw----  1 mysql mysql  48M Oct 30 18:21 history_str.ibd
-rw-rw----  1 mysql mysql  40M Oct 30 18:14 history_text.ibd
-rw-rw----  1 mysql mysql  40M Oct 30 18:14 triggers.ibd
-rw-rw----  1 mysql mysql  21M Oct 24 19:57 functions.ibd

zabbix_server -V
Zabbix server v2.0.3 (revision 30485) (03 October 2012)
Compilation time: Oct 24 2012 10:32:57

[jix]

!

root@db1:~# ls -lhS /var/lib/mysql/zbx | head -n 15
total 454G
-rw-rw---- 1 mysql mysql 409G Oct 30 20:17 history_uint.ibd
-rw-rw---- 1 mysql mysql  23G Oct 30 20:17 history.ibd
-rw-rw---- 1 mysql mysql  16G Oct 30 19:49 trends_uint.ibd
-rw-rw---- 1 mysql mysql 2.7G Oct 30 20:17 events.ibd
-rw-rw---- 1 mysql mysql 2.2G Oct 30 20:17 history_str.ibd
-rw-rw---- 1 mysql mysql 768M Oct 30 17:45 auditlog.ibd
-rw-rw---- 1 mysql mysql 516M Oct 30 17:09 auditlog_details.ibd
-rw-rw---- 1 mysql mysql 312M Oct 30 19:33 trends.ibd
-rw-rw---- 1 mysql mysql 144M Oct 30 20:17 history_text.ibd
-rw-rw---- 1 mysql mysql  64M Oct 30 20:09 items.ibd
-rw-rw---- 1 mysql mysql  56M Oct 30 20:17 triggers.ibd
-rw-rw---- 1 mysql mysql  48M Oct 28 17:45 housekeeper.ibd
-rw-rw---- 1 mysql mysql  31M Oct 30 17:29 functions.ibd
-rw-rw---- 1 mysql mysql  26M Oct 30 20:09 alerts.ibd
root@db1:~#

+--------------+------------+----------------+---------------------------+--------------+--------------+-----------+-------------------------+---------------------+
| 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 | 4825666227 |             59 |                   89.5655 | 288511606784 | 143701442560 | 447741952 |             412617.4844 | 2011-10-28 16:12:36 |
| history      |  283721559 |             53 |                   83.3762 |  15270854656 |   8384774144 |   4194304 |              22563.7656 | 2011-10-28 16:12:02 |
| trends_uint  |  198204980 |             82 |                   82.7913 |  16409657344 |            0 | 301989888 |              15937.4688 | 2011-10-28 16:38:22 |
| events       |   22052130 |             60 |                  127.4091 |   1332215808 |   1477427200 |  15728640 |               2694.4844 | 2011-10-28 16:12:00 |
| history_str  |   19840209 |             70 |                  114.1244 |   1408483328 |    855769088 |  27262976 |               2185.3594 | 2011-10-28 16:12:34 |
| trends       |    4169909 |             74 |                   74.9241 |    312426496 |            0 |   7340032 |                304.9531 | 2011-10-28 16:38:21 |
| history_text |     467760 |            240 |                  289.6692 |    112361472 |     23134208 |   9437184 |                138.2188 | 2011-10-28 16:12:36 |
| history_log  |          0 |              0 |                      NULL |        16384 |        32768 |         0 |                  0.0469 | 2011-10-28 16:12:34 |
+--------------+------------+----------------+---------------------------+--------------+--------------+-----------+-------------------------+---------------------+
8 rows in set (0.92 sec)

zabbix 1.8.15

[kmradke]

kmradke, thank you for your results !

Just to let you know - it seems your history_uint was created (2010-11-05 09:27:58) before enabling the innodb_file_per_table option and actually data from this table located in shared innodb pool (ibdata1 file)

Maybe reason that other tables have been optimized/recreated (at ~ 2011-01-21 14:57:14) but the history_uint not.

Yes, it will take about 8 hours to convert that table. I wasn't able to take the downtime when I originally decided to move to the file_per_table format.

[deejay1]

Server version 2.0.3

| 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 | 2112308121 |             64 |                  115.9868 | 137071443968 | 107928453120 |  75497472 |             233722.1094 | NULL                |
| history      |  832427680 |             61 |                  111.5451 |  51204374528 |  41648865280 |  81788928 |              88629.7500 | NULL                |
| trends_uint  |  610485978 |             77 |                   77.5655 |  47352643584 |            0 |   5242880 |              45164.0000 | 2012-01-16 12:04:38 |
| trends       |  191044462 |             80 |                   80.0519 |  15293480960 |            0 |   6291456 |              14591.0000 | 2012-01-16 12:04:38 |
| history_str  |   25948217 |             83 |                  132.0363 |   2163408896 |   1262698496 |  50331648 |               3315.3906 | NULL                |
| events       |   11367014 |            101 |                  204.5944 |   1148190720 |   1177436160 |   6291456 |               2223.8906 | 2012-08-10 14:35:12 |
| history_text |   13364623 |             87 |                  142.5896 |   1162854400 |    742801408 |  49283072 |               1864.3750 | NULL                |
| history_log  |         28 |          16384 |                32768.0000 |       458752 |       458752 |         0 |                  0.8750 | NULL                |
+--------------+------------+----------------+---------------------------+--------------+--------------+-----------+-------------------------+---------------------+

total 390G
-rw-rw---- 1 mysql mysql  45G 2012-11-07 15:35 trends_uint.ibd
-rw-rw---- 1 mysql mysql  28G 2012-11-06 23:20 history_uint#P#p20121105.ibd
-rw-rw---- 1 mysql mysql  28G 2012-11-06 23:14 history_uint#P#p20121104.ibd
-rw-rw---- 1 mysql mysql  27G 2012-11-06 10:44 history_uint#P#p20121103.ibd
-rw-rw---- 1 mysql mysql  27G 2012-11-03 23:20 history_uint#P#p20121102.ibd
-rw-rw---- 1 mysql mysql  27G 2012-11-01 23:20 history_uint#P#p20121031.ibd
-rw-rw---- 1 mysql mysql  27G 2012-11-07 03:32 history_uint#P#p20121106.ibd
-rw-rw---- 1 mysql mysql  27G 2012-11-02 23:21 history_uint#P#p20121101.ibd
-rw-rw---- 1 mysql mysql  27G 2012-10-31 23:18 history_uint#P#p20121030.ibd
-rw-rw---- 1 mysql mysql  18G 2012-11-07 15:35 history_uint#P#p20121107.ibd
-rw-rw---- 1 mysql mysql  15G 2012-11-07 15:35 trends.ibd
-rw-rw---- 1 mysql mysql  11G 2012-11-06 07:00 history#P#p20121105.ibd
-rw-rw---- 1 mysql mysql  11G 2012-11-07 03:32 history#P#p20121106.ibd
-rw-rw---- 1 mysql mysql  11G 2012-11-05 06:57 history#P#p20121104.ibd

[marcherren]

+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| 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 |   87106970 |             52 |                   82.4792 |  4579885056 |   2604630016 |         0 |               6851.6875 | 2012-11-28 01:11:39 |
| trends_uint  |   47037776 |             79 |                   79.7838 |  3752853504 |            0 |         0 |               3579.0000 | 2012-11-28 01:11:32 |
| history      |    9047587 |             52 |                   81.5851 |   474988544 |    263159808 |         0 |                703.9531 | 2012-11-28 01:11:39 |
| trends       |    2476148 |             78 |                   78.7060 |   194887680 |            0 |         0 |                185.8594 | 2012-11-28 01:00:01 |
| events       |     722319 |             71 |                  137.5013 |    52002816 |     47316992 |         0 |                 94.7188 | 2012-11-28 01:01:12 |
| history_text |     618624 |             81 |                  156.2855 |    50429952 |     46252032 |         0 |                 92.2031 | 2012-11-28 01:00:58 |
| history_str  |      10741 |            196 |                  228.8055 |     2113536 |       344064 |         0 |                  2.3438 | 2012-11-28 01:11:39 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |         0 |                  0.0469 | 2012-11-28 01:11:39 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+

total 13G
-rw-rw---- 1 mysql mysql 7.6G Nov 29 09:46 history_uint.ibd
-rw-rw---- 1 mysql mysql 3.6G Nov 29 09:45 trends_uint.ibd
-rw-rw---- 1 mysql mysql 728M Nov 29 09:45 history.ibd
-rw-rw---- 1 mysql mysql 516M Nov 29 09:45 node_cksum.ibd
-rw-rw---- 1 mysql mysql 196M Nov 29 09:45 trends.ibd
-rw-rw---- 1 mysql mysql 112M Nov 29 09:45 history_text.ibd
-rw-rw---- 1 mysql mysql 104M Nov 29 03:53 events.ibd
-rw-rw---- 1 mysql mysql  20M Nov 29 09:45 items.ibd
-rw-rw---- 1 mysql mysql  12M Nov 23 14:50 items_applications.ibd
-rw-rw---- 1 mysql mysql  12M Nov 29 03:52 triggers.ibd
-rw-rw---- 1 mysql mysql  11M Nov 28 14:47 alerts.ibd
-rw-rw---- 1 mysql mysql  11M Nov 23 14:50 functions.ibd
-rw-rw---- 1 mysql mysql  11M Nov  8 16:07 images.ibd
-rw-rw---- 1 mysql mysql  10M Nov 29 09:32 history_str.ibd

2.0.2, DM setup (1 master,1 child); this is from the master

[heaje]

+--------------+------------+----------------+---------------------------+-------------+--------------+------------+-------------------------+---------------------+
| 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 |  901640387 |             77 |                  109.1739 | 70067453952 |  28368125952 | 3163553792 |              96892.4844 | 2012-09-05 20:36:37 |
| history      |  138380712 |             81 |                  113.7794 | 11346657280 |   4398219264 |    6291456 |              15021.4844 | 2012-09-05 19:59:27 |
| trends_uint  |  144046891 |             78 |                   78.8489 | 11357945856 |            0 |   34603008 |              10864.7813 | 2012-09-05 23:29:33 |
| trends       |   48506007 |             72 |                   72.2985 |  3506913280 |            0 |   13631488 |               3357.4531 | 2012-09-05 23:21:49 |
| history_text |    1250820 |             93 |                  154.4850 |   116523008 |     76709888 |   30408704 |                213.2813 | 2012-09-05 20:36:06 |
| history_str  |     273142 |             57 |                  111.5692 |    15745024 |     14729216 |    7340032 |                 36.0625 | 2012-09-05 20:36:01 |
| events       |     116466 |             81 |                  218.0482 |     9453568 |     15941632 |   11534336 |                 35.2188 | 2012-09-05 19:59:18 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |          0 |                  0.0469 | 2012-09-05 20:36:01 |
+--------------+------------+----------------+---------------------------+-------------+--------------+------------+-------------------------+---------------------+

 96G Nov 29 16:50 history_uint.ibd
 15G Nov 29 16:50 history.ibd
 11G Nov 29 16:50 trends_uint.ibd
3.4G Nov 29 16:48 trends.ibd
220M Nov 29 16:50 history_text.ibd
 44M Nov 29 13:12 alerts.ibd
 40M Nov 28 22:11 auditlog_details.ibd
 40M Nov 29 16:09 auditlog.ibd
 40M Nov 29 16:39 events.ibd
 40M Nov 29 16:42 history_str.ibd
 40M Nov 29 16:48 items.ibd
 19M Nov 29 16:09 acknowledges.ibd
 17M Nov 28 22:02 items_applications.ibd
 16M Nov 29 16:47 triggers.ibd

Zabbix 2.0.2 on RHEL5 x64

[mushero]

Zabbix 1.8.3

DB:

| TABLE_NAME   | TABLE_ROWS | AVG_ROW_LENGTH | AVG_ROW_LENGTH_CALCULATED | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | SUMMARY_SPACE_IN_MBYTES | CREATE_TIME         |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+
| history      |  106064172 |             55 |                   86.8214 |  5853167616 |   3355475968 |   6291456 |               8788.0469 | 2011-06-09 14:31:44 |
| history_uint |   71734485 |             78 |                  118.9720 |  5636112384 |   2898280448 |  20971520 |               8159.0313 | 2011-06-09 14:31:44 |
| trends_uint  |   56386826 |             88 |                   88.9133 |  5013536768 |            0 | 699400192 |               5448.2813 | 2011-06-09 14:31:44 |
| trends       |   59251540 |             80 |                   80.8585 |  4790992896 |            0 |   8388608 |               4577.0469 | 2011-06-09 14:31:44 |
| history_str  |   11493482 |            125 |                  177.0903 |  1443905536 |    591478784 |  32505856 |               1972.0938 | 2011-06-09 14:31:44 |
| history_text |     573923 |            118 |                  206.8258 |    68255744 |     50446336 | 701497344 |                782.2031 | 2011-06-09 14:31:44 |
| events       |     425063 |             71 |                  269.2745 |    30474240 |     83984384 | 187695104 |                288.1563 | 2011-07-29 15:45:43 |
| history_log  |          0 |              0 |                      NULL |       16384 |        32768 |         0 |                  0.0469 | 2011-06-09 14:31:44 |
+--------------+------------+----------------+---------------------------+-------------+--------------+-----------+-------------------------+---------------------+

Files:

-rw-rw---- 1 mysql mysql 8.8G Jan  3 09:11 history.ibd
-rw-rw---- 1 mysql mysql 8.1G Jan  3 09:11 history_uint.ibd
-rw-rw---- 1 mysql mysql 5.4G Jan  3 09:11 trends_uint.ibd
-rw-rw---- 1 mysql mysql 4.6G Jan  3 09:11 trends.ibd
-rw-rw---- 1 mysql mysql 2.0G Jan  3 09:11 history_str.ibd
-rw-rw---- 1 mysql mysql 796M Jan  3 09:07 history_text.ibd
-rw-rw---- 1 mysql mysql 516M Oct 18 15:28 alerts.ibd
-rw-rw---- 1 mysql mysql 308M Jan  3 08:25 auditlog.ibd
-rw-rw---- 1 mysql mysql 296M Jan  3 09:06 events.ibd
-rw-rw---- 1 mysql mysql 128M Jan  3 09:11 items.ibd
-rw-rw---- 1 mysql mysql 104M Jan  2 02:28 auditlog_details.ibd
-rw-rw---- 1 mysql mysql  48M Dec 31 14:35 items_applications.ibd
-rw-rw---- 1 mysql mysql  48M Jan  3 08:59 triggers.ibd
-rw-rw---- 1 mysql mysql  27M Jan  2 03:15 functions.ibd