[TestCleanup()]
is performed after each test, so thats why your browser is closed after finishing the 1st test
If you want to close your browser after finishing all the tests in "SeleniumTest" class you should use [ClassCleanup()] instead of [TestCleanup()]
You cannot turn off logging permanently. What you can do to reduce logging:
LOAD utility with the NONRECOVERABLE option to populate staging tables;
Query A takes 3 seconds or more to produce 294 results:
SELECT user_id, id
FROM web_useradvisory
WHERE emailed IS NULL
ORDER BY user_id ASC, id ASC;
However, Query B takes about 10 milliseconds (!!) to produce the same 294 results:
SELECT user_id, id
FROM web_useradvisory
WHERE user_id IN (SELECT id FROM auth_user) AND emailed IS NULL
ORDER BY user_id ASC, id ASC;
EXPLAIN says both queries are using the same index, and web_useradvisory.user_id has a non-nullable foreign key index to auth_user.id. FYI there are only 5 distinct user IDs among the 294 records.
Query A:
mysql> EXPLAIN SELECT user_id, id FROM web_useradvisory WHERE emailed IS NULL ORDER BY user_id ASC, id ASC;
+----+-------------+------------------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
| 1 | SIMPLE | web_useradvisory | NULL | index | NULL | user_emailed_idx | 13 | NULL | 5075623 | 10.00 | Using where; Using index |
+----+-------------+------------------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
Query B:
mysql> EXPLAIN SELECT user_id, id FROM web_useradvisory WHERE user_id IN (SELECT id FROM auth_user) AND emailed IS NULL ORDER BY user_id ASC, id ASC;
+----+-------------+------------------+------------+-------+--------------------------------------------------------------------------------------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+--------------------------------------------------------------------------------------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | auth_user | NULL | index | PRIMARY | username | 152 | NULL | 170 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | web_useradvisory | NULL | ref | web_useradvisory_user_id_a9c47ffb_fk_auth_user_id,user_emailed_idx,user_localtime_idx,user_done_lt_idx | user_emailed_idx | 13 | TMVRemote.auth_user.id,const | 2 | 100.00 | Using where; Using index |
+----+-------------+------------------+------------+-------+--------------------------------------------------------------------------------------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
SHOW INDEXES shows the following:
SHOW INDEXES FROM web_useradvisory;
+------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| web_useradvisory | 0 | PRIMARY | 1 | id | A | 5001672 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | web_useradvisory_advisory_id_a8bea1ea_fk_web_advisory_id | 1 | advisory_id | A | 135733 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | web_useradvisory_user_id_a9c47ffb_fk_auth_user_id | 1 | user_id | A | 69 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | user_emailed_idx | 1 | user_id | A | 72 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | user_emailed_idx | 2 | emailed | A | 2361395 | NULL | NULL | YES | BTREE | | |
| web_useradvisory | 1 | user_localtime_idx | 1 | user_id | A | 72 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | user_localtime_idx | 2 | localtime | A | 4674121 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | user_done_lt_idx | 1 | user_id | A | 7409 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | user_done_lt_idx | 2 | done | A | 9261 | NULL | NULL | | BTREE | | |
| web_useradvisory | 1 | user_done_lt_idx | 3 | localtime | A | 4897349 | NULL | NULL | | BTREE | | |
+------------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Why doesn't Query A run as quickly as Query B? Query B's additional user_id WHERE condition doesn't actually further constrain the results, since the foreign key constraint on web_useradvisory.user_id already requires it to be a value from the list auth_user.id. It seems like my subquery selecting all the auth_user.id values in Query B is giving mysql a hint that the database engine fails to infer from Query A.
can someone help me turn this 3 querys into one?
I gather that it would be faster and better than having the 3 select running all the time.
this runs in python with flask and the db is mysql
mycursor.execute('SELECT COUNT(*) FROM testes__db WHERE module = %s', (line[0],))
mycursor.execute('SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s',(int(line[0]),int(session['test_id']),))
mycursor.execute('SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s where eval=0',(int(line[0]), int(session['test_id']),))
You can see these articles. It describes the locking process in the SQL server:
https://solutioncenter.apexsql.com/techniques-to-identify-blocking-queries-and-other-causes-of-sql-server-blocks/
https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/determine-which-queries-are-holding-locks?view=sql-server-ver15
https://docs.microsoft.com/en-us/troubleshoot/sql/performance/understand-resolve-blocking
I am exploring the 2 node cluster with file share.
The example is as shown here: https://docs.microsoft.com/en-us/azure-stack/hci/concepts/quorum#two-nodes-with-a-witness
Majority is 2/3. So when either node goes down, the cluster will stay up.
My question is - suppose the node 2 is restarted by an engineer as part of maintenance/patching without taking it out of the cluster, and the file share is unstable, then will the cluster dynamically adjust to survive with 1 node (primary) vote? Or will cluster go down?
I understand there is dynamic witness and dynamic node voting that should handle this? haven't found any documentation about this example - how dynamic vote kicks in when only node 1 and file share witness (2 of the originally configured 3) are alive.
However this migration link recommends an attach without any mention about detach.
You are not reading it correctly, the attach here means any database for which mdf file is present. A classic case here is stopping the database and moving the mdf files and then attaching it, this actually avoids detach. But I seldom use this method
https://dba.stackexchange.com/questions/11177/attach-detach-vs-backup-restore
Nothing beats good backup and restore.
Memory grants is just one usage of memory (a grant for memory, for instance for a sort operation). It does not include "regular" memory usage, like caching data from your database files.
It seems like you read and modify lots of data, to the extent that SQL Server can't keep the "active portion" of your databases in memory. This means that SQL server will throw things out of cache very frequently in order to have place for the data your queries refer to (for your SELECTs and modification).
The fact that you have high high lazy writes/sec and free list stalls/sec suggests that queries needing data to be put in memory cannot be satisfied without removing other data in memory including data that was modified since it was brought into memory. In general, CHECKPOINT takes care of that, but you seem to have a so high turnover of your data compared to memory size so checkpoint can't cope with that.
to me, this seems like a classic example of "add more memory or tune your queries" situation.
I have sql server with 8 cpus. Max worker threads setting is say 850. Max dop is say 8. And cost thread hold for parallelism is 50.
This means sql server will breakdown a query going above cost threshold into 8 threads. Since each threads runs on one cpu, then does this mean no other user will be allowed to run a query until at least one of the currently running 8 threads gets released?
So does setting max dop = number of cpu's cause situation where a single query blocks other queries?
I have been doing some reading up on Two Phase Locking and understand that it involved 2 phases:
1. Growing phase: locks are acquired gradually and no locks are released.
- Shrinking phase: locks are released gradually and no locks are acquired.
My question is, how does the Two Phase Locking (not "Strict Two Phase Locking") mechanism ever know when it can begin the shrinking phase? It is impossible to tell if a transaction needs to acquire any more locks unless the DB knows all the queries in the transaction (which goes against the whole point of the Two Phase Locking mechanism which is supposed to be able to ensure serializability without knowing all the queries in the transaction).
Suppose I have a transaction as follows:
start transaction;
select * from test_tab where id=1;
select * from test_tab where id=2;
update test_tab set age=100 where id=3;
commit;
From the transaction above, the Two Phase Locking mechanism should not release any locks until the last update statement is done (since it needs to acquire a lock here, and it cannot acquire a lock in the shrinking phase). But how can the DB know if it can begin the shrinking phase unless the client reaches the end of the transaction and commits (since the DB will never know if the client will send an update query right before it commits)? Am I misunderstanding something?
I tried changing a column on a table with a few million records, changing a varchar column length from 100 to 250. After more than one and a half hours of execution time, whereupon monitoring the connection, it was almost all the time with a SUSPENDED state and PAGEIOLATCH_EX wait type, with very short intervals where the state changed to RUNNING.
I couldn't tell if it took that long due to an index being recomputed, disk being slow (didn't seem like it), or this was simply the expected behavior. Nor could I tell how long the task would take, so I just gave up and stopped the process.
Is there a way to know beforehand if there's something to be done for the task to run on a shorter time interval, or at least to know how much is left to do when the task is running?
As suggested by @J.D, I've run a benchmark on the SQL server SSD and I got some below average results, at least less than I would expect from an SSD imho. Here are the results. So maybe it has something to to with the performance:
