MySQL slow queries - Widget issue

[AlexJ]

/*xxxx::xxxx::IPS\_Widget::__toString:18476*/ UPDATE `ipb_core_widgets` `core_widgets`  SET `caches`='{\"widget_topicFeed_86uiii4of_60d6ddb21f110bf3c9024baf58e4fcbd\":1676231243}' WHERE `key`='topicFeed' AND app='forums';
# Time: 2023-02-12T19:47:34.620616Z
# User@Host: xxxx[xxxx] @ localhost []  Id: 4064016
# Query_time: 11.263576  Lock_time: 0.000029 Rows_sent: 0  Rows_examined: 1
SET timestamp=1676231254;

I often see above in Slow Query log. Is their any reason why widget is taking 11 sec to update? I see problem with topicFeed, upcomingEvents, etc in widget. 

Is their any performance issues with Widgets getting updated? Thanks.

 

[wegorz23]

10 hours ago, AlexJ said:

/*xxxx::xxxx::IPS\_Widget::__toString:18476*/ UPDATE `ipb_core_widgets` `core_widgets`  SET `caches`='{\"widget_topicFeed_86uiii4of_60d6ddb21f110bf3c9024baf58e4fcbd\":1676231243}' WHERE `key`='topicFeed' AND app='forums';
# Time: 2023-02-12T19:47:34.620616Z
# User@Host: xxxx[xxxx] @ localhost []  Id: 4064016
# Query_time: 11.263576  Lock_time: 0.000029 Rows_sent: 0  Rows_examined: 1
SET timestamp=1676231254;

I often see above in Slow Query log. Is their any reason why widget is taking 11 sec to update? I see problem with topicFeed, upcomingEvents, etc in widget. 

Is their any performance issues with Widgets getting updated? Thanks.

 

check indeks on columnt in that tables. 

It shoud be:

primary: id

UNIQUE: key

INDEX: app

INDEX: plugin 

but u can add more if in select statement in where is column. 

 

in my db i have about 42 rows in that table so it cannot take that time to do something.

 

[Marc]

17 minutes ago, wegorz23 said:

check indeks on columnt in that tables. 

It shoud be:

primary: id

UNIQUE: key

INDEX: app

INDEX: plugin 

but u can add more if in select statement in where is column. 

 

in my db i have about 42 rows in that table so it cannot take that time to do something.

 

I would not advise on adding indexes that are not in the core platform. They would just be wiped on every upgrade

 

You should actually contact your hosting company regarding this problem. The query there is quite a simple query, so if there are issues on it updating the data, your mysql instance would need to be optimised appropriately.

[wegorz23]

23 minutes ago, Marc Stridgen said:

I would not advise on adding indexes that are not in the core platform. They would just be wiped on every upgrade

 

You should actually contact your hosting company regarding this problem. The query there is quite a simple query, so if there are issues on it updating the data, your mysql instance would need to be optimised appropriately.

thats true. 

but if have problem on that table it can be more problems on other one like posts ...

In my opinion its not mysql database problem but some 3-rd party plugin that system wait for them and thats why...

 

[Marc]

1 hour ago, wegorz23 said:

In my opinion its not mysql database problem but some 3-rd party plugin that system wait for them and thats why...

If that is the case, the correct course of action would be to report the issue to that 3rd party to correct properly. Adding indexes manually is not the correct answer there. 

[AlexJ]

11 hours ago, Marc Stridgen said:

You should actually contact your hosting company regarding this problem. The query there is quite a simple query, so if there are issues on it updating the data, your mysql instance would need to be optimised appropriately.

It's on dedicated server and overall MySQL is optimized. I don't see any pending locks in last 7 days. 

Locks:

Active Connections:

Row operations:

My thought was is if MySQL is not optimized - other queries will have issue as well.. but I don't see much for other queries. Those widget queries normally what I see very often. So i am targeting them first. 

Below are MySQL details from MySQLTuner. 

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 26d 12h 52m 58s (182M q [79.787 qps], 4M conn, TX: 2754G, RX: 80G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory     : 62.6G
[--] Max MySQL memory    : 25.0G
[--] Other process memory: 0B
[--] Total buffers: 14.6G global + 71.2M per thread (150 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 22.0G (35.16% of installed RAM)
[OK] Maximum possible memory usage: 25.0G (39.93% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (290/182M)
[OK] Highest usage of available connections: 71% (107/150)
[OK] Aborted connections: 0.00%  (217/4776645)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (10K cached / 159M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (236K temp sorts / 31M sorts)
[!!] Joins performed without indexes: 2604207
[!!] Temporary tables created on disk: 31% (2M on disk / 9M total)
[OK] Thread cache hit rate: 99% (107 created / 4M connections)
[OK] Table cache hit rate: 99% (450M hits / 452M requests)
[OK] table_definition_cache(4000) is upper than number of tables(1754)
[OK] Open file limit used: 3% (193/5K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)


-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (1.1G used / 6.0G cache)
[OK] Key buffer size / total MyISAM indexes: 6.0G/121.3M
[OK] Read Key buffer hit rate: 98.8% (70M cached / 813K reads)
[!!] Write Key buffer hit rate: 18.7% (1M cached / 195K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/5.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 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: 100.00% (63801399921 hits/ 63801663635 total)
[!!] InnoDB Write Log efficiency: 85.36% (35910577 hits/ 42069813 total)
[OK] InnoDB log waits: 0.00% (0 waits / 6159236 writes)

 

11 hours ago, wegorz23 said:

check indeks on columnt in that tables. 

It shoud be:

primary: id

UNIQUE: key

INDEX: app

INDEX: plugin 

but u can add more if in select statement in where is column. 

 

in my db i have about 42 rows in that table so it cannot take that time to do something.

 

Now this is interesting: I don't even have that table. @Marc Stridgen Is it because, I am using Redis? 

Edited February 13, 2023 by AlexJ

[Jim M]

Is there a second page to that in PHPMyAdmin? I believe that will only filter what's on the page because otherwise, that's quite odd as regardless you should get an error if the table doesn't exist and the software is attempting to connect to it via MySQL

[AlexJ]

8 hours ago, Jim M said:

Is there a second page to that in PHPMyAdmin?

Yes - that was it! Thanks for the tip. I was surprised for not finding that table.  It has only 77 records. So why it takes 10 sec and more to update this table?  

 Showing rows 0 - 77 (78 total, Query took 0.0003 seconds.)

 

 

 

[Marc]

Unfortunately, only your hosting company will be able to give you the answer to that question. As you have just ascertained yourself, there isnt much in there, and its not inserting much. So there isnt anything we can really amend our end to speed anything up.

[AlexJ]

Hosting datacenter doesn't know IPS application. It's IPS table and schema and MySQL is open source which you are recommending for your application. 

Please evaluate why this table is slow on updates or are you indirectly telling that their is no one in IPS team that can help with the issue? 

[Marc]

We are doing nothing more than updating a simple table there. While I understand what you are saying there, we dont actually run, maintain, nor configure your mysql instance. That is something done by your hosting company.

As you have said yourself there, the table only has 77 records in it. Therefore if its taking a long time to insert, there is only really the mysql instance there that can be an issue

[AlexJ]

Q: Is the table schema correct? is their anything missing which can cause the slowness on update?

Is any other customers that you aware off, facing same issue? 

PS You know well that hosting company will not do anything because IPS is not their application. 

 

[Jim M]

2 minutes ago, AlexJ said:

Q: Is the table schema correct? is their anything missing which can cause the slowness on update?

Is any other customers that you aware off, facing same issue? 

Our support tool in ACP -> Support checks schemas of tables. You are the only one at present stating this error.

3 minutes ago, AlexJ said:

PS You know well that hosting company will not do anything because IPS is not their application. 

You may need to move to a new hosting provider if they are unwilling to help you investigate slowness in your MySQL server. As they are the ones responsible for this process regardless of what application you're using.