Mysql memory calculator, check my math Plz

[rknobbe]

I've been getting oom-killer messages since I added a new camera. Rather than use a calculator to try to add up the total memory required, I figured I'd let MySQL do the work.

Somebody please check my math. Maybe others will benefit from the mysql script.

Code: Select all

mysql> select x.Id, x.Width, x.Height, x.ImageBufferCount, x.Colours, x.BufferSpace as BufferMB, 1.2*sum(x.BufferSpace) over (Order by Id) as RunningTotalMB_w_OH from (select Id, Width,Height,ImageBufferCount,Colours,(W
idth*Height*ImageBufferCount*Colours/1024/1024) as BufferSpace  from Monitors order by Id) x;
+----+-------+--------+------------------+---------+---------------+---------------------+
| Id | Width | Height | ImageBufferCount | Colours | BufferMB      | RunningTotalMB_w_OH |
+----+-------+--------+------------------+---------+---------------+---------------------+
|  1 |  1920 |   1080 |              100 |       3 |  593.26171875 |       711.914062500 |
|  2 |  1920 |   1080 |               50 |       3 |  296.63085938 |      1067.871093756 |
|  5 |  1920 |   1080 |               40 |       3 |  237.30468750 |      1352.636718756 |
|  6 |   704 |    480 |               20 |       3 |   19.33593750 |      1375.839843756 |
|  7 |   704 |    480 |               20 |       3 |   19.33593750 |      1399.042968756 |
|  8 |  1920 |   1080 |               20 |       3 |  118.65234375 |      1541.425781256 |
|  9 |   704 |    480 |               20 |       3 |   19.33593750 |      1564.628906256 |
| 10 |   704 |    480 |               20 |       3 |   19.33593750 |      1587.832031256 |
| 11 |   640 |    480 |               20 |       1 |    5.85937500 |      1594.863281256 |
| 12 |   480 |    360 |               20 |       1 |    3.29589844 |      1598.818359384 |
| 13 |  2560 |   1920 |              110 |       4 | 2062.50000000 |      4073.818359384 |
| 14 |  2560 |   1920 |              121 |       4 | 2268.75000000 |      6796.318359384 |
| 15 |   640 |    480 |               20 |       4 |   23.43750000 |      6824.443359384 |
+----+-------+--------+------------------+---------+---------------+---------------------+
13 rows in set (3.46 sec)

[burger]

Very cool. I added to wiki.zoneminder.com/MySQL. Not sure if this addresses the difference between ZMA and ZMC though. In my case, Htop says ZMC -m36 below (Record only) has 6% of memory while ZMA -m7 has 5% of memory. The numbers below don't line up. (Not that I know what I'm talking about).

ZMC == record
ZMA == analysis/motion detection

You can verify a given process memory use via /proc/<pid>/status ofc.

Code: Select all

+----+-------+--------+------------------+---------+---------------+---------------------+
| Id | Width | Height | ImageBufferCount | Colours | BufferMB      | RunningTotalMB_w_OH |
+----+-------+--------+------------------+---------+---------------+---------------------+
|  1 |  1280 |    720 |               50 |       4 |  175.78125000 |       210.937500000 |
|  4 |   800 |    600 |               50 |       4 |   91.55273438 |       742.675781256 |
|  5 |  1280 |    720 |               50 |       4 |  175.78125000 |       953.613281256 |
|  6 |   800 |    600 |               50 |       4 |   91.55273438 |      1063.476562512 |
|  7 |  1280 |   1024 |               50 |       4 |  250.00000000 |      1363.476562512 |
| 10 |   800 |    600 |               50 |       4 |   91.55273438 |      1473.339843768 |
| 11 |  1280 |    720 |               50 |       4 |  175.78125000 |      1684.277343768 |
| 14 |  1280 |    720 |               50 |       4 |  175.78125000 |      2317.089843768 |
| 15 |  1280 |    720 |               50 |       3 |  131.83593750 |      2475.292968768 |
| 17 |   704 |    480 |               50 |       4 |   64.45312500 |      2552.636718768 |
| 18 |   704 |    480 |               50 |       4 |   64.45312500 |      2629.980468768 |
| 21 |  1280 |    720 |               50 |       4 |  175.78125000 |      2840.917968768 |
| 24 |   352 |    240 |               50 |       4 |   16.11328125 |      3014.941406268 |
| 25 |   640 |    480 |               50 |       4 |   58.59375000 |      3085.253906268 |
| 26 |  1280 |    720 |               50 |       4 |  175.78125000 |      3296.191406268 |
| 27 |   800 |    600 |               50 |       4 |   91.55273438 |      3406.054687524 |
| 28 |   640 |    480 |               50 |       4 |   58.59375000 |      3476.367187524 |
| 29 |   800 |    600 |               25 |       4 |   45.77636719 |      3531.298828152 |
| 30 |  1280 |    720 |               50 |       4 |  175.78125000 |      3742.236328152 |
| 31 |   800 |    600 |               50 |       3 |   68.66455078 |      3824.633789088 |
| 32 |   800 |    600 |               50 |       4 |   91.55273438 |      3934.497070344 |
| 34 |   640 |    480 |              125 |       4 |  146.48437500 |      4220.141601600 |
| 35 |   640 |    360 |              100 |       4 |   87.89062500 |      4325.610351600 |
| 36 |  2560 |   1920 |              100 |       4 | 1875.00000000 |      6575.610351600 |
+----+-------+--------+------------------+---------+---------------+---------------------+

(Some entries have been removed for brevity)

[Edo911]

Visit the Releem MySQL Memory Calculator https://releem.com/tools/mysql-memory-calculator.

Input your MySQL configuration values to estimate memory requirements accurately.

Compare the calculated memory usage with your server's available memory to ensure you're not over-committing.