Multisite Woocommerce – Slow Database Queries

These DB queries takes almost 20 second to load and i’m trying to understand what is causing this. Why there are so many question marks? Is it possible to optimize this somehow? The site is using MultilingualPress with Woocommerce.

This are queries for archive product for example

    SELECT SQL_CALC_FOUND_ROWS  wp_?_posts.ID FROM wp_?_posts  INNER JOIN wp_?_postmeta ON ( wp_?_posts.ID = wp_?_postmeta.post_id )  INNER JOIN wp_?_postmeta AS mt? ON ( wp_?_posts.ID = mt?.post_id ) WHERE ?=?  AND (    wp_?_posts.ID NOT IN (                 SELECT object_id                 FROM wp_?_term_relationships                 WHERE term_taxonomy_id IN (?)             )    AND    wp_?_posts.ID NOT IN (                 SELECT object_id                 FROM wp_?_term_relationships                 WHERE term_taxonomy_id IN (?)             ) ) AND (    (      ( wp_?_postmeta.meta_key = ? AND wp_?_postmeta.meta_value != ? )      OR      ( mt?.meta_key = ? AND mt?.meta_value != ? )   ) ) AND wp_?_posts.post_type = ? AND (wp_?_posts.post_status = ? OR wp_?_posts.post_status = ?) GROUP BY wp_?_posts.ID ORDER BY wp_?_posts.menu_order ASC, wp_?_posts.post_title ASC LIMIT ?, ? 

Another one

    SELECT SQL_CALC_FOUND_ROWS  wp_?_posts.ID FROM wp_?_posts  INNER JOIN wp_?_postmeta ON ( wp_?_posts.ID = wp_?_postmeta.post_id )  INNER JOIN wp_?_postmeta AS mt? ON ( wp_?_posts.ID = mt?.post_id ) WHERE ?=?  AND wp_?_posts.ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND (    wp_?_posts.ID NOT IN (                 SELECT object_id                 FROM wp_?_term_relationships                 WHERE term_taxonomy_id IN (?)             ) ) AND (    (      ( wp_?_postmeta.meta_key = ? AND wp_?_postmeta.meta_value != ? )      OR      ( mt?.meta_key = ? AND mt?.meta_value != ? )   ) ) AND wp_?_posts.post_type = ? AND (wp_?_posts.post_status = ? OR wp_?_posts.post_status = ?) GROUP BY wp_?_posts.ID ORDER BY wp_?_posts.menu_order ASC, wp_?_posts.post_title ASC LIMIT ?, ? 

Slow queries constantly getting stuck on WordPress database of ~100,000 posts

I am constantly getting stuck SELECT queries on my wordpress databases like the following:

enter image description here

Many of the SELECT queries that get stuck are fairly ordinary, such as pulling the last 10 posts of an author or pulling 10 posts in a category – these are normal WordPress core queries, not from any plugin. My WordPress databases that get afflicted with this have around 100,000 rows in the wp_posts table, with a size of around 1GB. This is an example of the largest tables from one of the databases:

enter image description here

My dedicated server is has 4 CPU cores @ 3.4 GHz and 8 GB DDR4 RAM. For this server and these databases, should these kinds of issues be happening? What can I do to make normal WordPress queries always run without getting stuck? I have tried changing from MyISAM to InnoDB with no effect, as well as changing different settings in my.cnf – here it is currently:

[mysqld] pid-file = /var/run/mysqld/mysqld.pid log-error=/var/lib/mysql/errorlog.err performance-schema=0 default-storage-engine=MyISAM max_allowed_packet=268435456 open_files_limit=10000 slow_query_log=ON log_slow_verbosity=1 innodb_buffer_pool_size=1G aria_pagecache_buffer_size=512M query_cache_size=0 query_cache_type=0 query_cache_limit=0 join_buffer_size=512K tmp_table_size=32M max_heap_table_size=32M table_definition_cache=1200 

And the result of running https://github.com/major/MySQLTuner-perl:

[root@hostname ~]# perl mysqltuner.pl --host 127.0.0.1  >>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/  >>  Run with '--help' for additional options and output filtering  [--] Skipped version check for MySQLTuner script [--] Performing tests on 127.0.0.1:3306 [OK] Currently running supported MySQL version 10.3.20-MariaDB-log [OK] Operating on 64-bit architecture  -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/lib/mysql/errorlog.err exists [--] Log file: /var/lib/mysql/errorlog.err(764K) [OK] Log file /var/lib/mysql/errorlog.err is readable. [OK] Log file /var/lib/mysql/errorlog.err is not empty [OK] Log file /var/lib/mysql/errorlog.err is smaller than 32 Mb [!!] /var/lib/mysql/errorlog.err contains 1571 warning(s). [!!] /var/lib/mysql/errorlog.err contains 1424 error(s). [--] 124 start(s) detected in /var/lib/mysql/errorlog.err [--] 1) 2019-11-25  6:31:15 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2019-11-25  6:08:45 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2019-11-25  5:47:35 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2019-11-25  5:34:11 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2019-11-25  5:22:58 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 2019-11-25  5:02:11 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 2019-11-25  4:33:46 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 2019-11-25  4:27:54 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 2019-11-25  4:21:59 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 2019-11-25  4:21:52 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 123 shutdown(s) detected in /var/lib/mysql/errorlog.err [--] 1) 2019-11-25  6:31:08 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2019-11-25  6:08:26 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2019-11-25  5:47:27 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2019-11-25  5:33:59 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 2019-11-25  5:22:55 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 2019-11-25  5:02:05 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 2019-11-25  4:33:42 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 2019-11-25  4:27:47 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 2019-11-25  4:21:58 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 2019-11-25  4:21:48 0 [Note] /usr/sbin/mysqld: Shutdown complete  -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE  [--] Data in MyISAM tables: 2.3G (Tables: 1379) [--] Data in InnoDB tables: 2.7G (Tables: 284) [OK] Total fragmented tables: 0  -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA.  -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 620 basic passwords in the list.  -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION  -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 3m 3s (36K q [199.732 qps], 1K conn, TX: 222M, RX: 33M) [--] Reads / Writes: 96% / 4% [--] Binary logging is disabled [--] Physical Memory     : 7.6G [--] Max MySQL memory    : 2.1G [--] Other process memory: 0B [--] Total buffers: 1.7G global + 3.2M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 1.7G (22.90% of installed RAM) [OK] Maximum possible memory usage: 2.1G (28.14% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (11/36K) [OK] Highest usage of available connections: 14% (22/151) [OK] Aborted connections: 0.00%  (0/1174) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts) [!!] Joins performed without indexes: 50 [!!] Temporary tables created on disk: 70% (1K on disk / 2K total) [OK] Thread cache hit rate: 98% (22 created / 1K connections) [OK] Table cache hit rate: 98% (444 open / 450 opened) [!!] table_definition_cache(1200) is lower than number of tables(1928) [OK] Open file limit used: 5% (580/10K) [OK] Table locks acquired immediately: 99% (25K immediate / 25K locks)  -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed.  -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 8 thread(s). [--] Using default value is good enough for your version (10.3.20-MariaDB-log)  -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 35.6% (47M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/170.4M [OK] Read Key buffer hit rate: 99.9% (22M cached / 22K reads) [OK] Write Key buffer hit rate: 99.3% (824 cached / 818 writes)  -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 1.0G/2.7G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal to 25% [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1). [--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.71% (10795186 hits/ 10826096 total) [OK] InnoDB Write log efficiency: 98.51% (39722 hits/ 40324 total) [OK] InnoDB log waits: 0.00% (0 waits / 602 writes)  -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 512.0M/1B [OK] Aria pagecache hit rate: 98.7% (130K cached / 1K reads)  -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled.  -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled.  -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.  -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server  -------- Recommendations --------------------------------------------------------------------------- General recommendations:     Control warning line(s) into /var/lib/mysql/errorlog.err file     Control error line(s) into /var/lib/mysql/errorlog.err file     MySQL was started within the last 24 hours - recommendations may be inaccurate     Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1     We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.              See https://dev.mysql.com/doc/internals/en/join-buffer-size.html              (specially the conclusions at the bottom of the page).     When making adjustments, make tmp_table_size/max_heap_table_size equal     Reduce your SELECT DISTINCT queries which have no LIMIT clause     Performance schema should be activated for better diagnostics Variables to adjust:     join_buffer_size (> 512.0K, or always use indexes with JOINs)     tmp_table_size (> 32M)     max_heap_table_size (> 32M)     table_definition_cache(1200) > 1928 or -1 (autosizing if supported)     performance_schema = ON enable PFS     innodb_buffer_pool_size (>= 2.7G) if possible.     innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.     innodb_buffer_pool_instances (=1) 

I’ve tried these suggestions along with every other possible thing it feels like (caching, optimizing tables etc) and still queries get stuck over and over, causing connections to be stuck in apache with a ‘sending reply’ status and causing websites not to load. What else can I try? Should I upgrade to a more powerful server?

Would a slow effect block a hunter ranger from using volley or whirlwind attack?

The Slow spell includes this rule for any creature affected by it:

Regardless of the creature’s Abilities or Magic Items, it can’t make more than one melee or ranged Attack during its turn.

A Ranger who is followed the hunter archetype gets access to volley and whirlwind attack at level 7:

Volley:

You can use your action to make a ranged Attack against any number of creatures within 10 feet of a point you can see within your weapon’s range. You must have Ammunition for each target, as normal, and you make a separate Attack roll for each target.

Whirlwind Attack:

You can use your action to make a melee Attack against any number of creatures within 5 feet of you, with a separate Attack roll for each target.

As I understand it, both of these abilities are considered to be a single attack, albeit with multiple attack rolls. Does that mean the slow spell would not prevent a ranger from using either ability if they were under it’s influence?

Does the Slow spell limit the number of Eldritch Blast beams?

This question comes from something stated in an answer to another one of my questions:

[Regarding eldritch blast] This spell allows you to make a single attack that targets multiple creatures, by making several attack rolls.


The slow spell states:

[…] Regardless of the creature’s abilities or magic items, it can’t make more than one melee or ranged attack during its turn […]

Note I am aware the slow spell also affects the casting of spells, so let’s assume a caster always succeeds on the d20 roll.

And then there is the eldritch blast spell which states:

A beam of crackling energy streaks toward a creature within range. Make a ranged spell attack against the target. On a hit, the target takes 1d10 force damage.

The spell creates more than one beam when you reach higher levels: two beams at 5th level, three beams at 11th level, and four beams at 17th level. You can direct the beams at the same target or at different ones. Make a separate attack roll for each beam.

Does the slow spell prevents you from creating more than one eldritch blast beam?

Postgres extremely slow after upgrade from Version 10 to 11

Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4 but trying to understand better…

My queries was slow after upgrading to Postgres Version 11.5 from 10.7 I first ran ANALYZE on the DB but still was slow! so I can VACUUM ANALYZE on the DB, and was still slow…

as a last resort, I am running VACUUM FULL ANALYZE on a table by table basis using psycopg2 with autocommit on (http://initd.org/psycopg/docs/connection.html#connection.autocommit) to be able to run it in psycopg2

one table is taking hours to do … while I wait, I can’t help but wonder what went wrong. The reduction of the query performance was mainly from UPSERT.

Does the slow effect affect all attack types?

The Stone Golem has an ability called Slow:

Slow (Recharge 5-6): The golem Targets one or more creatures it can see within 10 ft. of it. Each target must make a DC 17 Wisdom saving throw against this magic. On a failed save, a target can’t use reactions, its speed is halved, and it can’t make more than one Attack on its turn. In addition, the target can take either an action or a Bonus Action on its turn, not both. These Effects last for 1 minute. A target can repeat the saving throw at the end of each of its turns, ending the effect on itself on a success.

The Slow spell has a similar effect.

My immediate thought was that this is aimed at weapon attacks eg. a fighter’s extra attack or a monk’s flurry of blows. Then I wondered, what about spells that have an attack roll eg. Eldritch Blast. At higher class levels that spell allows multiple attack rolls. Does the slow effect also prevent you from making the extra attacks with that spell?

What about class abilities like Volley or Whirlwind Attack from the Ranger – Hunter subclass?

Ubuntu 18.04 Samba share out of the blue is really slow

I’ve had a samba share setup on my ubuntu 18.04 server for a few months now, all running very smooth and very fast over my gigabit ethernet from the ubuntu server to another ubuntu laptop running 19.04 as well as a windows 10 laptop.

All of a sudden today, trying to access my share from my ubuntu laptop or windows laptop has been extremely laggy. Any file transfers are at around 2-3MB/s when it should be hovering around 100MB/s.

I’ve re-installed samba, restarted every device connected to my network.

I followed the steps in this tutorial: https://linuxize.com/post/how-to-install-and-configure-samba-on-ubuntu-18-04/

here is my smb.conf for the share.

[myusername-share] path = /mnt/storage browseable = yes read only = no force create mode = 0660 force directory mode = 2770 valid users = @sambashare @sadmin

Slow performance of embedding Mathematica Demonstrations in webpages

When I embed a Mathematica Demonstration in my webpage, the performance is very slow and laggy.

For instance, if I follow the instruction video and embed the Radial Engine Demonstration in an HTML page, it takes about 5 seconds to load (that’s ok) and when I drag a slider, it takes about 2 seconds for the image to update (that’s a big problem). This is the case even for simpler demonstrations, such as this magnetic field demonstration.

Is there any way to improve performance?

Ubuntu Mate (rendering) terribly slow on Ubuntu 18.04

I’m running Ubuntu Mate 18.04.3 on a MacBook Pro (Retina) from Mid 2014, but the speed on which some applications run makes it impossible to work with. Especially chrome, chromium, firefox, slack (uses chromium), there’s a lot of lag when typing or scrolling and it takes a very long time to load a website that uses a lot of javascript for visualizations, like a dashboard.

Booting up and running shell and other programs is quick as at should be and the other os, OSX, runs just fine.

I’d tried changing the hardware acceleration in chrome and firefox and installed mbpfan but nothing changed.

Any help would be greatly appreciated, I’d have to move back to use OSX as my default OS if there’s no way to fix this. And that’s something I’d like to avoid.

Below is more info on my system and some relevant output from several commands. I ran htop as well, and surely cpu usage did increase when running one of the applications, but it did not use a abnormal amount of cpu / ram.

Ubuntu is installed on the internal SSD.

Specifications of machine

inxi -SMIG -! 31

System:    Kernel: 5.0.0-31-generic x86_64 bits: 64 Desktop: MATE 1.20.1  Distro: Ubuntu 18.04.3 LTS Machine:   Device: laptop System: Apple product: MacBookPro11 2 v: 1.0 serial: N/A            Mobo: Apple model: Mac-3CBD00234E554E41 v: MacBookPro11 2 serial: N/A            UEFI: Apple v: MBP112.88Z.0146.B00.1804111138 date: 04/11/2018 Graphics:  Card: Intel Crystal Well Integrated Graphics Controller            Display Server: x11 (X.Org 1.20.4 ) drivers: modesetting (unloaded: fbdev,vesa)            Resolution: 2880x1800@59.99hz, 2560x1440@59.95hz            OpenGL: renderer: Mesa DRI Intel Haswell Mobile version: 4.5 Mesa 19.0.8 Info:      Processes: 261 Uptime: 37 min Memory: 1660.9/15946.0MB Client: Shell (zsh) inxi: 2.3.56 

cat /etc/os-release

NAME="Ubuntu" VERSION="18.04.3 LTS (Bionic Beaver)" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 18.04.3 LTS" VERSION_ID="18.04" 

cat /proc/version

 Linux version 5.0.0-31-generic (buildd@lgw01-amd64-046) (gcc version 7.4.0 (Ubuntu 7.4.0-1ubuntu1~18.04.1)) #33~18.04.1-Ubuntu SMP Tue Oct 1 10:20:39 UTC 2019 

glxinfo | grep render

direct rendering: Yes     GLX_MESA_multithread_makecurrent, GLX_MESA_query_renderer,      GLX_INTEL_swap_event, GLX_MESA_copy_sub_buffer, GLX_MESA_query_renderer,  Extended renderer info (GLX_MESA_query_renderer): OpenGL renderer string: Mesa DRI Intel(R) Haswell Mobile      GL_ARB_compute_shader, GL_ARB_conditional_render_inverted,      GL_MESA_texture_signed_rgba, GL_NV_conditional_render, GL_NV_depth_clamp,      GL_ARB_compute_shader, GL_ARB_conditional_render_inverted,      GL_NV_conditional_render, GL_NV_depth_clamp, GL_NV_fog_distance,      GL_EXT_render_snorm, GL_EXT_robustness, GL_EXT_sRGB_write_control,      GL_OES_fbo_render_mipmap, GL_OES_geometry_point_size, 

cpufreq-info copied for 1 core

cpufrequtils 008: cpufreq-info (C) Dominik Brodowski 2004-2009 Report errors and bugs to cpufreq@vger.kernel.org, please. analyzing CPU 0:   driver: intel_pstate   CPUs which run at the same hardware frequency: 0   CPUs which need to have their frequency coordinated by software: 0   maximum transition latency: 4294.55 ms.   hardware limits: 800 MHz - 3.40 GHz   available cpufreq governors: performance, powersave   current policy: frequency should be within 800 MHz and 3.40 GHz.                   The governor "powersave" may decide which speed to use                   within this range.   current CPU frequency is 3.27 GHz. 

cat /sys/class/thermal/thermal_zone*/temp

30900 76000 

cat /proc/cpuinfo copied for 1 core

processor   : 0 vendor_id   : GenuineIntel cpu family  : 6 model       : 70 model name  : Intel(R) Core(TM) i7-4770HQ CPU @ 2.20GHz stepping    : 1 microcode   : 0x1b cpu MHz     : 3236.768 cache size  : 6144 KB physical id : 0 siblings    : 8 core id     : 0 cpu cores   : 4 apicid      : 0 initial apicid  : 0 fpu     : yes fpu_exception   : yes cpuid level : 13 wp      : yes flags       : {REMOVED} bugs        : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs bogomips    : 4389.96 clflush size    : 64 cache_alignment : 64 address sizes   : 39 bits physical, 48 bits virtual power management: