Closed dgsiegel closed 1 year ago
grooverdan
commented
1 year ago I'm not sure either. my_print_defaults --mysqld will show non-default configuration quickly. Are other forms distro package or MariaDB packages?
Try optimizer trace to see differences.
If this doesn't help I'll look a little later.
dgsiegel
commented
1 year ago I'm not sure either.
my_print_defaults --mysqldwill show non-default configuration quickly. Are other forms distro package or MariaDB packages?
No I am using the default distro packages. Nothing custom installed.
Local:
# my_print_defaults --mysqld
--datadir=/var/lib/mysql
--socket=/var/lib/mysql/mysql.sock
--skip-host-cache
--skip-name-resolve
--log-error=/var/log/mariadb/mariadb.log
--pid-file=/run/mariadb/mariadb.pidDocker:
# my_print_defaults --mysqld
--socket=/run/mysqld/mysqld.sock
--skip-host-cache
--skip-name-resolve
--pid-file=/run/mysqld/mysqld.pid
--basedir=/usr
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ciTry optimizer trace to see differences.
Local: optimizer.txt
Docker: optimizer-docker.txt
grooverdan
commented
1 year ago It looks like statistical samples caused the container instance to use elements before entries while the local didn't.
Could try analyze table on the local instance of elements and entries to see if that affects the query plan.
With your IN list appearing in order, the order by FIELD(element.id, ...) could just be order by element.id right?
dgsiegel
commented
1 year ago It looks like statistical samples caused the container instance to use
elementsbeforeentrieswhile the local didn't.
Right, but I haven't found a way to change that. Or the reason why it does that on all other instances, except Docker.
Could try analyze table on the local instance of
elementsandentriesto see if that affects the query plan.
Sure, this is the output:
> ANALYZE TABLE elements PERSISTENT FOR ALL;
+---------------+---------+----------+-----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+-----------------------------------------+
| bnm2.elements | analyze | status | Engine-independent statistics collected |
| bnm2.elements | analyze | status | OK |
+---------------+---------+----------+-----------------------------------------+
2 rows in set (0.925 sec)
> ANALYZE TABLE entries PERSISTENT FOR ALL;
+--------------+---------+----------+-----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+-----------------------------------------+
| bnm2.entries | analyze | status | Engine-independent statistics collected |
| bnm2.entries | analyze | status | OK |
+--------------+---------+----------+-----------------------------------------+
2 rows in set (0.326 sec)Unfortunately this didn't change anything. I have then analyzed all tables in the DB, but this didn't have an impact either.
With your
INlist appearing in order, theorder by FIELD(element.id, ...)could just beorder by element.idright?
In this case yes, as the specific search query ny the CMS shows all entries. But normally, not all elements should appear there, but only the matching ones.
RitterKnightCreative
commented
1 year ago Your character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci looks like are being set on docker but not local?
dgsiegel
commented
1 year ago Your
character-set-server=utf8mb4andcollation-server=utf8mb4_general_cilooks like are being set on docker but not local?
That seems to be it! character-set-server was set to latin1 and collation-server was set to latin1_swedish_ci on my local system. By setting both to utf8mb4 and utf8mb4_general_ci I get this:
22004 rows in set (1,787 sec)
Looking at EXPLAIN it follows a slightly different path than the Docker one, but at least it doesn't have to iterate over the entries table:
+------+--------------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | elements_sites | ALL | PRIMARY,idx_yheraloujqdszecohbrfqnphataqmqqiqpom,idx_wfvbpajifbqjobqvgabeammaiyjndvbowmsy,idx_xcvtdfmcvfgqtynaiclsnlnclhecfcylvjkh | NULL | NULL | NULL | 77343 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | entries | eq_ref | PRIMARY,idx_pliwvstcbedtxmlgysmllcdsrhlyljttoiid,idx_ldvcibanbubfakyjeipskkesbeshvhppzmvs | PRIMARY | 4 | bnm2.elements_sites.elementId | 1 | Using where |
| 1 | PRIMARY | entries | eq_ref | PRIMARY | PRIMARY | 4 | bnm2.elements_sites.elementId | 1 | |
| 1 | PRIMARY | elements | eq_ref | PRIMARY | PRIMARY | 4 | bnm2.elements_sites.elementId | 1 | |
| 1 | PRIMARY | content | eq_ref | PRIMARY,idx_nsvbqtorsofqydjhbgmzwcetxfyxoohgnvua,idx_omhipfiowphtoweivjawvpiwerhyjkyovfuk | idx_nsvbqtorsofqydjhbgmzwcetxfyxoohgnvua | 8 | bnm2.elements_sites.elementId,const | 1 | Using index |
| 1 | PRIMARY | content | eq_ref | PRIMARY | PRIMARY | 4 | bnm2.content.id | 1 | |
| 1 | PRIMARY | elements | eq_ref | PRIMARY,idx_meipholhpnizcpmxvbcrbmevwnsnebawnltn,idx_iktcbxftnhyutgzsugumsdsmhzlaolkykdlc,idx_ypauezzubkcibagjgearovprycixuxckjuzr,fk_qhlmsdqdhxwyzhtchtdttjhvipcxedmrvgoc,fk_bcrgdsmcruzgdrlltpmtludqqmstpywtnjuf,idx_zjvmoxsqtycxitwbnuqczrpqkuubzorlimhz,idx_kdfsxejckoixbhyzvjekvjxzkzkbpglrfwwp | PRIMARY | 4 | bnm2.elements_sites.elementId | 1 | Using where |
| 1 | PRIMARY | structureelements | ref | idx_hbtojdjueqltcgmauyzlqoxwkptqcwjxamfg | idx_hbtojdjueqltcgmauyzlqoxwkptqcwjxamfg | 5 | bnm2.elements_sites.elementId | 1 | Using where |
| 1 | PRIMARY | structureelements | eq_ref | idx_egibobxqkbyqfrjejrmdddzwcrcgiuegydfr,idx_hbtojdjueqltcgmauyzlqoxwkptqcwjxamfg | idx_egibobxqkbyqfrjejrmdddzwcrcgiuegydfr | 9 | bnm2.structureelements.structureId,bnm2.elements_sites.elementId | 1 | Using where |
| 1 | PRIMARY | elements_sites | eq_ref | PRIMARY | PRIMARY | 4 | bnm2.elements_sites.id | 1 | |
| 3 | DEPENDENT SUBQUERY | structures | eq_ref | PRIMARY,idx_okivpybovoibhjdkncizpovelmveaeevosog | PRIMARY | 4 | bnm2.structureelements.structureId | 1 | Using where |
+------+--------------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------------------------------+-------+----------------------------------------------+
11 rows in set (0,170 sec)Thanks @RitterKnightCreative for spotting this!!
I am currently debugging a very strange issue with Craft CMS and MariaDB. Inside Craft I've got a collection with over 20k entries and running a search query on those entries. Craft produces a quite complicated and convoluted SQL query that takes around 180-200 seconds to complete on my local system (MariaDB 10.5). It takes the same time on other other boxes such as several VMs (Debian and Ubuntu, freshly installed) as well as a few cloud instances I've spun up (DigitalOcean, Uberspace). The database was exported using mysqldump and then imported on the individual MariaDB instances.
Running the same query on the same database inside Docker (e.g
docker run -it mariadb:10.5, works with 10.4 and 10.6 too) reduces the query time to about 2 seconds consistently. I've tried to compare the MariaDB settings between that Docker image and my local system but can't find any big difference. Even the following things didn't change anything:SELECT @@optimizer_switch)mysqlcheck -u root -p --auto-repair --optimize --all-databasesbarrier=0as filesystem optionsIt seems like MariaDB on Docker is mostly stock anyways. Do you have any idea what other factors could play part in this? Unfortunately I am not able to edit or improve the query at this point, so it's mostly about finding out why Docker MariaDB is so much faster... Thanks!
The long query: https://github.com/craftcms/cms/files/9811193/long-query.txt Output of
SHOW VARIABLES: https://github.com/craftcms/cms/files/9811023/mysql-variables.txtEXPLAINof the query inside Docker:EXPLAINof the query on my local system:CREATE TABLEinstructions of the relevant tables: