Recherche avancée

Sur d’autres sites (14984)

• 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


  


• Use data to develop impactful video content

  28 septembre 2021, par Ben Erskine — Analytics Tips, Plugins

  Creating impactful video content is at the heart of what you do. How you really engage with your audience, change behaviours and influence customers to complete your digital goals. But how do you create truly impactful marketing content ? By testing, trialling, analysing and ultimately tweaking and reacting to data-informed insights that gear your content to your audience (rather than simply producing great content and shooting arrows in the dark).

  Whether you want to know how many plays your video has, finish rates, how your video is consumed over time, how video was consumed on specific days or even which locations users are viewing your video content. Media Analytics will gather all of your video data in one place and provide answers to all of these questions (and much more).

  

  What is impactful video content ?

  Impactful video content grabs your audience’s attention, keeps their attention and promotes them to take measurable action. Be that time spent on your website, goal completion or brand engagement (including following, commenting or sharing on social). Maybe you’ve developed video content, had some really great results, but not consistently, nor every time and it can be difficult to identify what exactly it is that engages and entices each and every time. And we all want to find where that lovely sweet spot is for your audience.

  Embedded video on your website can be a marketing piece that talks about the benefits of your product. Or can be educational or informative that support the brand and overall impression of the brand. And at the very best entertaining at the same time. 

  84% of people say that they’ve been convinced to buy a product or service by watching a brand’s video. Building trust, knowledge and engagement are simply quicker with video. Viewers interact more, and are engaged longer with video, they are more likely to take in the message and trust what they are seeing through educational, informative or even entertaining video marketing content than solely through reading content on a website. And even better they take action, complete goals on your website and engage with your brand (potentially long term).

  It is not only necessary to have embedded video content on your website, it needs to deliver all the elements of a well functioning website, creating the very best user experience is essential to keeping your viewers engaged. This includes ensuring the video is quick to load, on-brand, expected (in format and tone) and easy to use and/or find. Ensuring that your video content is all of these things can mean that your website users will stick around longer on your website, spend more time exploring (and reading) your website and ultimately complete more of your goals. With a great user experience, your users, in turn, are more likely to come back again to your website and trust your brand. 

  All great reasons to create impactful video content that supports your website and brand ! And to analyse data around this behaviour to repeat (or better) the video content that really hits the mark.

  

  Let’s talk stats

  In terms of video marketing, there are stats to support that viewers retain 95% of a message when they view it in a video format. The psychology behind this should be fairly obvious. It is easier (and quicker) for humans to consume video and watch someone explain something than it is to read and take action. Simply look at the rise of YouTube for explanatory and instructional video content !

  And how about the 87% of marketers that report a positive ROI on using video in their marketing ? This number has steadily increased since 2015 and matches the increase in video views over the years. This should be enough to demonstrate that video marketing is the way forward, however it needs to be the right type of video to create impact and engagement.

  Do you need more reasons to consider honing and refining your video content for your audience ? And riding this wave of impactful video marketing success ?

  

  But, how do we do that ?

  So, how do you make content that consistently converts your audience to engaged customers ? The answer is in the numbers. The data. Collecting data on each and every piece of media that is produced and put out into the world. Measuring everything, from where it is viewed, how it is viewed, how much of it is viewed and what is your viewer’s action after the fact.

  While Vimeo and YouTube have their own video analytics they are each to their own, meaning a lot more work for you to combine and analyse your data before forming insights that are useful. 

  Your data is collected by external parties, and is owned and used by these platforms, for their own means. Using Web Analytics from Matomo to collect and collate media data can mean your robust data insights are all in one place. And you own the data, keeping your data private, clean and easy to digest. 

  Once your data is across a single platform, your time can be spent on analysing the data (rather than collating) and discovering those super valuable insights. Additionally, these insights can be collated and reported, in one place, and used to inform future digital and video marketing planning. Working with the data and alongside creative teams to produce video that talks to your audience in an impactful way.

  The more data that is collected the deeper the insights. Saving time and money across a single platform and with data-backed insights to inform decisions that can influence the time (and money) spent producing video content that truly hits the mark with your audience. No more wasted investment and firing into the dark without knowledge. 

  Interrogating the ideal length of your video media means it is more likely to be viewed to the end. Or understanding the play rate on your website of any video. How often is the video played ? And which is played more often ? Constant tweaking and updating of your video content planning can be informed by data-driven human-centric insights. By consistently tracking your media, analysing and forming insights you can build upon past work, and create a fuller picture of who your audience is and how they will engage with future video content. Understanding your media over time can lead to informed decisions that can impact the video content and the level of investment to deliver ROI that means something.

  

  Wrap Up

  Media Analytics puts you at the heart of video engagement. No more guessing at what your audience wants to see, how long or when. Make every piece of video content have the impact you want (and need) to drive engagement, goal completion and customer conversion. Create a user experience that keeps your users on your website for longer. Delivering on all of those delicious digital marketing goals and speaking the language of key stakeholders throughout the business. Back your digital marketing, with truly impactful content, and above all else deliver to your audience content that keeps them engaged and coming back for more.

  Don’t just take our word for it ! Take a look at what Matomo can offer you with streamlined and insightful Media Analytics, all in one place. And go forth and create impactful content, that matters.

  Next steps :

  Check out our detailed user guide to Media Analytics

  Or, if you have questions, see our helpful Video & Audio Analytics FAQ’s

• Anomalie #4543 (Nouveau) : Accessibilité des chargements ajax (live regions)

  2 septembre 2020, par nicod _

  Suite à un audit de site par Temesis, il remonte que les attributs aria qu’on utilise sont mal placés.

  Je cite :

  Il semble qu’il y a un problème plus global avec l’utilisation des live region :
  Je rencontre à nouveau le div englobant div.ariaformprop qui possèdent les attributs

  aria-live
  aria-atomic
  aria-relevant

  Cette utilisation de ces attributs est erronée :

  elle ne donne pas le résultat probablement attendu
  le support d’aria-relevant dans les assistances technologiques n’est pas assuré

  Il est donc nécessaire de ne pas utiliser de live region sur une div englobante comme là.
  Il faut supprimer ces attributs et utiliser aria-live et aria-atomic avec parcimonie.

  J’ai regardé l’exemple que tu donnes sur spip.net également.

  L’exemple était : https://www.spip.net/spip.php?page=recherche&recherche=plugin&debut_articles=0#pagination_articles

  Cette prolifération d’aria-live + aria-atomic part probablement d’un bon sentiment, mais comme parfois avec l’accessibilité, le mieux est l’ennemi du bien.

  En positionnant ces attributs, avec ces valeurs, sur des div englobantes, cela génère dans les lecteurs d’écran la lecture automatique des contenus qui ont été mis à jour.

  Certes la lecture peut être interrompue par l’utilisateur, mais cela revient, à mon avis à imposer quelque chose qui n’a pas été forcément souhaité. De cette manière le propriétaire du site impose une expérience différente aux utilisateurs de lecteur d’écran.
  Ensuite, il faut bien entendu prendre en compte le contexte. Il pourrait arriver que ce comportement soit pertinent. Ce n’est pas le cas ici.

  Dans le cas de rechargement ajax, il faut étudier chaque cas et son contexte, mais le plus souvent le principe est :

  Ne pas utiliser ces attributs lorsqu’il n’y a pas d’interactions / rechargement ajax . Exemple sur un formulaire, cela n’a pas de sens : on rempli un form et puis on valide le form. A priori (si j’ai bien compris le fonctionnement) il n ’y a aucune raison d’utiliser de live region. En théorie, la présence de ces attributs s’il n’y a pas de rechargement ne devrait pas gêner. Mais certains lecteurs d’écran peuvent prendre des libertés par rapport aux spécifications et vocaliser des contenus tout de même. C’est pourquoi il faut être parcimonieux.

  Lorsqu’ils sont utiles et nécessaires, utiliser ces attributs pour informer d’un changement. Exemple : donner le nouveau nombre de résultats après utilisation de filtre. Mais il faut bien penser que dans plusieurs cas ils ne sont pas nécessaires, en effet, un bouton d’action doit être explicite avant qu’on appuie dessus. Donc par exemple : lorsque je demande à afficher la page 2 dans une pagination de résultat, il n’y a pas d’info à vocaliser automatiquement, le fait que le bouton dise "afficher la page 2" est suffisant, il serait superflu, voire gênant d’aller plus loin dans ce qui est vocalisé.

  Enfin, il faut gérer le focus clavier. Cet aspect est essentiel dès qu’il y a des interactions ajax, mais il dépend totalement du contexte. Et cet aspect est indépendant de la nécessité d’utiliser les live regions. Exemple : dans une série de filtre on va laisser le focus sur le filtre pour que l’utilisateur puisse parcourir la série de filtre. A contrario, dans une pagination (ou quand on a un bouton de type "afficher plus d’articles", dans des news ou dans une page de produits pour un site de e-commerce) on va déplacer le focus sur le 1er nouveau élément qui vient de s’afficher (la zone qui a été mise à jour).