Recherche avancée

Sur d’autres sites (6412)

• 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


  


• 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

• Switch to Matomo for WordPress from Google Analytics

  10 mars 2020, par Joselyn Khor — Plugins, Privacy
  While Google Analytics may seem like a great plugin option on the WordPress directory, we’d like to present a new ethical alternative called Matomo for WordPress, which gives you 100% data ownership and privacy protection.

  Firstly what does Google Analytics offer in WordPress ?

  When you think of getting insights about visitors on your WordPress (WP) sites, the first thing that comes to mind might be Google Analytics. Why not right ? Especially when there are good free Google Analytics plugins, like Monster Insights and Site Kit. 

  These give you access to a great analytics platform, but the downside with Google Analytics is the lack of transparency around privacy and data ownership.

  

  Matomo Analytics for WordPress is an ethical alternative to Google Analytics for WordPress

  If you’re more interested in a privacy-respecting, GDPR compliant alternative, there’s now a new option on the WP plugins directory : Matomo Analytics – Ethical Stats. Powerful Insights. 

  It’s free and can be considered the #1 ethical alternative to Google Analytics in terms of features and capabilities. Why is it important to choose a web analytics platform that respects privacy ?

  

  Risk facing fines for non-GDPR compliance and privacy/data breaches

  In Europe there’s an overarching privacy law called GDPR which provides better privacy protection for EU citizens on the web. 

  Websites need to be GDPR compliant and follow rules governing how personal data is used or risk facing fines up to 4% of their yearly revenue for data/privacy breaches or non-compliance. Even if your website is based outside of Europe. If you have visitors from Europe, you can still be liable.

  

  In the US, there isn’t one main privacy law, there hundreds on both the federal and state levels to protect the personal data (or personally identifiable information) of US residents – like the California Consumer Privacy Act (CCPA). There are also industry-specific statutes related to data privacy like HIPAA.

  To protect your website from coming under fire for privacy breaches, best practise is to find platforms that are privacy and GDPR compliant by design. 

  When you own your own data – as with the case of Matomo – you have control over where data is stored, what you’re doing with it, and can better protect the privacy of your visitors.

  At this point you may be asking, “what’s the point of an analytics platform if you have to follow all these rules ?”

  The importance of analytics for your WordPress site

  • Figuring out how your audience behaves to increase conversions
  • Setting, tracking and measuring conversion goals
  • Being able to find insights to improve and optimize your site 
  • Making smarter, data-driven decisions so your company can thrive, rather than risk being left behind

  Analytics is used to answer questions like :

  • Where are your website visitors coming from (location) ?
  • How many people visit your website ?
  • Which are the most popular pages on your site ?
  • What sources of traffic are coming to your site (social, marketing campaigns, search) ?
  • Is your marketing campaign performing better this month compared to last ?

  Matomo can answer all of the above questions. BONUS : On top of that, with Matomo you get the peace of mind knowing you’re the only one who has access to those answers.

  

  Matomo Analytics vs Google Analytics on WordPress

  The top 5 most useful features in Matomo Analytics that’s comparable to GA

  1. Campaign measurement – traffic. Matomo also has a URL builder that lets you track which campaigns are working effectively
  2. Tracking goals. Matomo empowers you to set goals you can track. Being able to see this means you can accurately measure your return on investment (ROI) 
  3. Audience reports to learn about visitors. Matomo’s powerful visitors feature lets you learn who is visiting your site, what their journey is and the steps they take to conversion.
  4. In depth view of behaviour with Funnels in Matomo. This tracks the journey of your visitors from the moment they enter your site, to when they leave. Giving you insight into where and why you lose your visitors.
  5. Custom reports. Where you create your unique reports to fit your business goals.

  Other benefits of using Matomo :

  • No data sampling which means you get 100% accurate reporting
  • 100% data ownership
  • Free Tag Manager
  • Search engine keyword rankings
  • Unlimited websites
  • Unlimited team members
  • GDPR manager
  • API access
  • Hosted on your own servers so you have full control over where your data is stored

  Learn more about the differences in this comprehensive table.

  

  Matomo Analytics for WordPress is free !

  Matomo Analytics is the best free Google Analytics alternative on the WordPress Directory. In addition to having comparable features where you can do pretty much do everything you wanted to do in GA. Matomo Analytics for WordPress makes for an ethical choice because you can respect your visitor’s privacy, can become GDPR compliant, and maintain control over your own data.

  Google Analytics leads the market for good reasons. It’s a great free tool for those who want analytics, but there’s no clarity when it comes to grey areas like privacy and data ownership. If these are major concerns for you, Matomo offers complete peace of mind that you’re doing the best you can to stay ethical while growing your business and website.

  It’s just as easy to install in a few click !

  Install and see for yourself !