Recherche avancée

Sur d’autres sites (7428)

• Very slow writes on MySQL 8 - waiting for handler commit

  23 mai 2023, par Akshat Goel

  I have MySQL 8 docker installation installed on an edge device which has the following two tables to write to

  


  video_paths | CREATE TABLE `video_paths` (
  `entry` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` bigint(20) NOT NULL,
  `duration` int(11) NOT NULL,
  `path` varchar(255) NOT NULL,
  `motion` int(11) NOT NULL DEFAULT '0',
  `cam_id` varchar(255) NOT NULL DEFAULT '',
  `hd` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entry`),
  KEY `cam_id` (`cam_id`),
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=7342309 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


  


  AND

  


  CREATE TABLE `tracker` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(255) NOT NULL,
  `primary_key_name` varchar(255) NOT NULL,
  `pointer` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `table_name` (`table_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


  


  The following queries are run every few secs for up to 32 cameras and are taking a lot of time as indicated by the slow query log.

  


  UPDATE tracker SET pointer = 7342046 WHERE table_name = 'video_paths'

INSERT INTO video_paths (timestamp,duration,path,cam_id,hd) VALUES (1597548365000,5000,'/s/ss/x-0/v/2020-08-16/3/1.ts','x-1',1)



  


  Most of the time is spent in the waiting for handler commit state

  


  The total size of my data (tables + index) is 1GB and I have the following settings enabled to optimise for write

  


  skip-log-bin - Disabled the bin log because I don't have a replica and therefore no use for it
innodb_flush_log_at_trx_commit =2 - I am Optimising for performance rather than consistency here.
range_optimizer_max_mem_size =0 As mention in this question, I have allowed max memory to range optimiser.
inndo_buffer_pool_size= 512Mb - This should be enough for my data ?.
  
innodb_log_file_size= 96Mb *2 files

  


  I am seeing queries that are taking up to 90-100 secs sometimes.

  


  SET timestamp=1597549337;
INSERT INTO video_paths (timestamp,duration,path,cam_id,hd) VALUES (1597548365000,5000,'/s/ss/x-0/v/2020-08-16/3/1.ts','x-1',1);
# Time: 2020-08-16T03:42:24.533408Z
# Query_time: 96.712976  Lock_time: 0.000033 Rows_sent: 0  Rows_examined: 0


  


  ---UPDATE---
Here's the complete my.cnf file

  


  my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

skip-log-bin
innodb_buffer_pool_size=536870912
innodb_log_file_size=100663296

# Custom config should go here
!includedir /etc/mysql/conf.d/

conf.d/docker.cnf 
[mysqld]
skip-host-cache
skip-name-resolve 


  


  The docker container is using the host mode so complete 15GB memory is available to the container.

  


  --- UPDATE 2 ---
After increasing the innodb_buffer_pool_size to 2GB as suggested by @fyrye, the statements have now started getting stuck on STATE = UPDATE instead of waiting for handler commit.

  


  ---- UPDATE 3 ---
Looks like the CPU is causing the bottleneck


  


  ** ---- UPDATE 4 ---- **
Additional info

  


  

  1. Ram Size

  


  


                total        used        free      shared  buff/cache   available
Mem:          15909        1711        9385        2491        4813       11600
Swap:             0           0           0


  


  

  2. No SSD/NVMe devices attached

  


  3. SHOW GLOBAL STATUS - https://pastebin.com/vtWi0PUq

  


  4. SHOW GLOBAL VARIABLES - https://pastebin.com/MUZeG959

  


  5. SHOW FULL PROCESSLIST - https://pastebin.com/eebEcYk7

  


  6. htop - htop here is for the edge system which has 4 other containers running which include the main app, ffmpeg, mqtt, etc.


  


  7. ulimit -a :

  


  


  core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 62576
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 62576
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


  


  

  8. opstat -xm 5 4

  


  


  Linux 4.15.0-106-generic (xxxx)    08/18/2020  _x86_64_    (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
         26.97    0.00   22.36   22.53    0.00   28.14

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.00    0.00     0.00     0.00     3.20     0.00    2.40    2.40    0.00   0.00   0.00
sda              13.78     9.89   32.24   11.44     0.37     4.10   209.51    47.52 1079.07   44.07 3994.87  22.39  97.81

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
         19.71    0.00   27.85   40.87    0.00   11.57

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
sda               0.00     0.00    1.40    4.60     0.03     2.71   934.93   142.66 24221.33  666.29 31390.26 166.67 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
         20.16    0.00   26.77   28.30    0.00   24.77

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
sda               0.00     0.00    8.80    5.60     0.03     3.45   496.11   141.28 12507.78  194.00 31858.00  69.44 100.00


  


  

  9. mpstat -P ALL 5 3

  


  


  Linux 4.15.0-106-generic (sn-1f0ce8)    08/18/2020  _x86_64_    (4 CPU)

02:15:47 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:15:52 PM  all   21.48    0.00   20.40   29.01    0.00    7.94    0.00    0.00    0.00   21.17
02:15:52 PM    0   24.95    0.00   20.86    5.32    0.00    0.61    0.00    0.00    0.00   48.26
02:15:52 PM    1   17.59    0.00   18.81   57.67    0.00    5.93    0.00    0.00    0.00    0.00
02:15:52 PM    2   21.28    0.00   17.36    0.21    0.00   24.79    0.00    0.00    0.00   36.36
02:15:52 PM    3   22.34    0.00   24.59   52.46    0.00    0.61    0.00    0.00    0.00    0.00

02:15:52 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:15:57 PM  all   20.56    0.00   20.00   28.26    0.00    7.08    0.00    0.00    0.00   24.10
02:15:57 PM    0   24.44    0.00   18.89   12.32    0.00    0.21    0.00    0.00    0.00   44.15
02:15:57 PM    1   17.73    0.00   15.46   33.20    0.00    4.95    0.00    0.00    0.00   28.66
02:15:57 PM    2   18.93    0.00   22.22   12.35    0.00   22.84    0.00    0.00    0.00   23.66
02:15:57 PM    3   21.06    0.00   23.31   55.21    0.00    0.41    0.00    0.00    0.00    0.00

02:15:57 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:16:02 PM  all   21.81    0.00   18.32   26.42    0.00    7.03    0.00    0.00    0.00   26.42
02:16:02 PM    0   26.43    0.00   19.67    0.20    0.00    0.41    0.00    0.00    0.00   53.28
02:16:02 PM    1   20.57    0.00   17.11   45.21    0.00    5.30    0.00    0.00    0.00   11.81
02:16:02 PM    2   19.67    0.00   16.74    0.21    0.00   21.97    0.00    0.00    0.00   41.42
02:16:02 PM    3   20.45    0.00   19.84   58.91    0.00    0.81    0.00    0.00    0.00    0.00

Average:     CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
Average:     all   21.28    0.00   19.57   27.90    0.00    7.35    0.00    0.00    0.00   23.90
Average:       0   25.27    0.00   19.81    5.94    0.00    0.41    0.00    0.00    0.00   48.57
Average:       1   18.63    0.00   17.13   45.39    0.00    5.39    0.00    0.00    0.00   13.45
Average:       2   19.96    0.00   18.78    4.28    0.00   23.20    0.00    0.00    0.00   33.77
Average:       3   21.28    0.00   22.57   55.54    0.00    0.61    0.00    0.00    0.00    0.00


  


• ffmpeg youtube livestream stops after a while

  15 janvier 2021, par ohroblot

  I'll update this question

  


  ffmpeg -version

  


  ffmpeg -version
ffmpeg version 4.3.1-4ubuntu1 Copyright (c) 2000-2020 the FFmpeg developers
built with gcc 10 (Ubuntu 10.2.0-9ubuntu2)


  


  I run this command to use ffmpeg to stream to youtube ;

  


  ffmpeg -y -threads 12 \
-loop 1 -framerate 30 -re \
-i ./1280x720.jpg \
-i ./audio.mp3 \
-video_size 1280x720 \
-vcodec libx264 -pix_fmt yuv420p \
-b:v 4500k -maxrate 5500k -bufsize 22000k \
-preset ultrafast -crf 23 -tune stillimage \
-b:a 128k -ar 44100 -ac 2 -acodec aac \
-filter_complex "dynaudnorm=f=150:g=15" \
-r 30 -g 60 \
-f flv rtmp://a.rtmp.youtube.com/live2/xxxx 2>&1 | tee _LOG


  


  The stream is excellent for 45-53 minutes then i'll get an error like this from ffmpeg :

  


  [flv @ 0x56077027cd80] Delay between the first packet and last packet in the muxing queue is 10034000 > 10000000: forcing output


  


  then youtube starts to say, no data being received and the stream will end, which it does.

  


  This is the full log : http://0x0.st/-zUH.txt

  


• FFMPEG fade in/out expressed in milliseconds

  22 juillet 2020, par orestino

  I working on a programmatic sketch to cut, crop and fade in/out audio from a single input video file.
Everything works pretty well except for the audio fade in fade out.

  


  Everything work fine if I set a fade in and fade out time of 1 (or more) seconds. Here's my command :

  


  ffmpeg -ss 0:1:11.10 -i INPUT_FILE.mp4 -filter:a afade=t=in:st=0:d=1.0,afade=t=out:st=55.867:d=1.0 -filter:v crop=720:720:138:0 -to 0:0:56.86 OUTPUT_FILE.mp4


  


  Fade filter stop working if I set time less than 1 secs (say 100ms like the following example).

  


  ffmpeg -ss 0:1:11.10 -i INPUT_FILE.mp4 -filter:a afade=t=in:st=0:d=0.1,afade=t=out:st=56.767:d=0.1 -filter:v crop=720:720:138:0 -to 0:0:56.86 OUTPUT_FILE.mov


  


  I mean, ffmpeg is not complaining about any error but the output video file simply has no fade in or fade out.

  


  Why ?
What I'm doing wrong ?

  


  Thank you so much for your support

  


  Edit 22/07/2020 :
here's the output from the second command :

  


  ffmpeg -ss 0:1:11.10 -i 2020-07-20_00-13-35.mkv -filter:a afade=t=in:st=0:d=0.1,afade=t=out:st=56.767:d=0.1 -filter:v crop=720:720:138:0 -to 0:0:56.86 ./_selections/2020-07-20_00-13-35_R1_crop2_w_fadein_fadeout_0:1:11.10.mov
ffmpeg version 3.4.6-0ubuntu0.18.04.1 Copyright (c) 2000-2019 the FFmpeg developers
  built with gcc 7 (Ubuntu 7.3.0-16ubuntu3)
  configuration: --prefix=/usr --extra-version=0ubuntu0.18.04.1 --toolchain=hardened --libdir=/usr/lib/x86_64-linux-gnu --incdir=/usr/include/x86_64-linux-gnu --enable-gpl --disable-stripping --enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa --enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca --enable-libcdio --enable-libflite --enable-libfontconfig --enable-libfreetype --enable-libfribidi --enable-libgme --enable-libgsm --enable-libmp3lame --enable-libmysofa --enable-libopenjpeg --enable-libopenmpt --enable-libopus --enable-libpulse --enable-librubberband --enable-librsvg --enable-libshine --enable-libsnappy --enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora --enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack --enable-libwebp --enable-libx265 --enable-libxml2 --enable-libxvid --enable-libzmq --enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2 --enable-libdc1394 --enable-libdrm --enable-libiec61883 --enable-chromaprint --enable-frei0r --enable-libopencv --enable-libx264 --enable-shared
  WARNING: library configuration mismatch
  avcodec     configuration: --prefix=/usr --extra-version=0ubuntu0.18.04.1 --toolchain=hardened --libdir=/usr/lib/x86_64-linux-gnu --incdir=/usr/include/x86_64-linux-gnu --enable-gpl --disable-stripping --enable-avresample --enable-avisynth --enable-gnutls --enable-ladspa --enable-libass --enable-libbluray --enable-libbs2b --enable-libcaca --enable-libcdio --enable-libflite --enable-libfontconfig --enable-libfreetype --enable-libfribidi --enable-libgme --enable-libgsm --enable-libmp3lame --enable-libmysofa --enable-libopenjpeg --enable-libopenmpt --enable-libopus --enable-libpulse --enable-librubberband --enable-librsvg --enable-libshine --enable-libsnappy --enable-libsoxr --enable-libspeex --enable-libssh --enable-libtheora --enable-libtwolame --enable-libvorbis --enable-libvpx --enable-libwavpack --enable-libwebp --enable-libx265 --enable-libxml2 --enable-libxvid --enable-libzmq --enable-libzvbi --enable-omx --enable-openal --enable-opengl --enable-sdl2 --enable-libdc1394 --enable-libdrm --enable-libiec61883 --enable-chromaprint --enable-frei0r --enable-libopencv --enable-libx264 --enable-shared --enable-version3 --disable-doc --disable-programs --enable-libopencore_amrnb --enable-libopencore_amrwb --enable-libtesseract --enable-libvo_amrwbenc
  libavutil      55. 78.100 / 55. 78.100
  libavcodec     57.107.100 / 57.107.100
  libavformat    57. 83.100 / 57. 83.100
  libavdevice    57. 10.100 / 57. 10.100
  libavfilter     6.107.100 /  6.107.100
  libavresample   3.  7.  0 /  3.  7.  0
  libswscale      4.  8.100 /  4.  8.100
  libswresample   2.  9.100 /  2.  9.100
  libpostproc    54.  7.100 / 54.  7.100
Input #0, matroska,webm, from '2020-07-20_00-13-35.mkv':
  Metadata:
    ENCODER         : Lavf57.83.100
  Duration: 00:03:19.23, start: 0.000000, bitrate: 2661 kb/s
    Stream #0:0: Video: h264 (High), yuv420p(progressive), 1440x900, 30 fps, 30 tbr, 1k tbn, 60 tbc (default)
    Metadata:
      DURATION        : 00:03:19.233000000
    Stream #0:1: Audio: aac (LC), 44100 Hz, stereo, fltp (default)
    Metadata:
      title           : audio desktop
      DURATION        : 00:03:19.065000000
Stream mapping:
  Stream #0:0 -> #0:0 (h264 (native) -> h264 (libx264))
  Stream #0:1 -> #0:1 (aac (native) -> aac (native))
Press [q] to stop, [?] for help
[libx264 @ 0x564dad2f9680] using cpu capabilities: MMX2 SSE2Fast SSSE3 SSE4.2 AVX FMA3 BMI2 AVX2
[libx264 @ 0x564dad2f9680] profile High, level 3.1
[libx264 @ 0x564dad2f9680] 264 - core 152 r2854 e9a5903 - H.264/MPEG-4 AVC codec - Copyleft 2003-2017 - http://www.videolan.org/x264.html - options: cabac=1 ref=3 deblock=1:0:0 analyse=0x3:0x113 me=hex subme=7 psy=1 psy_rd=1.00:0.00 mixed_ref=1 me_range=16 chroma_me=1 trellis=1 8x8dct=1 cqm=0 deadzone=21,11 fast_pskip=1 chroma_qp_offset=-2 threads=6 lookahead_threads=1 sliced_threads=0 nr=0 decimate=1 interlaced=0 bluray_compat=0 constrained_intra=0 bframes=3 b_pyramid=2 b_adapt=1 b_bias=0 direct=1 weightb=1 open_gop=0 weightp=2 keyint=250 keyint_min=25 scenecut=40 intra_refresh=0 rc_lookahead=40 rc=crf mbtree=1 crf=23.0 qcomp=0.60 qpmin=0 qpmax=69 qpstep=4 ip_ratio=1.40 aq=1:1.00
Output #0, mov, to './_selections/2020-07-20_00-13-35_R1_crop2_w_fadein_fadeout_0:1:11.10.mov':
  Metadata:
    encoder         : Lavf57.83.100
    Stream #0:0: Video: h264 (libx264) (avc1 / 0x31637661), yuv420p, 720x720, q=-1--1, 30 fps, 15360 tbn, 30 tbc (default)
    Metadata:
      DURATION        : 00:03:19.233000000
      encoder         : Lavc57.107.100 libx264
    Side data:
      cpb: bitrate max/min/avg: 0/0/0 buffer size: 0 vbv_delay: -1
    Stream #0:1: Audio: aac (LC) (mp4a / 0x6134706D), 44100 Hz, stereo, fltp, 128 kb/s (default)
    Metadata:
      title           : audio desktop
      DURATION        : 00:03:19.065000000
      encoder         : Lavc57.107.100 aac
frame= 1706 fps=140 q=-1.0 Lsize=    2685kB time=00:00:56.86 bitrate= 386.7kbits/s speed=4.67x    
video:1681kB audio:942kB subtitle:0kB other streams:0kB global headers:0kB muxing overhead: 2.351145%
[libx264 @ 0x564dad2f9680] frame I:7     Avg QP:15.76  size: 60653
[libx264 @ 0x564dad2f9680] frame P:448   Avg QP:23.13  size:  1372
[libx264 @ 0x564dad2f9680] frame B:1251  Avg QP:31.17  size:   545
[libx264 @ 0x564dad2f9680] consecutive B-frames:  1.3%  2.3%  1.4% 95.0%
[libx264 @ 0x564dad2f9680] mb I  I16..4: 29.2% 37.7% 33.1%
[libx264 @ 0x564dad2f9680] mb P  I16..4:  0.1%  0.4%  0.5%  P16..4:  2.1%  0.2%  0.2%  0.0%  0.0%    skip:96.4%
[libx264 @ 0x564dad2f9680] mb B  I16..4:  0.0%  0.3%  0.2%  B16..8:  1.5%  0.1%  0.1%  direct: 0.0%  skip:97.8%  L0:51.7% L1:46.3% BI: 2.0%
[libx264 @ 0x564dad2f9680] 8x8 transform intra:43.2% inter:27.7%
[libx264 @ 0x564dad2f9680] coded y,uvDC,uvAC intra: 35.4% 42.2% 40.1% inter: 0.3% 0.5% 0.3%
[libx264 @ 0x564dad2f9680] i16 v,h,dc,p: 61% 37%  2%  0%
[libx264 @ 0x564dad2f9680] i8 v,h,dc,ddl,ddr,vr,hd,vl,hu: 30%  7% 59%  2%  0%  0%  0%  1%  1%
[libx264 @ 0x564dad2f9680] i4 v,h,dc,ddl,ddr,vr,hd,vl,hu: 19% 17% 25% 10%  4%  5%  5%  7%  7%
[libx264 @ 0x564dad2f9680] i8c dc,h,v,p: 66% 19%  7%  7%
[libx264 @ 0x564dad2f9680] Weighted P-Frames: Y:0.0% UV:0.0%
[libx264 @ 0x564dad2f9680] ref P L0: 65.1% 15.3% 13.6%  5.9%
[libx264 @ 0x564dad2f9680] ref B L0: 68.1% 27.1%  4.8%
[libx264 @ 0x564dad2f9680] ref B L1: 95.3%  4.7%
[libx264 @ 0x564dad2f9680] kb/s:242.09
[aac @ 0x564dad2f25a0] Qavg: 606.451