This can be related to Bug 83912. If you run SHOW ENGINE INNODB STATUS you may see transactions like this:
---TRANSACTION 4216198446592, not started sleeping before entering InnoDB
In order to fix it:
SET GLOBAL innodb_thread_concurrency=0;
The answer to this kind of question is always very subjective and will depend on on your very own requirements.
Maybe the most important question you need to answer to yourself is:
How important for you if high availability and uptime? Cloud-based computing usually offers better and higher availability/uptime than self-managed local servers.
Other than ...
Rate Per Second = RPS - Suggestions to consider for your my.cnf [mysqld] section
innodb_io_capacity=1900 # from 200 to use more of your SSD iops capacity
thread_cache_size=100 # from 8 to reduce threads_created count of 12,926 in 2 days
read_rnd_buffer_size=128K # from 256K to reduce handler_read_rnd_next RPS of 35,547
read_buffer_size=128K # from 64K ...
Consider trying row based replication ( https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html ) in your case to prevent issues like yours. If your inserts are mostly complex queries, inserting only the resulting rows, rather than reexecuting the full queries may decrese locking and performance. Note this is highly application specific and may have ...
SELECT employees.id, MAX(actions.date) AS latest_action
FROM employees, pivot, actions
WHERE employees.id = pivot.employee_id
AND pivot.actions_id = actions.id
AND actions.type = 'meeting'
GROUP BY employees.id
ORDER BY latest_action DESC
Create index pivot(employee_id, actions_id).
Create index actions(type,id,date) (or maybe actions(id,type,...
In general, you want to utilize as much RAM as possible (leaving enough for the O/S, etc), since that means data is in-memory instead of being on disk.
For a CRM system serving 100 people, you may find 16GB of RAM dedicated to the server is not enough. My phone has 8GB of RAM, for instance, and it's just a phone.
In-memory access times for data is a ...
You can get extra levels in the hierarchy by:
running multiple instances of MySQL servers - you can run multiple MySQL servers on the same host using different ports and sockets
using prefixes (e.g. a_, b_) in the names of tables or databases
The database or table name prefixes can be used mostly for visual purposes, although you can also give grants based ...
I assume there are no payments without a charge:
SELECT SUM(gross_total) AS charge_total
, ( SELECT SUM(payment_amount)
FROM payments p
WHERE p.booking_no = c.booking_no
AND p.room_id = c.room_id
AND p.guest_id = c.guest_id ) AS total_payments
FROM charges c
GROUP BY booking_no, room_id, guest_id
Whilst this ...
Have you tried deleting in batches? Maybe the SQL optimizer thinks that the FULL DELETE is too big so why using an index, instead it accesses the full table.. Try spitting in 5-10 batches, I think the explain delete will be different then
By deleting the data first, you are essentially making the data inaccessible. Don't you want to avoid this "downtime"?
Consider loading the replacement data into a temp table, then doing IODKU to update the main data:
INSERT INTO main (...)
ON DUPLICATE KEY UPDATE
col1 = VALUES(col1),
SELECT ... FROM temp;
IN ( SELECT ... ) is often poorly optimized. See if you can turn it into either a JOIN or EXISTS ( SELECT 1 ... )
Don't use a CURSOR if it is possible to write a JOIN or something to do the entire task rather than acting one row at a time.
PARTITIONing is unlikely to be beneficial; let's see the details. (Composite indexes often do the job adequately.)
Take a full dump again on the master node with below command
shell> mysqldump --single-transaction --master-data=2 --routines --triggers your_database_name > file.sql
--master-data=2 parameter will provide master's binary log position and file in file.sql
Restore the dump from master node in Slave node
login to slave and execute below
mysql> STOP SLAVE; ...
CREATE OR REPLACE VIEW VIEW_NAME AS (
IF(@tmp := (table1.foo or table2.foo IN ('State1', 'State2')), 0, table3.uniqueField) "uniqueFieldName",
IF(@tmp, 0, table3.uniqueField2) "uniqueFieldName2",
--more of these if statements with the same condition ,but different field
Your second select query does what you want ... - deliver all imageupload entries - together with who took it and where / when - and who marked it favorite ...
I suggest you put a proper alias for favorites.uid to make it more prominent
WHEN favorites.uid=1 THEN 1
END AS `my_favorite`,...
This will give you the english topper that has the max mark in math:
SELECT n.class as class, r.roll_number, max(r2.marks) as mathmarks, n.maxmarks as
(SELECT class, max(marks) as maxmarks
GROUP BY class) n
WHERE n.class=r.class AND n.maxmarks=r.marks AND r.subjects='...
This question intrigued me - the requirement to have the mark for Mathematics for the class (regardless of role) which (who?) obtained the best mark in English.
I decided to go one better and obtain the results for all subjects for the best in class in English - fiddle available here.
SELECT t1.class, t1.roll_number,
I have created a batchfile which runs under Windows.
It will create the .sql files in the current directory.
I left out Zipping the output.
The parameter '-N' will delete the header in the output from "show databases".
set mysql=C:\Program Files\MySQL\MySQL Server 8.0\bin\
Set it to 0 and let InnoDB pick the concurrency.
20 cores with currency=8 means that at least 12 cores will always be idle.
What is the value of Max_used_connections? If that is not very high, the "concurrency" is not your main problem.
Meanwhile, let's see one of the 'slow' queries, together with SHOW CREATE TABLE; there may be ways to speed it up, ...
Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility will make it even easier.
Unless you have special configuration or authentication plugins, this should mostly work (unlike trying to dump and restore all user ...