Bit vs. Boolean columns Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way.

Does it actually provide any benefit using a bit field for a boolean value? From what I can see it seems to suggest that space is the only real benefit.

Personally, I would use the BOOLEAN for a boolean value. But keep in mind the caveat that in MySQL, BOOLEAN is just a synonym of TINYINT(1) [src].

This, of course, means you can have values other than 0 or 1 if you are not careful. To avoid this, you can use the aliases TRUE and FALSE when inserting and updating data, as those map to 1 and 0 respectively.

For the sake of being complete, prior to 5.0.3, BIT was also a synonym of TINYINT(1).


Sudden High CPU Usage On RDS Postgres I manage an application that has what I would call a medium sized workload (20,000-30,000 records inserted per day with around 2GB of data total). We're on RDS Postgres 9.6 with a db.m4.xlarge (4 vCPU and 16 GB of RAM) instance (I'm way over provisioned, I know). Everything has been running smoothly for months with the average CPU utilization under 10%.

Yesterday I got an escalation from the application support that our CPU has spiked above 80% and since then I haven't gotten it to go back down under 50%.

This is the graph of the last 4 days of CPU Utilization

There's been no spike in usage over this same time.

I've looked into long running queries through out the day using

SELECT max(now() - xact_start) FROM pg_stat_activity
                           WHERE state IN ('idle in transaction', 'active');

The largest amount I've seen is 1.5 seconds

We have about 40 connections but most are inactive.

This same thing happened a few months ago and we moved to our now way overprovisioned server to hopefully not have it happen again. So now I'm trying to figure out the root cause.

Any help would be appreciated.

Hi It's not about just not the number of number of session or queries which are running.

Below will help you to understand how to troubleshoot this problem

Download the logs from RDS console for this instance to generate the Pgbader report. Below is the format which is most suitable to generate the logs
Download the pgbadger and use below command to generate the report.
https://github.com/darold/pgbadger
Match your CLoudwatch or RDS CPU time stamp with Pgbagder report to see how many queries are getting executed
Try to find the query which may be eating a lot of CPU.
pgbadger -q /postgresql*.log -f stderr -b "2019-07-17 08:55:19" -e "2019-07-22 07:30:07" -o pgbadger.html

I may be able to give specific guidance if you can hare more data.

Cheers,
Aj
http://www.postgresql-blog.com


Can I shrink the transaction log file on a mirror database? This is a followup question to a previous question about why I couldn't shrink the log file on the principal database.

To make a long story short, I setup database mirroring but forgot to make sure the job that backed up the transaction log was running again, and the transaction log grew to almost 60GB.

Since mirroring was set up, this size increase was duplicated on the mirrored server, and eventually took up all the disk space and made the mirror database unusable.

Per this question about transaction log maintenance on a mirror database, you can't back up the log on the mirror, but when asked specifically in comments about how to shrink an overgrown log file on a mirror database, a comment was left that

  One way to do this would be to failover to the mirrored database and
  do the shrink there. Thoroughly test this out in a non-production
  environment to ensure it has the behavior you want/expect.

This seems to suggest that there might be other ways to shrink the log file on a mirror, and this method might not necessarily be safe to do on a production server.

Is there a way to safely shrink the transaction log file on a database mirror?

The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database.  Here's some proof.

Create a sample database on the principal:

create database MirroredDb;
go

Create the same database from a backup with NORECOVERY:

restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go

Setup your mirroring session however which way you choose.

On the principal database look at the database file sizes:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like this following:

name            size
MirroredDb      392
MirroredDb_log  104

On the mirror database, create a snapshot and look at the same information:

create database MirroredDbss
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;

use MirroredDbss;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  104

Now grow the transaction log file on the principal database (I brought it to 1 GB):

alter database MirroredDb
modify file
(
    name = MirroredDb_log,
    size = 1GB
);
go

Looking at the principal database's transaction log size, we now see the adjusted size:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Create another snapshot on the mirrored database, and look at the transaction log file size there:

create database MirroredDbss2
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;

use MirroredDbss2;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Now do the DBCC SHRINKFILE on the principal:

use MirroredDb;
go

dbcc shrinkfile('MirroredDb_log', 0);
go

select
    name,
    size
from sys.database_files;

My result set is the following:

name            size
MirroredDb      392
MirroredDb_log  104

Create a third and final snapshot on the mirrored database, and look at the size:

create database MirroredDbss3
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;

use MirroredDbss3;
go

select
    name,
    size
from sys.database_files;

And I get the following result set:

name            size
MirroredDb      392
MirroredDb_log  104

So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.


Is there a way to speed up this update query using joins or any other method I have an update query that selects data from a table (HST) and updates it into another table (PCLN). The PCLN table contains ~17000 rows while the HST table contains about 1.1 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCLN, it takes 3-4 hours.

UPDATE PCLN
SET `T1A` = ( SELECT MIN(HST.`Date`)
              FROM `HST`
              WHERE HST.`SYM`=PCLN.INS
                AND HST.`DATE` >=  PCLN.Date
                AND HST.`HP` >= PCLN.`T1`)
WHERE `BS` = 'B';

I had asked a similar question before and did see some benefits after implementing the advise that was given, however the query is still very slow and takes hours to complete. I am not very good with MySQL yet but feel that there should be some way to improve the performance of this update query.

Here is the SHOW CREATE TABLE PCLN;

CREATE TABLE `PCLN` (
`SrNo` int(6) NOT NULL AUTO_INCREMENT,
`Date` date DEFAULT NULL,
`INS` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`T1` float(8,2) DEFAULT NULL,
`T1A` date DEFAULT NULL,
......
PRIMARY KEY (`SrNo`)
) ENGINE=InnoDB AUTO_INCREMENT=16893 DEFAULT CHARSET=latin1

and SHOW CREATE TABLE HST

CREATE TABLE `HST` (
`SrNo` int(11) NOT NULL AUTO_INCREMENT,
`SYM` varchar(20) DEFAULT NULL,
`Date` date DEFAULT NULL COMMENT 'YYYY-MM-DD',
`HP` float(8,2) DEFAULT NULL,
....
PRIMARY KEY (`SrNo`),
UNIQUE KEY `HST_idx_SYM_date_OP` (`SYM`,`Date`,`OP`),
UNIQUE KEY `HST_idx_SYM_date_CP` (`SYM`,`Date`,`CP`),
UNIQUE KEY `HST_idx_SYM_date_PC` (`SYM`,`Date`,`PC`),
UNIQUE KEY `HST_idx_SYM_date_LP` (`SYM`,`Date`,`LP`),
UNIQUE KEY `HST_idx_SYM_Date` (`SYM`,`Date`) USING BTREE,
UNIQUE KEY `HST_idx_SYM_date_HP` (`SYM`,`Date`,`HP`) USING BTREE,
KEY `HST_idx_date` (`Date`)
) ENGINE=InnoDB AUTO_INCREMENT=1103583 DEFAULT CHARSET=latin1 COMMENT='HST
Data - All'

On EXPLAIN I noticed that for the index HST_idx_date, it says 1002718 Range checked for each record (index map: 0x80). What does this signify, is there a way to limit the rows so that the query is sped up. Any suggestions to speed up the query would be greatly appreciated.

Server version: 5.6.39-cll-lve - MySQL Community Server (GPL).

Look for

UPDATE pcln, (  SELECT pcln.srno, MIN(hst.`date`) mindate
                FROM pcln, hst
                WHERE hst.sym=pcln.ins
                  AND hst.`date` >=  pcln.`date`
                  AND hst.hp >= pcln.t1
                  AND pcln.bs = 'B'
                GROUP BY pcln.srno  ) tmp
SET pcln.t1a = tmp.mindate
WHERE pcln.srno = tmp.srno;

To optimize the subquery create index pcln(bs,ins). Or the index which is covering for the subquery.


How to restore a file group from file group backup? I want to restore a specific file group from file group backup. I found examples how to restore it from full backup, but I want to do it from file group backup. Every time when I try, all of the files in the file group stuck in 'Restoring' state and cannot be accessed any more.
The way I'm trying is:

Creating Backups:

BACKUP DATABASE myDataBase FILEGROUP = 'secondaryFilegroup' TO DISK = N'D:\DBBackups\secondaryFilegroupBackup.bak' WITH INIT
BACKUP LOG myDataBase TO DISK = N'D:\DBBackups\tlog.trn';

Restoring:

RESTORE DATABASE myDataBase FILEGROUP = 'secondaryFilegroup' FROM DISK = N'D:\DBBackups\secondaryFilegroupBackup.bak'
RESTORE LOG myDataBase FROM DISK = N'D:\DBBackups\tlog.trn';

  Error: The log in this backup set terminates at LSN 4323000025691200001, which is too early to apply to the database. A more recent log backup that includes LSN 4323000025741600001 can be restored.

Can someone explain me what are the correct steps?

UPDATE
It looks like you misunderstood me. Sorry for my bad explanation and my late response. My task is to create a database with a file group with 3 files. Then I have to create a table in this file group. I have to make backup of the file group, then delete some rows and then have to restore the file group and the table must be as the beginning.
So i create a File group named "secondaryFilegroup" and a table in it named "testTable" with 1000 rows. Then I deleted 100 rows(so at the table remain 900) and restored the file group (so I expect to have again 1000 rows). But with this solution that you gave me I still have 900 rows.
My solution is based on answer from @Scott Hodgin.
PS: Im not sure is this even possible.

BACKUP DATABASE myDatabase FILEGROUP = 'secondaryFilegroup' TO DISK = N'E:\DBBackups\secondaryFilegroupBackup.bak' WITH INIT
BACKUP LOG myDatabase TO DISK = N'E:\DBBackups\tlog.trn' WITH INIT;

DELETE TOP(100) FROM testTable;

use master;

RESTORE DATABASE myDatabase FILEGROUP = 'secondaryFilegroup' FROM DISK = N'E:\DBBackups\secondaryFilegroupBackup.bak' WITH norecovery

RESTORE LOG myDatabase
FROM DISK = N'E:\DBBackups\tlog.trn'
WITH recovery;

BACKUP LOG myDatabase TO DISK = N'E:\DBBackups\ttomov_test\SZH\tlog2.trn' WITH INIT;

RESTORE LOG myDatabase
FROM DISK = N'E:\DBBackups\ttomov_test\SZH\tlog2.trn'
WITH recovery;

I think you might need to backup the transaction log (taillog) again at the end of your restore sequence and then restore that taillog backup.

The following demo worked for me:

USE [master]
GO

ALTER database [FGRestoreTEST] set offline with ROLLBACK IMMEDIATE;
DROP DATABASE [FGRestoreTEST]
GO

--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO

--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup
insert into TestTable values(1),(2)

--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\tlog.trn'
WITH INIT;

--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH norecovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\tlog.trn'
WITH recovery;

--At this point, you should have seen the messages about
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\Taillog.trn'
WITH recovery;

--Verify you can select from TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable

UPDATE: You added additional comments

  I tried your solution, but before the restore of the database, i
  deleted 100 columns from the table (I had table with 1000 rows, after
  the delete query they remain 900). Everything else was the same. So at
  the end all files are accessible, but the table doesn't restored and
  the rows remain 900.

Have a look at SQL Server Tricks: How Can I Restore a Subset of My Tables to A Prior Point in Time? - specifically the section Why Filegroup Level Restores Don’t Solve This Problem.

  Each filegroup-restore sequence restores and recovers one or more
  filegroups to a point consistent with the database.

  Why does it have to be this way? It’s because SQL Server doesn’t track
  transaction status by filegroups, and its mission is to ensure
  transactional consistency within a database. If we could restore
  individual filegroups to different points in time and bring everything
  online, we wouldn’t have a way to ensure consistency.


How can I separate some collections to another server? If my server disk is fulled, I want to move some collections to another server.

Is it possible?

Is there another way from sharded cluster implementation?

Yes, it's possible. But after moving data to another server, your application must have mean to connect that server when it needs to read/write that DB.Collection.

It is possible even in sharded cluster, I have done that. In that case too, application must have mean to connect that "second" RS or Cluster.

You must give more information, if you need doable solution.

With https://github.com/mongodb-labs/mongo-connector you can easily replicate single/RS/Cluster to destination...


Adding secondary replica from different subnet for AlwaysON If I understood the configuration of setting up a multi subnet AG setup where 2 nodes are in one datacenter and the 3rd node in another, I was under the assumption that we require 2 listner ip's - 1 listner ip for each subnet. Is this not the case? I was going through this article today, where the author no where mentions about creating another listner for a multi subnet AG setup. Can someone please clarify if we require a different listner ip for the second subnet or not?

Refering to https://www.mssqltips.com/sqlservertip/4598/add-secondary-replica-to-existing-sql-server-alwayson-availability-group/#comments

Thanks!

IP address for each subnet. Documented here: https://msdn.microsoft.com/en-us/library/hh213080.aspx#Prerequisites


100% index fragmentation I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them.
Rebuild ran for about 20 minutes.
Then I found a query to see how my indexes are looking

SELECT
t.NAME 'Table name',
i.NAME 'Index name',
ips.index_type_desc,
ips.alloc_unit_type_desc,
ips.index_depth,
ips.index_level,
ips.avg_fragmentation_in_percent,
ips.fragment_count,
ips.avg_fragment_size_in_pages,
ips.page_count,
ips.avg_page_space_used_in_percent,
ips.record_count,
ips.ghost_record_count,
ips.Version_ghost_record_count,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.avg_record_size_in_bytes,
ips.forwarded_record_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
AVG_FRAGMENTATION_IN_PERCENT, fragment_count

So most of my indexes have an average percentage fragmentation <1
But I have about 10 that are 85%, and another 10 that are 100%

I am no dba, and understand very little about what I am doing here, but surely this is very bad?

Also worth noting that the 100%'s are all primary keys

Am I in a world of trouble, or is my understanding of the stats incorrect?

note: worth mentioning my database is 24gb, and my drive has 46gb free out of 115gb

EDIT: as requested reindex script below

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name+']' AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR =     ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

results of original index fragmentation qry attached as requested
results

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them.

This is a knee-jerk response to a performance problem. Your page counts are less than 10 ! Rebuilding the indexes will be a least help in your case.

I would start by reading and understanding How to analyse SQL Server performance ?


Use specific array column in the where clause Having a function that returns an array and in a query we use the function to return results such as:

{"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"}
{"TUNDUMA RETURN (BORDER)","DEPARTURE DATE",29/01/2016,2,"ON THE ROAD"}

How can we add a WHERE clause that will filter by the 5th column of the array searching for the ON THE ROAD values only?

Is there a way to reference array[4]?

Tried with

where trip_status(tlid) <@ array['AT CHECKPOINT']::varchar

with trip_status() being the function that returns the array but am getting the following error:

Query failed: ERROR: operator does not exist: character varying[] <@ character varying
LINE 6: ...ere trip_closed(tlid)=false and trip_status(tlid) <@ array[ ... ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Does something like where trip_status(tlid)[4] = 'AT CHECKPOINT' exist?

You put the cast outside the array constructor - use ::varchar[] instead.  The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle.

But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to use those in a WHERE clause, for example.  Then construct your array directly before returning it to whatever that needs it.


Why MySQL Workbench creates more than two foreign keys for many to many relations? I'm using MySQL Workbench. I know that an additional table is needed to define a  many to many relation. But why sometimes workbench creates this table with more than two foreign keys?
Is it added managing performance issues?
How should I make queries with this structure?

For example this many to many relation is created automatically with three foreign keys:

If id is the primary key for pstx_project then you definitely don't need pstx_project_company_id in your many to many breakdown table. As you say, these tables just need their related tables primary keys (in which it's composite will become it's own primary key), and a separate foreign key against each referenced table (which might be composite also, depending on the amount of columns from the referenced table's primary key), with the addition of other optional columns you might want to add.

If company_id was part of the primary key of pstx_project in some point, then it would make sense to include it in your many to many table. Maybe you edited the primary key at some point?

Remove pstx_project_company_id from your many to many table as it will bring you problems with consistency, as it would be redundant. Also make sure that user_id with pstx_project_id inside user_has_pstx_project is a primary key so it enforces non-duplicates.

As a side note, I also note a loop in your design: there are 2 ways to get to a company from a user. While loops are possible in some scenarios, please review if it's correct for your case as you might have another redundant link between user and company.


Is it possible to define a function to prevent repeating lots of `case when` in transact sql? A meta post gives this code

Select top 500
(case
    when lower(title) like '%open source%' then 0
    when lower(title) like '%recommend%'   then 1
    when lower(title) like '%free%'        then 2
    when lower(title) like '%good%'        then 2
    when lower(title) like '%best%'        then 2
    when lower(title) like '%better%'      then 2
    when lower(title) like '%any%'         then 2
    when lower(title) like '%what%should%' then 3
    when lower(title) like '%which%should%'then 3
    when lower(title) like '%is there%'    then 3
    when lower(title) like '%looking for%' then 4
    else 5 end
),
left(title,25) --as [Post Link]

,ROW_NUMBER() OVER (

  PARTITION BY(case
      when lower(title) like '%open source%' then 0
      when lower(title) like '%recommend%'   then 1
      when lower(title) like '%free%'        then 2
      when lower(title) like '%good%'        then 2
      when lower(title) like '%best%'        then 2
      when lower(title) like '%better%'      then 2
      when lower(title) like '%any%'         then 2
      when lower(title) like '%what%should%' then 3
      when lower(title) like '%which%should%'then 3
      when lower(title) like '%is there%'    then 3
      when lower(title) like '%looking for%' then 4
      else 5 end
  )
  ORDER BY (case
      when lower(title) like '%open source%' then 0
      when lower(title) like '%recommend%'   then 1
      when lower(title) like '%free%'        then 2
      when lower(title) like '%good%'        then 2
      when lower(title) like '%best%'        then 2
      when lower(title) like '%better%'      then 2
      when lower(title) like '%any%'         then 2
      when lower(title) like '%what%should%' then 3
      when lower(title) like '%which%should%'then 3
      when lower(title) like '%is there%'    then 3
      when lower(title) like '%looking for%' then 4
      else 5 end
)) AS Number

from posts

where (title like '%library%'
   or title like '%tool%'
   or title like '%book%'
   or title like '%tutorial%')
and closeddate is null
and deletiondate is null

In Python, Java, and lots of other language, I can define a function to prevent repeating lots of case when. Is there similar syntax in transact sql?

Use APPLY, like the examples I show in: http://blogs.lobsterpot.com.au/2011/12/13/a-t-sql-tip-working-calculations/

...then you can use it everywhere else as a regular column - just a calculated one.

Select n.Num, left(title,25) --, etc
From posts
Cross apply (select case
  when lower(title) like '%open source%' then 0
  when lower(title) like '%recommend%'   then 1
  when lower(title) like '%free%'        then 2
  when lower(title) like '%good%'        then 2
  when lower(title) like '%best%'        then 2
  when lower(title) like '%better%'      then 2
  when lower(title) like '%any%'         then 2
  when lower(title) like '%what%should%' then 3
  when lower(title) like '%which%should%'then 3
  when lower(title) like '%is there%'    then 3
  when lower(title) like '%looking for%' then 4
  else 5 end as Num) n
-- where ...
Order by n.num


What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)? I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real examples of issues that can pop up as a result of doing so?

Looks like the Tags are even against me on this one:

If you're making regular backups of the log file or are running in SIMPLE recovery mode, the log file should mostly consist of free space; shrinking it will just truncate the free space from the log file.  This is usually safe to do, but you need to realize that the log file will likely just grow back to the previous size....when this happens the log will auto-grow, which can cause a performance hit every time it happens.

The only real reason for shrinking it is if you're low on disk space, if the log is filling up the drive you should consider adding additional disk space as a long term solution.


Pragmatic approach to k-means clusters with PostgreSQL I am looking for a super simple and very pragmatic approach to k-means clustering of questions using PostgreSQL database alone.

While I am fully aware that this method may not yield meaningful results if my assumptions are off, it may be a good and first attempt at categorizing my data.

Imagine a small online forum, where users are free to ask brief questions about ANY topic they want but without the need to categorize them, and other users need to be notified about a new question if it matches the topics of interest to which they have previously subscribed.

My plan is to first break down each incoming question into lexemes using to_tsvector but in all honesty I am a bit lost about what to do afterwards.

Even assuming I have correctly identified the k categories to which the question may be matched, how would I go about deciding whether a questions should fall into one (or more) category?

you could use the ranking returned from the text search as a cut-off to decide if the question matches a topic.
For each category you could hold a document with relevant terms and search the query text against these terms.

Btw, there's a nice extension called madlib (it's more of a utility package) - it contains many useful features/algorithms including topic analysis & clustering.
Take a look at madlib documentation


Shrinking log files on development I've been looking at getting some disk space back on some of our development machines.
These are machines that are not being backed up, not being developed on directly, and not being maintained particularly well.
Any recovery is done from PRD or PRE-PRD to the DEV environment, nothing on this particular DEV environment is critical.

The recovery models on these databases has to remain on FULL because it was so ordained (despite no actual backups being taken).
As these databases in some cases have been around for quite some time without backups, I'm looking into a proper way to shrink their logs.

I've found two approaches that might serve the purpose:

BACKUP LOG [databasename] TO DISK=’NUL:’

Followed by shrinking the log.

Or:

ALTER DATABASE [databasename] SET RECOVERY SIMPLE;
ALTER DATABASE [databasename] SET RECOVERY FULL;

Followed by shrinking the log.

My actual questions:
What are the differences between these two approaches?
Is there something I'm missing / overlooking?

For this scenario I prefer the method of doing (I added WITH NO_COMPRESSION just in case the server default setting is to use Compression since there is little point in using extra CPU when the resulting file isn't going to take up any space anyway):

BACKUP LOG [databasename] TO DISK = 'NUL:' WITH NO_COMPRESSION;

The reason I prefer this method over switching to SIMPLE and then back to FULL is that it doesn't change the overall backup process. This way you can have more consistency between your environments. It also allows you to have a testing environment for your backup process because it isn't different than what you have in Production. And if sending the file to NUL: presents too much of a difference, then just send it to a folder where the backup file can be deleted.

Flipping to SIMPLE and then back to FULL makes it harder to test DIFFerential backups since you need to have a FULL backup first. So if you do a FULL backup once a week and DIFF the rest of the week, flipping to SIMPLE and then back to FULL would work if done just prior to the weekly FULL backup, but not at any other time during the week. On the other hand, taking the LOG backup and either directing it to NUL: or deleting the backup file can be done at any point during the week.

Also, taking the LOG backup and either directing it to NUL: or deleting the backup file allows you to maintain a pre-grown LOG / .ldf file. And at that point you can use DBCC SHRINKFILE to enforce a maximum initial-size of the empty LOG file and not get stuck with a file that grew A LOT due to reindexing or something else.

PLEASE NOTE that getting rid of your LOG backups means that you will not be able to restore to points in time between your FULL / DIFF backups. Of course, if you don't have FULL / DIFF backups in the first place, then this is a moot point.


How do I interpolate months in PLSQL I'm trying to find a way to include all months in a result set
my query looks like this:
select to_char(changedate, 'YY-Month') Mon,
  sum(downtime) dt
from (select * from assetstatus
where assetnum in ('x','y','z')
  and siteid = 'xyz'
  and changedate between to_date('10/1/2019','mm/dd/yy')
                            and to_date('9/30/2020','mm/dd/yy')
  and exists (select 1 from workorder where worktype = 'RD'
                  and workorder.wonum = assetstatus.wonum
                  and workorder.siteid = assetstatus.siteid))
group by to_char(changedate, 'YY-Month'), to_char(changedate, 'YY-Mm')
order by to_char(changedate, 'YY-Mm');

but if i'm missing a month of data say December 2019 where no downtime was reported then it will return
19-October      91.85
19-November     26.55
20-January      0
20-February     29.1999999999999987
20-March        32.133333333333333666
20-April        1664.4333333333333
20-May          246.95
20-June         12.25
20-July         2.1
20-August       0
20-September    3

So I'm looking to have it include 19-December with 0 hours if nothing is present.
The dates are chosen dynamically based on user input one being a year prior to the later date.
using 12c drivers  if anyone has any thoughts I'd greatly appreciate it!

It is a calendar you need. How to get it? Using a row generator technique.
As I don't have your table, I'll use Scott's EMP and count employees hired in every month. Calendar I'm going to create will contain months between MIN and MAX hiredate (you can create any dates you want, of course).
EMP table's contents:
SQL> select empno, ename, hiredate
  2  from emp
  3  order by hiredate;

     EMPNO ENAME      HIREDATE
---------- ---------- ----------
      7369 SMITH      17.12.1980
      7499 ALLEN      20.02.1981
      7521 WARD       22.02.1981
      7566 JONES      02.04.1981
      7698 BLAKE      01.05.1981
      7782 CLARK      09.06.1981
      7844 TURNER     08.09.1981
      7654 MARTIN     28.09.1981
      7839 KING       17.11.1981
      7900 JAMES      03.12.1981
      7902 FORD       03.12.1981
      7934 MILLER     23.01.1982
      7788 SCOTT      09.12.1982
      7876 ADAMS      12.01.1983

14 rows selected.

SQL>

Query that does the job:
SQL> with
  2  -- MIN and MAX hiredate
  3  minimax as
  4    (select min(trunc(hiredate, 'mm')) mindat,
  5            max(trunc(hiredate, 'mm')) maxdat
  6     from emp
  7    ),
  8  calendar (datum) as
  9  -- calendar, based on MIN and MAX hiredate
 10    (select add_months(mindat, level)
 11     from minimax
 12     connect by level <= months_between(maxdat, mindat)
 13    )
 14  -- the final result
 15  select
 16    to_char(c.datum, 'yyyy-Month', 'nls_date_language = english') datum,
 17    count(e.empno) cnt
 18  from calendar c left join emp e on trunc(e.hiredate, 'mm') = trunc(c.datum, 'mm')
 19  group by c.datum
 20  order by c.datum;

DATUM                                            CNT
----------------------------------------- ----------
1981-January                                       0
1981-February                                      2
1981-March                                         0
1981-April                                         1
1981-May                                           1
1981-June                                          1
1981-July                                          0
1981-August                                        0
1981-September                                     2
1981-October                                       0
1981-November                                      1
1981-December                                      2
1982-January                                       1
1982-February                                      0
1982-March                                         0
1982-April                                         0
1982-May                                           0
1982-June                                          0
1982-July                                          0
1982-August                                        0
1982-September                                     0
1982-October                                       0
1982-November                                      0
1982-December                                      1
1983-January                                       1

25 rows selected.

SQL>


MySqli - select from db based on time difference with CURTIME() or SUBTIME() I have a db with some customers data. There I store their last login time as datetime.

I want to run a cron php script every min which will query mysqli for their last login datetime and if that was more than 20 mins in the past i will run some js popup etc (something like NOW() - last_login).

Anyway I am trying to query the db directly for that kind of users based on that 20 min time difference.

With php it's easy to filter results but I want to bring directly results from my mysql db without any intermediate process from php.

How can I accomplish this?

Ok, i 've found the solution and i want to share it with you.

SELECT * FROM tbl_name WHERE dt_column < DATE_SUB(NOW(), INTERVAL 20 MINUTE)

This brings all entries were last login  was more than 20 mins in the past.


Is two unrelated tables with foreign keys to a third table breaking first normal form? I was wondering if this breaks first normal form.

AddressTable
  Id           - PK
  Street
  City
  State

BusinessTable
  Id           - PK
  BusinessName
  AddressId    - FK

PersonTable
  Id           - PK
  FirstName
  LastName
  AddressId    - FK

The reason I ask is that I've heard that because any given field on the address table may be a business address or a person address, you have two different data types in the same column.  Under that argument, I'd need to have a BusinessAddress and a PersonalAddress table.  At the same time, this database seems to be very well ordered, and I'm use to databases that break first normal form being far less sensible, and it doesn't appear easy for an anomaly to appear.  The closest thing to an anomaly appearing would be if a Business and Person linked to the same address, which then creates a problem if one of them updates their address but the other doesn't (or they could point to two different records with the same data in each except for the id, which might also be an anomaly).

No, it's fine.

Consider the situation where you have owners of things. Many types of thing can have an owner and it's not worth splitting owners into types of owner.


How to create Postgres DB with case insensitive collation I am using the postgres version 9.4.1 64-bit on windows 7 64-bit.
I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and other forums as well but even after trying out all those options my DB is still case sensitive and my search queries returns only partial results.

Does any one have success getting this work on windows environment?

Note: I am aware of the ILIKE operator but that option is not going to be considered by Architect team as we have a clear requirements that Database storage should be CASE INSENSITIVE by default. We do ot have any scenarios where we will need case sensitive search.

This may be too late for the original poster, but for completeness, the way to achieve case insensitive behaviour from PostgreSQL is to set a non-deterministic collation. This is only for Postgres 12.

Details are described in docs here. Reproducing relevant portion for completeness:

  A collation is either deterministic or nondeterministic. A
  deterministic collation uses deterministic comparisons, which means
  that it considers strings to be equal only if they consist of the same
  byte sequence. Nondeterministic comparison may determine strings to be
  equal even if they consist of different bytes. Typical situations
  include case-insensitive comparison, accent-insensitive comparison, as
  well as comparison of strings in different Unicode normal forms. It is
  up to the collation provider to actually implement such insensitive
  comparisons; the deterministic flag only determines whether ties are
  to be broken using bytewise comparison. See also Unicode Technical
  Standard 10 for more information on the terminology.

  To create a nondeterministic collation, specify the property
  deterministic = false to CREATE COLLATION, for example:

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

  This example would use the standard Unicode collation in a
  nondeterministic way. In particular, this would allow strings in
  different normal forms to be compared correctly. More interesting
  examples make use of the ICU customization facilities explained above.
  For example:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

  All standard and predefined collations are deterministic, all
  user-defined collations are deterministic by default. While
  nondeterministic collations give a more “correct” behavior, especially
  when considering the full power of Unicode and its many special cases,
  they also have some drawbacks. Foremost, their use leads to a
  performance penalty. Also, certain operations are not possible with
  nondeterministic collations, such as pattern matching operations.
  Therefore, they should be used only in cases where they are
  specifically wanted.


sp_send_dbmail issue when using variable for subject line When using a variable for the subject line for a database trigger email, I get the variables back in the subject line but they look like this:

<i loan_id="6686"><cm cust_name="John Bonham  IIIII"><ld loan_number="5555"><e FAMILIAR_NAME="Test User"/></ld></cm></i>

Is there a way to clean it up so that it looks better? For instance remove the <>?

   SET @subject =
    CAST ( ( SELECT top 1 i.loan_id,cm.cust_name,  ld.loan_number,  e.FAMILIAR_NAME
              FROM inserted i inner join deleted d on i.LOAN_ID = d.Loan_ID and i.STATUS = 4
                inner join Status s  on i.STATUS = s.Status_ID
                inner join Loan_Details ld on i.LOAN_ID = ld.loan_id and ld.LOAN_ID = d.LOAN_ID
                and ld.STATUS = s.Status_ID
                inner join Employee e on ld.OFFICER = e.EMP_ID
                inner join REL_CUST rc on rc.LOAN_ID = ld.loan_id
                inner join Customer_Master cm on cm.CUST_ID = rc.CUST_ID and cm.CUST_ID = rc.REL_TRAN_ID
                and relationship_id = 1

          WHERE i.STATUS <> d.STATUS and i.STATUS = 4 and relationship_id = 1 and d.STATUS <>4
          ORDER BY i.loan_id ASC
          FOR XML PATH('')
) AS NVARCHAR(MAX) ) ;

An option would be to enhance the FOR XML PATH('') to pull the actual node values out.

See the following examples (with the results of both at the bottom).

(Original represents what you are basically doing now)

(Updated uses the enhanced FOR XML PATH('') to pull the actual node values out).

set nocount on
Declare @T Table (LoanId int, FirstName varchar(100), LastName varchar(100))
insert into @T(LoanId,FirstName,LastName) values
    (12345,'John','Smith'),(54321,'James','Smith')

declare @subject varchar(100)

--Original statement
SET @subject = cast((
            SELECT TOP 1 LoanId, FirstName, LastName
            FROM @t
            FOR XML PATH('')) AS NVARCHAR(MAX) ) ;

print @subject

--Updated statement
SET @subject = (
            SELECT TOP 1 'LoanId = ' + convert(varchar(10),Loanid) + ',' + 'FirstName = ' + FirstName + ',' + 'Lastname = ' + lastname
            FROM @t
            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');

print @subject

<LoanId>12345</LoanId><FirstName>John</FirstName><LastName>Smith</LastName>
LoanId = 12345,FirstName = John,Lastname = Smith


MongoDB cluster deployment I am new to MongoDB. I am trying to deploy a MongoDB cluster. I am following:

https://docs.mongodb.org/manual/tutorial/deploy-shard-cluster/

I initiated Config Server Replica Set and after that I tried to addShard but I receive always error:

mongos> sh.addShard("rs1/10.1.10.12:27017,10.1.10.13:27017,10.1.10.14:27017")
{
        "ok" : 0,
        "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rs1",
        "code" : 133
}

I have tried to add a shard but I receive the following error:

mongos> sh.addShard("firstRS/centos-m-1.ovm:27017")
{
        "ok" : 0,
        "errmsg" : "Cannot add firstRS/centos-m-1.ovm:27017 as a shard since     it is part of a config server replica set",
    "code" : 96
}

In replicaSet I see:

rs.conf()
{
        "_id" : "firstRS",
        "version" : 3,
        "configsvr" : true,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "centos-m-1.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "centos-m-2.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "centos-m-3.ovm:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("572e55c00408eaa72066cef3")
        }
}

I cannot understand. Could you help me?

MongoDB shell version: 3.2.6
connecting to: 10.1.10.12:28017/test

mongos> rs.status()
{
        "info" : "mongos",
        "ok" : 0,
        "errmsg" : "replSetGetStatus is not supported through mongos"
}

If I connect to config server and I give: rs.status() I found all nodes with one node highlighted as PRIMARY. After than I try to connect to a mongos instances and I try to add a shard:

sh.addShard( "rs1/10.1.10.14:28017" )

and I get error I reported previously and in log I found:

  2016-05-06T12:37:33.787Z W NETWORK  [conn1] No primary detected for set rs1
  2016-05-06T12:37:33.787Z I NETWORK  [conn1] All nodes for set rs1 are down. This has happened for 1 checks in a row. Polling will stop after 29 more failed checks

creat three replica set with different port number and then try


Limiting total number of records in a table with a nightly job We have a table that stores audit records (Log in, log out that kind of thing), we already have a nightly job that archives any data that is older than 3 months.
However every week or two we get spikes in traffic and the table grows rapidly (compared to the tables normal growth).   We have also noticed if we get too many rows in the table the performance goes off a cliff.
So what I'm looking for is an optimal way to keep a maximum of 3 million rows in the table as part of either as part of the nightly job above or as a separate nightly job.   This job may be deleting upwards of half a million records a night, so performance is what we are looking for as the table is always being written to.
The table has a primary key of a long identity, and we are running SQL Server 2012.
We have another work thread to fix our performance issues with this table, this is just a stop gap until that work has been completed.

Thinking outside the box a little bit, but instead of a nightly job, what about a Table Trigger to handle it sooner so there's a much smaller batch to delete?
Basically you can have an after insert Trigger that checks the new size of the table, and deletes the amount of rows > 3 million. I'd imagine this would always be 1 row since the Trigger would fire on every INSERT. Deleting 1 row should be very quick, but depending on the existing busyness of the table, I'm not sure if the additional contention for deleting will help or hurt more than a larger batch job at night. You'd have to test both ways and compare.

Another trick you could use is create an Indexed View on top of the table that only brings back the latest 3 million records, and do your querying off the View instead of the Table. Then the Table you can prune and archive less frequently, in a routine maintenance window perhaps.
As far as determining the latest 3 million rows, you can store the max value of your identity column of your Table in another Table that only has 1 row to keep track of this. Then in your View you'd INNER JOIN to that Table ON AuditTable.IdentityColumn >= MaxAuditIdentityValueTable.Value - 3000000.
You can accomplish storing that max identity value a number of ways, one being an after insert Table Trigger as mentioned above. In this case, it should be an even more efficient Trigger because you're not locking your audit table when it fires (since you're not deleting from it), rather you're locking the MaxAuditIdentityValueTable instead, and since there's only 1 record to UPDATE in that table, it should be extremely fast. (By the way, please pick a better name than my example of MaxAuditIdentityValueTable, I suck at naming things. :)


Database replication with ability to revert to previous states I want to setup a development database such that it'll initially be a mirror of the database used in production.  At fixed points during the day (maybe midnight or something) and/or manually, I want it to be sync'd once again with the production DB.

My goal is to have a database I can play and mess with, with no lasting consequences.  I've looked at replication, and it doesn't seem to be a good fit for this scenario.  See, what I thought initially is that I could set up a slave, dump it, do my work, then use the dump to revert it to its previous (pristine?) state, before syncing with the master.  Repeat when needed.  While I might be able to get away with this with relatively small DBs, 8 gigs worth of dumps tend to take a while to be generated and imported.  Plus I'd like to be able to have something that will scale to several devs.

Any hints as to where I should look next?

You can look into MySQL snapshots using the file system. More details in this link. Snapshots are very quick to create, and can be rolled back to.


SSRS, Limit Fixed number of Columns in Matrix within a Tablix - issue in sorting matrix data I am converting a crystal report to ssrs report, i am trying to implement "Format With Multiple Columns" feature of crystal report to ssrs.

I have a Main tablix that has two Two Groups

1st Group - PolicyType
2nd Group - WeekNumber

Under Week Number group, i have inserted a rectangle and a matrix within it. The matrix will show the policy number generated in every week. The matrix should only contain 6 columns and the grouping for matrix is as below

Row Grouping : Ceiling(RowNumber("WeekNumber") / 6)
Column Grouping : Ceiling(RowNumber("WeekNumber") MOD 6)

I need a output like below

InsuranceType1
Week 1
Policy Number
1001 1002 1003 1004 1005 1006
1007 1008 1009 1010
Week 2
Policy Number
2001 2002 2003 2004 2005 2006
2007 2008 2009 2010

 I am able to get a similar output but the sorting within the matrix is not working, ie., i get the following result
InsuranceType1
Week 1
Policy Number
1002 1003 1001 1004 1007 1006
1005 1008 1009 1010
Week 2
Policy Number
2008 2009 2003 2004 2005 2006
2007 2001 2002 2010

 i have not used any sorting on the Matrix, nor in the group of matrix the only sorting is on the main tablix groups PolicyType and WeekNumber and both are in ascending order.

What i have tried is:

Sort Matrix using PolicyNumber, but that will only output first row and first column (very strange!!!).
Add an additional sort of PolicyNumber in Group WeekNumber (did not work either)

 i have no option of modifying the stored procedure to return the row number back to the report as the client is strict on SP modification as they want to get this report backward compatible.

Please help!!

I have managed to resolve this issue, thought i'll be helpful for others. The order needs to be on the main tablix and not on the inner group or Matrix.


SQL count(*) grouped by date range I have a table as follows:

DROP TABLE IF EXISTS #PersonnelRecord;

CREATE TABLE #PersonnelRecord
(
    person_id int NOT NULL,
    company_id int NOT NULL,
    entry_date date NOT NULL,
    exit_date date NOT NULL,

    CONSTRAINT PK_PersonnelRecord PRIMARY KEY (person_id)
);

INSERT INTO #PersonnelRecord
    (person_id, company_id, entry_date, exit_date)
VALUES
    (1, 24, '2004-03-17', '2010-12-31'),
    (2, 24, '2011-04-18', '2019-11-28'),
    (3, 25, '2017-02-10', '2019-10-20'),
    (4, 34, '2004-03-17', '2010-12-31'),
    (5, 24, '2004-03-17', '2999-01-01'),
    (6, 24, '2010-03-20', '2999-01-01');

SELECT *
FROM #PersonnelRecord pr
ORDER BY pr.person_id;

db<>fiddle link

So a table that shows when people have entered and exited a company. 2999-01-01 means the person is still at the company.

I would like to add a count to each row of how many people are working at the company at time of the exit date. The result would be:

|-----|
|  3  |
|-----|
|  3  |
|-----|
|  1  |
|-----|
|  1  |
|-----|
|  2  |
|-----|
|  2  |
|-----|

How could I achieve that? Thanks for the help.

You can use the APPLY operator to count the number of employees active at the termination date.  As I looked at the question at first I missed that there were different companies, so once I added AND company_id = pr.company_id to the query I get the result you are expecting.

SELECT * FROM #PersonnelRecord pr
OUTER APPLY (
    SELECT count(person_id) as person_count FROM #PersonnelRecord
    WHERE pr.exit_date > entry_date
        AND pr.exit_date <= exit_date
        AND company_id = pr.company_id
     ) a
ORDER by person_id


to_tsquery - Does it always accept quert text? I am very new to FTS features in Postgres, in fact, have started exploring them since yesterday only.
I wanted to pass list of values stored in columns of a DB tables instead of query text in to_tsquery.
Something like this:
select  *
from t1
 where to_tsvector("col1") @@ to_tsquery((select col2 from t2 where id = 123))

Not sure if this is a possibility? Is there any alternative by which such kind of implementation can be achieved using to_tsquery?

If the values can be arbitrary strings, you can get errors:
SELECT to_tsquery('a&');
ERROR:  no operand in tsquery: "a&"
SELECT to_tsquery('''q');
ERROR:  syntax error in tsquery: "'q"

Use plainto_tsquery, which is designed to accept plain text strings.


Does daily pg_dump mess up postgres cache? I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory.
Performances are way worse than on my previous provider, and new provider claims this is because the pg cache has to been "warmed up" everyday after automatic pg_dump backuping operations occuring in the night.
Geospatial queries that would normally take 50ms sometimes take 5-10s on first request, and some that would run in 800ms take minutes.
Is there something else looming or is the technical support right ?
If so, should I disable daily backups ? Or can I somehow use a utility function to restore the cache ? (pg_prewarm ?)

If you don't want to have backups, then sure, disable them.  Of course, then you won't have backups.  We cannot tell you if your data is valuable to you or not.  But why would you pay someone to host data you don't care about?  Maybe you can just turn them off for a day, and see if that actually fixes the problem.
I don't think that pg_dump does anything special that should drive data out of the cache.  It does access all of your table data, but PostgreSQL server has code to prevent a sequential scan of a large table from driving all other data out of cache.  This should be just as effective for pg_dump as it is for anything else.  But if you have a very large number of small tables, the cumulative effect of reading all of them might be enough to drive everything else out of cache, as I think the antispoliation code applies to each table individually, not all of them collectively.
pg_prewarm is certainly an option.  Another would be a cron job that just runs the query in question every morning.


Getting results without using IN Oracle has a limit on the arguments in the IN statement of 1000, I believe, is there a way around this? If I had a statement similar to this (with 1000 ids or more):

SELECT COUNT(*)
FROM "VERSION"
WHERE "VERSION"."VERSION_ID" IN
      (59156, 57045, 54779, 54048, 54048,
       --- long list ---
       12648, 11160, 11160, 11160, 11160)

How could I write simple count statement?

Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table.

create table tmpIds(id int not null)
insert into tmpIds (59156)
insert into tmpIds (57045)
etc...

SELECT COUNT(*)
FROM "VERSION"
INNER JOIN tmpIds ON
"VERSION"."VERSION_ID" = tmpIds.id

drop table tmpIds


Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER) DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for punctual, on demand backups in complement to the more robust, scheduled ones.

Pretty much I want to know if there will be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a transaction.

Most tables are small, so we could just take the risk, but there are a couple that worries me becuase of their size. I don't mind waiting for the transaction to get done, what worries me is getting a read error and failing to get a response from the query

That's about it, if you know something please let me know

+1 to @RolandoMySQLDBA of course for another valiant answer. But more to the point of your question:

  ...will [there] be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a [write] transaction.

YES.

You can't get a consistent backup even for a single MyISAM table unless you do some type of locking to prevent writes. Rolando gave a pretty thorough answer with options available to you for locking.

One other option that people use for backing up MyISAM data: LVM snapshots. See http://www.lenzg.net/mylvmbackup/ for a great tool to assist with this.

The final recommendation is to stop using MyISAM, and use InnoDB instead. Then you can do fast, non-locking physical backups with Percona XtraBackup.

Re your comment:

Because reading through a large file isn't instantaneous or atomic. While your backup is progressing through the table, other concurrent updates could change both rows that your backup has already read, and rows that your backup hasn't reached yet.

Take a textbook example for transaction behavior: I do a bank transfer by debiting my bank account and crediting your bank account. My bank account is stored on a row that is physically early in the file, and your bank account is stored on a row later in the file.

While this is going on, the backup is reading through the file, and it has read up to a mid-point at the time our transaction happens. When we restore, we get my original account balance, without the debit applied, because the backup had already passed that point when the debit occurred (and it isn't able to go backwards). But the restore includes your updated account balance, because the backup got to that point in the file after we increased your balance.

Ergo, free money! ;-)

MyISAM solves this by requiring the table to be locked against updates while it's doing a read.

InnoDB solves this by keeping multiple versions of rows, for as long as a reading transaction needs to see them for the sake of a consistent view of the database. So anyone can update the data without waiting, even though the backup is in progress.

Percona XtraBackup solves this in a slightly different way: it can go backwards, in a way. While it's reading the data file, it keeps checking the transaction log continually, to see if there are any late changes it needs to include. These changes may apply to parts of the datafile that Percona XtraBackup has already read. But as long as it gets the data file plus any changes that were logged since the backup started, it can reconstruct the full database.

But that only works for storage engines like InnoDB, that create a reliable transaction log. Percona XtraBackup can also back up MyISAM, but only by using locking, like any other backup tool.


Clustered Index and PK - Two different Objects? I have a table which has a Clustered PK as follows:

CREATE TABLE [dbo].[PanelCcmRawData] ADD  CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED
(
    [CcmRawDataId] ASC,
    [LineageId] ASC
)

I understand that when you create a Clustered Index, the table itself is the clustered index. However, when you make the Clustered Index the PK as well, it seems SQL Server creates an actual index file. I noticed this because when I ran a query to see all the indexes on my table, the number of seeks on the index and the size etc., I saw an entry for PK_CcmRawData with a size of 331GB.

I was a bit surprised to see this. I'm curious why, when creating a Clustered Index that is also the PK, SQL Server creates an index file. I would have thought the table itself would act as the Clustered Index and PK. So there appears to be something I'm not understanding.

The leaf level of the Clustered index is the table.  You still have the other levels of the B-Tree  (root, intermediate) as a part of the clustered index which account for why there is an index for your PK on the table.


Query requires 'Update statistics' very often in SQL Server We have a few tables in our database. Since most of our queries are date based, we have constructed clustered index on 'date + primary key (surrogate)' combination and we enforce joins on date as well in our queries. We have enabled 'page compression' on these tables/indexes.

Data is bulk inserted into these tables in the order of ~ 200k rows per day.

We have a query which joins a few of these tables and has date filter for two consecutive days.

Problem :
This query times out(no data returned even after minutes) for the latest dates for which data was bulk inserted, but works perfectly fine for the older dates. Further debugging revealed that the execution plan chosen by the optimizer changes (and is not optimal) when we pass latest two dates in the filter.

Currently, we are able to solve this issue by just running 'Update statistics' on these tables, post which optimizer chooses good old plan for the latest dates as well.

I noticed from the stats that, out of ~5 million rows, only ~300k rows are sampled for stats purpose.

Has anyone faced a similar problem of having to update statistics so often. Is there a way, I can fix this problem or this can be avoided?

Data is bulk inserted into these tables in the order of ~ 200k rows per day... out of ~5 million rows

That's 200k new rows that do not have any stats for, so querying for those values will make the optimizer come up with scans because of the outdated stats do not inform it that the new values are in the range sampled.  Force a stats update after the bulk insert.

Not sure autostats can help given that 200k inserts fall bellow the 5 million 20% cadinality trigger, see Statistical maintenance functionality (autostats) in SQL Server.


how to alter sequence in db2 stored procedure by another user I am using DB2 LUW Version 10.5.0.5 on Linux (x86_64)
I have written a procedure to alter a sequence to restart with a certain number. The procedure takes the sequence name and the number to restart with as parameters.
The procedure got created successfully . I granted a developer connect access on the database and execute access on the procedure. But he is getting SQL -551 (permission issue for alter sequence sql) when he executes the procedure.
Create table works perfectly fine when it is executed in a procedure by anyone who even doesnt have create table access. WHy alter doesn't work then? Is there a way to allow developers to alter a sequence in a database without granting alter access on the sequence itself?

Effect of DYNAMICRULES bind option on dynamic SQL

You must set the DYNAMICRULES bind option to BIND before the routine creation to make dynamic sql statements of the routine execute with the routine creator authorization. They are executed with authorization of a user who runs the routine otherwise.
You can do it by calling the following statement before the CREATE OR REPLACE PROCEDURE statement in the same session:

CALL SET_ROUTINE_OPTS('DYNAMICRULES BIND')

But you are not able to execute dynamically the following statements with such a bind option as described in Table 2. Definitions of Dynamic SQL Statement Behaviors:

GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE

This means, that you are not able to run dynamically ALTER SEQUENCE with an authorization different from the invoker's one.
You may grant ALTER privilege on sequence to your developer to resolve the issue.


ERROR 1 (HY000): Can't create/write to file (Errcode: 13 - Permission denied) I've been struggling for a couple of days with this now and have been trying to use this thread amongst other google searches to resolve it but can't seem to get it to work.

When I run this SELECT query (Ubuntu Server 15.10 & MySQL 5.6)...

SELECT column1 ,column2
FROM table
INTO OUTFILE '/home/user/mysql_temp/file.csv';

I get this permission error...

ERROR 1 (HY000): Can't create/write to file '/home/mysql_temp/file.csv' (Errcode: 13 - Permission denied)

From my searching, I understand this to be a system permissions issue rather than a MySQL problem but I can't seem to find a solution which fixes it for me.

Checking apparmor shows that mysqld IS in enforce mode...

apparmor module is loaded.
6 profiles are loaded.
6 profiles are in enforce mode.
   /sbin/dhclient
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/sbin/mysqld
   /usr/sbin/tcpdump
0 profiles are in complain mode.
2 processes have profiles defined.
2 processes are in enforce mode.
   /sbin/dhclient (676)
   /usr/sbin/mysqld (1285)
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

So I edited /etc/apparmor.d/usr.sbin.mysqld and added the following lines and then restarted apparmor...

/home/user/mysql_temp/ rw,
/home/user/mysql_temp/* rw,

I also changed the owner:group of mysql_temp to mysql and gave it full permissions...

sudo chown mysql:mysql mysql_temp

...

drwxrwxrwx 2 mysql   mysql       6 Apr  1 22:02 mysql_temp

After this, I still receive the same error.

Have I missed something? Misunderstood some of the instruction? Would be grateful for advice. Thanks.

Not sure if this applies to your situation but I ran into similar problems on RHEL 8.
First gotcha is that systemd was configured to run the MySQL service with the privateTmp option (see https://www.freedesktop.org/software/systemd/man/systemd.exec.html#PrivateTmp=).
This means that when you write to /tmp it actually attempts to write to a directory named something like /tmp/systemd-private-<random>-mariadb.service-<random>
Secondly the directory you write to needs to be owned by mysql (and group mysql).
Lastly the SELinux context needs to be correct as well when SELinux is Enforcing.


Logical reads different when accessing the same LOB data Here are three simple tests that read the same data, yet report very different logical reads:

Setup

The following script creates a test table with 100 identical rows, each containing an xml column with enough data to ensure it is stored off row. In my test database, the length of the xml generated is 20,204 bytes for each row.

-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
    DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
    ID integer IDENTITY PRIMARY KEY,
    X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;

SET @X =
(
    SELECT TOP (100) *
    FROM  sys.columns AS C
    FOR XML
        PATH ('row'),
        ROOT ('root'),
        TYPE
);

INSERT dbo.XMLTest
    (X)
SELECT TOP (100)
    @X
FROM  sys.columns AS C;

-- Flush dirty buffers
CHECKPOINT;

Tests

The following three tests read the xml column with:

A plain SELECT statement
Assigning the xml to a variable
Using SELECT INTO to create a temporary table

-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DECLARE @X xml;

SELECT
    @X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
    DROP TABLE #T;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT
    XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;

Results

The output is:

=== Plain SELECT ====
Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0,
    lob logical reads 795, lob physical reads 37, lob read-ahead reads 796.

=== Assign to a variable ====
Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0,
    lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

=== SELECT INTO ====
Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0,
    lob logical reads 300, lob physical reads 37, lob read-ahead reads 400.

Questions

Why are the LOB reads so different?
Surely the exact same data was read in each test?

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case:

Summary

The numbers are different because:

The select reads the LOB in packet-sized chunks
The variable assignment test does not read the LOB at all
The "select into" test reads the LOB in whole pages

Detail

Plain SELECT

The Clustered Index Scan does not read any LOB data. It only assigns a storage engine LOB handle. The handle isn't used until control returns to the root of the plan.

The current row's LOB content is read in TDS packet sized chunks and streamed to the client. Logical reads count the number of times a page is touched, so:

The number of reads reported equals the number of chunked reads performed, plus one for each time a LOB page transition occurs.

For example: A logical read is counted at the start of each chunk as the process touches the page corresponding to the current position of the stream. Where packets are smaller than a database page (the usual case), several logical reads are counted for the same page. If the packet size were so large that the entire LOB could fit in one chunk, the number of logical reads reported would be the number of LOB pages.
Variable assignment

The Clustered Index Scan assigns a LOB handle as before. At the root of the plan, the LOB handle is copied to the variable. The LOB data itself is never accessed (zero LOB reads), because the variable is never read. Even if it were, it would only be via the LOB handle last assigned.

There are no LOB reads because the LOB data is never accessed.
SELECT INTO

This plan uses the bulk rowset provider to copy the LOB data from the source table to the new table. It processes a complete LOB page on each read (no streaming or chunking).

The number of logical reads corresponds to the number of LOB pages in the test table.


How can I disable swappiness just for Mariadb and not for other applications on the same EC2 Instance? I have an EC2 instance where DB(Mariadb) runs on the same EC2 instance as the other applications.
MySQL documentation suggests to disable swappiness since DB doesn't like swap. How can I disable swappiness just for Mariadb and not for all other users/apps ?
Thanks
Moe

Plan A (if possible):  Use "huge pages" for the buffer_pool.  They can't be swapped, thereby helping keep from swapping MariaDB.
Plan B:  Use swappiness = 1 -- This, in theory, discourages the OS from swapping any process.  It will still swap if it is absolutely necessary.  At that point, perhaps it is better to slow down some process(es) rather than crashing?


Oracle shrink space release space to operational system in one step? Does oracle release space to operational system when is issued alter table ... shrink space (one step) or just after move HWM (two steps) alter table ... shrink space compact?

Table Shrinking is not about freeing space for OS, but replacing the blocks freed due to UPDATE and DELETE operations, with the used blocks. Thus, reducing the load on the database during table scans. Moving HWM does not 'give back' the free blocks to OS, it just indicates where the used blocks end, therefore Oracle knows there is no data after that.
Managing OS space for a database is done in OS layer, if you want to free up some space in OS, and you know that there are some free blocks inside Oracle datafiles, you have to take a dump using Oracle Datapump (expdp and impdp), drop the datafile, re-create the datafile with AUTOEXTEND option and then import your dump.


SQL Temporal Tables Include Current State We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables.

We noticed that history tables only include history. We want to create a table which has history and current state (with EndDate being Null or 12/31/9999).
Is there any way to perform this, without creating a view to UNION current and history tables?

If I have to go with a UNION view, did SQL Server optimize internals, so there would not be any performance issue, for 50 million rows? I have to give the datawarehouse to customers and executive management, there should not be any noticeable difference.

 CREATE TABLE dbo.Department
    (
        DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
        DeptName VARCHAR(50) NOT NULL,
        ManagerID INT NULL,
        ParentDeptID INT NULL,
        SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
          CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
        SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
          CONSTRAINT DF_Department_SysEndTime
        DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
        PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Temporal tables support the FOR SYSTEM_TIME syntax. One of its options effectively does the UNION for you. From the documentation:

SELECT * FROM my_table
FOR SYSTEM_TIME ALL;

  ALL   Returns the union of rows that belong to the current and the history table.


How to fully recreate database in MS SQL server 2016 from exported data Recently I get many consistency errors very often in my ~40Gb database. Of course I use DBCC CHECKDB, even with REPAIR_ALLOW_DATA_LOSS (when it asks), but then again I get errors, even after read-only operations.

Database itself is stored on SSD drive which is ok, so I suspect there are some hidden errors in database file.

So, the full cycle: I have MSSQL 2016 on local PC, with only 1 user attached. Every day I get data (~1.5M records) in custom format, backup base and insert data to the base (non-bulk inserts, indexes on). During that operation no one touches the base. Later from time to time I make high load queries (different statistics). And often I get consistency errors, check sum errors, index errors (don't have exact output stored in the moment). Index errors can be corrected by rebuilding indexes; others can only be fixed with DBCC CHECKDB using the REPAIR_ALLOW_DATA_LOSS option. After that, it works again - for a little while.

I don't believe the problem is with my SSD drive. I have tried different drives on this PC over the past few years, with no change in behavior.

I suspect that, even after running DBCC CHECKDB successfully, there are some hidden errors in file structure which lead to new errors later. So I want extract only the data to linear file and then import it. Standard Export wizard exports hundreds of lines and then stopped without error.

Is there a correct and robust way to export the whole DB to text/binary file(s) (but not mssql) and then recreate database without data losses from those file(s)? I tried backup-restore, but it looks like database consistency problems persist through restoration.

This is only a summary of some vague assumptions as a lot of details that should be added to the question were either discussed in a chat or have not yet been asked.

** Before you continue**

Create a database backup (WITH CHECKSUM) and store this database in a safe location. Even better: Seeing as your disk might be faulty, backup the database (WITH CHECKSUM) to an external drive and store in a safe location.

Possible reasons for data corruption on data growth

Your SSD disk has corrupt areas. DBCC CHECKDB... corrects the data (albeit with REPAIR_ALLOW_DATA_LOSS). Adding data places it in the corrupt areas of the SSD even though the SSD controller unit should avoid corrupt areas (once marked).

Solution: Check SSD S.M.A.R.T. parameters for errors and/or replace SSD.

Reference: How to See If Your Hard Drive Is Dying with S.M.A.R.T. (How-To Geek Site)

Your motherboard's disk controller (SATA interface) is having issues handling the large amount of data.

Solution: Replace disk controller and/or update disk controller driver.

The disk and/or disk controller is set to use a WRITE CACHE mechanism which is unable to keep up with the import of large amounts of data. The corruption is occurring on flushing.

Solution: Turn off (disk) cache mechanisms.

Reference: Disable Windows Disk Write Cache for Data Integrity and Better Performance (Long White Virtual Clouds)

General Reference: Data corruption (Wikipedia)


Automate: importing xls to table (without microsoft excel) I am importing 5 different reports from payment sites (paypal, worldpay, etc)

It works like a charm using csv!

BULK

INSERT Report_Table

FROM '\\Files_Server\departments\finance\Paypal.csv'

WITH

(

FIRSTROW=2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '0x0a'

)

But there is this one site that gives us an .xls file (Excel 97-2003 Worksheet).

I can’t use SSIS because in the server I don’t have excel installed and I don’t have permission to install it… My question is, if I convert it myself from xls to csv, will I be able to BULK INSERT it easlly, or it will contain garbage remaning from the xls to csv conversion?!

Options I see:

1-      ssis (I can’t install excel on the server).

2-      convert xls to csv and then bulk insert? (can I?)

What other options do you see/recommend?

You don't need to install Excel on the server to import with SSIS, you only need the "Access Database Engine", which is simply the data access layer:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

We definitely don't have Excel installed on our database server, and Excel imports work just fine with this.


database design - psychometric testing layout Good Afternoon,

I'm working on the table layout for a SQL database that will store individual and test information.  This includes individual identifying information like Name, medical id, date of birth, etc, and test specific information - specifically raw and standard scores from a variety of psychometric tools.  For the purposes of this question, I'll be providing the following sample tables:

Indiv_Information;
Test_Log;
Test_1;
Test_2;
Test_3

I have some questions about the best way to link individual information to these test scores.  Option 1 looks like this:

In this example each test logged is linked to the medical id of the individual who took the test.  My only concern with this layout is that if I want to create a display a list of all tests a user has taken I have to scan through all of these tables, and currently there are 20+

Option 2: Create a test log table and link that to multiple test tables that contain the scores for each test.  Looks like this:

It's much easier to generate a list of all tests each individual has taken with this setup, but I'm not sure this is a good setup for reporting.  The main problem here is that the field "assessment_id" is linked to multiple tables, and the only way to specify which score data to join to the test log table is to use "test_name_id" direct traffic.  I don't even know if this is possible.

I'm looking for opinions on which of these two layouts to choose.  Am I close to something optimal, or completely off base.  I'm not too experienced at database design yet.

Cheers, and have a great weekend!

Based on your information, let have a draft ERD like below:

TEST
+ ID (PK)
+ NAME (UK)
+ ...

TEST_QUESTION
+ TEST_ID (PK)
+ QUESTION_ORDER (PK)
+ QUESTION_CONTENT
+ ...

INDIVIDUAL
+ ID (PK)
+ MEDICAL_ID
+ ...

TEST_LOG
+ ID (PK)
+ INDIVIDUAL_ID (FK to INDIVIDUAL)
+ TEST_ID (FK to TEST)
+ ...

If we're talking about to optimize the reporting, we should denormalize the database structure, that means to accept the data redundancy.
In that case, we can merge TEST and TEST_QUESTION into one table.

Hope this help for an idea.


Mysql MYISAM table keeps crashing I have a Mysql server where the same big table keeps crashing now and then, usully during high load. The table in phpmyadmin gets marked as crashed and need to be repaired. Usually i have to do a myisamchk -r table.MYI to repair it which takes quite a while.
That table is about 6.4 GB with 23.500.000 rows in it and running MYISAM. Is my best bet to convert that table to INODB and how much will that help? or is there anything i can do to my MySQL config to help?
The server has 8GB of RAM and My MySQL config looks like this.
[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]
log-error=/var/lib/mysql/server.servername.com.err
performance-schema=0
table_open_cache=2000
innodb_strict_mode="ON"
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
open_files_limit=40000

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 64M
myisam-sort-buffer-size        = 64M
myisam-recover-options         = FORCE

# SAFETY #
skip-external-locking
max-allowed-packet             = 128M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 32
max-connections                = 500
thread-cache-size              = 286
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 512
group-concat-max-len           = 1048576

# INNODB #
#innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-log-buffer-size         = 16M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 4G

# LOGGING #
# log-queries-not-using-indexes  = 1
# slow-query-log                 = 1
# slow-query-log-file            = /var/lib/mysql/mysql-slow.log

max_allowed_packet=268435456
innodb_file_per_table=1
[mysqldump]
quick
max_allowed_packet             = 128M

[myisamchk]
key_buffer_size                = 768M
sort_buffer_size               = 256M
read_buffer                    = 2M
write_buffer                   = 2M

[mysqlhotcopy]
interactive-timeout

If OOM triggers the "crashes", then there is too much running on that machine and/or the processes are using too much ram.
It sounds like you have a mixture of MyISAM and InnoDB tables:
innodb-buffer-pool-size        = 4G  -- change to 3G

That might decrease the frequency of OOMs.
Yes, moving to InnoDB is a good idea for many reasons, including avoiding the need to "repair" that big table.  Caution: That big table may grow to 15GB when switching to InnoDB.


improve speed of fuzzy search I have configured trigram index for fuzzy search in Postgres. I inserted 8500000 rows to this table with 4 words text.

CREATE DATABASE people;
\connect people;
CREATE TABLE person
(
    id   bigserial NOT NULL,
    name text      NULL
);

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pg_trgm;

CREATE INDEX name_trigram_idx ON person USING gin (name gin_trgm_ops);

INSERT INTO person (name)
SELECT CONCAT(g.name, ' ', g.name, ' ', g.name, ' ', (SELECT array_to_string(
                                                                     array(select substr(
                                                                                'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
                                                                                ((random() * (36 - 1) + 1)::integer),
                                                                                1)
                                                                           from generate_series(1, 10)), '')))
FROM generate_series(1, 8500000) AS g (name);

Now I do a fuzzy search:

SELECT * FROM person WHERE name % '1200000'

And have results:

   id    |                name
---------+------------------------------------
   12000 | 12000 12000 12000 CLXBHQVEKC
  120000 | 120000 120000 120000 CLXBHQVEKC
 1200000 | 1200000 1200000 1200000 CLXBHQVEKC
 1202000 | 1202000 1202000 1202000 CLXBHQVEKC
 1212000 | 1212000 1212000 1212000 CLXBHQVEKC
(5 rows)

It takes about 1 second. That's good but I would like to have it faster (300ms).

What's are possible ways for optimization the speed?
Here is explain-analyze request:

EXPLAIN (ANALYZE) SELECT * FROM person WHERE name % '1200000'
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on person  (cost=201.87..24804.13 rows=8499 width=42) (actual time=45.135..972.583 rows=5 loops=1)
   Recheck Cond: (name % '1200000'::text)
   Rows Removed by Index Recheck: 147980
   Heap Blocks: exact=25286
   ->  Bitmap Index Scan on name_trigram_idx  (cost=0.00..199.74 rows=8499 width=0) (actual time=40.833..40.840 rows=147985 loops=1)
         Index Cond: (name % '1200000'::text)
 Planning Time: 1.538 ms
 Execution Time: 973.046 ms
(8 rows)

And a link to my test repo.

Generally speaking, I would like to have a typo-proof search for a text with several words which has about 9 million rows and it shouldn't respond longer than 300ms.

P.S. for this experiment I'm running Postgres in docker with 6 CPUs, 8GB memory and 1GB of swap.

You create the fuzzystrmatch extension, but you never use it.  Are you missing an example?

This type of query is very context sensitive, so you have to test it on realistic example data with realistic queries.  Your example doesn't look very realistic.  For one thing, your random string is the same random string in every row, not a different random one for each.  Also, having small integers embedded in your names seem pretty odd.

Your query of '120000' also doesn't seem very realistic, or if it is then it is pretty unfortunate.  Due to the repetition of zeros, it is a low complexity query and doesn't give the query engine much to grab on to.  If you are worried about the average case rather than the worst case, it is a poor choice of example.

Using the "%" operator on strings of quite unequal length is rarely the right thing to do.  Perhaps you want "%>" or "%>>" instead. There were added to pg_trgm in 9.6 and 11, respectively.  They measure how similar the query is to the most similar substring chunk of the column, rather than the whole of the column. They differ in that %>> requires the "chunk" to be aligned at word boundaries, while %> does not.  Not only do they give what I think are better results, but they are also much faster (in this case, I don't know about in general).

Finally, your query would be much faster if you increased the setting of pg_trgm.similarity_threshold.  Of course, then you also get no results, because none of your data is really all that similar to '120000'.  This goes back to the choice of operator, as the presence of 'CLXBHQVEKC' drags down how similar anything can be when using "%".


How do I compare large stored procedures? I want to compare stored procedures that should be identical in several different databases, by retrieving and comparing  the definitions from sys.sql_modules.

Currently I'm thinking about procedures which are "identical" except that one or other copy has blank lines or other white space at the start or end.  These seem to be added by making a script of a procedure.  I'd like to remove the blank lines and such and compare what's left.  So I could do:

IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) )
    SET @definition1 = SUBSTRING(@definition1, 3, 1048576)

That will work if the length of the procedure is less than 1048576 bytes.  But what is the actual limit on length - if any?

I suppose actually I should ask: what is a good way to compare procedures?  (Apart from "Buy the SQL Compare utility from Red Gate.")

Updated

(revised)
I just realised that sys.sql_modules.definition has data type nvarchar(max) and that has a specified length... in bytes, POWER(2, 31)-1, except that that formula doesn't work ;-)  In nchar characters, no more than POWER(2, 30)-1.

So this ought to do it:

IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) )
    SET @definition1 = SUBSTRING(@definition1, 3, 2147483647)

Yes, that is 2^31-1, it ought to be 2^30-1, but as long as it's bigger than @definition1 is or can be, I get away with it.  Until they increase "max" to 2^63-1 bytes.  Before there was "max", I used 8000.

And maybe I should include double-checking the DATALENGTH() in case it accidentally gets truncated to the first 8000 bytes somewhere.

As for your wise words about good code management to not have this need in the first place - useful ammunition, thank you (now to extract the bullets from my own hide).  I recognise the value of methodical working practices, even when they have to apply to me, as well.  But a problem is management who let's just say hypothetically just want a report or a result produced -now-, and who have been getting and expecting service like that for many years - and now they've learned to call it "Agile".

This question exists on two levels:

Comparing between two databases:

In this context you do not need to worry about length (outside of perhaps using DATALENGTH as a quick indicator of there being a difference and hence no need to compare the actual string values). Here you just need to make sure that the definitions are identical, and that does not require SUBSTRING, but it does require adding COLLATE Latin1_General_100_BIN2 to the comparison to ensure that you catch differences in case, etc that otherwise evaluate as being equal (using an everything-sensitive Collation, such as anything ending with _CS_AS_WS_KS[_SC], can't catch differences when equality is determined by combining characters and/or linguistic rules).

Within a particular environment, an object should be identical across all databases where it is deployed to. There can't be any differences due to scripting since none of the definitions are being compared to the actual source.

While comparisons on this level are easier (since you need not worry about white-space differences), they can also be misleading if all definitions are identical, yet they are all different from the source definition in the exact same way.
Comparing between database and source script

In this context you do need to worry about length and white-space variation depending on how your source object scripts are written and how they are packaged up for deployment. Here T-SQL is not going to help you as there are too many variations in what could be different yet still be "the same".

This is where several 3rd party tools come into play and you should choose one of them. They can be configured to ignore certain white-space as well as include additional properties and sometimes even permissions.

If you want to know if there are differences in Production that should either be removed OR factored into the source definition, you can only determine that by comparing to the source object script.

If you do not have source scripts for each object, then you absolutely need to do this and now is as good of a time as any to start.

If you do not have your source scripts in some sort of version control (Git, SVN, TFS, anything..), then you absolutely need to do this and now is as good of a time as any to start.

On a related note, the equation for the max size (in bytes, not "characters") is correct:

SELECT POWER(CONVERT(BIGINT, 2), 31) - 1
-- 2147483647

That value matches the max size of an INT as noted here. Since NVARCHAR is UTF-16, that is either 2 or 4 bytes per Code Point (a "character" is comprised of 1 or more Code Points, such as when using combining characters), hence the max "length" of a string is half of that, assuming that all "characters" are of the single, 2-byte variety (and they usually are). Meaning:

SELECT 2147483647 / 2
-- 1073741823

So the max amount of "characters" in an NVARCHAR(MAX) column is 1,073,741,823.

HOWEVER, this is irrelevant for SUBSTRING since it doesn't error in T-SQL if requested to return more characters than are in the input string. For example:

SELECT SUBSTRING('A', 1, 100);
-- A

So you can just put an arbitrary high value as the 3rd parameter.


Select all rows of all user logins, where user had logged in from more than 3 ip address within the past month Our SQL Server UserLogins table has these columns: UserID, DateTime, IpAddress.
How can I select rows of users who have logged in from more than three different ip addresses within a specified time period (e.g., one month), ordered by User ID

We can actually do this in one scan of the table, by using window functions. Unfortunately, we cannot use COUNT(DISTINCT... as a window aggregate, so we must hack it with DENSE_RANK and MAX
SELECT UserID, [DateTime], IpAddress
FROM (
    SELECT UserID, [DateTime], IpAddress,
        distinctIps = MAX(rn) OVER (PARTITION BY UserID)
    FROM (
        SELECT UserID, [DateTime], IpAddress,
            rn = DENSE_RANK() OVER (PARTITION BY UserID ORDER BY IpAddress)
            -- row-number distinct ip addresses
        FROM UserLogins
        WHERE [DateTime] >= DATEADD(month, -1, GETDATE())
    ) ul
) ul
WHERE distinctIps > 3;


MySQL - ERROR 1045 (28000): Access denied for user I just installed a fresh copy of Ubuntu 10.04.2 LTS on a new machine.  I logged into MySQL as root:

david@server1:~$ mysql -u root -p123

I created a new user called repl.  I left host blank, so the new user can may have access from any location.

mysql> CREATE USER 'repl' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

I checked the user table to verify the new user repl was properly created.

mysql> select host, user, password from mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| server1   | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost |                  |                                           |
| server1   |                  |                                           |
| localhost | debian-sys-maint | *27F00A6BAAE5070BCEF92DF91805028725C30188 |
| %         | repl             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------------------+-------------------------------------------+
8 rows in set (0.00 sec)

I then exit, try to login as user repl, but access is denied.

david@server1:~$ mysql -u repl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$ mysql -urepl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$

Why is access denied?

The reason you could not login as repl@'%' has to do with MySQL's user authentication protocol. It does not cover patterns of users as one would believe.

Look at how you tried to logged in

mysql -u repl -p123

Since you did not specify an IP address, mysql assumes host is localhost and tries to connect via the socket file. This is why the error message says Access denied for user 'repl'@'localhost' (using password: YES).

One would think repl@'%' would allow repl@localhost. According to how MySQL perform user authentication, that will simply never happen. Would doing this help ?

mysql -u repl -p123 -h127.0.0.1

Believe it or not, mysql would attempt repl@localhost again. Why? The mysql client sees 127.0.0.1 and tries the socket file again.

Try it like this:

mysql -u repl -p123 -h127.0.0.1 --protocol=tcp

This would force the mysql client to user the TCP/IP protocol explicitly. It would then have no choice but to user repl@'%'.


Any way to limit the number of connection to mysql for a command I am on Centos 6 and mysql version is 5.6. -
I have several relatively large tables and when I try to run a command it takes up all the connections to the database so the site gets down and I'll get this error:

SQLSTATE[HY000] [1040] Too many connections ...

Lets say I want to run this command:

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

Is there anyway that I can limit the number of connection that this command takes to mysql?

Hm, unless mysql works like any other database I know...

one command takes exactly ONE connection.

The error is not your command, the error is having and keeping too many connections open (application error), likely by not properly closing them and forgetting about them.

But one command runs on one connection. Period.

You basically ask how to avoid a traffic jam in front of your house by limiting the pizza you ordered to only come with one car - which it always does (because you only ordered one pizza anyway).