Mysql High cpu usage. Any tweaks to fix it?

Forum for questions and support relating to the 1.28.x releases only.
Locked
3gem
Posts: 19
Joined: Tue Apr 10, 2007 8:45 pm
Location: Los Angeles

Mysql High cpu usage. Any tweaks to fix it?

Post by 3gem »

I am running zm 1.28.2 on ubuntu and have one rtsp connection. The cpu usage for mysql is 133-143% is there a way to fix this? I have listed the output from top, and mysql for reference. Below shows that mysql has two of the same processes queued. Is that normal?

Code: Select all

 PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 1188 mysql     20   0 1340028 186464  10624 S 133.0  4.7   1177:10 mysqld

mysql> SHOW PROCESSLIST;
+-----+--------+-----------+------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| Id  | User   | Host      | db   | Command | Time  | State        | Info                                                                                                 |
+-----+--------+-----------+------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
|  78 | zmuser | localhost | zm   | Query   | 42942 | Sending data | select * from Events as E left join Frames as F on (E.Id = F.EventId) where isnull(F.EventId) and no |
| 305 | zmuser | localhost | zm   | Sleep   |     1 |              | NULL                                                                                                 |
| 314 | zmuser | localhost | zm   | Sleep   |    41 |              | NULL                                                                                                 |
| 335 | zmuser | localhost | zm   | Query   | 40863 | Sending data | select * from Events as E left join Frames as F on (E.Id = F.EventId) where isnull(F.EventId) and no |
| 339 | zmuser | localhost | zm   | Sleep   | 15266 |              | NULL                                                                                                 |
| 355 | zmuser | localhost | zm   | Sleep   |     8 |              | NULL                                                                                                 |
| 538 | zmuser | localhost | zm   | Sleep   |   305 |              | NULL                                                                                                 |
| 539 | zmuser | localhost | zm   | Sleep   |    15 |              | NULL                                                                                                 |
| 862 | root   | localhost | NULL | Query   |     0 | NULL         | SHOW PROCESSLIST                                                                                     |
| 863 | zmuser | localhost | zm   | Sleep   |    30 |              | NULL                                                                                                 |
+-----+--------+-----------+------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
The camera is set to ffmpeg. The capture is set to 1280x640. The camera is sending 20fps and keyframes every 11 seconds. I have tried libvlc, and the built in rtsp, but those options do not work wit modect, and cpu usage remains the same.

Any help is appreciated.
bbunge
Posts: 2930
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by bbunge »

Ubuntu version?

Also would be good to have some hardware info on your server...

Lower your resolution for a start..

bb
3gem
Posts: 19
Joined: Tue Apr 10, 2007 8:45 pm
Location: Los Angeles

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by 3gem »

Ubuntu version?

Also would be good to have some hardware info on your server...
I am running Ubuntu 14.04.3 server 4gb ram
Here is a snip from cpuinfo

Code: Select all

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Core(TM)2 Duo CPU     T9300  @ 2.50GHz
stepping        : 6
microcode       : 0x60f
cpu MHz         : 2501.000
cache size      : 6144 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 2
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good nopl aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 lahf_lm ida dtherm tpr_shadow vnmi flexpriority
bugs            :
bogomips        : 4987.72
clflush size    : 64
cache_alignment : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:
Thanks,
-3gem
aaronl
Posts: 33
Joined: Wed Aug 19, 2015 9:11 pm

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by aaronl »

Log into the camera and lower the resolution if you can, and definitely the frame rate. Try cutting the frame rate in half: 10fps. That should make a difference.

Aaron
3gem
Posts: 19
Joined: Tue Apr 10, 2007 8:45 pm
Location: Los Angeles

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by 3gem »

Lowering the frame rate helped a bit. I decided to dump the database and start over, now my cpu usage is at 12% total. Minimal mysql instead of 100% and 12-13% for the zma and zmc processes. I assume it was some sort of database corruption.

It is much better now. Thanks for the suggestions.

-3g
bbunge
Posts: 2930
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by bbunge »

Keep in mind that Ubuntu grabs half of the RAM to use as tmpfs or RAM disk. Knowing this I change some Zoneminder settings to tmpfs:

PATH_MAP and PATH_SWAP to /run/shm for Ubuntu 14.04
littlej
Posts: 68
Joined: Sun Oct 18, 2009 11:46 am

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by littlej »

i have same problem, but this appeared when i upgraded zm to 1.28.107, my load went from ~1.5 to almost ~3.5 only with a zoneminder upgrade :(
highel
Posts: 1
Joined: Wed May 05, 2021 1:29 pm

Re: Mysql High cpu usage. Any tweaks to fix it?

Post by highel »

I had a problem that after some time mysql was taking too much cpu time - I fixed it by simply running

Code: Select all

sudo mysqlcheck -o --all-databases
This operation took some time on large tables but now everything runs fast
Locked