New answers tagged

0

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;


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 ...


1

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 ...


0

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 ...


0

Try: 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,...


5

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 ...


0

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 ...


3

An alternative: SELECT charges_grouped.booking_no , charges_grouped.room_id , charges_grouped.guest_id , charges_grouped.charge_total , payments_grouped.payment_total , COALESCE(payments_grouped.total_payments, 0) AS total_payments_no_null FROM ( SELECT booking_no , room_id , guest_id , SUM(c.gross_total) AS charge_total ...


1

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 ...


0

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


0

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; If ...


0

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.) ...


0

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; ...


1

Test CREATE OR REPLACE VIEW VIEW_NAME AS ( SELECT table0.fieldBlah "FieldBlah", --more fields 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 FROM ...


0

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 http://sqlfiddle.com/#!9/b04a03/4 SELECT CASE WHEN favorites.uid=1 THEN 1 ELSE 0 END AS `my_favorite`,...


0

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 englishmarks FROM resultdata r, resultdata r2, (SELECT class, max(marks) as maxmarks FROM resultdata WHERE subjects='english' GROUP BY class) n WHERE n.class=r.class AND n.maxmarks=r.marks AND r.subjects='...


0

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. The SQL: SELECT t1.class, t1.roll_number, t1.sci_mark, ...


0

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". @echo off set mysql=C:\Program Files\MySQL\MySQL Server 8.0\bin\ set mysql=c:\progra~1\mysql\mysqls~2.0\bin\ set password=******** FOR /...


0

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, ...


1

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 ...


Top 50 recent answers are included