How to speed up looped INSERT INTO statements? I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO takes less then 50ms. I need it to update about 300 million rows and I can't really wait 52 days (!) for it to complete.

Bottom line of the following update query is that I need to go over each row, perform calculations on a VARBINARY and extract proper values from it (we need to get rid of the packed VARBINARY fields), and store that in a new table.

FETCH NEXT FROM LocCities INTO @LocCity
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- several sets, removed calculations for clarity
    SET @LocationId = Calculation1()
    SET @CityId = Calculation2()

    IF(@LocCity <> 0)
    BEGIN
        -- left out an inner loop here on the VARBINARY based on its length
        INSERT INTO LocationCities (LocationId, CityId)
        VALUES (@LocationId, @CityId)
    END
    FETCH NEXT FROM RespCursor INTO @TuningRow
END

I understand that I can use the WITH keyword with table hints, but I am not sure what to use. I expect the final update query to run in several hours, and hope there's a way to do that. I really can't wait almost two months ;).

Isn't there something similar like BULKINSERT that I can use?

I really don't think table hints or BULKINSERT are going to help you here - your approach is still to process each varbinary value one at a time, and this will be your downfall regardless - especially when you discard the idea of set-based queries because you "don't think it's possible."

Here's a set-based approach with no awful loops or cursors. This assumes that the pattern is always the same (LocationID is the first byte, and CityID is the next two).

DECLARE @x TABLE(x VARBINARY(32));

INSERT @x VALUES(0x010734),(0x030735040736),(0x030742050743060712);

;WITH n(n) AS
(
  SELECT TOP (300) (number*3)+1
  FROM [master].dbo.spt_values -- your own Numbers table is better
  WHERE [type] = N'P' ORDER BY number
)
-- INSERT dbo.LocationCities(LocationId, CityId)
SELECT
  x.x,      -- comment this out before insert
  LocationID = CONVERT(INT, SUBSTRING(x.x, n, 1)),
  CityID     = CONVERT(INT, SUBSTRING(x.x, n+1, 2))
FROM @x AS x INNER JOIN n ON LEN(x) > n.n;

Results:

x                        LocationID    CityID
---------------------    ----------    ------
0x010734                 1             1844
0x030735040736           3             1845
0x030735040736           4             1846
0x030742050743060712     3             1858
0x030742050743060712     5             1859
0x030742050743060712     6             1810

Some articles that will help you understand numbers tables and why generating sets in SQL Server is far superior to even the most efficient loop you can derive.

http://web.archive.org/web/20150411042510/http://web.archive.org/web/20150411042510/http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
Why are numbers tables "invaluable"?
http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


Database Connection vs Raw TCP Connection I have some fundamental questions about how database clients & database interact

Do databases support multiple transactions simultaneously on a single database connection from client? If not, why not? (as multiplexing would save on resource overhead per connection & connection pools are a source of contention when thousands of simultaneous queries needs to be executed simultaneously, which multiplexing for sure avoids)
Whats the relationship between database client's level Connection vs physical raw TCP connection. Is it many-to-one[multuplexing] (or) one-to-one? If not multiplexed why not?
If multiplexed, does the database server maintain a single logical connection from its end (or) multiple logical connections

PS: I understand some of these details will vary from database to database, buit want to know in general how popular implementations such as Postgres, Mysql, Oracle, SQL server & DB2 implement these

Do databases support multiple transactions simultaneously on a single database connection from client?

For SQL Server, no.

  If not, why not? (as multiplexing would save on resource overhead per connection)

It would seriously complicate the network protocol, which has to be implemented on multiple client platforms, creating a possible source of bugs and performance issues.

And the resource overhead caused by multiple connections is small, and largely mitigated by connection pooling, where a set of long-lived connections is shared among all the threads in a client program.


Table Design Cardinality I am designing a database for an application, in that I need to consider both BUYER (BUYER_ID) and VENDOR (VENDOR_ID) as a USER (USER_ID). I have designed this logic in a following design.

USER

User_ID (PK),
BUYER_ID(FK),
VENDOR_ID(FK).

I am confused, whether is this good method, while considering the following things,

Buyer may be a vendor.
Vendor can also be a Buyer.

I have created the USERROLE Table to achieve this, but when a user is only a buyer or vendor will the above mentioned relationship table work? Will this accept Foreign key as NULL?
I need the explanation for this - "A order could be placed by a Buyer or Vendor, but a single order cannot be placed by both buyer and Vendor at the same time".
Thanks in advance.

--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that plays the role of a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v');
commit;

--create a company that plays the role of a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);

Fiddle with it here: http://sqlfiddle.com/#!15/a76fd/2


Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY' I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a wiki.

User id of the new wiki user table register and default group id (2) should also be inserted in another wiki table, so that a new user belongs to a default group.

I coded this:

USE users_web;

DELIMITER |
CREATE TRIGGER doku_users_ins AFTER INSERT ON users_web.users
       FOR EACH ROW
       BEGIN
                INSERT INTO users_web.doku_users (login, pass, email)
                       VALUES (NEW.user, md5(NEW.pass), NEW.email);

                INSERT INTO users_web.doku_usergroup (uid, gid)
                       SELECT du.uid, "2" FROM doku_users du
                            INNER JOIN users_web.users u ON NEW.user = du.login;

       END |

DELIMITER ;

If the trigger is composed only of the first INSERT statement it works well, as the doku_users table is inserted of one more register.

But when I create the trigger as above, with the two INSERT statements, I receive an error as feedback: Duplicate entry '521-2' for key 'PRIMARY'.

I guess that the second sql statement tries to read the NEW value before it is available somehow.

How can I solve this issue?

I have been baffled by a similar situation. Use variables to store the values of NEW.column and remove the join from the 2nd Insert:

USE users_web;

DELIMITER |
CREATE TRIGGER doku_users_ins AFTER INSERT ON users_web.users
       FOR EACH ROW
       BEGIN

                DECLARE new_pass VARCHAR(255);         -- choose the datatypes
                DECLARE new_email VARCHAR(255);        --
                DECLARE new_user INT;                  --

                SET new_pass = NEW.pass ;
                SET new_email = NEW.email ;
                SET new_user = NEW.user ;

                INSERT INTO users_web.doku_users (login, pass, email)
                       VALUES (new_user, md5(new_pass), new_email);

                INSERT INTO users_web.doku_usergroup (uid, gid)
                       SELECT du.uid, '2' FROM doku_users du
                            WHERE new_user = du.login;

       END |

DELIMITER ;


What is the best index implementation for a really large databse I have a really large SQL SERVER table with only non clustered indices for several columns , while querying the table the execution plan shows RID Lookups , i expected to see this kind of lookup because the columns in the select list are not part of a covering index or included in another index .

The non-clustered indices were created on several columns because each one of these columns is a part of a different query with different select lists and search conditions.

Due to size and storage issues creating a covering index or a new index with included columns while keeping the current non-clustered indices is not feasible.

Now the question is :

Can i replace the non-clustered indices with one covering index ?
If not, is there any way to get rid of RID Lookup rather than a covering index ?
Does each query require a different covering index depending on the columns in the select list and the search conditions ?
I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?
Any notes or comments to solve such problems are welcome ..

Can i replace the non-clustered indices with one covering index ?

No. Suppose you sometimes find people by LastName and sometimes by FirstName. An index on (LastName, FirstName) won't help you find people by FirstName.

  If not, is there any way to get rid of RID Lookup rather than a covering index ?

Not in a way that's particularly useful.

  Does each query require a different covering index depending on the columns in the select list and the search conditions ?

Pretty much. Your job is to come up with a compromise.

  I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?

A PK doesn't necessarily mean a clustered index. And having a CIX just means your RID Lookups will become Key Lookups, which are potentially worse. But without CIXs you have heaps which can fragment when you change or delete data. So CIXs are fine, but won't improve performance of your Lookups.


Mysql Cluster 8 - "Jumpy" Auto Increment Values Im using mysql cluster 8 - using PHP  to insert data I keep running into a problem where the Ids jump and then "recover" I.E

SELECT `MSU_ID`, `MSU_Date_Created` FROM `Material_Spreadsheet_Upload` ORDER BY `MSU_ID` DESC

603     2020-04-28 10:55:13
602     2020-04-28 10:55:07
601     2020-04-28 10:55:05
600     2020-04-28 10:55:03
599     2020-04-28 10:55:00
598     2020-04-28 10:54:57

SELECT `MSU_ID`, `MSU_Date_Created` FROM `Material_Spreadsheet_Upload` ORDER BY `MSU_Date_Created` DESC

100 2020-04-28 12:05:28
99  2020-04-28 11:54:49
98  2020-04-28 11:54:47
97  2020-04-28 11:54:45
96  2020-04-28 11:54:39

This is obviously a problem, Im not doing anything in-between inserting the data (I.E messing with auto_increment values)

Sinse the upgrade to mysql 8 my tables all have comments NDB_TABLE=READ_BACKUP=1 they enabled this by default according to the docs in Mysql 8 but im wondering if this is whats causing the issue

After the upgrade I noticed but then I had to write an run a bunch of SQL queries to fix the issue initially - but it keeps happening and im not sure how to prevent it

the ids aren't jumpy, that is only an interpretation.

Tables are per definition unsorted, that why you put an order by in your querys.

the ids only serve to be unique and can sol identify a single row.

In a single maschine this happens, because processors have the capability to sort on a basic level if they have the need, mysql it self sorts things too and calls it optimizing. Then there a on heavy performing sytsem many processes which the operating system handles around.

Finally there are clusters that have to guaranty the uniqueness of every id, which usually works fine too.

And of course the table types play also a role see https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/example-auto-increment.html

So ignore the sorting of the ids, use it for identifying the rows.


Existing maintenance plan backup causing 3rd party SQL backup to fail I have a problem with SQL Server backup on the MS SQL Server 2008R2 (SP1) 10.50.2500.

Basically there is a maintenance plan backing up SQL databases into the physical_device_id which is showing up as a string of numbers similar to UUID ({761607B3-XXXX-XXXX-XXXX-XXXXXXXXXXXX}11}), the software_vendor_id = 4608 and the user_name = NT AUTHORITY\SYSTEM.

When trying to access the existing maintenance plan:

use msdb

select * from sysmaintplan_sublpans
select * from sysmaintplan_plans

I have no output.

Any help will be much appreciated!

The database server is being backed up by a third party tool not the maintenance plans.
The following query will show you the backup vendor information

select
 software_vendor_id
 ,name
 ,description
 ,user_name
from msdb.dbo.backupset


What happens to an insert when a mysql(innoDB) table is being altered(locked)? I am trying to alter a database column by using:

ALTER TABLE [table name] MODIFY id bigint;

I searched and found out that the table gets locked during the ALTER process.

How do the inserts get affected during the ALTER process? Do they wait until the lock is released? Is there a timeout?

If the inserts wait for the lock to be released, is there a limit of how many inserts that could wait?

What version of MySQL are you using?

Have you considered ALGORITHM=inplace and LOCK=none in your ALTER TABLE statement?

See also Using the LOCK clause to control concurrency


Find the average number of n:m connections of two tables? I want to know to how many lists a user is assigned in average.

User
====
id
name

Lists_Users
===========
user_id
list_id

List
====
id
title

Is there a good way of receiving this number via SQL?

I have no idea which RDBMS you are using, but on SQL Server using CTEs (Common Table Expression) it might look like something similar:

with tbl_count (user_id, nboflists) as
(
   select user_id, count(list_id)
   from lists_users
   group by user_id
)
select avg(nboflists)
from tbl_count

There must also be dozen of other (better?) ways  of doing this...


query optimization of a left join I have the following query written into the slow log (mysql-slow.log).

I don't have much idea how to optimize this query. I already have indexes for:

mode,
f.user_id... etc

but doesn't seem to be helping me. Mind you, the table Engine is InnoDb.

# Query_time: 10.830652
    Lock_time: 0.000058
    Rows_sent: 1
    Rows_examined: 18194
    Rows_affected: 0
# Bytes_sent: 75
SET timestamp=1439531119;
SELECT
  COUNT(u.user_id)
FROM (
    users AS u
    LEFT JOIN photos AS f
        ON u.user_id=f.user_id
        AND f.main='Y')
WHERE  u.mode NOT IN('suspended','deleted','hidden')
AND u.activated='Y';

Often times slow queries are caused by a lack of index on a column that is being used in a where or join clause.

Try indexing all of the following:

u.user_id
f.user_id
f.main
u.mode
u.activated

You could also try moving f.main='Y' to the WHERE condition - I believe it will result in the same outcome.


Make index work on join to table variable I a table with several columns, but the most importants are: DocumentId(bigint), StatusId(int) and ReceiveDateTime(datetimeoffset(3)).

CREATE TABLE [DocumentInfo](
    [DocumentId] [bigint] NOT NULL,
    [StatusId] [int] NOT NULL,
    [ReceiveDateTime] [datetimeoffset](3) NOT NULL,
    [IsDeleting] [bit] NOT NULL,
    [StorageId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED
(
    [DocumentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I also have a table variable which contains several rows (4 for now):

DECLARE @Tresholds TABLE
(
    DocumentStatusId int NOT NULL,
    Treshold datetime2 NOT NULL
);

I used indices on DocumentStatusId (is was a primary clustered key), and unique constraint (Treshold, DocumentStatusId), but query didn't use it, so I removed them.

My query looks like:

WITH TopOutdatedDocuments AS
(
    SELECT TOP (100000) di.DocumentId
    FROM [DocumentInfo] di WITH (NOLOCK)
    JOIN @Tresholds t ON di.StatusId = t.DocumentStatusId
    WHERE (ReceiveDateTime < @DocOldestTreshold OR (ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold))
    AND NOT EXISTS (SELECT * FROM [doc].[document] d (NOLOCK) WHERE d.ParentId = di.DocumentId)
)

The problem that the query plan always use index scan:

I tried to create an index for every permutation of these 3 columns, but it didn't use it anyway. DocOldestTreshold and @DocNewestTreshold are used to speedup a query, becuase they can be computed without a join. But it seems that engine doesn't use this opportunity.

How can I speedup a query? Its logical sense is that we have some tresholds for every StatusId, and if document is older than its treshold, it should be selected by this query.

Any help will be appreciated. I am now on SQL Server 2014. Order of [DocumentInfo]'s size is hundred millions of rows, typical size of @Tresholds is < 10.

Assuming your variables are always NOT NULL

WHERE  ( ReceiveDateTime < @DocOldestTreshold
          OR ( ReceiveDateTime < @DocNewestTreshold
               AND ReceiveDateTime < t.Treshold ) )

Is equivalent to

WHERE  ReceiveDateTime < @DocOldestTreshold
        OR ReceiveDateTime < LEAST(@DocNewestTreshold, t.Treshold)

Which is equivalent to

WHERE  ReceiveDateTime < GREATEST(@DocOldestTreshold, LEAST(@DocNewestTreshold, t.Treshold))

So if you have this index

CREATE INDEX ix
  ON [DocumentInfo](StatusId, [ReceiveDateTime])

And the following query

SELECT di.DocumentId
FROM   @Tresholds T
       CROSS APPLY (SELECT IIF(@DocNewestTreshold < t.Treshold, @DocNewestTreshold, t.Treshold)) CA1(Least)
       CROSS APPLY (SELECT IIF(@DocOldestTreshold > CA1.Least, @DocOldestTreshold, CA1.Least)) CA2(Target)
       JOIN [DocumentInfo] di
         ON di.StatusId = t.DocumentStatusId
            AND di.ReceiveDateTime < CA2.Target
            AND NOT EXISTS (SELECT *
                            FROM   [doc].[document] d (NOLOCK)
                            WHERE  d.ParentId = di.DocumentId)

It can do an equality seek on StatusId and a range seek on ReceiveDateTime


TSQL trigger on actual changes - update field Background -
Table that looks like this:
country_id
product_id
flow_date
update_date
amount

country_id, product_id, flow_date, and update_date are a composite PK, because I need to track the history of updates.
However, many times, data is inserted to the table where the amount hasn't changed. (I.e. country, product, flow date and amount are the same, but update_date is new.)  In those cases, I just want to update the update_date of the existing entry, rather than insert a new field.  Otherwise, I want it to insert as usual.  (I'm really not sure that the below will do this later part.)
This is what I have so far:
CREATE TRIGGER history_archive_changes_only
ON history_archive
AFTER INSERT
AS
BEGIN
    UPDATE history_archive
    SET update_date = i.update_date
    from history_archive
    INNER JOIN inserted i on (i.country_id = history_archive.country_id
                            AND i.product_id = history_archive.product_id
                            AND i.flow_date = history_archive.flow_date
    WHERE abs(history_archive.amount - i.amount) < 0.01

END;

Thoughts?

Instead of:

Update a row instead of inserting it when you see that the amount hasn't changed.

Why not:

Just insert the row. If you then find previous rows where there are multiple consecutive rows with the same value, delete all but the most recent one.

Create this trigger against a test version of your table please:
CREATE TRIGGER dbo.hist_arch_test ON dbo.history_archive_test_copy
FOR INSERT AS
BEGIN
  IF (@@ROWCOUNT > 0 AND UPDATE(amount))
  BEGIN
    ;WITH x AS
    (
      SELECT *, delta = ABS
           (
             /* amount from *next* row */
             LEAD(a.amount, 1) OVER
             (
               PARTITION BY a.country_id, a.product_id, a.flow_date
               ORDER BY a.update_date
             )

             -  /* minus */

             /* amount from *current* row */
             a.amount
           )
      FROM dbo.history_archive_test_copy AS a
      WHERE EXISTS
      (
        SELECT 1 FROM inserted AS i
          WHERE i.country_id = a.country_id
            AND i.product_id = a.product_id
            AND i.flow_date  = a.flow_date
      )
    )
    SELECT *, status = CASE delta WHEN 0 THEN 'mark as delete' ELSE '' END
    FROM x ORDER BY country_id, product_id, flow_date, update_date;
    -- when happy, delete the select, uncomment the delete:
    -- DELETE x WHERE delta = 0;
  END
END

I didn't test all possible permutations of existing values but this should clean up any old junk not directly related to the most recent row/series too.


MySQL Database Dump To Project Folder Currently I am dumping my database (running under Linux Mint), from the command line, using the following:-

mysqldump -u[user] -p[password] MyDatabase > MyDatabase.sql

Now rather than dumping to a self-contained file I need to dump to a Project Folder   so that I have a .sql file per table. However despite trawling the web and a couple of MySQL books I have been unable to find an option for this. Can anyone help?

Do it in 2 steps...

Write a SELECT against information_schema.TABLES to generate one mysqldump statement per table.  Send the output of that to a file; then execute that file.  (Also, give the file execute permission).


Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but the explanations still puzzle me a bit.

Specifically:

I am doing a weekly FULL backup, a daily DIFF backup, and an hourly LOG backup.
The FULL backup is using the default CleanupTime of 24h. The DIFF and LOG backup have NULL as CleanupTime.

From the documentation of the CleanupTime paramter, I fail to understand if setting the CleanupTime setting for a backup of BackupType FULL, will also delete older DIFF and LOG backup files, or only FULL backup files.

  Specify the time, in hours, after which the backup files are deleted.
  If no time is specified, then no backup files are deleted.

The latter paragraph makes me think that setting CleanupTime on backups of BackupType FULL will also delete older transaction logs. Yet it is unclear if this paragraph only applies to backups of the BackupType LOG, or also to backups of the BackupType FULL.

  DatabaseBackup has a check to verify that transaction log backups that
  are newer than the most recent full or differential backup are not
  deleted.

What I am trying to achieve, is that I can do a point-in-time recovery up to 1 week. (We have a very slowly changing database, so this is feasible) The way I understand it now, this would require a week old full backup, and a weeks worth of Transaction log backup. Since the full, and differential backups can only be used to restore to one specific point in time.

So, should I just set the CleanupTime option of my FULL backup job to 24*7? What I'm guessing now is that setting it to 24h, will cause the next FULL backup to delete all older Full, diff and Transaction log backup files, leaving me with a point-in-time recovery window of ... 0 hours. Right?

The @CleanupTime is always specified for a specific backup job. For example, if you create a Full backup job, a Differential backup job and a Transaction Log backup job, then the @CleanupTime always relates to the extension of the job.

Let's take a look at a Full backup example.

Full Backup

If you create a full backup job, then you will normally add one or more of the following parameters:

@Databases : Which databases get backed up (not really relevant for this example)
@Directory : The directory to store the backups
@BackupType : Full, Differential, TLog
@CleanupTime : How much hours worth of backups to keep
@FileExtensionFull : The extension of your backup.

So you have a backup job in place that will create a full backup according to the schedule you defined for the at job. Let's assume the following:

this job runs at 20:00 (8 p.m.)
@FileExtensionFull has been set to 'BAK'
@Directory has been set to 'F:\SQLBACKUP'
@CleanupTime has been set to 24 (hours)

If we look at the MaintenanceSolution.sql file then you will find the description for the parameter:

SET @CleanupTime         = NULL
-- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

Well, that's not helping much. Same as in the official documentation on the site. Let's dig further. If you scan through the script you will eventually find a section that looks like this:

The script has been wrapped to increase readability

IF @BackupSoftware IS NULL
    BEGIN
        SET @CurrentCommandType02 = 'xp_delete_file'
        SET @CurrentCommand02 =
            'DECLARE @ReturnCode int
            EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0,
                N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', -- first parameter
                ''' + @CurrentFileExtension + ''', --second parameter
                ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' -- third parameter
        IF @ReturnCode  0 RAISERROR(''Error deleting files.'', 16, 1)'
    END

So Ola is basically using the built-in xp_delete_file function of SQL Server to delete a file at a certain time according to:

@CurrentDirectoryPath
@CurrentFileExtension
@CurrentCleanupDate

But wait what would for example, the @CurrentCleanupDate be? If we go back a bit in the script you can find a section that looks like this:

INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
    SELECT DATEADD(hh,-(@CleanupTime),GETDATE()), 0

Ah, so the @CurrentCleanupDate is a date addition which is calculated from the @CleanupTime and the current time GETDATE(). Cool.
(...and we might have just found a typo in the code, because the sections for normal and mirror database both contain Mirror in the code.)

What is the relevant section then for @CurrentFileExtension? Let's search around a bit again. And we find:

SELECT @CurrentFileExtension = CASE
    WHEN @CurrentBackupType = 'FULL' THEN @FileExtensionFull
    WHEN @CurrentBackupType = 'DIFF' THEN @FileExtensionDiff
    WHEN @CurrentBackupType = 'LOG' THEN @FileExtensionLog
    END

So there you have it.

Summary

If the parameters for your Full backup job are set as @FileExtensionFull='BAK' and you have set a @CleanupTime=24 then the procedure will delete all Full backup files that are at least a day old (24 hours).

The @CurrentCommand02 that gets executed is basically:

xp_delete_file 0, 'F:\SQLBACKUP', 'BAK', '2018-08-20 20:00:00.045'

So it doesn't touch on any other backup files. (Unless of course you have defined 'BAK' to be the extension of all backup types, in which case you lose).


'USE db_name' takes too long to change when login remotely I gave SELECT privilege to a user on a particular database, Now whenever I try to connect remotely

$ mysql -h ip-of-remote -u username -p

with that user credentials and try to change the database it takes to long to get Database changed while it connects (goes mysql prompt) very fast

mysql> USE db_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

On the contrary, when I ssh the mysql server and then try to login mysql prompt locally (without host) with the same user credentials and then try to change database it quickly changes and I don't get that message

mysql> USE db_name;
Database changed
mysql>

I tried to  --disable-auto-rehash in my.cnf but no luck. Please help

[mysql]
no-auto-rehash

since the the following command use my_database; executed too fast when you try this locally (on the same server), and take a log time when you executed remotely, there is some common causes for that like the connection between the server and client is slow, or there is a delay (lag) on attempting to retrieve and verify reverse DNS, you can try to add the following parameter skip_name_resolve for testing to my.ini or my.cnf under [mysqld] section.


What administration utilities does Ubuntu and Debian package with PostgreSQL? I see that there are a lot of tools authored by Ubuntu and Debian for PostgreSQL? Does any one have a list of them, or the build-differences?

Here is the Ubuntu-specific management utilities,

pg_archivecleanup
pg_basebackup
pg_config
pg_conftool
pg_createcluster
pg_ctlcluster
pg_dropcluster
pg_dump
pg_dumpall
pg_isready
pg_lsclusters
pg_receivexlog
pg_recvlogical
pg_renamecluster
pg_restore
pg_upgradecluster
pg_virtualenv

There are some other subtle differences with the variants provided upstream. Because Ubuntu is capable of running multiple PostgreSQL's of different versions, it distributes but disables some of their utilities (like pg_ctl as addressed in this question). In place of those utilities, it simplifies use with service [start|stop|restart|reload] postgresql and pg_ctlcluster.


SSMS Extremely Slow connecting to local dev db So I found this question and believe this is related.
SQL Server Management studio slow connection or timeout when using Windows Authentication

If I extend my timeout to 30 seconds I get SSMS connected but everything is terribly slow!  The application is extremely slow/timing out when debugging.

They mention adding an entry to the hosts file to bypass DNS lookup.  This is a local dev db running on my machine.  What would I add to the hosts file to eliminate these DNS issues???  Servername = "ROG-DREAM-MACHI\SQLDEV".

I'm a db noob.  Plz help. :(

Your DNS entry in the C:\Windows\System32\drivers\etc\hosts file will be:

ROG-DREAM-MACHI   127.0.0.1

You'll need to use Notepad with administrative rights, to be able to make any changes to that file.

(However, I'm voting to close this question as a duplicate of the one you posted.)


Best pratice to store dates group by months-year key par value I am making one service which needs to hold data grouped and calculated by year-month combination.  I know how to calculate data and put it on new table. But I am confused what kind of data type I should use for storing month - year value.  Here is what I have considered.

Two Integer columns one for year other for month (clear to understand and strong to manipulate (anybody who uses this table can easily use ranges and orders) but I think that would take more space than another solution.
Use a Date field, and always store one date for entire month. (Hard to explain to users how it works and how to create WHERE clauses)
One varchar(7) column and put there stings like 2012-02,2013-01. For me this is easy to understand but hard to manipulate.

Whick I should pick? or is there other solution.  Can someone suggest according to performance which way is going to be good solution, Because most of queries is going to using data range in where solution and my new table will have around 2-5 million records.

I'd use a date field with the 1st of a month and a CHECK constraint to ensure it stays day 1.

This keeps it in the native date/time format (which is your observation about option 3)

Option 1 would requires less storage, but complicates comparisons. And 5 million rows isn't much: you use less storage but add code and query complexity


How to correct Failed global > initialization: FileNotOpen I am trying to run eos and using the std docs - https://developers.eos.io/eosio-nodeos/docs/build-validation

I am trying - ~/opt/mongodb/bin/mongod -f ~/opt/mongodb/mongod.conf and I get the following -

  trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt/mongodb$
  2018-08-03T13:03:01.096+0000 F CONTROL  [main] Failed global
  initialization: FileNotOpen: Failed to open
  "/home/trevor/opt/mongodb/log/mongodb.log"
  ~/opt/mongodb/bin/mongod -f ~/opt/mongodb/mongod.conf

I tried already chmod 755 on the log and checking the conf as per other posts here. Nothing worked. Here is the conf -

trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt/mongodb$ more *conf
systemLog:
 destination: file
 path: /home/trevor/opt/mongodb/log/mongodb.log
 logAppend: true
 logRotate: reopen
net:
 bindIp: 127.0.0.1,::1
 ipv6: true
storage:
 dbPath: /home/trevor/opt/mongodb/data

I have Ubuntu 18.04.

Any idea what could be the cause?

Run as sudo. That worked for me.

  trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt$ sudo ~/opt/mongodb/bin/mongod
  -f ~/opt/mongodb/mongod.conf & [1] 23948
   trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt$


How to structure a MongoDB database to allow for multiple users to use the same account with restrictions based on roles? Ok, firstly I'm very new to MongoDB, and newish to DB development/management as a whole. I've done a unit using Oracle through uni, but apart from just manipulating fields through the phpmyadmin interface for wordpress plugins/sites, that's about it.

I want to model a (fairly complex?) user system. Here's an excerpt from the brief relating to it.

Users should be able to log in with their email and password, create a new account, invite other users and set different access rights. This way multiple users can share the same account with different levels of authority (ready-only, editing rights, etc.)

The backend needs a section where the business owner can set global restrictions on areas/sections of the app for different groups of users.

I've done a bit of research into structuring a MongoDB database and understand there should be a balance between embedding data into documents, and referencing other documents so that in the end you require fewer queries than if you embedded everything or referenced everything.

Here are my initial thoughts.

Side note - I don't know how to properly document a MongoDB schema so sorry if this doesn't actually make sense. I'll try explain it as best I can.

The diamond connections are for embedded objects inside documents, while the straight connections show references. (i.e AccountUsers are embedded in an Account while a Role is referenced by a User and AccountUser). I should probably update this to show directions and relation. Anyway...

I believe this setup allows both Users on an application level, and users on an account level to have separate roles that can be retrieved later. Global restrictions could be set by the business owner for all users of a certain role, and the admin of an account can set account wide restrictions based on setting a user's account role.

The roles themselves are arrays of objects containing a boolean and a reference to a SitePermission. The idea being that the SitePermissions will be setup by the dev team, with one SitePermission for each section of the app, from which the business owner or account owners can create their own roles.

Does this setup seem like a good way to go about creating a database that suits the brief? Like I said, I'm really new to MongoDB so I could have this whole thing completely wrong by treating it similarly to an RDBMS like Oracle. I basically just need to know if I'm doing this right? Thanks!

When working on an authorization requirement for any application, there are three essential elements that are part of the setup:

•   The resources that need to be secured/authorized
•   A list of roles and users that are part of these roles
•   A mapping between the resources and the roles that define who can access what


log_reuse_wait_desc - AVAILABILITY_REPLICA Unable to truncate transaction log I have just 1 secondary replica and just one database in my availability group. Both Primary and Secondary replicas are in synchronous-commit availability mode. I inserted 1000 records into the table and transaction was committed in both primary and secondary databases. Checkpoints were also issued. Now when I suspend the secondary database and try to take log backup of my primary database in order to truncate transaction log, it won't be truncated with the following log_reuse_wait_desc value: AVAILABILITY_REPLICA. Why isn't it truncated?
As far as I know as long as logs are inactive and VLF is filled up then transaction log can be truncated. Some of you can answer it in a way: SQL Server is waiting to send logs to the secondary Always On Availability Group replica, but my question is if 1000 records were already committed on both sides and checkpoints were issued, what logs are then waiting to be sent to the secondary replica?

The reason you are seeing this behaviour is due to the fact that you have SUSPENDED the data synchronization (see @AMtwo's comment).
From BOL,

a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

so if you unsuspend the data synchronization, then the log can be reused since you took the log backup.


How to optimize this select with join and order-by? We have two tables:

 CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` int(10) unsigned DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
....
  `subject_id` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`),
  KEY `created` (`created`),
  KEY `text_id` (`text_id`) USING BTREE,
  KEY `subject_id` (`subject_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=237542180 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

The second one:

CREATE TABLE `users` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `email` char(150) DEFAULT NULL,
  `reg_time` int(10) unsigned DEFAULT '0',
  `password` char(255) DEFAULT NULL,
...................
  `moderation` int(1) unsigned NOT NULL DEFAULT '0',
  `tag` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `created` (`reg_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=123585 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

messages has ~49M records, users has 13k. DB engine: Aurora(MySQL-Compatible) 5.6.10a

The terribly long request is

SELECT messages.*, users.administrator_group_id FROM messages
    LEFT JOIN users ON messages.user_id = users.id
    ORDER BY messages.id desc LIMIT 0,20

If I run this request without order by then it takes 14-16 sec. With order it takes longer than 5 min.

I considering change business logic to avoid this request and limit recordset from messages e.g. by messages date but would like to know if there is any way to speed it up on the same hardware as is.

I have never used Aurora and there might differences with MySQL but there is a method that works very often in MySQL in similar issues, when the execution plan is not optimal, i.e. when it does the joins first and then has to do the ORDER BY of the big intermediate result set.

Instead of joining the 2 tables, we try to first LIMIT the results in a derived table and then JOIN back. This way indexes will be used for the ORDER BY - LIMIT and then it will only have to do N seeks (20 in this case) in the 2nd table:

SELECT
    m.*,
    u.administrator_group_id
FROM
    ( SELECT id
      FROM messages
      ORDER BY id DESC
      LIMIT 20
    ) AS mi
  JOIN
    messages AS m ON m.id = mi.id
  LEFT JOIN
    users AS u ON m.user_id = u.id
ORDER BY
    mi.id DESC ;

And a variation:

SELECT
    m.*,
    u.administrator_group_id
FROM
    ( SELECT mi.*
      FROM messages AS mi
      ORDER BY mi.id DESC
      LIMIT 20
    ) AS m
  LEFT JOIN
    users AS u ON m.user_id = u.id
ORDER BY
    m.id DESC ;

Try both and check execution plan and performance. In any reasonable hardware a query that just gets 20 rows from a table or two and uses indexes should be really efficient. In milliseconds range, not seconds or minutes.


ISO 27001 - resetting passwords for SQL Server accounts We are implementing security changes in order to pass an ISO 27001 audit.
We are in the process of setting all domain accounts (user accounts, admin accounts, service accounts) to have their passwords changed automatically by our information security management system (Thycotic).

The question is, do we need to also change the non-domain SQL Server authentication accounts (such as the "SA" accounts) and if so do you know of a way to do this?

Is is standard to reset the passwords for these SQL Server authentication accounts?

The ISO/IEC 27001:2013 paper Information technology -- Security techniques -- Information security management systems -- Requirements is a standard that:

  ...specifies the requirements for establishing, implementing, maintaining and continually improving an information security management system within the context of the organization. It also includes requirements for the assessment and treatment of information security risks tailored to the needs of the organization. The requirements set out in ISO/IEC 27001:2013 are generic and are intended to be applicable to all organizations, regardless of type, size or nature.

If this standard defines that you should change the passwords every so often to ensure security, then by all means go ahead and do this.

Regarding SQL Server

Changing the password of an account is as simple as logging in to the instance and setting off the command:

ALTER LOGIN [sa] SET PASSWORD = 'ThisIsMyN3wSecurePassw0rd'
GO

Answering Your Question

  Is is standard to reset the passwords for these SQL Server authentication accounts?

There are no standards, regarding resetting the [sa] login's (or for that matter any other SQL Server authenticated account's) password.

But if you want to abide by the standards laid out in ISO/IEC 27001:2013 then you probably should change the SQL Server authenticated account's passwords at regular intervals.

  regular: existing or happening repeatedly in a fixed pattern, with equal or similar amounts of space or time between one and the next; even:

Reference: regular (Cambridge Dictionary)

That means that once a year can be considered regular.


Can an entity in an entity-relationship diagram have more than one relationship? I am practicing drawing entity-relationsip diagrams and in the example it states "many employees work on a project, and employee can work many projects but can only be assigned to at most one project at a given time." Does the final statement after the "but" mean that there is another relationship?

It means that time is an essential component of the relationship.  It's telling you that there's a functional dependency (employee, time) -> project.

In an entity-relationship diagram, I might model it like so:

I made it a weak entity dependent on Employee and with time as a weak key, but it's still a relationship between Employee and Project, hence a weak associative entity.


How to support arbitrary column sorting in a database? Our product has a "table view" feature that allows customers to display their data in tabular form.

That data is made up of form fields the customer creates, and thus there can be an arbitrary number of fields that aren't known ahead of time.

Right now, this is implemented in Mongo using an array of "column" objects. This works fine right now because we don't allow sorting by arbitrary fields.

What we would like to do is allow our users to sort by any columns, in any order.

For example, if a customer had fields f1, f2, f3, then we would like to allow them to sort by (f3 desc, f1 asc, f2 desc).

So far, the possible solutions I've thought of are:

index every field: I feel like that will have enormous storage overhead
create a collection for each customer: this keeps their collection small, making indexes not required until they have tons of data
combo: make a collection for each customer, and only index them when they pass a certain size

We are not 100% tied to using Mongo, so if there's a database that is capable of doing this naturally, we're open to switching.

Are these the only options in Mongo, or are there better suited databases for this task?

Also, if it's helpful, we are also using PostgreSQL as our canonical data store.

You don't need index to sort data. Because this customers data must have customer_id included to every document, just create index for that customer_id. You make query with that customer_id and set sort parameters according what customer wants data to be sorted. Sorting is done in memory at mongodb node (or nodes, if you have cluster). Of course, if customer have more data than 32MB, sorting without index is impossible. With aggregate and allowDiskUse you can do sort of very large results.


Exporting data to excel I want to execute some stored procedure and I want to export that result to Excel. How can Do that ? I'm new to SQL. any help or tutorials about this will appreciated.

Couple of ways that I know of

Run procedure in ssms, then copy results into excel (make sure to turn on the option to copy column headers in tools,options,query results)
Create a SQL server integration services (SSIS) package to run the procedure and write the results to a CDC or excel file.
If you have SQL server reporting services (SSRS) you can create a report to run the procedure and display the results, then from the report website you can export the report to excel/csv. This can also be scheduled through SSRS subscriptions.


Collecting Wait Stats We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time.  Below are the wait stats by number of tasks waiting and also wait time per task.

We have users complaining of slow down in the system, yet the metrics for server seem fine in terms of Disk IO, memory and CPU. Does anyone know if the PREEMPTIVE waits are an issue?

Number of waiting tasks
SOS_SCHEDULER_YIELD
PAGELATCH_EX
PAGELATCH_SH
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT

Average wait per task
PAGEIOLATCH_SH
PREEMPTIVE_XE_GETTARGETSTATE

Update:
I ran a query from Paul Randal similar to what you posted and got the following:

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
PREEMPTIVE_XE_GETTARGETSTATE    9704.81 9704.81 0.00    604647  44.60   0.0161  0.0161  0.0000

I know this doesn't line up very well, but basically this wait type has accounted for %44.60 of all wait types.  Also as there has been no Signal Waits on this type then this indicates no CPU pressure but instead a wait on some other resource.  Not sure how I deduce what that resource is however.

Also this is SQL 2012 SP1

Update2
AS requested here is results from your query.  In regards extended events the only sessions running is the default system_health one and 2 SharePoint ones I have just noticed, they must have been put there by default.  I may switch these off I wonder if these are causing an issue.

Its interesting that my PREEMPTIVE_XE_GETTARGETSTATE doesn't seem to be in this list.

wait_type   wait_time_ms    signal_wait_time_ms resource_wait_time_ms   percent_total_waits percent_total_signal_waits  percent_total_resource_waits
SP_SERVER_DIAGNOSTICS_SLEEP 300014  355508314   0   24.621089361251698  99.883069863550302  0.000000000000000
MSQL_XP 96782   0   4268999 0.295653861591811   0.000000000000000   0.295653861591811
ASYNC_IO_COMPLETION 56193   64  345552  0.023935987107964   0.000017981341700   0.023931554722962
BACKUPTHREAD    41100   6850    265025  0.018828979257262   0.001924565478840   0.018354575549998
LCK_M_U 40500   71  41030   0.002846491499596   0.000019948050948   0.002841574322484
PWAIT_ALL_COMPONENTS_INITIALIZED    31422   0   94205   0.006524262955146   0.000000000000000   0.006524262955146
XE_LIVE_TARGET_TVF  28050   0   33458   0.002317167771915   0.000000000000000   0.002317167771915
LCK_M_X 4027    50  29195   0.002025392177944   0.000014047923203   0.002021929377161
SQLTRACE_INCREMENTAL_FLUSH_SLEEP    4018    613 355390660   24.612983567922965  0.000172227538471   24.612941113985366
CXPACKET    3756    1   14755   0.001021941767062   0.000000280958464   0.001021872511047

The preemptive_xe_* wait types are associated with Extended Events from what I understand and can find. Considering that and your first sentence:

  We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time.

I would start looking at your monitoring tools as the culprit. However being that your data from Paul's script shows average wait is low I would not put to much concern into it right now.

Your main challenge here that I think you may be overlooking is your user's told you there was a slow down in the system/application. Most will always point to the database/server as the culprit. My general next question when someone tells me that is "what portion of the system is slow". If they tell me things like pages loading slowly, and they are not actually executing any ad-hoc reports or queries, I ask them to check with the server folks or application admins to ensure nothing else is messed up. Then while they are doing that I will go check the database server performance. However as you have shown if the XE wait type is the only one above 10% or so I don't think the issue is with the database server.

If you want to find the culprit of that wait type you can begin querying sys.dm_os_waitting_tasks, this will show you the session_id and wait that is occurring. You can also use sp_WhoIsActive to get similar information a bit more easily. I would bet you are going to find it to be sessions associated with your monitoring tool.


SQL Server: Using 4 part identifiers when database may be on the same server In my client's setup, the database servers in lower environments are all on one server for cost saving purposes, but in higher environments, these databases are deployed on separate servers, and linked servers are used when queries run across the servers.

Example setup:

Dev:
  Server1: DB1, DB2
UAT:
  Server1: DB1 (with linked server for DB2)
  Server2: DB2

I am trying to maintain the databases with a single set of SQL Server Data Tools projects, using 4 part [server].[database].[schema].[object] identifiers and database reference variables to model the relationships.  This works well when the databases are not on the same server, but fails when the databases are on the same server.

My questions:

Is there a default way to refer to the local server in a 4 part identifier other than omitting the server definition?
If not, is there a way to add a linked server pointing to the local server?
If neither of the above is possible, is there another way to manage this in SQL Server?

Use a synonym, and just don't sync the synonyms. So when it is on a local server:

CREATE SYNONYM dbo.FooBar FOR DBName.dbo.TableName;

When it is remote:

CREATE SYNONYM dbo.FooBar FOR ServerName.DBName.dbo.TableName;

Now all your code just has to reference dbo.FooBar no matter where it is.


Batch Requests/sec reported by DMV millions of times larger than Activity Monitor I have verified through SQL Server Profiler that my request per-second are around 30 request/s as is corroborated by SSMS Activity Monitor but sys.dm_os_performance_counters is reporting hundreds of millions/s.

Any idea what might be causing this gross discrepancy?

Query:

SELECT
    RTrim(LTrim(object_name)) as object_name,
    RTrim(LTrim(counter_name)) as counter_name,
    cntr_value
FROM
    sys.dm_os_performance_counters
WHERE
    instance_name IN ('', '_Total')
    and counter_name IN (
        N'Batch Requests/sec'
        , N'SQL Compilations/sec'
        , N'SQL Re-Compilations/sec'
        , N'Transactions/sec')

Results:

object_name counter_name    cntr_value
SQLServer:Databases Transactions/sec    191721399
SQLServer:SQL Statistics    Batch Requests/sec  242955426
SQLServer:SQL Statistics    SQL Compilations/sec    42048371
SQLServer:SQL Statistics    SQL Re-Compilations/sec 1200947

From the documentation:

  Note: For per-second counters, this value is cumulative. The rate
  value must be calculated by sampling the value at discrete time
  intervals. The difference between any two successive sample values is
  equal to the rate for the time interval used.

If you'd like something that already does interval sampling, sp_BlitzFirst is pretty neat, and free, and full disclosure: I am a contributor, etc.


mongodump fails after exactly 10 minutes Using single instance MongoDB v4.2.0 on Debian 9.11 with collection of 78GB and 60M documents on a 2 vCPUs, 13 GB memory server. This command is invoked on the same server where database runs:

mongodump --username user --password pwd --authenticationDatabase admin --host localhost --gzip --archive=out.gz --db database --collection collection

And after 10 minutes this is the output:

2019-10-17T16:13:09.523+0200    Failed: error creating intents to
dump: error counting database.collection:
connection(localhost:27017[-2]) unable to decode message length: read
tcp 127.0.0.1:57798->127.0.0.1:27017: i/o timeout

By looking at mongod.log this is what mongodump outputs:

2019-10-17T16:13:09.523+0200 I  NETWORK  [conn20] end connection 127.0.0.1:57794 (4 connections now open)
2019-10-17T16:13:09.748+0200 I  -        [conn21] operation was interrupted because a client disconnected
2019-10-17T16:13:10.371+0200 W  COMMAND  [conn21] Unable to gather storage statistics for a slow operation due to lock aquire timeout
2019-10-17T16:13:10.371+0200 I  COMMAND  [conn21] command database.collection appName: "mongodump" command: aggregate { aggregate: "collection",
pipeline: [ { $match: {} }, { $group: { _id: 1, n: { $sum: 1 } } } ], cursor: {}, lsid: { id: UUID("4759f9ad-7d37-44c7-bd41-8610af565c47") },
$db: "database" } planSummary: COLLSCAN numYields:437454 ok:0 errMsg:"Error in $cursor stage :: caused by :: operation was interrupted because
a client disconnected" errName:ClientDisconnect errCode:279 reslen:186 locks:{ ReplicationStateTransition: { acquireCount: { w: 437456 } },
Global: { acquireCount: { r: 437456 } }, Database: { acquireCount: { r: 437455 } }, Collection: { acquireCount: { r: 437455 } }, Mutex: {
acquireCount: { r: 2 } } } protocol:op_msg 600852ms

It looks like mongodump internally runs this aggregate query:

[ { $match: {} }, { $group: { _id: 1, n: { $sum: 1 } } } ]

And when the same query is run from MongoDB shell it works:

> db.collection.aggregate([ { $match: {} }, { $group: { _id: 1, n: { $sum: 1 } } } ])
{ "_id" : 1, "n" : 60488853 }

And this is the mongod.log output:

2019-10-17T15:01:37.130+0200 I  COMMAND  [conn2] command database.collection  appName: "MongoDB Shell" command: aggregate
{ aggregate: "collection", pipeline: [ { $match: {} }, { $group: { _id: 1.0, n: { $sum: 1.0 } } } ], cursor: {}, lsid: {
id: UUID("3b732623-e8b4-4365-bac7-efa710db035c") }, $db: "database" } planSummary: COLLSCAN keysExamined:0
docsExamined:60488853 cursorExhausted:1 numYields:472591 nreturned:1 reslen:136 locks:{ ReplicationStateTransition: {
acquireCount: { w: 472593 } }, Global: { acquireCount: { r: 472593 } }, Database: { acquireCount: { r: 472593 } },
Collection: { acquireCount: { r: 472593 } }, Mutex: { acquireCount: { r: 2 } } } storage:{ data: { bytesRead: 79320933249,
timeReadingMicros: 610249624 } } protocol:op_msg 653559ms

What can be seen is that this count query from shell takes 653 seconds while the mongodump internal query timeouts after 600 seconds (10 minutes exactly).

Other smaller collections and databases on the same server do not have this issue, just this large one.

How this timeout or issue for large queries can be solved so that mongodump runs without issues?

It looks like there was a bug as after upgrading to MongoDB v4.2.1 the issue does not happen any more.


why is this left join faster than an inner join? So I'm tuning this query, and I am pretty sure that in this instance, I can replace an inner join with a left join without affecting the data. However, I'm not entirely sure why this is faster. Here is the query:

SELECT DISTINCT  cl.NAME                            AS company_name,
                     cl.id                              AS company_id,
                     ep.Plan__c                           AS plan_id,
                     ep.Employee__c,
                     ep.id,
                     do.Subcategory__c,
                     ep.Plan_Type__c,
                     pt.SubType,
                     Sum((pt.[shares] * fvh.[ValuePerShare])) AS TotalValue,
                     ppe.Deferral_Option__c,
                     dt.Defer_type_Code,
                     do.Short_Code__c,
                     pt.ContributionYear
  FROM   dbo.ParticipantTrades pt WITH (NOLOCK)
        INNER JOIN dbo.PayoutPathElection ppe WITH (NOLOCK)
                ON pt.payoutPathElectionID = ppe.Id
        INNER JOIN dbo.DeferralOption do WITH (NOLOCK)
                ON ppe.Deferral_Option__c = do.id
        INNER JOIN dbo.EmployeePlan ep WITH (NOLOCK)
                ON pt.employeePlan = ep.Id
        LEFT JOIN dbo.DeferralType dt WITH (NOLOCK)
                ON pt.deferralType = dt.defID
        INNER JOIN dbo.Fnc_lastfundvalue('2019-01-30') AS fvh
                ON pt.fund = fvh.Fund
        INNER JOIN dbo.Clients cl with (NOLOCK)
                ON ep.Company__c = cl.Id
  WHERE ep.Company__c = '0017000001WL1HfAAL' AND ep.Plan_Type__c  LIKE '%' AND pt.tradeDate <= '2019-01-30'
  Group by   cl.NAME,
             cl.id,
             ep.Plan__c,
             ep.Employee__c,
             ep.id,
             do.Subcategory__c,
             ep.Plan_Type__c,
             pt.SubType,
             ppe.Deferral_Option__c,
             dt.Defer_type_Code,
             do.Short_Code__c,
             pt.ContributionYear

The bottleneck is at the table-value function join (Fnc_lastfundvalue). My hunch to why changing it to a left join is faster is that it can then reorder the joins and it causes less spillage into tempdb? Here is the query plan before and after changing INNER JOIN dbo.Fnc_lastfundvalue.. to LEFT JOIN dbo.Fnc_lastfundvalue..

Before (29 seconds): https://www.brentozar.com/pastetheplan/?id=Bk1Y-WqEE

After(3 seconds): https://www.brentozar.com/pastetheplan/?id=B1hoW-544

NB: Execution plans above are created on a dev box. The production server is still on SQL Server 2008.

It looks as though some of the difference is because the slow plan was run first.

The slow plan was clearly operating against a cold cache as it shows additional physical reads and managed to accumulate an additional 15 seconds of PAGEIOLATCH_SH waits compared to the fast case.

This looks as though it affected both the execution of the TVF itself (which took 5.5 seconds compared with 1.35 in the fast case.) and the wider plan using the result of it.

You say in the comments that on second execution it took 11 seconds. This is still 3 times slower than the fast plan (3.446 seconds) so doesn't explain all the performance difference.

The main problem you are experiencing is due to the poor default estimation for multi statement TVFs (fixed guess of 100 rows in compatibility levels 2014/2016 and 1 in earlier versions). In reality your TVF returns 1,715 rows.

In the inner join case because inner joins are associative and commutative it can be re-ordered flexibly and gets moved to the deepest part of the tree. This would make sense if one row was actually returned as it could whittle down the row count early for the other joins in the plan.

The execution plan is below. The pink annotations are "Actual Elapsed Time (ms)" from the XML.

Because of the 1 row estimate it starts off badly by joining onto dbo.ParticipantTrades and selecting a plan with nested loops and lookups. That nested loops has an elapsed time of 13.976 seconds (presumably most of which was spent waiting on the spilling sort immediately upstream to request rows from it, with 3.26 seconds taken up on the lookups themselves and associated IO waits for the physical reads at that operator).

923,646 rows are emitted from the join vs an estimated 1423.18 and this mis-estimate propagates upwards in the plan through 3 sorts and a hash join spilling as it goes (due to the row underestimate)

When you add the LEFT JOIN it can not be as freely re-ordered and the join happens much higher up in the plan (where it would do less damage in the problematic INNER JOIN case). The semantics of outer join help here anyway.

Whilst the estimate for the number of rows coming out of the TVF is still 1 this does not adversely affect the estimates for the join it is directly involved in (SQL Server assumes that the cardinality coming out of that join will be the same as that of the other sub tree to the join and this is in fact what happens - an outer join cannot reduce this number as a non joined row would still pass through - just with NULL for the fvh columns).

There are still cardinality estimation errors in the outer join plan but not of the same magnitude and it requests a sufficient memory grant to avoid spilling anywhere.

This cardinality estimation issue has been resolved in the most recent version with interleaved execution. In the meantime (as you say in the comments it needs to be 2008 compatible) you can manually interleave it by storing the TVF result into a #temp table and joining onto that to allow the count of the intermediate result (and column statistics) to be taken into account.


What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement? 2M rows inserted using LOAD DATA INFILE to innodb takes 7.5min, and profiling shows 99% of that time is in "System lock".

  Does this tell me anything useful?

   mysql> set profiling=1;
   Query OK, 0 rows affected (0.00 sec)

   mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
   Query OK, 1964807 rows affected, 8 warnings (7 min 27.35 sec)
   Records: 1964806  Deleted: 1  Skipped: 0  Warnings: 8

   mysql> show profile for query 1;
   +------------------------------+------------+
   | Status                       | Duration   |
   +------------------------------+------------+
   | starting                     |   0.000206 |
   | checking permissions         |   0.000015 |
   | Opening tables               |   0.000034 |
   | System lock                  | 447.327523 |
   | Waiting for query cache lock |   0.000352 |
   | query end                    |   0.000011 |
   | closing tables               |   0.000014 |
   | freeing items                |   0.000033 |
   | logging slow query           |   0.000007 |
   | logging slow query           |   0.000006 |
   | cleaning up                  |   0.000006 |
   +------------------------------+------------+
   11 rows in set (0.02 sec)

The mysql_load() function calls the open_and_lock_tables() function to lock the table mentioned in the LOAD DATA statement.

MySQL obtains an exclusive lock on the table so that it can very quickly load data into the table.  There is very little overhead in the LOAD DATA process, just the bare minimum parsing is done to make it work.

The CONCURRENT option only affects MyISAM tables, if you are loading into an InnoDB table it does not permit concurrent access.

The reason why System lock took so long is that the actual data load was lumped into the timing for that step.  The profiler works by measuring time between fenceposts, that is the entry time is logged for each instrumented user function.  The mysql_lock_tables() function is called from inside mysql_load(), but mysql_load() is not instrumented, so the elapsed time starts at the system lock and ends at the next fencepost, which was the query cache lock.

Your data load of 1.96mn rows took about 447 seconds, there isn't any way to separate the actual lock time overhead from the loading time because the loading isn't instrumented.


What is this query, involving system views & index properties https://www.brentozar.com/pastetheplan/?id=Bkg8vBVzb

What in the heck is this query from?  I thought it may have been sp_BlitzIndex or sp_BlitzCache but I searched thru them and didn't see anything like it (looked for keywords like IgnoreDuplicateKeys or spatial_indexes).  When I searched for is_hypothetical I got a hit on both sp_BlitzIndex and Ola Hallengren's IndexOptimize, but further eyeballing tells me those aren't the culprits because they're missing so many other pieces that this rogue plan has (like the spatial and xml stuff).

I noticed it because it's executed over 20k times per day and shows a lot of wait time in my monitoring tool, but then again it typically only shows waits between 1am and 7am, which is kinda around our maintenance window... I'm just really confused at this point, though, because I'm using Ola's solution for indexing and the occasional ad-hoc alter index all on some_table rebuild.

Any ideas?

Now that you know where this query is coming from, the next step to take will be based on if you think this process is critical or not.

If I were in  your shoes, I would setup a Login Trigger that won't allow this connection in and see who complains or what tool starts erring out.  Just a word of warning, a poorly defined Logon trigger can make your life maddening, but hopefully this example will do the trick.  I modified an example of a different restrictive trigger based from the one found in this post:

CREATE TRIGGER application_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF HOST_NAME() IN (N'ServerNameOfAnnoyingQuery', N'PCNameOfAnnoyingQuery')
AND ORIGINAL_LOGIN() NOT IN ('sa', 'DOMAIN\username')
          BEGIN
            RAISERROR('Connection Denied.  Please contact the Database Administrator Group at dba@companyname.com to resolve this issue.', 16, 1)
            ROLLBACK;
          ENDEND;

If you suspect the query does support a critical process, I would start reaching out to users who have profiles on the originating server in question and start asking them about processes they have used from there.

Finally, if you can't identify any useful information from users and you don't want to go so far as to create the logon trigger, I would then track it down by  process-of-elimination.  Start this by logging into the Server where this query originates from and slowly stop any running services (i.e. services.msc) that look suspect as well as review/disable any suspect Windows Scheduler jobs.  Eventually I would hope you turn off the proper process and eventually figure out what's going on.

Hopefully this gives you some ideas on routes to take.  Good luck and hopefully you figure it out.


What is the meaning of filtered in MySQL explain? As described here in the MySQL docs:

  The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. Before MySQL 5.7.3, this column is displayed if you use EXPLAIN EXTENDED. As of MySQL 5.7.3, extended output is enabled by default and the EXTENDED keyword is unnecessary.

I still don't get it. What is the meaning of "filtered" here? What information we can get from this column?

For example, when I start querying, some query will show 100, and some others show 18 or any lower than 100.

+-------------+-------+--------+---------+---------+------+----------+
| select_type | table | type   | key     | key_len | rows | filtered |
+-------------+-------+--------+---------+---------+------+----------+
| PRIMARY     | a     | range  | search  | 4       |  174 |   18.00  | <--
| PRIMARY     | b     | eq_ref | PRIMARY | 4       |    1 |   100.00 |
| PRIMARY     | c     | ALL    | PRIMARY | 4       |    1 |   100.00 |

What the main point we can conclude from this value?

Is it says that, the column filtered only 18%? Or if the  more lower the score, the more good the index/query ?

I'm using MySQL 5.7

To filter here means to apply a condition on a set of rows that were selected by a type-search as potential rows, and to only keep rows that fulfill the condition:

MySQL will first try to use an index, e.g. do a range scan on your table a using the search-key. It estimates to get 174 rows out of using that index, which is the number in rows. This step is not yet called filtering.

After that, these 174 rows have to be checked against additional conditions (usually in your where-clause). MySQL now estimated that only 32 rows, so 18% of these 174 rows, will remain after that filter has been applied. This 18% is the value in filtered.

While it is obviously better to have 32 rows instead of 174 (if you e.g. have to later join them with another table), a "perfect" index would have given you these 32 rows directly from the initial search, saving you the time to look at and filter out 82% of all potential rows.

So a low value might indicate that there could be a better index: e.g. a full table scan with rows=1000 and filtered=0.1% could become an index lookup with rows=1 and filtered=100% if you add a good index.

On the other hand, you can very well completely ignore this filtered-value (which is in most cases a really bad estimate anyway), and focus on the other more important columns (especially type, key and extra) to optimize your query. It can e.g.  be better to get rid of a filesort (e.g. by using an index that satisfies the order by), even if it results in a lower filtered value. And a better type can result in a huge performance improvement, even if it might not change or even lower filtered. In the example above with filtered=0.1%, type=all would have already be enough to indicate that you might be able to improve that query by adding an index, without looking at filtered at all.

So don't take that value too seriously: neither does 100 mean your indexes are good, nor does a lower value necessarily indicate bad indexes. type is a much better indicator for that.


Add column on huge InnoDB table in MySQL 4.1 I need to add a column on a huge InnoDB table (200GB) which resides on MySQL 4.1.21 (SunOS).

CPU has 16GB RAM and 200GB of more disk space.

Is there any quick way of adding this rather than ALTER TABLE? Because, ALTER TABLE as far as I know would take some months to complete.

Any thoughts experts?

You can use statement-based replication and add the column to a slave first.  Any pending modifications will queue up on the slave, and apply once the alter has complete.  When the slave is fully up to date with the new schema, you can effectively 'promote' it.

I am sure you are aware, but MySQL 4.1 is quite old.  Triggers were not introduced until MySQL 5.0 (2005), so this limits your options to use tools like pt-online-schema-change.


How to forcibly create/modify MySQL user grants, without restarting the database service I am familiar with how to start MySQL server with the "--init-file" option, or the "--skip-grant-tables" options.

However I have a nagios based mysql monitor, which needs to query a table, and therefore I need to configure a user (GRANT SELECT ON heartbeart to 'nagios-monitor'@'123.123.123.123') for that operation with appropriate permissions granted to the nagios server host, or nrpe script host.

I'd like to have an automated installer script, to configure the table. Hence I would like to be able to carry out these operations automated, with little risk to the underlying application uptime.

It is not always clear what the mysql root user password is.  (targets are arbitrarily used developer and test boxes, and they change the passwords)
I want to be able to add monitoring without causing any downtime
Other strategies to manage users/passwords are fine, but again, I still need to apply those grants without restarting the database.

Notes

If I install the mysql from scratch, then I add a "dba" type user similar to 'debian-sys-maint' in addition to the root user, with a complex password, which sidesteps the above problem. - this is not the use-case that i am considering here.

Possible Solutions

suggestion: Spin up a second mysqld process on port 3307 (with the --init-file option to create my nagios-monitor user), pointed at the same /var/lib/mysql, but masked to only open the "mysql" database

consequences?: probably corrupt everything - could pause

sudo kill -STOP $(cat /var/run/mysqld/mysqld.pid)

and then start -

sudo kill -CONT $(cat /var/run/mysqld/mysqld.pid)

How to force the main mysql to re-read the tables from disk?

some sort of scheduled task that is run by mysqld? could append my commands to that, as its not particularly urgent to run the grants.

Multiple mysqld

http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html

  Beware of the dangers of using multiple mysqld servers with the same
  data directory. Use separate data directories, unless you know what
  you are doing. Starting multiple servers with the same data directory
  does not give you extra performance in a threaded system.

http://dev.mysql.com/doc/refman/5.5/en/multiple-data-directories.html

5.3.1. Setting Up Multiple Data Directories

  Normally, you should never have two servers that update data in the same
  databases.
  Even when the preceding precautions are observed, this kind of setup works
  only with MyISAM and MERGE tables, and not with any of the other storage
  engines.

mysql> SHOW TABLE STATUS where Name = 'user'\G
*************************** 1. row ***************************
           Name: user
         **Engine: MyISAM**
        Version: 10
     Row_format: Dynamic
           Rows: 27
 Avg_row_length: 91
    Data_length: 2476
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2012-04-12 11:46:21
    Update_time: 2013-01-22 00:16:00
     Check_time: 2013-01-19 20:52:09
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges
1 row in set (0.00 sec)

So I spun up a 2nd mysqld on port 3307, with a masked datadir like so;

# mkdir /var/lib/mysql2/
# ln -s /var/lib/mysql/mysql /var/lib/mysql2/mysql

# /usr/sbin/mysqld --basedir=/usr \
--datadir=/var/lib/mysql2 \
--plugin-dir=/usr/lib64/mysql/plugin \
--user=mysql \
--log-error=/var/lib/mysql2/server-64654.err \
--pid-file=/var/lib/mysql2/server-64654.pid \
--socket=/var/lib/mysql2/mysql.sock \
--port=3307 \
--init-file=/var/cache/chef/grants.sql \
--datadir=/var/lib/mysql2/

with a grants.sql;

GRANT ALL ON *.* TO 'nagios-user'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql --port=3307 -h 127.0.0.1 -e 'select User from mysql.user'
+-------------+
| User        |
+-------------+
| root |
| nagios_user |

But the original mysql,doesn;t pick it up until it has been restarted! gah!

mysql --port=3306 -h 127.0.0.1 -e 'select User from mysql.user'
+-------------+
| User        |
+-------------+
| root |

You could copy just the the "mysql" database away to another location and start another daemon on it.  Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext.  This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.


Index fragmentation and SSD disks I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation.
Has anyone experienced this?
I'm thinking of changing my hardware and I'm wandering if that would help

Using solid-state drives certainly does reduce the performance impact of fragmentation, primarily because random I/O is so much faster than traditional storage.

Nevertheless, the reduction in page density (and extra logging) caused by page splitting is still undesirable and may still impact performance.

Generally speaking, well-maintained indexes with an appropriate FILLFACTOR are good regardless of physical storage considerations.

See Paul Randal's excellent article on TechNet for details.


MYSQL Multiple table optimization for advanced search I have a users table where there is the basic information of each user:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_type` varchar(10) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(500) NOT NULL,
  `registration_date` timestamp NULL DEFAULT NULL,
  `account_status` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

On the other hand, I have a `user_data' table in which we put every other data:

CREATE TABLE `user_data` (
  `data_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `value` text,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Basically, the data is stored as a name => value

I fetch the data in the following fashion:

SELECT email, ud_desc.value as description, ud_lang.value as language
    FROM users
        JOIN user_data as ud_desc
        ON ud_desc.user_id = users.user_id
            AND ud_desc.name = 'description'
        JOIN user_data as ud_lang
        ON ud_lang.user_id = users.user_id
            AND ud_lang.name = 'language'

I made that second table this way because there is a ton of different data for each user, certain depending on their users.account_type.
Also my client keeps adding, removing and changing different kinds of data all the time.
So I needed something quite flexible and this is the best idea I came up with so far, from a developer point of view. Because I fear that this so called "best idea" is just a faint dream and will be too slow on the long term.

Knowing that I might have to perform LIKE and MATCH AGAINST queries on the large volume of data that user_data will hold.

Also, you might take note that I am not very familiar with indexes. But at this point, I have no idea what is the most efficient, given that the data is fetch based on two columns user_data.user_id and user_data.name.

Should I make both of those columns (user_id and name) indexes? Should user_id be a foreign key (for the users table JOIN)?

Is there a way to improve either my database model or my queries?

I thank you all in advance. I am a bit lost at the moment and need some fresh points of view on this matter. Don't hesitate to tell me if you need more details.

Don't use MyISAM, use InnoDB.  http://mysql.rjweb.org/doc.php/myisam2innodb
EAV is inefficient, especially when you normalize either of the columns.  http://mysql.rjweb.org/doc.php/eav  And read more about Entity-Attribute-Value schemas in stackoverflow.com
You are missing some critical indexes that are critical to performance.  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
At this point, FOREIGN KEYs are the least of the issues.


Best way to store immutable/read-only copies of data for logging? I'm storing data about tasks like so

TASK (id, name, employee_id, machine_id, ...)

For logging purposes I would like to store any information about that TASK once it was made.
Since the reference fields might change or be removed (let's say we remove an employee or a machine, or change its hard_drive...), I want to make sure I log everything as it was when the task was done.

My first approach was to just have a field for everything like so

TASK_DONE (id, name, employee_name, ..., machine_hard_drive, machine_ram, ...)

but it made a lot of redundant data and a quite huge table (there are already about 5 referenced tables in TASK each with a lot of logging relevant fields)

I felt like having some other logging tables like MACHINE_ON_TASK and referencing only to their ids could spare a lot of redundancy, but it also means creating new tables for everything log-worthy. On the other hand, it might make more sense that a TASK and TASK_DONE (and their associated data) look the same.

What do you think would be a better way to approach or solve this problem?

I'm using SQL-server but I don't believe this is relevant here.

You could treat your reference tables as slowly changing dimensions and perform type 2 maintenance i.e. add a new reference row when any reference data changes.  Over time you may choose to horizontally partition your reference tables into "active" and "archive" parts, depending on the data churn you experience.  This can be achieved using using SQL Server's built-in partitioning functionality or a roll-your-own approach with two separate tables.  Your needs at the time will dictate which.

There is no good reason why your active OLTP table and your logging table should look the same.  They perform different roles and have different read and write requirements.  If the logging table needs to be wide and sparse then that's what it needs to be.  Create the objects to solve the problem you have.

And one last suggestion, which is defininetly from the "clutching at straws" bucket: define TASK_DONE (id int, reference_values xml).  Extract your pertinent values at the point in time and save them away.  This allows for changes in the reference data schema without having to bring existing log records up to the new schema.  That will make historical searching more complicated, of course.


MySQL read/write ratio from information_schema.GLOBAL_STATUS Could you please help me to construct a query to sum specific rows from a table, basically desired output is:

+-----------------+----------------+
| Total reads     | Total writes   |
+-----------------+----------------+
| 345436          | 345435         |
+-----------------+----------------+

Total reads = Com_select
Total writes = Com_delete, Com_insert, Com_update, Com_replace

Use the typical trick of sum(IF the column is the right one, use it, if not read 0:

SELECT sum(IF(variable_name = 'Com_select', variable_value, 0)) as `Total reads`,
       sum(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'),
           variable_value, 0)) as `Total writes`
FROM information_schema.GLOBAL_STATUS;


Database instance and database I did some research and finally came to this conclusion:

Database instance = processes + memory
Database = (physical) log files + control files + data files.

Is this understanding correct?
Can an application use multiple database instances of the same database?
Can a table in database be used by multiple database instances at the same time?

Thanks.

Technically instance resides in memory and database (physical files) resides on disk. There can be an instance without a database (an example is a started instance right before you create a database). There also can be a database without an instance (example of this is database files residing on disk but instance is not running, or files copied to some other location, something along those lines). Typically you use database by connecting your client to it's instance in order to do some work on data contained within. So an instance, upon startup, associates itself with one or more databases (Firebird, for example - one server process can be used to connect to multiple databases).

Second question: Only in some High-Availability environment, namely RAC on Oracle, where multiple instances are clustered to service the same database. This is transparent to end-users, so practically no, it can't. If you're talking about non-clustered configurations then no - no two instances can use same set of database files, therefore no application can use more than one instance connected to the same set of database files. At least not in RDBMS I know about. However an application can, theoretically, use two instances servicing two distinct copies of the same database, why not. Only I can't see real-world reason to do so.

Third question: already answered. No database can be serviced by two instances unless it's some kind of HA environment.

EDIT about object types

This is what resulted from a query of select distinct object_type from dba_objects to my Ora 10g, it surprised even myself:

CONSUMER GROUP, INDEX PARTITION, SEQUENCE, QUEUE, SCHEDULE, TABLE PARTITION, RULE, PROCEDURE, OPERATOR, LOB PARTITION, WINDOW, LOB, PACKAGE, PACKAGE BODY, LIBRARY, RULE SET,
PROGRAM, TYPE BODY, CONTEXT, TRIGGER, JOB CLASS, UNDEFINED, DIRECTORY, MATERIALIZED VIEW, TABLE, INDEX, SYNONYM, VIEW, FUNCTION, WINDOW GROUP, CLUSTER, TYPE, RESOURCE PLAN, EVALUATION CONTEXT, JOB


Seconds Behind Master in Cloud Service I have few mysql servers in replication master-salves in cloud service.

The problem is even thought all configuration, mysql versions, server's resources are the same, still I have inconsistency in slave's behavior.

For example, I have a set of stable mysql slaves which work perfectly, and a set of unstable slaves which still have seconds behind master even in idle mode, without traffic, and the set of unstable is always the same servers.

I am looking for something that is different in stable and unstable slaves. So far I didn't find something like this. Please, if you know how to find the thing that different share this with us.

Thanks.

If all clocks are sync'd, I would suspect high write load on the Master. Why?

The Master is responsible for doing two things:

Executing the SQL
Recording the SQL and its timestamp

If there is any intermittency between steps 1 and 2, this could account for what seems to be replication lag.

Since there are stable servers, I suggest resetting the relay logs on the unstable servers by doing the following:

STOP SLAVE;
SHOW SLAVE STATUS\G
Get LOGFIL from Relay_Master_Log_File
Get LOGPOS from Exec_Master_Log_Pos
CHANGE MASTER TO master_log_file='LOGFIL',master_log_pos=LOGPOS;
START SLAVE;

The CHANGE MASTER TO command erases the relay logs it currently has and starts fresh.

Give it a Try, and see if this clears up the replication lag !!!


How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)? I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?)

SELECT cars.*, users.*, makers.*, places.*
  FROM cars
  LEFT OUTER JOIN "users" ON "users"."id" = "cars"."user_id"
  LEFT OUTER JOIN "makers" ON "makers"."id" = "cars"."company_id"
  LEFT OUTER JOIN "places" ON "places"."id" = "cars"."location_id"
  WHERE "cars"."deleted_at" IS NULL
    AND "cars"."draft" = 'f'
    AND ((cars.reference_number::text ILIKE '%abc123%')
      OR (makers.name::text ILIKE '%abc123%')
      OR (places.name::text ILIKE '%abc123%'))
  ORDER BY cars.id DESC NULLS LAST
  LIMIT 20
  OFFSET 0;

EXPLAIN ANALYZE output:

Planning time: 1.300 ms
Execution time: 105.936 ms

SELECT output using \timing:

Time: 2100.704 ms (00:02.101)

It seems that EXPLAIN ANALYZE does not include time to read the data.

How can I get the total time without using SQL (and not PSQL console)?

PostgreSQL 9.6.6

From a_horse_with_no_name in the comments,

  The time reported by explain (analyze) indeed does not include the time it takes to send the data to the SQL client. It is the time the server needed to completely prepare the result so that it could be sent to the driver. As any delay after that is completely up to the combination of the SQL client and the network between client & server it's impossible for explain to show that

That's pretty much all there is too it. One of them is server side, the other network side.


MySQL events with TRUNCATE and INSERT Can I create event that would first TRUNCATE table and then make INSERT?
I know how to TRUNCATE and INSERT in separate events but need to do it immediately one after the other. I just don't know the syntax for events.
I can do it in myPHPadmin

TRUNCATE tbl1;

INSERT INTO
tbl1
SELECT
....

I know I could you PHP to run those two queries. I prefer event.
Is it possible to do it in single event or at least to call those two event in other event.
BTW, are MySQL events ok to use or PHP file with cron would be better?
Thank you

You can create:

Step#1 - stored procedure, which will run as many queries as You need - http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
Step#2 - Native MySQL Events - which will call this stored procedure - https://dev.mysql.com/doc/refman/5.7/en/create-event.html

In case when You do not want use MySQL bud scheduler, You always can call this stored procedure from PHP, cron, any other SQL script and etc


Reset SQL Server usage In my development environment, I am playing around with queries. For that I need to reset SQL Server memory, plans, etc. things which can ensure me that my query is the only stress/working process on server with all values reset

Few of the things which I am doing

Firing checkpoint
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FLUSHPROCINDB
DBCC FREESESSIONCACHE
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

Am I doing it correctly? or do I need more things to clear or see fresh statistics?

Short of restarting the SQL Server service or the box this is enough. If you want to be absolutely fresh you might want to drop user created statistics from your last run.

SELECT stats.name, objects.name, schemas.name,objects.create_date
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0
  AND auto_created = 1
  order by objects.create_date desc


How to select values consisting of numerals only? I have a column like this:

WW0476091
YA0457514
547856232
856987452
254785W11

I want to get the values that contain numerals only (decimal digits from 0 to 9). For the example above, I want only these records to be printed:

547856232
856987452

How can I do that?

You can use a regular expression for that:

select *
from the_table
where the_column ~ '^[0-9]+$';

Related question on Stackoverflow


Loading Oracle ctl export file into postgresql I have a number of tables exported from an oracle database in the form of Ctl files with that data included within the file.

Im looking for a way to import these into postgresql but the the enterprise db toolkit seems to want a direct link to an already running oracle instance. A program similar to sql-loader would be excellent.

The solution is to load the data into oracle and then use one of the migration toolkits to move it to Postgres. Oracle produce a number of virtual box VMs that limit the effort needed but its still an extra step.


Trigger creation syntax error I'm trying to create a trigger where I set a colunm to 0 if another column is set to 0 and 1 if it's set higher than 0. This is what I wrote

CREATE TRIGGER livre_disponible BEFORE INSERT ON livres
FOR EACH ROW
BEGIN
    IF NEW.nb_exemplaires=0 THEN
            SET NEW.disponible=0;
    ELSEIF NEW.nb_exemplaires>=1 THEN
            SET NEW.disponible=1;
    END IF
END

The error is said to be on line 5 which is the first "SET".

I this the correct syntax to do it?

I didn't find anything wrong with your statement, you need perhaps DELIMITERs

DELIMITER $$
CREATE
    TRIGGER `livre_disponible` BEFORE INSERT ON `livres`
    FOR EACH ROW
   BEGIN
        IF NEW.nb_exemplaires=0 THEN
            SET NEW.disponible=0;
        ELSEIF NEW.nb_exemplaires>=1 THEN
            SET NEW.disponible=1;
        END IF;
    END;
$$

DELIMITER ;

  Updated : you missed also a ';' ==> END IF; END;


Trace flag 10260 on SQL Server 2016 Our SQL Server 2016 development servers have trace flag 10260 set on start up.
The other servers that run SQL Server 2016 do not.
I've searched high and low, but have not been able to find any info on the subject.

What does this trace flag do?

Is it safe to remove?

At some point in the pre-RTM phase, it was used for some Polybase related process. As far as I can tell, it's 10260 is no longer in use for RTM so you can remove. Doesn't actually do anything anymore.


For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers? Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns?

Like if I have this table:

    CREATE TABLE [dbo].[tbl_shipping_boxes]
    (
        [boxid]     [int] IDENTITY(1,1) NOT NULL,
        [costperten][numeric](10,2),
        [weight]    [int] NOT NULL,
        [length]    [numeric](10,2) NOT NULL,
        [width]     [numeric](10,2) NOT NULL,
        [height]    [numeric](10,2) NOT NULL,
        [volume]    [numeric](10,2),
        [dimwgt]    [numeric](10,2),
        [costperone][numeric](10,2)
    )

I would like to calculate the volume, dim weight, and cost per one. Which would offer me better performance? More reads on this table than writes.

Is one preferred over the other for constant reads, and low writes? What about if I add a quantity field that I would decrement or increment to? Does that change the model I should have?

I am not having luck with persisted columns not recompiling on each query even when marked persisted and using deterministic values and functions. I used database tuning wizard, and it is faster, but I am concerned about the space this will eventually take up.

Thanks

I would not bother with persisted columns at all. I would just define a computed column and have it recalculated every time we select, so that I save considerable storage and have my selects do less reads, at the expense of the negligible amount of CPU needed to calculate volume.

Of course, I did some benchmarks in the past.


How does a subquery use the main query columns? I'm really confused by this SQL here, it's used in our app but I have no idea how it works. I put it in from examples I found online and it seems to have no issues.

What it's doing is grabbing the latest timestamp of a set. We couldn't use ORDER BY as there had to be multiple groupings. The actual query is much larger and this is just a cut down version showing the fundamental selection.

select * from table1 as main
WHERE NOT (EXISTS (select * from table1 sub
WHERE sub.datetime > main.datetime));

(Live online version: http://sqlfiddle.com/#!17/290cc/2)

What I think should be happening in my mind:

Two identical tables selected, the constraint in the subquery is saying:

  Only select the rows from the subquery which have a date greater than any other row in the main query.

In this case:

Subquery id:1 has only a smaller than or equal to date value from all rows in the main query so it doesn't get selected.
Subquery id:2 has a date which is larger than id:1 from the main query selection, so id:2 gets selected once. It is not larger than anything else.
Subquery id:3 has a date which is larger than both id:1 and id:2 from the main query selection, so it gets selected twice.

So the return from the subquery should be (2, 3, 3). So the main query should do a selection which not exists in that set, which should return id:1, but it returns id:3.

Where is my misunderstanding?

Well, there are some misunderstandings about what the subquery is doing.

First of all, EXISTS works in this case by evaluating the subquery for every row of the main query, and returns true if the subquery returns even a single row.

Since you are using WHERE NOT EXISTS(....), what's happening for every row from the main query is the following:

For id = 1: The subquery evaluates if there exists a row in the same table, where the date is greater than the date for id = 1. And the results would be ids 2 and 3, hence EXISTS evaluates to true, and NOT EXISTS eliminates this row
For id = 2: Same as before, in this case the subquery would return id 3, hence EXISTS evaluates to true, and NOT EXISTS eliminates this row
For id = 3: There are no other rows in this table that have a date greater than it, so EXISTS evaluates to false, and NOT EXISTS returns true, so you get this row as a result


MariaDB Full Text Index - How to query old data as well I have added new full text index to mariadb

ALTER TABLE <table name> ADD FULLTEXT INDEX records

I can query against this index only newly added and updated records but for older records (which is added before this index) I cannot query

Is there any workaround that I can also fetch older records

normally it would be build, when you added the index

But you can OTIMIZE TABLE yourtablename

  The fulltext index is only fully re-organized when an OPTIMIZE TABLE statement is run.