Recherche avancée

Sur d’autres sites (5723)

• 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


  


• How to overlay 2 videos, one is main second one is overlaying it, and play sound simultaneously. using FFMPEG in ANDROID STUDIO

  6 août 2020, par Dusan Lilic

  as title say I'm trying to overlay 2 videos and play sound simultaneously. So far i managed to put 1 video over another using this command :

  


  String[] command = {"-i", mainVideoPath, "-vf",
            "movie=" + overlayVideo + ", scale=300:-1[inner]; [in][inner]overlay=10:10[out]" ,combinedVideoOutput};


  


  and this works but I have 3 problems here.
First, video is rotated by 90 degrees (overlay video), second Audio is played only from main video (I want to play sound from both videos simultaneously), and third overlay video is longer (for example : overlayVideo duration is 10 seconds and main video last 7 seconds) then mainVideo, so i want to final video last as long as mainVideo, as soon as mainVideo finish, overlayVideo should also stop (need to cut it prolly ?)

  


  String[] command = {"-i", mainVideoPath, "-i", overlayVideo ,
            "-filter_complex", "[1:v][0:v]scale2ref=(256/256)*ih/8/sar:ih/8[wm][base];[base][wm]overlay=10:10" ,combinedVideoOutput};


  


  Using this command i have 2 problems same as above except video is not rotated here.
I have to say that I'm not very familiar with ffmpeg commands. I was trying to figure it out from documentation link to documentation but without any success.
I know that I'm missing some filters like -map merge or something but can't figure it out.
Thanks in advance !

  


  EDIT1 :
This is logcat from second commad as asked

  


  D/LISKO: ffmpeg version n4.0-39-gda39990 Copyright (c) 2000-2018 the FFmpeg developers
      built with gcc 4.9.x (GCC) 20150123 (prerelease)
D/LISKO:   configuration: --target-os=linux --cross-prefix=/root/bravobit/ffmpeg-android/toolchain-android/bin/arm-linux-androideabi- --arch=arm --cpu=cortex-a8 --enable-runtime-cpudetect --sysroot=/root/bravobit/ffmpeg-android/toolchain-android/sysroot --enable-pic --enable-libx264 --enable-ffprobe --enable-libopus --enable-libvorbis --enable-libfdk-aac --enable-libfreetype --enable-libfribidi --enable-libmp3lame --enable-fontconfig --enable-libvpx --enable-libass --enable-yasm --enable-pthreads --disable-debug --enable-version3 --enable-hardcoded-tables --disable-ffplay --disable-linux-perf --disable-doc --disable-shared --enable-static --enable-runtime-cpudetect --enable-nonfree --enable-network --enable-avresample --enable-avformat --enable-avcodec --enable-indev=lavfi --enable-hwaccels --enable-ffmpeg --enable-zlib --enable-gpl --enable-small --enable-nonfree --pkg-config=pkg-config --pkg-config-flags=--static --prefix=/root/bravobit/ffmpeg-android/build/armeabi-v7a --extra-cflags='-I/root/bravobit/ffmpeg-android/toolchain-android/include -U_FORTIFY_SOURCE -D_FORTIFY_SOURCE=2 -fno-strict-overflow -fstack-protector-all' --extra-ldflags='-L/root/bravobit/ffmpeg-android/toolchain-android/lib -Wl,-z,relro -Wl,-z,now -pie' --extra-cxxflags=
D/LISKO:   libavutil      56. 14.100 / 56. 14.100
      libavcodec     58. 18.100 / 58. 18.100
      libavformat    58. 12.100 / 58. 12.100
      libavdevice    58.  3.100 / 58.  3.100
      libavfilter     7. 16.100 /  7. 16.100
D/LISKO:   libavresample   4.  0.  0 /  4.  0.  0
      libswscale      5.  1.100 /  5.  1.100
D/LISKO:   libswresample   3.  1.100 /  3.  1.100
      libpostproc    55.  1.100 / 55.  1.100
D/LISKO: Input #0, mov,mp4,m4a,3gp,3g2,mj2, from '/storage/emulated/0/mainVideo.mp4':
      Metadata:
        major_brand     : iso6
        minor_version   : 1
        compatible_brands: mp42iso6avc1isom
        creation_time   : 2020-08-03T13:20:11.000000Z
      Duration: 00:00:07.04, start: 0.000000, bitrate: 1380 kb/s
        Stream #0:0(und): Audio: aac (mp4a / 0x6134706D), 44100 Hz, stereo, fltp, 140 kb/s (default)
        Metadata:
          creation_time   : 2020-07-28T08:11:36.000000Z
        Stream #0:1(und): Video: h264 (avc1 / 0x31637661), yuv420p(tv, bt709), 1280x720 [SAR 81:256 DAR 9:16], 1264 kb/s, 30 fps, 30 tbr, 90k tbn, 180k tbc (default)
        Metadata:
          creation_time   : 2020-07-28T08:11:36.000000Z
D/LISKO: Input #1, mov,mp4,m4a,3gp,3g2,mj2, from '/storage/emulated/0/overlayVideo.mp4':
      Metadata:
        major_brand     : mp42
        minor_version   : 0
        compatible_brands: isommp42
        creation_time   : 2020-08-04T07:27:47.000000Z
        com.android.version: 10
      Duration: 00:00:11.19, start: 0.000000, bitrate: 9993 kb/s
D/LISKO:     Stream #1:0(eng): Video: h264 (avc1 / 0x31637661), yuv420p(tv, bt709), 1280x720, 9238 kb/s, SAR 1:1 DAR 16:9, 28.38 fps, 29.75 tbr, 90k tbn, 180k tbc (default)
        Metadata:
          rotate          : 270
          creation_time   : 2020-08-04T07:27:47.000000Z
          handler_name    : VideoHandle
        Side data:
          displaymatrix: rotation of 90.00 degrees
        Stream #1:1(eng): Audio: aac (mp4a / 0x6134706D), 48000 Hz, stereo, fltp, 192 kb/s (default)
        Metadata:
          creation_time   : 2020-08-04T07:27:47.000000Z
          handler_name    : SoundHandle
    Stream mapping:
      Stream #0:1 (h264) -> scale2ref:ref (graph 0)
      Stream #1:0 (h264) -> scale2ref:default (graph 0)
      overlay (graph 0) -> Stream #0:0 (libx264)
      Stream #0:0 -> #0:1 (aac (native) -> aac (native))
    Press [q] to stop, [?] for help
D/LISKO: [libx264 @ 0xee986100] using SAR=81/256
D/LISKO: [libx264 @ 0xee986100] using cpu capabilities: ARMv6 NEON
    [libx264 @ 0xee986100] profile High, level 3.1
D/LISKO: [libx264 @ 0xee986100] 264 - core 152 r2851M ba24899 - 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=12 lookahead_threads=2 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
D/LISKO: Output #0, mp4, to '/storage/emulated/0/outputVideo.mp4':
D/LISKO:   Metadata:
        major_brand     : iso6
D/LISKO:     minor_version   : 1
D/LISKO:     compatible_brands: mp42iso6avc1isom
D/LISKO:     encoder         : Lavf58.12.100
        Stream #0:0: Video: h264 (libx264) (avc1 / 0x31637661), yuv420p, 1280x720 [SAR 81:256 DAR 9:16], q=-1--1, 30 fps, 15360 tbn, 30 tbc (default)
D/LISKO:     Metadata:
          encoder         : Lavc58.18.100 libx264
        Side data:
          cpb: bitrate max/min/avg: 0/0/0 buffer size: 0 vbv_delay: -1
D/LISKO:     Stream #0:1(und): Audio: aac (mp4a / 0x6134706D), 44100 Hz, stereo, fltp, 128 kb/s (default)
        Metadata:
          creation_time   : 2020-07-28T08:11:36.000000Z
D/LISKO:       encoder         : Lavc58.18.100 aac
D/LISKO: frame=   26 fps=0.0 q=0.0 size=       0kB time=00:00:00.09 bitrate=   4.1kbits/s dup=2 drop=0 speed=0.185x    
D/LISKO: frame=   41 fps= 41 q=0.0 size=       0kB time=00:00:00.58 bitrate=   0.7kbits/s dup=2 drop=0 speed=0.574x    
D/LISKO: frame=   49 fps= 32 q=0.0 size=       0kB time=00:00:00.92 bitrate=   0.4kbits/s dup=2 drop=0 speed=0.613x    
D/LISKO: frame=   59 fps= 29 q=29.0 size=       0kB time=00:00:01.97 bitrate=   0.2kbits/s dup=2 drop=0 speed=0.974x    
D/LISKO: frame=   75 fps= 29 q=29.0 size=       0kB time=00:00:01.97 bitrate=   0.2kbits/s dup=2 drop=0 speed=0.762x 


  


  EDIT2 :
After adding "-shortest" to command i managed to cut overlay video to be the same length as main video (because overlay video is always longer then mainVideo, "-shortest" take short one duration. So now, command looks like this :

  


      String[] command = {"-i", mainVideoPath, "-i", overlayVideo ,"-filter_complex", 
"[1:v][0:v]scale2ref=(256/256)*ih/8/sar:ih/8[wm][base];[base][wm]overlay=10:10", "-shortest", combinedVideoOutput};


  


  Rotation is good so only need to merge their audios. For now, only mainVideo audio is playing, overlay video audio isn't

  


  EDIT 3 :

  


     String[] command = {"-i", mainVideoPath, "-i", overlayVideo ,
            "-strict", "experimental",
            "-filter_complex",
            "[1:v][0:v]scale2ref=(256/256)*ih/8/sar:ih/8[wm][base];" +
                    "[base][wm]overlay=10:10; " +
                    "pan=stereo|c0=2*c0|c1=3*c0[a0];[1:a]pan=stereo|c0=1*c0|c1=4*c0[a1];[a0][a1]amix=inputs=2:duration=first:dropout_transition=2",
            "-shortest" ,combinedVideoOutput};


  


  With this command i managed to overlay videos, and play sound from both of them, rotation is good, but -shortest doesn't work now. Only existing problem now is to make them to last as shorter one (mainVideo is always shorter) ???

  


  EDIT 4 :

  


  This is finally working command

  


          String[] command = {"-i", mainVideoPath, "-i", overlayVideo,
            "-filter_complex",
            "[1:v][0:v]scale2ref=(256/256)*ih/8/sar:ih/8[wm][base];" +
                    "[base][wm]overlay=10:10:shortest=1;" +
                    "pan=stereo|c0=2*c0|c1=3*c0[a0];[1:a]pan=stereo|c0=1*c0|c1=4*c0[a1];" +
                    "[a0][a1]amix=inputs=2:duration=first:dropout_transition=2",
            combinedVideoOutput};


  


  Thanks

  


• avutil/internal : remove FF_ALLOCx{_ARRAY}_OR_GOTO macros

  2 juin 2020, par Limin Wang

  avutil/internal : remove FF_ALLOCx_ARRAY_OR_GOTO macros
  These functions have a terrible design, let us fix them before extending
  
  them.
  
  First design mistake : no error code. A helper function for testing
  
  memory allocation failure where AVERROR(ENOMEM) does not appear is
  
  absurd.
  Second design mistake : printing a message. Return the error code, let
  
  the caller print the error message.
  Third design mistake : hard-coded use of goto.
  http://ffmpeg.org/pipermail/ffmpeg-devel/2020-May/262544.html
  Signed-off-by : Limin Wang <lance.lmwang@gmail.com>
  

  • [DH] libavutil/internal.h