MySQL Large Table Indexes - Recommended length of index I am working with a table that has a couple hundred million rows of data. It uses varchar(32) UUID's as it's keys both primary and foreign (there are two). I was wondering with this size of table, how much of each field do I need to index to keep good performance but not index every character of those three fields.

For example if I set an index length of 24 characters, will I see any noticeable difference in performance? I was hoping there might be some kind of algorithm out there that can be used to estimate performance based on length of field and length of index.

Currently the table is ~100GB in size, and the index is taking up over half of that space. I would like to recover some of that space, and reduce the growth rate of the table.

This is not an answer to your question (how to cut index length), but an alternative to reach the same objective: use less space in your DB.

If you want to optimize storage you should not store UUIDs as varchar(32) (nor character(32)).

You can store them as binary(16) with no loss of information, and save (at least) 50% of space. A UUID contains 128 bits of information. If stored as a text representing the hexadecimal value, you use 4 bits of information out of every character, which employs 8 bits assuming the text is encoded as ASCII or latin1. So, you waste half your available bits. Storing the information in binary format, you waste none.

My response is heavily inspired in Storing UUID Values in MySQL Tables, which I would recommend you read.

The following example shows you how to store the same UUID data in the two different formats:

 CREATE TABLE t
 (
      uuid_a character(32) PRIMARY KEY
     ,uuid_b binary(16) UNIQUE KEY
 ) ;

Both uuid_a and uuid_b will be used to store the same UUID, encoded in two different ways.

Assuming we have a table called generator_64k with 64k of data... we fill t with lots of uuid() using:

 -- We fill it with random data
 INSERT INTO
     t
 SELECT
      replace(u,'-','')             -- text version
     ,unhex(replace(u,'-',''))      -- binary version
 FROM
     (SELECT
         uuid() AS u
      FROM
         generator_64k
     ) AS s0 ;

... and we add also a well known value:

 -- We fill it with one known piece of data
 INSERT INTO
     t
 SELECT
      replace(u,'-','')             -- text version
     ,unhex(replace(u,'-',''))      -- binary version
 FROM
     (SELECT
         'aab5d5fd-70c1-11e5-a4fb-b026b977eb28' AS u
     ) AS s0 ;

You can then query the table and look for the known value using either of the representations:

 -- Encoded as character(32)
 SELECT
     uuid_a, hex(uuid_b)
 FROM
     t
 WHERE
     uuid_a = replace('aab5d5fd-70c1-11e5-a4fb-b026b977eb28', '-', '') ;

 -- Encoded as binary(16)
 SELECT
     uuid_a, hex(uuid_b)
 FROM
     t
 WHERE
     uuid_b = unhex(replace('aab5d5fd-70c1-11e5-a4fb-b026b977eb28', '-', '')) ;

You get, in both cases, the same result:

 uuid_a                          | hex(uuid_b)
 :------------------------------- | :-------------------------------
 aab5d5fd70c111e5a4fbb026b977eb28 | AAB5D5FD70C111E5A4FBB026B977EB28

In practice, you would only use uuid_b in your table.

By doing this, you halve the amount of space used to store your UUIDs (both in table and indexes), without having to resort to using a substring in your index (which can affect your performance, and still not save that much).

See all the code at dbfiddle here


ODBC Curtime() after migration from 2000 to 2012 omits datepart A client migrated a db from SQL Server 2000 to 2012.

Their application runs into trouble afterwards. The cause is the following function invocation:

SELECT {fn curtime() }

Which returned date + time on SQL 2000 ('2017-01-20 07:24:26.997'), but only returns the time part ('07:24:26.997') on SQL 2012.

The compatibility mode cannot be set back further then 90 (SQL 2005).

Given that the application cannot be changed, is there any alternative solution for this issue?

The short answer is there is no way to fix this without changing the app.

That {fn curtime() } is and ODBC Scalar function that SQL Server does interpret, but really shouldn't be used natively.

Use GetDate() instead.

(I suppose you could use {fn curdate() } combined with {fn curtime() } )

I haven't got a SQL 2000 install anymore (that was 17 years ago!), so can't test what the {fn curtime() } was like then but I would've assumed that it only ever returned the time. Maybe not, but it doesn't make much difference now - you're a bit stuck.

The only option is to change the application. The justification shouldn't be too hard - an app that only works on SQL Server 2000 is not really acceptable, that has been out of support for a long while - it's a 17 year old system.

This earlier post is similar and the answer is a good one and gives some more information.

This is not relevant really but I'll add it (you can just ignore this bit):

curtime() is a builtin function in MySql. Your case is definitely ODBC syntax, but it's similar to the MySql one so thought I'd add it as trivia.


Automating the service restart from another server in Windows server 2016 I want to restart the services on Application server from DB server depending on the output of query.
Ex: In DB server i have written one procedure which will generate automated mail if no transaction inserted in one table.
If no transaction inserted from last 3 minutes , i want to restart the dependent service which is on another app server

Although you could restart the remote application service from T-SQL using a command invoked via xp_cmdshell, I suggest this be done with a PowerShell command instead.
The PowerShell script below calls a proc and restarts a service when the return code is 1, indicating no transactions were processed. This script can be included in a SQL Server Agent PowerShell job step running under an account with permissions needed to execute the proc and restart the remote service.
try {
    $connection = New-Object System.Data.SqlClient.SqlConnection("Integrated Security=SSPI")
    $command = New-Object System.Data.SqlClient.SqlCommand("YourDatabase.dbo.usp_CheckNoTransactions", $connection)
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    $returnValueParamter = $command.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]::Int)
    $returnValueParamter.Direction = [System.Data.ParameterDirection]::ReturnValue
    $connection.Open()
    [void]$command.ExecuteNonQuery()
    $connection.Close()
    if($returnValueParamter.Value -eq 1) {
        Write-Output "No transactions processed - restarting application service..."
        Get-Service -ComputerName "YourApplicationServer" -Name "YourAppServiceName" | Restart-Service
    }
}
catch {
    throw
}

Sample proc:
CREATE PROC dbo.usp_CheckNoTransactions
AS
IF NOT EXISTS(SELECT 1 FROM dbo.Transactions WHERE CreateTimestamp > DATEADD(minute, -3, GETDATE()))
BEGIN
    --application has stalled - send email and return 1 to indicate a service restart is needed
    EXEC msdb.dbo.sp_send_dbmail ...
    RETURN 1;
END;
GO


Is SQL Server 2012 Management Studio "Express" any different? Are there any differences from the version of SSMS that comes as part of the SQL Server 2012 Enterprise Installer and the SQL Server 2012 Express Management Studio?

In the RTM release of SQL Server 2012 (and in previous versions), yes, they are quite different. The Express version was missing a bunch of functionality, most notably the ability to manage SQL Server Agent (even on non-Express instances). There are other things missing like Profiler but technically that is not part of Management Studio itself, just part of the suite of client tools.

Starting with SQL Server 2012 SP1, however, Management Studio Express now has full parity in terms of feature set. Essentially this means that full Management Studio functionality no longer requires a license. You can download the more recent version, SP2, here:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

You want the file SQLManagementStudio_x64_ENU.exe or, if you're really old school, SQLManagementStudio_x86_ENU.exe.

As with previous versions, to get Management Studio Express, you'll need either Express with Tools, Express with Tools and Advanced Services, or stand-alone Management Studio Express.

I don't have a system handy without Management Studio proper to test this on, but they've blogged about this on the Internet (it was first announced with CTP4 back in September), so it must be true:

http://blogs.msdn.com/b/sqlreleaseservices/archive/2012/09/20/sql-server-2012-sp1-ctp4-now-available.aspx

I'm going to spin up a VM and install it and come back here and enumerate any differences I find. Unfortunately this is one of those cases where things have changed and pointing to a "duplicate" question doesn't help, since it deals with an older version...


A query for merging date intervals I have a simple table with 3 columns:

(cid, from, to) -> (int, datetime, datetime)

The difference between dateFrom and dateTo should be >= 1 month. And unfortunately i have some corrupt data which do not satisfy this condition.

INPUT:

An example data (cid - from - to) :
8 - 01.05.2012 - 01.09.2012
8 - 02.09.2012 - 10.09.2012
8 - 11.09.2012 - 18.09.2012
8 - 18.09.2012 - 02.10.2012
8 - 03.10.2012 - 04.10.2013

DESIRED OUTPUT in a temp table:

I want to merge the dates whose interval is less than a month into 1 row:

8 - 01.05.2012 - 01.09.2012
8 - 02.09.2012 - 02.10.2012
8 - 03.10.2012 - 04.10.2013

So everything between 02.09.2012 - 02.10.2012 is merged into one row.

Is this possible using only T-SQL? (even without a cursor)

I think what you'll need to do is create another temp table that has the same columns but with an identity column so you can use it for joining purposes.  I also noticed that your date format had day.month.year which threw me off initially....

This will get you pretty close:

create table newtable ( cid int, fromdate datetime, todate datetime)
go

insert into dbo.newtable
values
(8 , '05/01/2012', '09/01/2012'),
(8 , '09/02/2012', '09/10/2012'),
(8 , '09/11/2012', '09/18/2012'),
(8 , '09/18/2012', '10/02/2012'),
(8 , '10/03/2012', '10/04/2013')

create table dbo.newtable2 (newIdent int identity(1,1), cid int, fromdate datetime, todate datetime, targetdate datetime)

insert into dbo.newtable2 (cid, fromdate, todate, targetdate)
select cid, fromdate, todate, dateadd(m, 1, fromdate) from dbo.newtable

select cid, fromdate, todate from newtable2 where todate > targetdate

union

select cid, min(fromdate), max(todate) from newtable2 where newident in
(
select a.newident
from newtable2 a inner join newtable2 b on dateadd(m, 1, a.fromdate) > b.fromdate and a.newident = b.newident
where
a.todate <= a.targetdate
order by a.fromdate
)
group by cid

The nested select is sorted by fromdate so you will get merged records in consecutive rows so that you can leverage MIN and MAX functions.

The only thing is the UNION will not have the rows sorted but you can always dump that into another temp table and then sort it.


When do I use another type index than integer? (eg string) [integer vs string index] I was reading about relational databases like sqlite and I always see the schema for user storage like:

UserID (index) | Username | Password

but wouldn't it be actually much faster to use Username as an index?

For example when a user logs in you query WHERE username == some_name, the database engine has to search through all record for this data, right? (namely SQLite? - I am using this)

Why do I always see this "ID" and not just username as the index?

Assuming all names will be unique, what is the point in an integer based index?

What is the actual difference in terms of lookup speed? Or space? (assuming a maximum character count at 24 characters).

Or would it be even better to just make a additional/separate lookup table for username<->userid resolving and then load the data with userid from the main table where the index is the userid?

There are a lot of places internally where it is possible to reference data using a user id, which is better than a string.  For example, if you had 4 tables referencing the user table, they'd each have a string column which is less efficient than 4 integer indexed columns.  This is the reason why most schemas use this layout; you're more likely to want to reference a user than to refer to the user by name.

However, you can also set username to be an index, which you might want to do if you are looking it up a lot.

To answer your edited question, a 24 character text field will require roughly 24 bytes.  Depending on whether it's a varchar or a char it will be stored differently, but the full amount will be needed for searching, so let's consider them both to be 24.

An integer field will require 4 bytes.

You can see how this will add up over time.  For comparisons, assume a linear time comparison, meaning 24 operations to compare two a string field, while only 4 operations to compare an integer field.  In practice, they will probably take roughly the same amount of time, though the string comparison cannot be faster.

If you want to search by username, create a table with the schema of username | user_id.  That'll let you search by username and join with the other data you are looking for while still being efficient.


Single or multi tenancy database recommendations I'm really confused searching for a database(nosql or mysql) that can handle unlimited multi or single tenancy with good scaling method either by software or physical resources. So i can differentiate each customers by namespace or any form. I was thinking couchbase before but just to find out that couchbase UPDATE fields is a big mess.

Please i need recommendation that can fit my needs!

There are diverging views on this (thorny) topic within the database world!

However, the consensus (as best as I can "take the pulse" of the database community is, if at all possible, to use separate databases!

If one app goes down, at least the others will still work, with a
central database, if one app brings the server down, you're bunched
(nothing will work).
Also, for migrations, if you need to, say, migrate one of your apps
(size/speed/whatever problems - or you wish to dip your toe into the
cloud testing with one app), then you will have difficulty separating
out your apps, whereas if you use separate systems, you won't have
any difficulties on that score!

In 10 years time, when you tell people that you have software on-prem(-ises), you'll get strange looks and you'll be taken for a Luddite (think generating your own electricity in the basement which did happen before the rise of electricity companies, esp. in US cities) so best be prepared for the cloud ahead of time.

You mention NoSQL or MySQL - my strong advice is to go with PostgreSQL if at all possible - it is vastly superior to MySQL on a large number of levels and NoSQL is a bandwagon whose time has come and gone - all the major vendors are scrabbling to put SQL interfaces and ACID semantics on their products.

Take the time to look here at my answers and the other posts and links in the threads there to get some "feel" for the area.

HTH (oh, and did I mention that you should give PostgreSQL careful consideration? :-) ). I'm not saying that my suggestion above is the definitive answer for all scenarios - I just believe that it is (in the majority of cases) the go-to solution and, unless you have a compelling reason to the contrary, you should go with multiple separate databases. Modern servers can cope with thousands of schemas - and if you have 1000's of clients, maybe you can afford more than 1 machine? :-)


Unexpected query execution plan on MySQL 8 when using Datetime range I have a MySQL 8 database with about 900 millions of rows. When I run the following query the response is very slow because table B doesn't seem to be using the right index:

SELECT  B.name

FROM A JOIN B ON A.key1 = B.key1 AND A.key2 = B.key2
WHERE
    A.active = True
    AND B.datetime  > '2020-05-28 00:00:00' AND B.datetime  < '2020-05-29 00:00:00'
    AND A.type = 1

The query has the following execution plan (EXPLAIN FORMAT=JSON):

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10202799.44"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "A",
          "access_type": "ref",
          "possible_keys": [
            "ix_A_type_key1",
          ],
          "key": "ix_A_type_active",
          "used_key_parts": [
            "type",
            "active"
          ],
          "key_length": "5",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 62738,
          "rows_produced_per_join": 62738,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "62738.00",
            "eval_cost": "6273.80",
            "prefix_cost": "69011.80",
            "data_read_per_join": "1G"
          },
          "used_columns": [
            "active",
            "type",
            "key1",
            "key2"
          ]
        }
      },
      {
        "table": {
          "table_name": "B",
          "access_type": "ref",
          "possible_keys": [
            "ix_B_key1_datetime",
            "ix_B_key2_datetime"
          ],
          "key": "ix_B_key2_datetime",
          "used_key_parts": [
            "key2"
          ],
          "key_length": "258",
          "ref": [
            "A.key2"
          ],
          "rows_examined_per_scan": 147,
          "rows_produced_per_join": 3136,
          "filtered": "0.03",
          "cost_info": {
            "read_cost": "9211170.19",
            "eval_cost": "313.69",
            "prefix_cost": "10202799.44",
            "data_read_per_join": "96M"
          },
          "used_columns": [
            "key1",
            "key2",
            "datetime",
            "name",
          ],
          "attached_condition": "((`B`.`key1` = `A`.`key1`) and (`B`.`datetime` > TIMESTAMP'2020-05-28 00:00:00') and (`B`.`datetime` < TIMESTAMP'2020-05-29 00:00:00'))"
        }
      }
    ]
  }
}

Table B In the used_key_parts it's only using the column key2 instead of the full composite index key2 + datetime (ix_B_key2_datetime). Why is not using the datetime part of the composite index? Is there a way to speed up this query?

For a closer analysis we would need the table definitions for both tables and indexes as well as the row counts, but I think the problem is that you use key2 for joining and mysql can then not use it for filtering of the datetime (since the first part of the index, the key2, is different for the rows in the join result).

Can you try to create an index on datetime alone? Maybe optimize the query like this afterwards:

select sub.name
from A join
(select name,key1,key2 from B where B.datetime between '2020-05-28 00:00:00' AND '2020-05-29 00:00:00') sub
on A.key1 = sub.key1 and A.key2 = sub.key2
where A.active = True
and A.type = 1;

This way it could use the most restrictive filtering first (which I assume will be the datetime column on table B) and join the few rows it has with the many in table A.


Can I upload database backup made through cPanel into phpmyadmin? I have been using mybb forum software. As, I use shared hosting so they had provided me cPanel. Through it I had make a backup which is a .gzip file.

Now, my query here is that I want to upload it to another server to do some experiments with my forum. Can I upload the file through phpmyadmin? As my server has no cPanel installed. Will it work?

I've just checked and yes, the gzipped file is a standard mysql dump, so you can import it from PhpMyAdmin (given that the MySQL versions are compatible, of course).

The backup doesn't contain the CREATE SCHEMA statement, so you have to create the db first, and then import the backup into it.


Automatic Reboot after installing SQL Server 2012? I'm being paranoid here I'm sure. I'm at the end of an SQL Server 2012 install and this message appears.  I know it says that I must restart the computer but I've had instances where installer automatically restarted the server when I didn't want it to. I cannot reboot this production server right now.  Am I safe to click OK to complete the installation?

You can safely click OK and it won't reboot it.  The instance will even be up, online and accessible without the reboot.

It's always good practice to reboot after a fresh installation or patches as when you try to perform another patch or installation down the road, the installer will more than likely fail of the 'Restart Required' portion of the checks.


-E startup option and SSDs Has anybody seen evidence of the effect of -E when using SSDs?

The effect on 'spinning rust' drives is not disputed - but SSDs aren't really flustered by random I/O. I wonder if the -E option might even hurt performance.

On servers with a mix of drives (SSD SAN, PCI SSDs and traditional SAN) SQL Server has to decide on startup whether to use -E or not. I have some empirical evidence that the option might be detrimental to performance, but I'd like other people's feedback before I consider taking it off.

My setup uses standard 64K RAID stripes, and the NTFS cluster size is also 64K.

The following articles explore the advantages and disadvantages of the -E start-up option:

Focus on Fast Track : Understanding the –E Startup Parameter

Summary:

-E changes the proportional fill algorithm. Rather than moving on to the next file after a single extent allocation, the movement occurs after 64 extents have been allocated.

These extent allocations do not need to come from a single table. For example, when loading two tables in parallel with –E enabled, both tables consume the 64 extents in the single file before moving on to the next file. -E does not alter the fact that SQL Server allocates one extent at a time.

The purpose of –E is to enable large-I/O read-ahead.  If two tables that share the same files/filegroup are loaded in parallel then the advantage of –E is removed. The extents will be fragmented resulting in small-I/O read-ahead.

-E was originally put into the code to allow index builds and rebuilds to use 256KB of each file before round-robining, and so get better sequential I/O for large data warehouses. It was originally a 'benchmark special' and had very little testing, it was only documented because it was used during benchmarks (and so had to be documented).

Microsoft White Paper (pdf)

Community Wiki answer generated from a comment on the question by @Kin


Is it possible to determine mysql pre-allocated memory footprint At startup, mysql will use some memory.

Some of the memory is pre-allocated for "key_buffer" and "innodb_buffer_pool_size" ( and other things ).

Is it possible to determine how much memory is pre-allocated ( for data ) and how much memory has been used ?

Thanks

UPDATE:

Let me take an example to explain what I want to ask.
After I start mysql server, it use some memory as part of "key buffer". Say, I set key_buffer=1G. Maybe at startup, mysql server has pre-allocated 200M as "key buffer". Then when users begin to do queries against this server. The server can only get extra 800M as "key buffer".
200 is a random number I pick. And apparently besides key_buffer, there are other things ( such as innnodb buffer )  What I want to know is the exact size or the way to calculate it.

Hope I've explained myself clearly.

Yes, and you absolutely should! Don't set MySQL to have a potential maximum use more than about 80% of available memory.

MySQL has two types of buffers, global and per-connection. Maximum possible memory use is: global buffers + (connection buffers * max connections).

The mysqltuner.pl script will calculate this for you (http://mysqltuner.com/). Here are the values this script uses for calculating:

Global buffers:

key_buffer_size
max_tmp_table_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
query_cache_size

Per-connection buffers:

read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
join_buffer_size

You can find how much memory is currently used from your Operating System.


SELECT INTO OUTFILE vs INSERT INTO ... SELECT During my cursory research I have not been able to find a definitive answer on the benefits that SELECT INTO OUTFILE provides over INSERT INTO ... SELECT. Upon reading the docs related to INSERT INTO ... SELECT concerning locks on InnoDB tables it states:

  sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED or innodb_locks_unsafe_for_binlog is enabled, and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S.

To avoid the lock with INSERT INTO ... SELECT it seems that I must ensure the isolation level is READ COMMITTED to avoid locks on the source table during the query.

However, I have not been able to find any authoritative answer regarding locks and using SELECT INTO OUTFILE, not even the MySQL docs reference lock info.

My goal is to avoid locking the source table while the query runs to avoid connection stacking.

You should use SELECT ... LOCK IN SHARE MODE. Why ?

  SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

In your case, you could attempt this

START TRANSACTION;
SELECT ... LOCK IN SHARE MODE;
SELECT ... INTO OUTFILE;
ROLLBACK;

This would do two SELECT queries

First SELECT to lock the rows in the table you wish
Second SELECT to perform SELECT ... INTO OUTFILE

Personally, I do not think you have to be this heavy-handed. Transaction isolation should be smart enough to pull off this atomic SELECT and use the same rows for the INSERT. I know I said should be which is why your are asking you question in the first place.

Whether you do SELECT ... INTO OUTFILE as one command or in the heavy-handed manner I am proposing, the row data of the source table will be fully readable.

GIVE IT A TRY !!!

UPDATE 2014-12-10 15:12 EST

Your comment

  Thx for the answer, and it does help, but the main point of the OP was to determine if there is a benefit for using SELECT INTO OUTFILE over INSERT INTO ... SELECT?

They are operationally different

SELECT INTO OUTFILE creates a text file
INSERT INTO SELECT loads one table from the results of the SELECT

UPDATE 2014-12-11 12:21 EST

The only thing I can think of in this context is the point-in-time of the data and when you are using it. With both types of operations, there will some implicit shared locking.

With SELECT INTO OUTFILE, you are preparing a result and saving it externally. Loading that data into a table using LOAD DATA INFILE will not involve any shared locking during the load process. Keep in mind that SELECT INTO OUTFILE will incur disk I/O and still impose some caching along the way.

With INSERT INTO SELECT, the shared locks would probably have to live longer in InnoDB because you are locking rows and using those same rows to INSERT into another table.

Therefore, if I was looking for a performance bonus, I would have give the edge to INSERT INTO SELECT because you are doing the same amount of shared row locking, The disk I/O for the single operation would have to be less than the separate SELECT INTO OUTFILE and subsequent LOAD DATA INFILE. Of course, you would have to compare the two methods against your dataset. What could a performance bonus for one dataset might be a performance cost for another dataset.

UPDATE 2014-12-17 00:00 EST

Your Comment

  I wasn't notified that you updated your answer, so I did bounty assuming it wasn't. Your explanation does make sense, in theory, however I am looking for a more authoritative response, in hopes that the overhead of the separate file (as you correctly mentioned) may be worth the trade-off in complexity for improved performance.

The only authoritative response would to come from the MySQL Documentation.

First, what does the MySQL Documentation LOAD DATA INFILE say ?

  The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See Section 13.2.9.1, “SELECT ... INTO Syntax”.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE.

Two paragraphs later, it says

  For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section 8.2.2.1, “Speed of INSERT Statements”.

When you look at Speed of INSERT Statements, it says this:

  To optimize insert speed, combine many small operations into a single
  large operation. Ideally, you make a single connection, send the data
  for many new rows at once, and delay all index updates and consistency
  checking until the very end.

  The time required for inserting a row is determined by the following
  factors, where the numbers indicate approximate proportions:

  Connecting: (3)

  Sending query to server: (2)

  Parsing query: (2)

  Inserting row: (1 × size of row)

  Inserting indexes: (1 × number of indexes)

  Closing: (1)

  This does not take into consideration the initial overhead to open
  tables, which is done once for each concurrently running query.

  The size of the table slows down the insertion of indexes by log N,
  assuming B-tree indexes.

  You can use the following methods to speed up inserts:

  If you are inserting many rows from the same client at the same time,
  use INSERT statements with multiple VALUES lists to insert several
  rows at a time. This is considerably faster (many times faster in some
  cases) than using separate single-row INSERT statements. If you are
  adding data to a nonempty table, you can tune the
  bulk_insert_buffer_size variable to make data insertion even faster.
  See Section 5.1.4, “Server System Variables”.

  When loading a table from a text file, use LOAD DATA INFILE. This is
  usually 20 times faster than using INSERT statements. See Section
  13.2.6, “LOAD DATA INFILE Syntax”.

  Take advantage of the fact that columns have default values. Insert
  values explicitly only when the value to be inserted differs from the
  default. This reduces the parsing that MySQL must do and improves the
  insert speed.

  See Section 8.5.4, “Bulk Data Loading for InnoDB Tables” for tips
  specific to InnoDB tables.

  See Section 8.6.2, “Bulk Data Loading for MyISAM Tables” for tips
  specific to MyISAM tables.

Things start to look a little nebulous at this point because you must tune the load process in terms of the storage engine. MyISAM is rather straight forward in this statement because the bulk insert buffer is for MyISAM only and LOAD DATA INFILE will harness the bulk insert buffer. InnoDB will not.

Take a look at this Pictorial Representation of InnoDB (Percona CTO Vadim Tchachenko)

There are other considerations to tweak options but LOAD DATA INFILE will literally slam everything into the InnoDB Buffer Pool, funneling changes through the Log Buffer, Double Write Buffer, Insert Buffer (if the target table has nonunique indexes), Redo Logs (ib_logfile0,ib_logfile1), and the Physical File of the Table. This is where LOAD DATA INFILE's benefits have to get nullified.

I wrote about this

Feb 06, 2012 : LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?
Apr 20, 2012 : Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
Jul 13, 2012 : Mysql load from infile stuck waiting on hard drive
Jan 11, 2013 : MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

EPILOGUE

As I already said in my previous update to this answer

  Therefore, if I was looking for a performance bonus, I would have give the edge to INSERT INTO SELECT because you are doing the same amount of shared row locking, The disk I/O for the single operation would have to be less than the separate SELECT INTO OUTFILE and subsequent LOAD DATA INFILE. Of course, you would have to compare the two methods against your dataset. What could a performance bonus for one dataset might be a performance cost for another dataset.

Basically, you will have to test SELECT INTO OUTFILE/LOAD DATA INFILE against INSERT INTO SELECT. It may be 6 of one, half a dozen of the other for one dataset, and a landside victory for another dataset.

All being said from the MySQL Docs and my past posts, I still give the edge to INSERT INTO SELECT. You will just have to test the two methods.


Buffer pool above 90% utilized causes MySQL to crash Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner?

EDIT

I'm using Rolando's script to identify how full the innodb_buffer_pool is.

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Thanks

First, take a look at the InnoDB Architecture

Look closely at the Buffer Pool in the upper left corner.

There is an Insert Buffer inside it. It is responsible for migrating changes to nonunique indexes from the Buffer Pool to the Insert Buffer inside the system tablespace (better known to us commoners as ibdata1).

When InnoDB tables have lots of secondary indexes, heavy INSERTs, UPDATEs, and DELETEs to those tables will trigger growth and congestion in the Buffer Pool's Insert Buffer.

The setting innodb_change_buffer_max_size governs what percentage of the Buffer Pool is to be use for the Insert Buffer. The default is 25. That means if you allocate 24G for innodb_buffer_pool_size, don't be shocked if the Insert Buffer uses 6G of it.

When a Buffer Pool is 90% full of data, it's only good for doing heavy SELECTs (when doing reports and analytics). In your particular case, since you ran that query and determined that 90% of the Buffer Pool had data, that leaves only 10% for the Insert Buffer and other InnoDB incidentals. It is entirely possible that a loading of a mysqldump could push the limits of a Buffer Pool that has an insufficient amount of room for its Insert Buffer. Personally, I have never heard anyone complain about InnoDB crashing MySQL because of this.

For the sake of erring on the side of caution, perhaps you may want to try setting innodb_change_buffer_max_size to 50 when doing heavy INSERTs with

SET GLOBAL innodb_change_buffer_max_size = 50;

and scaling it back to 25 with

SET GLOBAL innodb_change_buffer_max_size = 25;

afterwards.

Incidentally, you could set it with

SET GLOBAL innodb_change_buffer_max_size = 10;

If you are not doing any heavy INSERTs for days or weeks. Just remember to set it back to 25.


The primary key issue with slave The slave is not working due to primary key error as shown below:

      Last_SQL_Errno: 1062
       Last_SQL_Error: Error 'Duplicate entry '550696860' for key 'PRIMARY'' on query.
 Default database: 'vserv'.
 Query:
  'INSERT INTO ox_data_intermediate_ad
        (date_time, ad_id, zone_id, clicks, impressions,
         requests, operation_interval, operation_interval_id,
         interval_start, interval_end, creative_id, updated)
    SELECT
        date_time, ad_id, zone_id, SUM(clicks) AS clicks,
        SUM(impressions) AS impressions, SUM(requests) AS requests,
        5 AS operation_interval, 1871 AS operation_interval_id,
        '2012-05-26 11:55:00' AS interval_start,
        '2012-05-26 11:59:59' AS interval_end,
        0 AS creative_id, '2012-05-26 12:04:01' AS updated
    FROM
        (
        SELECT
            interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id,
            count AS clicks, 0 AS impressions, 0 AS requests
        FROM
            ox_data_bkt_c
        WHERE
            interval_start >= '2012-05-26 11:55:00'
            AN

The query mentioned does not try to insert the auto-incremented column  data_intermediate_ad_id. It expects mysql to automatically add the next ID. When I checked the slave, I found a record for primary key 550696860.

Master is 5.0 and slave is 5.5.24

The second slave with version 5.5.21 is working fine. Is this a bug related to 5.5.24 version?

The query that has an issue is as follows:

INSERT INTO
                ox_data_intermediate_ad
                (date_time, ad_id, zone_id, clicks, impressions, requests, operation_interval, operation_interval_id, interval_start, interval_end, creative_id, updated)
            SELECT
                date_time, ad_id, zone_id, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(requests) AS requests, 5 AS operation_interval, 1591 AS operation_interval_id, '2012-05-25 12:35:00' AS interval_start, '2012-05-25 12:39:59' AS interval_end, 0 AS creative_id, '2012-05-25 12:43:01' AS updated
            FROM
                (
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, count AS clicks, 0 AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_c
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, count AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_m
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, 0 AS impressions, count AS requests
                FROM
                    ox_data_bkt_r
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59'
                ) AS virtual_table
            GROUP BY
                date_time, ad_id, zone_id

I will like to know why does the slave has an issue with the insert into ... select statement.

Duplicate key error should never happen.  When it does, then probably

The Master and Slave were not in sync (perhaps for a long time)
You are writing to the Slave.

Never write to a Slave.  Recommend setting readonly=ON on the Slave.  This will (mostly) prevent accidental writes.  However, user 'root' (or any SUPER user) will bypass the readonly check.

As Aaron mentioned, percona.com has a tool for checking consistency between Master and Slave.  They have another tool for syncing them.


(macOS/Docker) "Cannot bulk load. The file path does not exist or you don't have file access rights." I am running SQL Server from a Docker container on macOS. All queries except for the loading of the csv file is getting executed. The column names etc are correct. Any ideas?

error:

  Cannot bulk load. The file "/Users/me/snp500-company-by-sector.csv" does not exist or you don't have file access rights.

bulk insert CompanyBySector
from '/Users/me/snp500-company-by-sector.csv'
with
(
    firstrow = 2,
    fieldterminator = ';',
    rowterminator = '\n',
    tablock
)
go

For anyone else who is new to running SQL Server in a macOS Docker container, you first need to find out your Docker container ID. The commands will be like this:

List Docker info:

$ docker container ls
47940489ee07

Copy from the macOS directory to the Docker container directory:

$ docker cp /Users/Trump/snp500-companies.csv 47940489ee07:/snp500-company-by-sector.csv

https://docs.docker.com/docker-for-mac/


Full Outer Join in MS Access I have two staff lists:

List A:

StaffID   Supervisor
====================
0001234         NULL
0001235      0001234
0001237      0001234
0001239      0001237

and

List B:

StaffID   Supervisor
====================
0001234         NULL
0001235      0001234
0001238      0001235
0001239      0001235

And I need the following output:

StaffID   SupervisorA   SupervisorB
===================================
0001234          NULL          NULL
0001235       0001234       0001234
0001237       0001234          NULL
0001238          NULL       0001235
0001239       0001237       0001235

Notice that the staff IDs from List A and List B have been merged into a single, non-repeating list, and that I have merged the two supervisor details which may not match between the two lists.

Query doesn't have to be nice. I have ~8000 records in each list and I will run it nice. I'm happy to do some minor Excel manipulation afterwards if necessary.

I wanted to do a full join, but the Access query designer properties window on the join allows me to choose ALL from table A and matching from table B, ALL from table B and matching from table A, or only those which match in both A and B.

I'm sure this is very simple to do, but I very rarely use MS Access.

I have two queries so far which don't give me what I want. I wanted to combine them in the full outer join, since Access won't give me an option, but not sure how:

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID;

and

SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID;

The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g.

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID
UNION
SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID


Possible to have duplicate fully qualified schema names with separate data in DB2 for z/OS? We have several application teams that are looking to do a re-write of large legacy applications.

This is mostly COBOL going against DB2 for z/OS (v11).

The COBOL code was generated using a tool, so we don't have any access to the source. Let's say the SQL cannot be changed for the sake of this question.

They would like environments (dev/test/perf) where they can copy the existing data and test the re-written application (still using the same SQL) while still having a team supporting the legacy system through dev->test->perf->prod.

Current SQL example:

SELECT FOO FROM MYAPP.BAR;

Because the table name was fully qualified with "MYAPP" instead of supplying it later, they cannot simply copy the code that they already have within the same DB2 and just change the schema name. They would like entirely new DB2 subsystems to avoid this issue.

Is there any way to avoid this? Are there any bind parameters or other strategies I could employ to have duplicate schema names running on the same DB2 subsystem?

If the application is hard-coded with the qualifier, then it can only use that qualifier and SCHEMA.table-name is unique.

The best approach is rewrite the application to use unqualified table names.  If you do end up considering creating additional subsystems, you might consider zD&T.  This is z/OS running on Linux on x86 for dev/test environments.  https://developer.ibm.com/mainframe/products/z-systems-development-test-environment/


Mariadb database with more than 50 million rows I recently designed a database to analyze data using the clickhouse database as an engine.

Everything works perfect and the performance of the database is very good considering that the server is more or less good and with almost 40 million records.

Up to this point everything is fine, but now I need to migrate to a mariadb database in order to use the data in conjunction with the glpi database.

I've been investigating and I can't find anything that answers the following questions:

Is mariadb able to handle this amount of data, knowing that over
time we can reach more than 100 million?
Using mariadb galera cluster could you get a better performance?

I appreciate any suggestions or options you can give me.

  I'm sorry if you don't understand something, my English is not very good.

Thank you so much.

I'm working on a fairly simple 140mm record table in MariaDB installation (granted it's a fairly underpowered machine)... it crawls. Hardware doesn't seem relevant since CPU nor memory is close to its limit.


PIVOT with SQL Server I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column)

I want to PIVOT that query result so that it uses the latest 'ReceivedTime' for those same filenames. And the Outcome values should be split into separate columns - with a COUNT of each occurrence.

Here is my code so far. However, this returns multiple rows rather than grouping by filename.
What do I need to change?

DECLARE @TmpTable AS TABLE
(
    Filename varchar(80),
    ReceivedTime datetime,
    Outcome int NULL
);
INSERT INTO @TmpTable
VALUES
    ('FileABC','2018/06/29 03:34', 1),
    ('FileABC','2018/06/28 11:01', 3),
    ('FileABC','2018/06/28 09:30', 2),
    ('FileABC','2018/06/28 05:23', NULL),
    ('FileABC','2018/06/28 16:21', 3);

SELECT
     Filename as 'LatestReceivedTime'
    ,[-1] AS 'NotProcessing'
    ,[0] AS 'InProgress'
    ,[2] AS 'Warning'
    ,[3] AS 'Failed'
    ,[1] AS 'Success'
FROM
(
    SELECT x.Filename, x.ReceivedTime, COALESCE(x.Outcome, -1) AS 'Outcome'
    FROM @TmpTable x

) x
PIVOT
(
COUNT(Outcome)
FOR Outcome IN ([-1], [0], [1], [2], [3], [4])
) AS PivotTable;

You need to add some aggregation:

SELECT
     [Filename]
    ,MAX(ReceivedTime) as 'LatestReceivedTime'
    ,SUM([-1]) AS 'NotProcessing'
    ,SUM([0]) AS 'InProgress'
    ,SUM([2]) AS 'Warning'
    ,SUM([3]) AS 'Failed'
    ,SUM([1]) AS 'Success'
FROM
(
    SELECT x.Filename, x.ReceivedTime, COALESCE(x.Outcome, -1) AS 'Outcome'
    FROM @TmpTable x

) x
PIVOT
(
COUNT(Outcome)
FOR Outcome IN ([-1], [0], [1], [2], [3], [4])
) AS PivotTable

GROUP BY [Filename];


Are JOINs on non-indexed columns faster than sub-queries? I know JOIN on indexed columns are faster than sub-queries in a query result.

I am confused if : JOINs on non-indexed columns are also faster than sub-queries or not.

In a query such as this:

SELECT *
FROM
  some_table
  JOIN (SELECT foo, col1 FROM bar) s_sub
  ON (some_table.col1 = s_sub.col1)

MySQL is able to use an index on some_table.col1. So that it starts with the derived table (full scan), then moves on to some_table using index.

This means it can utilize an index. So a subquery does not altogether negates use of an index.

If no index exists on any relevant column, obviously hard work is done (full scan, join buffer) to make the JOIN.


Bulk exporting SQL Server 7 data and bulk importing it to SQL Server 2008 R2 Like the title suggests, I want to bulk export data within an sql server 7.0 database into a file and then import it to an sql server 2008 r2 database, I did the following, I first typed this command on the windows 2000 shell:

C:> bcp sticom.dbo.societe format nul -c -f C:\test.fmt -t, -T

it was executed successfully, then I copied the C:\test.fmt in a network shared folder so I can access it from the other windows 7 system, I then copied on the desktop and tried to execute this command on the command prompt:

C:\ bcp sticom.dbo.societe in C:\test.bcp -f C:\test.fmt  -T

and I got this error:

  SQLState = 08001, NativeError = 2   Error = [Microsoft][SQL Server
  Native Client 10.0][SQL Native Client]An error has occurred while
  establishing a connection to the server. When connecting to SQL Server
  2005, this failure may be caused by the fact that under the default
  settings SQL Server does not allow remote connections. SQLState =
  S1T00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login
  timeout expired

I checked my connection, I checked the services and the paths, everything was in place

The BCP utility attempts to connect to the default instance if you don't specify the -S parameter.  My guess is there is no default instance available for the 'import' BCP.


Named and Default Instances on single SQL Cluster current environment we have Server A and Server B - WFCS with Default instance serving applications(SQL 2012) Now we want move a named instance (SQL 2008) to the above cluster.

What will be the consequences if I create a Named instance on the above existing setup. will the idea work ?

If yes,

do we need separate IP in the same VLAN for named instance and add it to cluster resources ? Also will fail-over happen as before ?

Suggestions pls

What will be the consequences if I create a Named instance on the above existing setup. will the idea work ?

It should work like any other instance. Lot of people have multiple instances of SQL FCI running peacefully.

  do we need separate IP in the same VLAN for named instance and add it to cluster resources ? Also will fail-over happen as before ?

Yes, you would need a separate IP, which will be used for virtual network name. You would also need separate storage for the new SQL FCI. They all can use same network though. Failover should  work just fine and manually you would have control to just failover the FCI you like.


To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run (as an apprentice DBA!) to maximize the performance of the database?

For instance, I executed EXEC sp_updatestats but it reported that no indexes required updating.

Is there a list of things to do once large amounts of data has been loaded or does SQL Server 2008 just take care of all that for you?

If you're loading to an empty database, you could/should take steps to avoid requiring any additional maintenance steps post load. Fragmentation is the enemy, that's what you're trying to avoid.

Drop all NC indexes before loading.
Post load, add the NC indexes for each table in sequence i.e. don't add an index to TableA, then TableB, then back to TableA. This is has no effect on fragmentation but it can improve the time taken to add the indexes on very large datasets (reduces buffer pool churn).
If you can insert data to a table in clustered index order, leave the clustered index in place. If you can't, dump the data into a heap and rebuild in to a clustered index post load.

Rather than copy and paste, I'll point you toward the very comprehensive list of references for ETL optimisation @Marian put together in an answer to a question I asked on BCP. Many of these will be equally applicable to your scenario.


How to do data migration between different schemas? My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data from the source (third party database), transforms it into our schema, and inserts into the target (our application database). Unfortunately there are multiple issues with this method:

Inefficient. Our current customer has a huge amount of data and the total time for migrating the data is nearly 6 days of processing time.
Customer Downtime. The data is only is current up to the time we begin the migration. This means the customer must cease use of their current system during this time, leaving us a limited amount of time to perform the migration, with little room for error.
Error prone. The customer's data is full of inconsistencies that need handling. We also mostly rewrite this application to fit each customer, as there is little overlap in the systems they use. This requires large amounts of developer and QA time.

This must be a fairly common problem, as companies often move between applications and need to migrate their current data. Some possible solutions I am looking into are:

Oracle Data Integrator.
SQL Server Integration Services
Informatica.

Our own web application is designed to use either Oracle or SQL Server, so I am open to using products used with both Oracle and SQL Server databases.

Is there a solution that would allow continuous replication of a customer's source data into our target database while transforming the data into our schema?

Did I understand this correctly that your customer has MSSQL Server?

Source Database MSSQL or other

You have two options:

Script your own replication
Use Oracle Golden Gate to stream the data to your Oracle Database. Golden Gate supports "zero downtime" migration.

Source Database Oracle

Additionally to the options mentioned in "Source Database MSSQL or other" you can do:

Use Streams, Basic or Advanced Replication Features (requires Enterprise Edition, Basic Replication is included in SE, SE1 and SE2)
Export/Import DataPump (this does not support continuous replication)
Import DataPump via database link
Transportable Tablespace (requires Enterprise Edition)

There are several other posibilites but I need more details to recommend you one method:

How big is the customer's database? Please use the view dba_tables to get this information. The size of the datafiles does not reflect the actual size of a database.
How fast can you copy 1GB from the customer's side to your side?
Do you want to convert and cleanup the data while transfering it to your side?
Which method did you use when it took 6 days?


Is there a way to determine if SQL Server queries are running in memory or going to disk? I came across a set of stored procedures in an application today that are called repeatedly within a long running process.  Within each procedure I found multiple different select statements, some within loops; not surprisingly, these routines as currently used take several minutes to run, when intuition would expect them to complete in a few seconds.

It seems fairly obvious that performance wasn't taken into consideration when these procedures were written, there are multiple instances of things that are just "not a good idea".

Processing each row when importing data is taking 300ms per row, so relatively small imports are taking several minutes to process.

However, the tables involved in the procedures are for the most part quite small.  I'm thinking, if all of these tables are fully resident in memory, perhaps there's not all that much that can be gained by rewriting any of this.

I'm trying to determine....for this obviously inefficient code, how much of a real affect is it having?  Is it worth fixing?

So the question is:
 - is there a way to determine what tables are entirely pinned in memory?
 - is there a way to turn on tracing in order to monitor nested stored procedures to find the particularly expensive portions?

Note: This is on SQL Server 2008 R2

You can use one of these two queries to see the total logical reads and the total physical reads.

SELECT  DB_NAME(st.dbid) Db,
        OBJECT_NAME(st.objectid, st.dbid) Prc,
        qs.execution_count,
        qs.total_logical_reads,
        qs.total_physical_reads,
        qs.statement_start_offset,
        qs.statement_end_offset,
        st.text
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st;

SELECT  DB_NAME(database_id) Db,
        OBJECT_NAME(object_id, database_id) Prc,
        execution_count,
        total_logical_reads,
        total_physical_reads
FROM    sys.dm_exec_procedure_stats ps;

The first one breaks this down by statement, the second one counts in the entire procedure.

Physical reads are reads against the disk, logical reads are against memory.
You can use this to figure out which procedures or statements are the most expensive ones in your system and try to tune those.

Keep in mind, that while logical reads are significantly cheaper that physical reads, they are still expensive, so reducing the number of them (for example by adding an appropriate index) can make your queries run a lot faster.

There are a lot of additional columns in the DMVs above that you might find interesting too.

How does an index help to reduce logical reads?

In SQL Server all data is organized in blocks, 8KB in size. These blocks are called "pages".

Every table contains "meta" pages that contain information about the struktur of the table as well as pata pages. If no index exists and you run a query like SELECT * FROM tbl WHERE Id = 7 SQL Server has to go looking for this or these rows in the entire table. So it reads in one page at a time, loops through all rows in each page to determine the rows that fit the WHERE clause. So if the table requires 1,000,000 pages to be stored, this query will take 1,000,000 logical reads to execute.

If you have an index, SQL Server sorts the data logically within the pages and establishes a linked list between the pages. This allows for running queries with an ORDER BY to be executed without an expensive sort operation. But mor importantly that the sorting, SQL Server adds a B+Tree to the table. A B+Tree is a structure comparable to the index in a book, where looking for a specific keyword allows me to directly jump to the page that contains the keyword. The typical book has only one index level whereas a B+Tree can have multiple. Just think of a large book, where the index itself is multiple pages long. In a case like that it makes sense the add an additional index layer that tells us on wich page the index words starting with S are to be found.

B+Trees are optimized to have as few levels as possible while providing the property that any record in the index can be found by reading one page per index level. So assume the above WHERE Id = 7 query when you have an index sorted by Id. Let's say that index has 5 levels. Now, to find all records that match this query I have to read one page per index level (that is 5 pages).This is called an "Index Seek". If there are multiple records that fit the bill I might have to follow the sorted index for a while to retrieve all of them. But lets assume there is only one record.

So, without the index running that query required 1,000,000 reads, with indes it required 5 reads. Even though a logical read is an in-memory operation there is stil a substantial cost - in fact it is the most expensive operation in a trivial query like the one above. So reducing the amount of logical reads needed by a factor of 200,000 will speed up your query by a similar factor.

So, a logical read is not equivalent to a table scan, but a table scan causes a whole lot more logical reads than an index seek.


PostgreSQL for windows create tablespace on network location (nfs shared) failed because directory not exist My server is Windows Server 2016. The server mounted a NFS remote storage with below command:
mount \\192.168.6.68\nfsshare z:
mkdir z:\pg_data

All the relevant permissions such as read/write/execute were set correctly on z:
I installed PostgreSQL 12.3 to C:\PostgreSQL. And then created tablespace with below commands:
create tablespace my_db_space location 'z:\pg_data'

PostgreSQL replied me:
Error, directory 'z:\pg_data' does not exist.

I also tried 'z:\pg_data', 'z:/pg_data', but all got the same error message.
I also tried another way, which is, I installed PostgreSQL in Z:\postgresql. The installation was successful. but postgreSQL service failed to start because of the error "directory not found".
In Linux, the same situation didn't happen the same error.
It seems that on Windows, PostgreSQL can only use local storage as its data location, right?
Any siggestion ?

It seems that on Windows, PostgreSQL can only use local storage as its data location, right?

On any operating system, PostgreSQL should only use "local" file systems.
On Linux, those file systems can be mounted from other machines in a way that PostgreSQL is not aware of so things "just work".
On Windows, a network drive is another thing entirely.
Under which account are you running PostgreSQL?  (Probably as a Windows Service under "Local System").
Does that account have the ability to "see" anything not on the local machine ("No").
Even if you were to change this, putting your data "further" from the process that needs to manipulate it is just asking for [major] Trouble.


NoSQL : Getting latest item from DynamoDB table I'm implementing chat server currently, with dynamoDB.

Here is keys from ChatData table

Primary partition key : roomNo
Primary sort key : seq

Chat server, can created many instant rooms, approximated to 100~120 daily.
So I choosed primary partition key as room Number(roomNo), and sort by data created time(or sequential value) on seq.

I want to increase seq automatically, however, I know that DynamoDB hasn't support such as auto_increment or identity. So, I'm going to try using Timestamp or AtomicLong from my client application(JAVA 1.8).

But, each has problems.
first, Timestamp, may inserted duplicated value in table.

and AtomicLong, can increased atomic value but when I restart my application, it starts from 1 again.

Maybe if I can get maximum seq value from DynamoDB, what should I use for?
or how to change keys on table?

I think you're on the right track with AtomicLong, but you'd have to run a scan operation each time your app starts to determine which value to start at. You'd want to test this to ensure it doesn't degrade performance too much.

Another option is a metadata item. You could create an additional table for this, or you could add an item to your existing table using a hash attribute value that will never otherwise exist (so it won't clash with your application, and can be easily retrieved using its well-known value). Store name/value pairs that your app needs to know about (e.g. lastSeq) and use conditional writes to change this as necessary (e.g. increment lastSeq but only if the current value is 234).

If there are multiple instances of the client application, you'll want to consider contention and how this might affect things; using conditional writes should alleviate this (but you'll need to query and update the metadata item every time an item is created).

You can read more about conditional writes in the AWS DynamoDB Developer's Guide.


SSIS package succesfully completes but does not import 100% of records from flat file into table I have a fixed width text file. I have created an SSIS package that imports the file into a table in a SQL 2014 server.

When I open the file (with notepad++) i see that it contains 259,735 records.

When I run the SSIS package it only imports 216,610 records. The package finishes and gives a Success output message but I am wondering why it is not importing all the records.

The package was previously working and it would import the 259,735 records but I made a change to the file layout and underlying tables and now I only get the 216,610 loaded.

I am not sure what is the best way to trouble shoot this and can't think of anything that would cause this issue other then maybe a line not being terminated correctly but then I would have probably got a truncation error if that were the case.

my row delimiter is CRLF and it appears in the same position (400) on every line of the file and as I mentioned yesterday I was able to import all 259k records with this same package so I am pretty sure that the file is properly formatted and is not the issue.

Does anyone have any ideas on what can be causing this issue or how I should go about troubleshooting further?

Yesterday when I was working with the file I went to the preview tab and I set the Data rows to skip to around 45k so I could preview what some of those rows were looking like.

I was under the impression that the Data rows to skip value would only affect the preview and not actually be used at run time.

Setting the Data rows to skip back to 0 made it so all the records were once again being imported into the table

Some of the other things I did while trouble shooting which might help someone else who is having a similar issue  but due to a different reason can be found here:

ssis-text-file-import-skips-some-rows


MySQL Cluster: cannot start the management node When I try to start the management node of MySQL Cluster on Windows platform, I get the following error:

  Could neither create or open key 'SYSTEM\CurrentControlSet\Services\EventLog\Application\MySQL Cluster Management Server', error: 5
  Failed to setup event logging

Could someone explain this to me?

I believe that you can ignore this error as the mysqld should still start up.

However, in trying to reproduce the problem, I've found a glitch in the quick start guide for Windows, a "-" (the standard dash, ASCII 45) has been substituted with a "–" (the long dash, ASCII 150), the correct options for the MySQL client should be "-h 127.0.0.1 -P5000 -u root".

Try this out (i.e. type out the option rather than cutting and pasting from the quick start guide).

With the wrong character, the MySQL client refuses to connect to the MySQL daemon which could give the impression that the MySQL daemon process (mysqld) wasn't running correctly.


Oracle Linked Server in Azure SQL Database Managed Instance I am migrating my On Premise SQL Server to Azure SQL Database Managed Instance. But I have linked server to Oracle database in my SQL Server.

Can we create linked server to an On Premise Oracle Database in SQL Azure Database Managed Instance? Can anyone please guide?

If not, what is the alternative?

Linked servers in Managed Instance support a limited number of targets. Supported targets: SQL Server and SQL Database Not supported targets: files, Analysis Services, and other RDBMS. For more information, please read this documentation.

You may consider to try with SQL Server installed on Azure VM using a private VNET instead of using a Managed Instance.


Availability Groups using Multi-Subnet Clustering: Preferred Owners for Roles and Possible Owners for AG Listener IPs We use a SQL Server 2016 Availability Group (AG) on a Windows Server 2012 multi-subnet failover cluster for HA/DR. There are two nodes in our New York datacenter (10.7.x.x subnet) and one node in our Colorado datacenter (10.8.x.x subnet). The Colorado server is primarily for Disaster Recovery or extended maintenance where NY is offline, so we currently set the Quorum NodeWeight/Votes for the CO-SQL01 node to zero to prevent it from automatically taking ownership of the Cluster or AG if there are any issues.

My question is this: should we change the default settings in the Failover Cluster Manager, specifically the Preferred Owners of the AG role and the Possible Owners of the AG listener IP resources? The defaults that are used seem to conflict with our goals of High Availability using the two nodes in NY and only using the CO node for Disaster Recovery.

Here are the default settings for the Preferred Owners of our Multi-Subnet AG:

  Should we add a check next to NY-SQL02 and move it above CO-SQL01 so
  that it is preferred? What about other roles, can we set the preferred owners of the core cluster resources (like the cluster name)?

And here are the default settings for the SQL-StackOverflow_AG IP resources:

  Should we remove the check marks next to the servers that are in a different subnet than that IP address?

This question came up on a recent office hours, but it is something we though might help prevent downtime when our cluster has issues. We recently had a 5 minute outage when we replaced our CO-SQL01 server hardware and it was added back to the failover cluster (but not the AG) without removing it's vote. The CO-SQL01 server then experienced a hard crash (we think it was an NVMe/PCIe driver bug under heavy load) and managed to take down the AG with it (we think CO-SQL01 took ownership of the core cluster resources when it came back online).

To be honest we have had a number of unexpected issues with using Multi-Subnet Failover Cluster Availability Groups, and it seems like the default preferred role owners and possible resource owners may be incorrect or at least not optimal for our scenario. We currently are looking at using the new Distributed Availability Groups feature in SQL Server 2016 to split our Multi-Subnet AG into to two single subnet AGs (one for each datacenter) as a way to prevent these issues in the future. We also think that will allow us to upgrade the cluster OS with minimal downtime.

My question is this: should we change the default settings in the Failover Cluster Manager, specifically the Preferred Owners of the AG role and the Possible Owners of the AG listener IP resources?

NEVER check or uncheck the preferred or possible owners for AG resources. When using SQL Server 2016 the preferred owners list can be moved up and down to control preferred failover targets given the multiple failover targets change but do not check or uncheck any boxes, ever, with Availability Groups. Period.

If you check and uncheck things like this, you're AG isn't going to work properly. When I teach the Availability Group class, I always show how this works, why it works, and why we NEVER want to do this (spoiler alert: The AG will most likely fail, hard).

Let me explain this, though, to make it a little clearer.

Preferred and Possible Owners

These values are set by SQL Server based on the settings for the Availability Group. The cluster has its own copy of the metadata of how things should work and SQL Server also has a copy of how it believes things should work.

This is great most of the time, when no one changes anything and both SQL Server and the cluster continue to hum along.

However, SQL Server knows what it believes should and shouldn't be owners of the resources based on the AG settings and reflects this by calling cluster APIs to set the expected behavior. You'll see this reflected in the HADR_CLUSAPI_CALL wait type.

Three Async Replicas

Let's look at SQL Server with an AG setup so that there are three Async commit replicas. If we do this, our AG and role resource should look as such:

Remember, async commit replicas cannot have automatic failover. Thus, this makes sense; our current role owner is the only possible and preferred owner. Since SQL Server is setup so that the only way to fail over is by forcing it then we don't want any other replica to pick up and start running with this, thus enforcing this at the clustering level.

Two Synchronous Manual Failover, One Async - Demo

The results will be the same as the Three Async demo. Why?

This is the same explanation as the three async replicas, we have no automatic failover yet so we don't want anyone AUTOMATICALLY taking over. Thus, the settings are the same.

Two Asynchronous Automatic Failover, One Async - Demo

Here is where things change and get interesting! Since we're now introducing automatic failover - and that's one of the roles of WSFC (along with health checking, metadata distribution, etc) - we're going to want to have preferred and possible owners.

Other Responses

  We recently had a 5 minute outage when we replaced our CO-SQL01 server hardware ...

You've specifically removed the vote from CO and only had 1 other voter left then had an unexpected crash. Sounds like it was working as it should.

  To be honest we have had a number of unexpected issues with using Multi-Subnet Failover Cluster Availability Groups, and it seems like the default preferred role owners and possible resource owners may be incorrect or at least not optimal for our scenario.

I'm going to be brutally honest here. Availability Groups are not a silver bullet, fix everything HA & DR. It seems that the technology is being used, but not too much is known about how it works or why it does these things on both an AG level or a WSFC level, so I completely agree it may not be setup properly... however, it is working as it is setup - I can't fault the cluster for doing what it is supposed to be doing.

  currently are looking at using the new Distributed Availability Groups feature in SQL Server 2016 to split our Multi-Subnet AG into to two single subnet AGs (one for each datacenter) as a way to prevent these issues in the future.

If you're having this many issues with Availability Groups I would not foray into Distributed Availability Groups on your own. It may or may not fit your use cases - but if I were you, I'd bring in someone who helps architect these types of solutions and have your use cases ready. Otherwise, you're going to make another post like this.

  We also think that will allow us to upgrade the cluster OS with minimal downtime.

One of the use cases for Distributed Availability Groups is for extremely low downtime cross cluster migrations, you're correct. If you're using Windows Server 2012R2, you can do rolling cluster upgrades [NOT available in 2012, must be R2] without needing to do Distributed Availability Groups.

  And here are the default settings for the SQL-StackOverflow_AG IP resources

Yes, do not touch them :) Leave them as is.


WHERE attribute having two relation lines in the same SQL I have a database designed with tree tables:

checklist (id, name)
tag (id, name)
checklist_tag (checklist_id - FK, tag_id - FK)

Running this SQL:

SELECT cc.id as checklist_id, cc.name as checklist_name, ct.name as tag_name
FROM checklist cc INNER JOIN checklist_tag cct ON cc.id = cct.checklistId
INNER JOIN tag ct ON cct.tagId = ct.id
WHERE ct.name IN ('tag 1', 'tag 2')

I get this result:

checklist_id    checklist_name  tag_name
1               checklist 1     tag 1
1               checklist 1     tag 2
3               checklist 3     tag 2
4               checklist 4     tag 2

But I need to select the checklists having the tags 1 and 2. Something like this:

checklist_id    checklist_name  tag_name
1               checklist 1     tag 1
1               checklist 1     tag 2

Is there anyway to do that with a single select?

Use a subquery that has the checklist id that has both tags

SELECT
    B.id checklist_id,
    B.name checklist_name,
    D.name tag_name
FROM
(
    SELECT AA.checklistId,COUNT(1) tagcount
    FROM checklist_tag AA
    INNER JOIN tag BB ON AA.tagId = BB.id
    WHERE BB.name IN ('tag 1', 'tag 2')
    GROUP BY AA.checklistId HAVING COUNT(1)=2
) A
INNER JOIN checklist B     ON A.checklistId = B.id
INNER JOIN checklist_tag C ON A.checklistId = C.checklist_id
INNER JOIN tag D           ON C.tag_id = D.id;


How to join with null value? This code returns only posts having a rating. How I can get all posts with null value where the post does not have a rating?

select
    posts.id,
    posts.user_id,
    posts.name,
    posts.created_at,
    ratings.post_id,
    ratings.avg,
    ratings.count
from
    posts
join (
    select
        post_id,
        avg(rating) as avg,
        count(rating) as count
    from
        ratings
    group by
        ratings.post_id
) ratings
on ratings.post_id = posts.id

Have you tried using a LEFT OUTER JOIN? A LEFT OUTER JOIN will keep all the rows from the 'left' side of the join statement (posts table) that do not have a correponding row on the 'right' side of the statement (ratings table). The missing rows from the 'right' side will be filled with NULLS.


Oracle GRANT ROLE error I am trying to apply a role to a user and getting this error: SQL Error: ORA-00990: missing or invalid privilege. Here is where I created the role, which worked fine logged in as SYSTEM.

    CREATE ROLE LahmanDBDev;
    GRANT CREATE SESSION TO LahmanDBDev;
    GRANT CREATE TABLE TO LahmanDBDev;
    GRANT CREATE TABLESPACE TO LahmanDBDev;
    GRANT CREATE VIEW TO LahmanDBDev;
    GRANT CREATE ANY INDEX TO LahmanDBDev;
    GRANT CREATE SEQUENCE TO LahmanDBDev;
    GRANT CREATE SYNONYM TO LahmanDBDev;
    GRANT SELECT ON BASEBALL.MASTER TO LahmanDBDev;
    GRANT SELECT ON BASEBALL.BATTING TO LahmanDBDev;
    GRANT SELECT ON BASEBALL.FIELDING TO LahmanDBDev;

Then I went on to create the user and grant the role to it, the second statement here where I grant the role is where I get the error:

    CREATE USER BudSelig
    IDENTIFIED BY BudSelig;

    GRANT ROLE LahmanDBDev
    TO BudSelig;

The correct syntax is:

   GRANT LahmanDBDev
   TO BudSelig;


Randomly slow inserts with InnoDB TL;DR : How can I improve insertion time in my table so that every inserts take under 100ms to perform?

This table is used by a webservice which receives transactions one by one so I can't group inserts. The timeout of the webservice must be under 150ms.

Infrastructure : AWS
Application (php) : EC2 t2.micro
Mysql : RDS t2.small - 100GB magnetic storage

Here are the measurments I've done in production

Top left hand corner : We can see that a lot of inserts take more than 500ms and some even take more than 1s. Only a few are under 50ms. (red bars are 95th percentile)

Top right hand corner : RDS cpu is always low (memory usage is also constant and fine)

Bottom left hand corner : Number of inserts in the table

Bottom right hand corner : blue line is write IOPS, red line write lentency, blue bars is disk_queue_depth * 100. Disk queue is pretty much always below 1. We can see huge spikes in write lentency when everything else looks fine : no increase in IOPS nor in hardware ressource consumption.

I've created a script to measure insertion time in account_transaction in isolation :

// pseudo code
for ($i=0; $i<100; $i++) {
    $ts=microtime(true);
    $db->insert(data);
    echo microtime(true)-$ts;
}

Output for 100 inserts : (sorted)

0.012468814849854
0.012633085250854
0.012740135192871
0.012858867645264
0.012872934341431
...
...
...
0.038048028945923
0.040112018585205
0.040735960006714
0.042028903961182
0.42462396621704
Avg : 0.025037434101105

I pretty much always end up with the same results : 1% or 2% of the inserts are very slow. Same if i perform 1000 inserts.

What I've tried

disable all indexes except PK : no improvements.
rebuild all indexes : no improvements.
innodb_flush_log_at_trx_commit=0 : great improvements :

slow inserts totally disappeared
average insert time 5 time faster

switch to a bigger RDS (m4.large with 100GB SSD with 1000 IOPS preprovisioed) : some improvements :

slow inserts almost disappeared : maybe 0.1% if any and it rarely exceeds 100ms
average insert time 3 time faster

Unfortunately innodb_flush_log_at_trx_commit=0 isn't an option as I can't afford to lose any transaction.

My thoughts

I would expect inserts to be pretty fast most of the time (<1ms) even on basic hardware. Table only has 4 indexes and 200k rows. Am I wrong to assume that?

I suspect the bottleneck to be in the I/O realm because of the test with innodb_flush_log_at_trx_commit=0 but the metrics I've got from AWS doesn't seem to confirm that. Am I wrong again?

Table account_transaction

CREATE TABLE `account_transaction` (
  `id` char(21) COLLATE utf8_unicode_ci NOT NULL,
  `seqid` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` char(21) COLLATE utf8_unicode_ci NOT NULL,
  `token` bigint(20) unsigned DEFAULT NULL,
  ...
  ...
  `creation_date` datetime NOT NULL,
  `processing_date` datetime DEFAULT NULL,
  PRIMARY KEY (`seqid`),
  UNIQUE KEY `id` (`id`),
  KEY `consolidation_idx` (`trans_link`,`token`),
  KEY `idx_token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=239304 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id look like 1473430240HBCRSPL9FPB = unix epoch + 10 random chars : we use this format vs a standard UUID to preserve good insert performance. Always 21 char.

seqid is used to keep track of the row number. Our id has a random part so we can't use it for that purpose.

select count(1) from account_transaction = 239k

Questions

Is there a way to remove the high lentency spikes?
Should i go with bigger hardware to improve performance? My traffic is really low...
I must ensure that 99.9% of inserts are done under 100ms. Is that realistic on RDS mysql?

I would suggest that you are 12ms away (latency) from the AWS datacenter.  You are stuck with that overhead.  Conclusion:  Beware of the timings.
The "<1ms" is realistic only if client and server are in the same datacenter and the disk is SSD.  What is "magnetic storage"?  Perhaps that is an old, spinning, disk.  In that case 10ms is a reasonable guess for one write.  And it is hard to determine from the available numbers what the latency really is.  (Presumably less than 12ms.)  Conclusion:  Be sure to use SSDs.
What does Amazon say about interference from other instances on the same hardware?  You have a "small" instance; there are probably many others sharing the same I/O.  If one of them has a spike, you might be slowed down.  With "large", you might have fewer competing customers.  And it might be SSD.  Conclusion:  Focus on average or 95th percentile, not spikes.
innodb_flush_log_at_trx_commit=1 requires at least 1 disk write per transaction.  =0 delays and combines writes, hence the noticeable speedup.  One way to soften it is to batch multiple queries into a single BEGIN...COMMIT.  (Only do such where the logic makes sense.)  Conclusion:  Bigger transactions.  (However, you will still be stuck with 12+ms per transaction instead of per insert.)
Updates to indexes (except for UNIQUE indexes) are delayed and can usually be treated as zero impact.
char(21) COLLATE utf8_unicode_ci -- Is it always 21 characters?  If not, use VARCHAR.  Perhaps it is always ascii, not utf8?  If so, use CHARACTER SET ascii.
PRIMARY KEY (seqid),  UNIQUE KEYid(id) -- Why have both?  As already mentioned, it causes overhead included in the INSERT time, namely to verify uniqueness before committing.
The activity seems to be very low.  Normally people ask questions like yours when they are having trouble with hundreds or thousands of queries per second.
Here's a way to get a 16-byte, chronologically ordered, UUID:  http://mysql.rjweb.org/doc.php/uuid .  (But not as easily displayed.)


How can I verify that all possible FLOAT(53) values convert to unique BINARY(8) values? Part of our application code converts data types to BINARY for the purposes of hashing. For example, we convert BIGINT values to BINARY(8). The documentation warns that conversions may change between versions of SQL server:

  Conversions between any data type and the binary data types are not
  guaranteed to be the same between versions of SQL Server.

As a defense against that, whenever we start supporting a new version of SQL Server we try to run tests to validate that all of our conversions are still valid. For something like BIGINT, we check the length of the converted value for the minimum and maximum values allowed for BIGINT. That hopefully means that we would know if, for example, SQL Server 2019 started to require BINARY(9) to fit all possible values of BIGINT.

How can we do this type of analysis for FLOAT(53)? Right now we think that all possible values map to unique values that fit in a BINARY(8), but I don't know how to validate that. This may not be as simple as just checking the number of bytes required for the data type. For example, the TIME data type requires 5 bytes for storage but must be converted to a BINARY(6) to avoid errors. Perhaps this is irrelevant, but it also makes me nervous that BINARY cannot be converted back to FLOAT. I admit that I might be overthinking the problem, so I welcome frame challenges as answers.

How can I write code to validate that all possible inputs for FLOAT(53) do not overflow a BINARY(8) and that two different FLOAT(53) values do not convert to the same BINARY(8) value?

The size, in bytes, of both BIGINT and FLOAT(53) are documented.  It would be a breaking change to make either one not convertible back and forth to BINARY(8).

The documentation you quote is about the details of the conversion, which are undocumented and subject to change.  EG the byte layout of a float(53) in this conversion could change.  So if you convert a float to binary(8), store the binary(8) in a table, upgrade SQL Server, and convert the binary(8) back to float, you might not get the same value.

These particular types have well-known byte layouts.  BIGINT is an 8-byte little endian integer, and float(53) is a standard double-precision floating point number.  And it would be pretty surprising if one of those changed.  But other types, especially CLR-based types, really might change their layout between versions.


Mysql Join with filters I have two tables: MAINCOLOR and SUBCOLOR with the data as:

MAINCOLOR :
MAINID  COLOR
1       RED
2       BLUE
3       GREEN

SUBCOLOR:
SUBID MAINID SUBCOLOR STATUS
1     1      PINK     0
2     1      ORANGE   1
3     2      VIOLET   1

I need a result set as:

MAINID  COLOR   SUBCOLOR
1       RED     ORANGE
2       BLUE    VIOLET
3       GREEN

Resultset should be a join of MAINCOLOR and SUBCOLOR using MAINID. Need child data having status as 1.

You can get the result by using a LEFT JOIN between the maincolor and the subcolor.

The key is placing the filter of status=1 on the JOIN condition.  Your code will be:

select m.mainid,
  m.color,
  s.subcolor
from maincolor m
left join subcolor s
  on m.mainid = s.mainid
  and s.status = 1

See SQL Fiddle with Demo.

The LEFT JOIN will return all rows from the maincolor table and any matching rows in the subcolor table. Since you what to apply a WHERE to the data in the subcolor table, then you will want to move that filter to the JOIN condition to get the result.


What is the deal with the "invisible schema" pg_catalog? If I run this query:
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes ORDER BY schemaname ASC, tablename ASC, indexname ASC;

It returns a ton of records for the nonexistent schema "pg_catalog". It's not a schema in my database. I assume it's some kind of internal, "secret" schema, but I find no mention of it in the PG manual when I searched for it before asking.

It's not a schema in my database.

Yes, it is. And it's neither "invisible" nor undocumented:
Quote from the manual

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path.

All views and tables in the pg_catalog schema are documented here


Delete Duplicates Leaving One of Two Identical Records Delete Duplicates Leaving One of Two Identical Records

One of my MySQL database tables contains four BLObs in each row. When it was first set up the table had no keys. However when I now try to add a Primary Key (PK) I find I cannot do so as one of my people ran the same add twice. As a result I now have nearly a thousand duplicate records in over 5000 total records. Having Googled and searched here for a bit there seems more than one way of removing duplicates but which is the best one to use here?

Thank you...

Without knowing the structure, generically, you can GROUP BY the fields that have been duplicated and store it in a separate table for check purposes, then substitute the old table with the new one.

So, imagine that I have a table my_table with the fields a and b duplicated. I can do:

CREATE TABLE my_table_new (id int primary key auto_increment, a blob, b blob);

SET @i := 0;

INSERT INTO my_table_new SELECT @i := @i + 1, a, b FROM my_table GROUP BY a, b;

The exact query will depend on the older structure, the fields you want to group by, the order in which you want to insert, etc. This example creates an integer primary key from 1 to number of non-duplicate records.


adding autoincrement to id already part of foreign key I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without dropping the foreign key constraint for all of them, adding the autoincrement property and re-creating the foreign key constraints?

Thank you very much!

Try temporary disabling foreign keys (make sure no ones allowed to update the db meanwhile):

create table t1 (id int not null primary key) engine = innodb;
create table t2 (id int not null primary key
                ,t1_id int not null
                ,    constraint abc foreign key (t1_id)
                                    references t1 (id)
) engine = innodb;

set foreign_key_checks = 0;
alter table t1 change column id id int auto_increment;
set foreign_key_checks = 1;

Note that set foreign_key_checks = 1; does not validate foreign keys, so if someone manages to add invalid values whilst foreign keys are disables, you end up with an inconsistent db:

insert into t1 (id) values (1);
set foreign_key_checks = 0;
insert into t2 (id, t1_id) values (1,1);
insert into t2 (id, t1_id) values (2,2); -- invalid
set foreign_key_checks = 1; -- does not validate foreign keys

I vaguely remember that this was also the case when adding foreign keys, but this bug seems to have been fixed since:

select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)

alter table t2 drop foreign key abc;
alter table t2 add constraint abc foreign key (t1_id)                                      references t1 (id);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails ("test"."#sql-5fa_a",
CONSTRAINT "abc" FOREIGN KEY ("t1_id")
REFERENCES "t1" ("id"))


How to get CPU usage by database for particular instance? I have found the following queries to detect the CPU usage by database, but they are showing different results:

WITH DB_CPU_Stats
AS
(
    SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
      SUM(total_worker_time) AS [CPU_Time_Ms]
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY (
                    SELECT CONVERT(int, value) AS [DatabaseID]
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
    GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName,
        [CPU_Time_Ms],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

The above query tells that the issue is with one of my database (almost 96%).

And the query below tells that the issue is with the master and the distribution databases (about 90%):

DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
    join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

I have checked thet the sys.sysprocesses is decpreated. Does this mean that the results from the second query are wrong?

While I, like @Thomas, completely agree with @Aaron in the comments on the question regarding the concerns of "per-database CPU usage" being either accurate or useful, I can at least answer the question as to why those two queries are so different. And the reason why they are different will indicate which one is more accurate, though that higher level of accuracy is still relative to the one that is specifically inaccurate, hence still not truly accurate ;-).

The first query uses sys.dm_exec_query_stats to get CPU info (i.e. total_worker_time). If you go to the linked page that is the MSDN documentation for that DMV you will see a short, 3 sentence intro and 2 of those sentences give us most of what we need to understand the context of this info ("how reliable is it" and "how does it compare to sys.sysprocesses"). Those two sentences are:

  Returns aggregate performance statistics for cached query plans in SQL Server. ... When a plan is removed from the cache, the corresponding rows are eliminated from this view

The first sentence, "Returns aggregate performance statistics", tells us that the information in this DMV (just like several others) is cumulative and not specific to only queries that are currently running. This is also indicated by a field in that DMV that is not part of the query in the Question, execution_count, which again shows that this is cumulative data. And it is quite handy to have this data be cumulative as you can get averages, etc by dividing some of the metrics by the execution_count.

The second sentence, "plans being removed from the cache are also removed from this DMV", indicate that it is not a complete picture at all, especially if the server has a pretty full plan cache already and is under load and hence is expiring plans somewhat frequently. Also, most DMVs are reset when the server resets so they are not a true history even if these rows weren't removed when the plans expire.

Now let's contrast the above with sys.sysprocesses. This system view is only showing what is currently running, just like the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests (which is stated on the linked page for sys.dm_exec_sessions). This is an entirely different view of the server as compared to the sys.dm_exec_query_stats DMV which holds the data even after the process completes. Meaning, in relation to the "are the results from the second query wrong?" question, they are not wrong, they just pertain to a different aspect (i.e. time-frame) of performance stats.

So, the query using sys.sysprocesses is only looking at "right now". And the query using sys.dm_exec_query_stats is looking at mostly (maybe) what has happened since the last restart of the SQL Server service (or obviously system reboot). For general performance analysis it seems that sys.dm_exec_query_stats is far better, but again, it drops useful info all the time. And, in both cases, you also need to consider the points made by @Aaron in the question comments (since removed) regarding the accuracy of the "database_id" value in the first place (i.e. it only reflects the active DB that initiated the code, not necessarily where the "issue" is occurring).

But, if you just need/want to get a sense of what is happening right now across all Databases, possibly because things are slowing down right now, you are better off using the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests (and not the deprecated sys.sysprocesses). Just keep in mind that you are looking at/for queries, not databases, because queries can join across multiple databases, include UDFs from one or more databases, etc.

EDIT:
If the overall concern is reducing high CPU consumers, then look for the queries that are taking up the most CPU, because databases don't actually take up CPU (looking per database might work at a hosting company where each database is isolated and owned by a different customer).

The following query will help identify queries with high average CPU usage. It condenses the data in the query_stats DMV since those records can show the same query (yes, the same subset of the query batch) multiple times, each with a different execution plan.

;WITH cte AS
(
  SELECT stat.[sql_handle],
         stat.statement_start_offset,
         stat.statement_end_offset,
         COUNT(*) AS [NumExecutionPlans],
         SUM(stat.execution_count) AS [TotalExecutions],
         ((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS [AvgLogicalReads],
         ((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS [AvgCPU]
  FROM sys.dm_exec_query_stats stat
  GROUP BY stat.[sql_handle], stat.statement_start_offset, stat.statement_end_offset
)
SELECT CONVERT(DECIMAL(15, 5), cte.AvgCPU) AS [AvgCPU],
       CONVERT(DECIMAL(15, 5), cte.AvgLogicalReads) AS [AvgLogicalReads],
       cte.NumExecutionPlans,
       cte.TotalExecutions,
       DB_NAME(txt.[dbid]) AS [DatabaseName],
       OBJECT_NAME(txt.objectid, txt.[dbid]) AS [ObjectName],
       SUBSTRING(txt.[text], (cte.statement_start_offset / 2) + 1,
       (
         (CASE cte.statement_end_offset
           WHEN -1 THEN DATALENGTH(txt.[text])
           ELSE cte.statement_end_offset
          END - cte.statement_start_offset) / 2
         ) + 1
       )
FROM cte
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPU DESC;


Import data problem in SQL Server 2008 I want to import data from one database to another.  On the destination database, I don't have permission to restore a backup file to it, so I have only one option which is to import data.

I used the wizard and I set the option of allow identity insert, but after it ran I faced a problem with foreign keys insertion. Is there anything I missed or shall I drop the foreign keys and recreate it after import?

I really wonder why Microsoft made it complicated.

You could drop the foreign keys, but if the tables are large and/or you have busy environment, be sure to test your insert times before you do anything on production.
In regard to new keys, it shouldn't throw a problem, but if you're trying to insert duplicate keys then you'll get errors.
Also, be sure to insert parent entities first (if you don't drop foreign constraints first!) then child entities.


mongod memory usage doesn't decrease I'm using mongodb for saving log data.

When I have to find something from log data, which could be over than 1,000,000 documents, all the process work properly but the issue is that memory usage doesn't get down even though the process has been finished.

When I enter 'top' command on the server that runs mongod process, it shows resource information like below screenshot.

Memory usage for the mongod process was less than 10%, but after using a lot of find() queries, it increased to 38.9% and never decreasing. What causes this kind of issue and what do I do to decrease the memory usage of mongod process except restarting?

I didn't know that this question is not appropriate for stackoverflow site, but when I asked the same question in stackoverflow someone replied me like this.
"Of course it doesn't, it's a database. They are meant to consume memory and hold on to it"

But what I did with my code was just using .find() query, nothing was added to database. Does it mean that only using find() query could have caused increasing of  memory usage and not releasing it? I don't understand help me please

MongoDB use all memory, always! Reason why find increases memory usage (compare to fresh started machine) is that with every find, mongod will fill it's cache more to server clients better. It tries to keep active indexes and data in the memory, if possible.