Sep 18, 2023 23:09
" it gave a value close to 1.8 million." -- 8.0's Data Dictionary provides some good features, but probably at a cost when you have that many tables! Redesign the schema to have fewer tables.
 
Aug 3, 2023 15:25
@RahulK - This Q&A is getting messy to follow. Start a new Question with only the remaining issues concisely stated.
Aug 3, 2023 15:25
@RahulK - I added more.
Aug 3, 2023 15:25
Prefix indexing is virtually useless and often gets in the way. FULLTEXT, when it is applicable, is orders of magnitude faster than REGEXP. List the 4 most important queries; I will recommend the optimal set of index(es) for them.
Aug 3, 2023 15:25
FULLTEXT is used with TEXT and VARCHAR only via MATCH..AGAINST. INDEX works with VARCHAR via = (etc), LIKE, and REGEXP, It is OK to have both types of indexes on the same column, but the syntax in the WHERE (MATCH vs =) is different.
Aug 3, 2023 15:25
I added some more.
Aug 3, 2023 15:25
Please provide the revised SQL; I still see JOIN movies mo and EXISTS ( SELECT 1 FROM movies -- Those are two separate lookups, each with their own optimization issues.
Aug 3, 2023 15:25
You mentioned WP -- See also WP Index Improvements
Aug 3, 2023 15:25
@RahulK - The table movies is mentioned twice; was this deliberate or can you get both streaminginfo and type at the same time? This might be a big performance issue.
Aug 3, 2023 15:25
I added some more. Is pd.date the data of the post? Or something relating to the 'movie'?
Aug 3, 2023 15:25
@J.D. - When id is the PRIMARY KEY is essentially never beneficial also to have INDEX(id, ...) (a composite index starting with the PK). The index I suggested will implicitly have the PK on the end.
 
Aug 3, 2023 15:14
Be aware that Redis is probably assuming that all the work can be done in RAM. A general DBMS will design data structures to handle tables much bigger than RAM. The cached BTree is an excellent compromise -- can handle arbitrarily large tables, while being reasonably efficient if all the data is cached.
Aug 3, 2023 15:14
@Luatic - Sorting is O(NLogN); "updates of all ancestors" for N changes is worse: O(N^2). For that analysis, alone, I would steer away from it. But there is the question of -- Do you do more inserts that selects? If you are rarely selecting, then let the selects do the hard work. (And vice versa.)
Aug 3, 2023 15:14
Describe your real application. There may be some trick that veers away from those two Selects that you presented.
 
Jul 31, 2023 18:07
I see no PRIMARY KEY on movies. This need rectifying.
Jul 27, 2023 23:58
@RahulK And that is way I suggested WP Index Improvements
Jul 27, 2023 19:49
Aha -- I need that CREATE; I suspect that tax_id is VARCHAR !
Jul 27, 2023 19:45
Please provide SHOW CREATE TABLE term_relation \G If it is a many-to-many relationship table, change the indexes as discussed here: mysql.rjweb.org/doc.php/…
Jul 27, 2023 19:42
@RahulK Are there actually 3633 rows with those 3 tax_id values?
Jul 26, 2023 22:34
@RahulK That EXPLAIN looks very good. The Optimizer decided to start with tr because it could use an index and find maybe 1 row. Then it could reach into the other tables for about one row each. Please provide EXPLAIN ANALYZE SELECT ... so we can see if those estimates are terribly far off.
Jul 20, 2023 09:22
FULLTEXT is used with TEXT and VARCHAR only via MATCH..AGAINST. INDEX works with VARCHAR via = (etc), LIKE, and REGEXP, It is OK to have both types of indexes on the same column, but the syntax in the WHERE (MATCH vs =) is different.
Jul 20, 2023 09:22
I added some more.
Jul 20, 2023 09:22
Please provide the revised SQL; I still see JOIN movies mo and EXISTS ( SELECT 1 FROM movies -- Those are two separate lookups, each with their own optimization issues.
Jul 20, 2023 09:22
You mentioned WP -- See also WP Index Improvements
Jul 20, 2023 09:22
@RahulK - The table movies is mentioned twice; was this deliberate or can you get both streaminginfo and type at the same time? This might be a big performance issue.
Jul 20, 2023 09:22
I added some more. Is pd.date the data of the post? Or something relating to the 'movie'?
Jul 20, 2023 09:22
@J.D. - When id is the PRIMARY KEY is essentially never beneficial also to have INDEX(id, ...) (a composite index starting with the PK). The index I suggested will implicitly have the PK on the end.
 
May 20, 2023 05:05
Have you considered the FROM_BASE64() and TO_BASE64() functions in MySQL? Note that they tack on some stuff at the end to allow for 8 bits vs 6 bits not coming out even.
May 16, 2023 16:29
Found this! (I'll abandon the room I created. I could not figure out how to "nominate".)
May 16, 2023 15:26
I found the Join button. I have not seen anything where i need to authorize you to get in.
May 16, 2023 15:26
See if this link lets you join a chat room here: chat.stackexchange.com/rooms/info/146028/rickjoe?tab=general
May 16, 2023 15:26
whatsapp? Or something else?
May 16, 2023 15:26
At worst, there is case-folding -- meaning that ABcd would match ABCD, thereby making it slightly easier to hack.
May 16, 2023 15:26
Hmmm.. No charset issues.
May 16, 2023 15:26
Please provide a sample hash. I want to see whether utf8, etc, is actually needed.
 
May 17, 2023 23:50
What key will be sharding on? Does Vitess do the proxying?
May 17, 2023 23:50
You are at the limits of what the database can handle. I don't know which tweaks would help (or hurt). There are probably less than a dozen settings that would have an impact. I do see ways to restructure the data flow that are likely to lighten the load. But you are stuck with a 'legacy', so I will back off. Good luck. I suggest hiring ($$$) Percona for assistance; they have deeper expertise in tuning.
May 17, 2023 23:50
What about the 3 alarm columns? Do they ever get set and/or changed? What if they were moved to a separate table? (Yeah, I realize you have legacy issues.)
May 17, 2023 23:50
Is this 'hundreds' of readings from each of 'hundreds' of devices? How often is the data arriving? How often is it read?
May 17, 2023 23:50
"Circular"? What key is used to know where to UPDATE?
May 17, 2023 23:50
Sounds about right. The front end -- was it doing some big query against the rows that were being updated? Any recent deadlock in SHOW ENGINE INNODB STATUS;?
May 17, 2023 23:50
500K vs 40K -- Could it be that most of the Updates do not change any values?
May 17, 2023 23:50
MySQL keeps the above high-water-mark, and a counter called Connections. But the graph of "db.Users.Connections.avg" does not feel like either of those.
May 17, 2023 23:50
128 "runners", yet about 13K connections? What is the STATUS value of Max_used_connections?
May 17, 2023 23:50
Any graph on "history length"?
May 17, 2023 23:50
innodb_log_file_size = 8G is quite high; I doubt if more would help. How much RAM do you have?
May 17, 2023 23:50
Do you have a graph of Threads_running?
 
Feb 23, 2023 23:18
@DamiToma The pingponging tends to batch up the INSERTs, which them faster, which helps avoid collisions that may cause deadlocks. Without knowing further details about your data and processing I cannot guarantee improvements.
Feb 22, 2023 20:28
@DamiToma Instead of once a minute, I prefer to use a ping-pong method that runs continually. See mysql.rjweb.org/doc.php/staging_table
Feb 19, 2023 23:04
I would simply catch the deadlock and replay the transaction. This does not eliminate the deadlock, but it does prevent it from being a problem.