How to permanently delete a row in oracle forever

There is likely a more streamlined set of steps, but here is a start.

Delete the data out of any materialized views or home grown
replication methods using triggers or the like.
Delete the data out of any database audit tables.
Change the database to noarchivelog.
Create a new undo tablespace.
Switch log files until all are archived.
Shutdown the database.
Turn off and purge any storage or virtual machine level snapshot capability.
Delete all trace files that may contain the data.
Delete all backups sets including archive log backups that could contain the data.
Delete all OS audit logs that could contain the data.
Visit any computer that may have the data cached or displayed and destroy it there.
Visit any other storage devices the data may have been copied to and purge those.
Find any hard copies of the data and destroy those.
If someone memorized the data, memory erasure is beyond the scope of this site.


What does slow down an InnoDB database that is fixed by re-importing it?

Performance degradation was most likely caused by your table getting more and more fragmented by inserting and deleting many rows every day.
Dumping your table, dropping it, and reloading after many deletes will compact the space on disk and reduce the size of the .ibd file, assuming that it uses its own tablespace.

You could accomplish the same thing with a null alter, like this:

ALTER TABLE sometable FORCE;

which will rebuild the table.


Oracle - query by two column combination

Since you don't care which products are returned

SELECT product, category, market
  FROM (SELECT product,
               category,
               market,
               row_number() over (partition by market, category
                                      order by rownum) rnk
          FROM product_table
               JOIN availability_table USING (product)
         WHERE num_available > 0)
 WHERE rnk <= 3;


When ADFS is installed using the built-in Windows Information Database (which runs an embedded SQL Server on the AD DC), these databases are installed in SIMPLE recovery model. You could possibly take that as an indication that it would be OK to run them in SIMPLE model in your own environment, but that is up to you.

In general, if you're taking a full SQL backup daily, you should be OK discarding any historical transaction log backups prior to the full backup to save on disk space. But this depends on your need to be able to restore the database to any point in time in the past. If your policy requires you to be able to restore the database to a specific point in time, then you'll likely need to hang onto all those full, diff, and tlog backups that comprise your RPO.


Effective indexing and querying of overlapping date/time ranges

If the intervals within a particular account do not overlap with each other, you can replace your query with a TOP (1) query, to find the last start date before your desired end date. Do the same but opposite to get your earliest end date after your desired start date. These will form a small range you can use to check.

If they do overlap within a particular account, then you have one of the classically hard problems, and I'd recommend you read the material that Dejan Sarka has written at http://solidqblogs.azurewebsites.net/en/businessanalytics/interval-queries-in-sql-server-part-1/ (and its follow ups).

The problem is that an indexes on either startdate or enddate are less than ideal, because either one alone doesn't give a clue about whether the interval reaches your desired range. Another strategy is useful instead, such as indexing values within the range (lots of storage, and you have to consider the granularity), or come up with a creative solution such as interval trees or spatial analytics.


I'm not great with recursion either, so don't feel bad.  I've had success with structures similar to the following. It's not elegant, but you can see exactly what's going on.  Basically you need to gather all the possible values to evaluate against both ID and ParentID for a given ParentID value. This solution assumes integer values.  It will get a little more tricky with alphanumric values. The nice thing about this is you can run it as is or modify it to display a hierarchical ordering.

    --create the table for the example
    IF OBJECT_ID('tbl_RecursiveExample','U') IS NOT NULL
        DROP TABLE tbl_RecursiveExample

    CREATE TABLE tbl_RecursiveExample
    (
          ID INT
        , ParentID INT
    )

    INSERT tbl_RecursiveExample
    VALUES   (1,NULL)
           , (2,1)
           , (3,1)
           , (5,1)
           , (5,4)
           , (5,6)
           , (8,6)
           , (7,9)
           , (9,10);

    --change the value to the parentID you are interested in
    DECLARE @ParentIDVal INT=10;

    WITH
    cteFirstRelationship --Top level relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample
        WHERE ParentID = @ParentIDVal
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample
        WHERE ID = @ParentIDVal
    ),
    cteSecondRelationship --secondary relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ParentID
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ID
    )
    --possible add another level if needed
    SELECT DISTINCT t.* --> Needed due to the match of both values
    FROM tbl_RecursiveExample t
        JOIN
        (
            SELECT RelatedIDs from cteFirstRelationship
            UNION
            SELECT RelatedIDs from cteSecondRelationship
        )r ON r.RelatedIDs = t.ID OR r.RelatedIDs = t.ParentID


How to set slow query log in MariaDB my.cnf?

You said you added this

log_slow_queries = 1
long_query_time = 1
log-slow-queries = /var/log/mysql/slow_query.log
log_queries_not_using_indexes

That's the old way MySQL 5.0 sets the slow query log.

To activate the slow query log for MySQL 5.6 and MariaDB, you must use the following

slow_query_log
slow_query_log_file

Therefore, you need this in my.cnf

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow_query.log
log_queries_not_using_indexes


How to improve restoring the PostgreSQL database?

Something like:

psql -U postgres -d postgres -c "DROP DATABASE netcatalog_old"
psql -U postgres -d postgres -c "ALTER DATABASE netcatalog RENAME TO netcatalog_old"

You can redirect the info output of the restore like

pg_restore ... > my_log_file.txt

If you add the "-v" flag you can get more details of what's happening, but probably too much.


No.
What you describe are all variants of ELT.
The difference between ETL and ELT is in where you do the "T". The "traditional" ETL flow would implement the "T" (data transformation) outside the DBMS, using a specialized tool like DataStage, Informatica, Talend, etc. The data transformed to the target model would then be simply loaded into the destination DBMS tables.
The ELT flow differs in that it is the destination database engine that performs the "T"; you load raw data into staging tables in the target database, then use the DBMS tools to "massage" them into the final form and copy to the destination tables. It is an appropriate alternative when the source and target data type impedance is relatively low and the DBMS in question has the tools and capacity to do the job.


How does disabling a network protocol affect SQL server performance

Yes, IMHO you should disable named pipes and use only TCP/IP for better performance. You can force this in the connection strings for your apps and change them one at a time, rather than just shutting off named pipes and then getting a bunch of complaints:

Data Source = tcp:IP-or-host-name[\instance-name]

Don't shut off named pipes until all your apps are using TCP/IP. You can check what your apps are using via sys.dm_exec_connections.net_transport - this will have values such as TCP or Shared Memory. You can check the program too via a query like this:

SELECT c.net_transport, s.program_name, c.client_net_address
  FROM sys.dm_exec_sessions AS s
  INNER JOIN sys.dm_exec_connections AS c
  ON s.session_id = c.session_id;

However, for your second question, using one login or multiple logins won't affect performance much unless you're at the upper end and exceeding your pooled connections limit. In most cases consolidating into a single login will just make it much harder to later audit etc.


ORDER BY using custom priorities for text columns

Assuming you can't change your structure, you can use a CASE statement in your ORDER BY:

ORDER BY case cat when 'mgr' then 1 when 'dev' then 2 else 3

If you can change your structure, however, you can create a Category table including the category code and a ranking, join your sample table to the Category table and sort by this ranking.


Full text search results in a large amount of time spent in 'FULLTEXT initialization'

Others have found this a troublesome situation

InnoDB Full-text Search in MySQL 5.6: Part 3, Performance
InnoDB Fulltext Search WITH QUERY EXPANSION is very slow

Sep 16, 2010
5.0.51a, 5.0.75, 5.0 bzr
Status : No Feedback

SELECT query hang in state FULLTEXT initialization

Oct 17, 2014
MySQL 5.6.19
Status : Won't Fix

Since the MySQL Documentation is very terse on this thread state

  FULLTEXT initialization

  The server is preparing to perform a natural-language full-text search.

your only recourse would be to make preparation with less data. How ?

SUGGESTION #1

Look at your query again. It is selecting all columns. I would refactor the query to collect only the id columns from socomments. Then, join those retrieved ids back to the socomments table.

SELECT B.* FROM
(SELECT id FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)) A
LEFT JOIN socomments B USING (id);

This might produce an uglier EXPLAIN plan but I think profiling will change for the better. The basic idea is : If you have an aggressive FULLTEXT Search, make it gather the least amount of data during that FULLTEXT initialization phase, thus reducing the time.

I have recommended this many times before

May 14, 2012 : slow query with fulltext and left join
Mar 18, 2012 : Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?
Jan 26, 2012 : Mysql fulltext search my.cnf optimization :
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

SUGGESTION #2

Please make sure you are setting the InnoDB-based FULLTEXT options, not the ones for MyISAM. The two options you should be concerned are

innodb_ft_cache_size

Def Value 8000000 (7.629M)
Max Value 80000000 (76.29M)

innodb_ft_total_cache_size

Def Value 640000000 (610M)
Max Value 1600000000 (1525M = 1.49G)

Think about it for a moment. The text field is VARCHAR(600). Say the average is 300 bytes. You have 29,000,000 million of them. That would be that would be a little of 8GB. Perhaps increasing innodb_ft_cache_size and innodb_ft_total_cache_size may also help.

Make sure you have enough RAM for larger InnoDB FULLTEXT buffers.

GIVE IT A TRY !!!


Many times a patch is rolled back before a new one is applied so you may not be able to get a clear picture of everything that has ever been applied. You don't state your Oracle version, but if it is 12c, you can try a similar query to get some information:

set linesize 200
set pagesize 100
column version format a10
column action format a10
column action_time format a25
column BS format a5
column description format a75
select patch_id, version, action,
to_char(action_time, 'yyyy/mm/dd hh24:mi:ss') as action_time, bundle_series as BS, description
from dba_registry_sqlpatch
order by action_time desc;

This won't get non-sqlpatch patches though. Meaning if it is only a binary patch, this won't show that. But that should be in lsinventory.

You can also poke around file: $ORACLE_HOME/inventory/ContentsXML/comps.xml

Take care not to modify this file! But I do believe this shows patches that have been rolled back and ones that are currently applied. I'm not exactly sure what's all in there though.

Another thing to keep in mind is the Oracle Home. Some patches are for GRID home and some are for the RDBMS home. And some shops build an entire new home with each patch... Just ensure you are looking in the correct place.


This is probably shared memory that is common to all PostgreSQL processes.
ps is not smart about distinguishing private memory and shared memory.
Don't worry.


It might be that pgAdmin is trying to connect to the database pruebas expecting it to be encoded as UTF-8. If you think of your database as a file, the client is trying to decode all the byte sequences in it using "the rules of UTF-8" as it were, it comes across the byte sequence 0xF3 which it cannot interpret and therefore fails.

This may be due to the fact that the encoding that you are using on the host computer (the computer where you created the database) is not UTF-8 and when you created the database pruebas you didn't explicitly specify which encoding to use and the creation process used your local setting (whatever it may be) as the default.

The documentation on the supported character sets (specific to version 9.6) says that:

  Each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.)

I don't know the details of your OS, but on Linux, I can run the command:

locale

to see all the locale specific information which tells me that my LC_TYPE (which stands for character classification and case conversion) is en_GB.UTF-8 and I can also connect to my DB using the client psql and run:

SHOW server_encoding;

to see the encoding of my database, e.g.:

 server_encoding
-----------------
 UTF8
(1 row)

If the database pruebas is a small play database you created to play around with and you don't mind deleting it, you could drop it and create it again setting the encoding to UTF-8 and then see if you can connect to it.

I once didn't check the locale on the host (Linux) prior to initialising a database, went ahead and created the database only to find out that the process set up the database using the encoding on the host as default (which wasn't what I wanted). After digging around in the documentation, I found out that the only way to set the locale of the DB to what I wanted was to start all over.


Normalization is not done to make reading data easier or more efficient.  Normalization is done to make changing data simpler and less risky.  It's when you insert, update or delete data that you will see the benefits of normalization.

Therefore, if you are going to update your data much less frequently than you are going to read it, for example, if your data almost never changes, then optimizing for reading might make good sense.  However, if you are going to be adding new data and changing existing data pretty often then you will be better off with Third Normal Form (3NF) as your starting point for your database design.


SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)?

Try ShortestLineTo():

SELECT
  pt1.ToString(),
  pt2.ToString(),
  pt1.ShortestLineTo(pt2).ToString() AS line -- what goes here
FROM #tmp;

Returns

POINT (1 1)   POINT (2 2)   LINESTRING (1 1, 2 2)
POINT (2 2)   POINT (5 5)   LINESTRING (2 2, 5 5)
POINT (3 3)   POINT (4 4)   LINESTRING (3 3, 4 4)
POINT (4 4)   POINT (3 3)   LINESTRING (4 4, 3 3)
POINT (5 5)   POINT (4 4)   LINESTRING (5 5, 4 4)


Update an Oracle DB with a max number for the null rows

Is this a one-time update, or are you planning on running that query after inserts, where the insert statement is not setting SSC.NUMAR_CHEST? If you want ascending numbers, it sounds like you need to use an Oracle sequence; your subquery (select lpad(...  will return the max value at the point in time of the start of your query.

To use a sequence, create it once (using the name SEQ_CHEST in the examples below), then reference it in your update; every time you access SEQ_CHEST.nextval you will get a unique number:

create sequence SEQ_CHEST maxvalue 99999;  -- one time deal

update ss_chest ssc
   set SSC.NUMAR_CHEST = 'RO' || lpad(SEQ_CHEST.nextval,5,'0')
   where SSC.NUMAR_CHEST is null;

and your insert statements would be like

insert into ss_chest values('A', 'B', ... 'RO' || lpad(SEQ_CHEST.nextval,5,'0'));


I will quote Dictionary.com, as I take this as the meaning of database:

  a comprehensive collection of related data  organized for convenient access, generally in a computer.

Under this definition, you can consider a database anything from a full-fledged RDBMS (SQL Server, Oracle, etc.) to a basic flat file.  If it stores data, it technically can be considered a database.

Now, like most things in our modern world, there's the accepted meaning of a name.  And in the case of database, that will vary from person to person.  A lot of people think of a database solely as an entity managed by a data system.

It is worth noting @FrustratedWithFormsDesigner's comment:

  card-catalogues would also count if you removed the "...generally in a computer".

I agree with that statement, and I don't necessarily think that a database needs to live in a "computer" or any electronic device.  A card-catalogue is a perfect example of a non-computerized database.


First attend to the error for which the server had unexpected shutdown. Without attending this you can not move ahead.

And then for the database / tables missing data...

1. Log files are to start with.

Find the date/time range in which those particular .frm and .ibd files and the ibdata1 are last updated (not last accessed) and look for all entries of all relevant log files. Hope you can check all relevant...

Once the lines of log files with issues are identified, you yourself can correct them or else just post them here

2. Look at
https://dev.mysql.com/doc/refman/5.7/en/repair-table.html


As you already mentioned below command should work

 Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All

  -All

  If this switch is enabled, an object with server name, IPAddress (ipv4
  and ipv6), port and static ($true/$false) for one or more SQL
  Servers is returned.

Documentation on Get-DbaTcpPort -All switch

This can be validated on the github code as well.

If you want specific port info for the connections using ipv4 then you could run:

$ipV4 = Test-Connection -ComputerName ComputerName -Count 1  | Select IPV4Address
Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All |  Where-Object {( $_.IPAddress -eq $ipV4.IPV4Address )}

If you then want specific columns such as port number and if it is static, you could  add select-object port, Static

$ipV4 = Test-Connection -ComputerName ComputerName -Count 1  | Select IPV4Address
Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All |  Where-Object {( $_.IPAddress -eq $ipV4.IPV4Address )}  | select-object port, Static

If you want specific port info for the connections using ipv6

$ipV6 = Test-Connection -ComputerName ComputerName -Count 1  | Select IPV6Address
Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All |  Where-Object {( $_.IPAddress -eq $ipV6.IPV6Address )}

If you are on  an older version of dbatools
 then the -detailed flag is used instead of

-All


Avoiding "Row by row" fetch method when dealing with source LOB columns

I used Data Conversion for the varchar larger than 128 as NTEXT but what removed the error for me eventually is the set Validate External Data to False.


Do you need to include all foreing keys of a parent table in the child table?

I think I follow but here's an example using more meaningful names:

Given these two tables:

CREATE TABLE Persons
(
 person_ID INTEGER NOT NULL UNIQUE
);

CREATE TABLE Employees
(
 employee_ID INTEGER NOT NULL UNIQUE,
 person_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (person_ID)
    REFERENCES Persons (person_ID),
);

I think you are saying you would add a referencing table like this:

CREATE TABLE HourlyPaidEmployees
(
 employee_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (employee_ID)
    REFERENCES Employees (employee_ID)
);

I would be inclined to agree because I am a human who can understand that given two candidate keys I will choose employee_ID because the table name HourlyPaidEmployees strongly suggests it pertains to employees.

However, it seems your software generates this:

CREATE TABLE HourlyPaidEmployees
(
 employee_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (employee_ID)
    REFERENCES Employees (employee_ID),
 person_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (person_ID)
    REFERENCES Employees (person_ID)
);

I would guess that the software has been designed so that given two candidate keys it should not attempt to 'guess' which one to use and therefore uses both and leaves it to the user to decide which one to remove.


SQL Server: grant select access to a user in a view and not in its tables

If you want users to select from the view, why are you granting to the table? By "revoke" do you mean explicitly revoke/deny? Deny will override grant so there's your problem... you should be able to accomplish this by adding grant to the view and not doing anything either way on the tables.

Here's a quick example where SELECT has not been explicitly granted on the table, but has been on the view. The user can select from the view but not the table.

CREATE USER foo WITHOUT LOGIN;
GO
CREATE TABLE dbo.a(id INT);
CREATE TABLE dbo.b(id INT);
GO
CREATE VIEW dbo.v
AS
  SELECT a.id FROM a INNER JOIN b ON a.id = b.id;
GO
GRANT SELECT ON dbo.v TO foo;
GO
EXECUTE AS USER = N'foo';
GO
-- works:
SELECT id FROM dbo.v;
GO
-- Msg 229, SELECT denied:
SELECT id FROM dbo.a;
GO
REVERT;

Note that this assumes foo has not been granted elevated privileges through explicit permissions on the schema or database, or through role or group membership.

Since you are using tables in multiple databases (sorry I missed the end of that first sentence initially), you also may need explicit grants on the table(s) in the database where the view does not exist. In order to avoid granting select to the table(s), you could create a view in each database, and then join the views.

Create two databases and a login:

CREATE DATABASE d1;
GO
CREATE DATABASE d2;
GO
USE [master];
GO
CREATE LOGIN blat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO

In database d1, create a user, then create a table and a simple view against that table. Grant select to the user only against the view:

USE d1;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t1(id INT);
GO
CREATE VIEW dbo.v1
AS
  SELECT id FROM dbo.t1;
GO
GRANT SELECT ON dbo.v1 TO blat;
GO

Now, in the second database, create the user, then create another table and a view that joins that table to the view in d1. Grant select only to the view.

USE d2;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t2(id INT);
GO
CREATE VIEW dbo.v2
AS
  SELECT v1.id FROM dbo.t2
    INNER JOIN d1.dbo.v1 AS v1
    ON t2.id = v1.id;
GO
GRANT SELECT ON dbo.v2 TO blat;
GO

Now launch a new query window and change the credentials to be for the login blat (EXECUTE AS does not work here). Then run the following from the context of either database, and it should work fine:

SELECT id FROM d1.dbo.v2;

These should both yield Msg 229 errors:

SELECT id FROM d1.dbo.t1;
GO
SELECT id FROM d2.dbo.t2;

Results:

  Msg 229, Level 14, State 5, Line 1
  The SELECT permission was denied on the object 't1', database 'd1', schema 'dbo'.
  Msg 229, Level 14, State 5, Line 3
  The SELECT permission was denied on the object 't2', database 'd2', schema 'dbo'.


SQL Help - Exclude data with just one distinct value

One way to do this:

with stats as (
  select agentname,CallCenterID, count(leadid) Dials
  from LeadCallHistory
  where agentname not in ('vdad','system')
    and cast(callCenterStartTime as date) between cast(getdate()-1 as date)
    and cast(getdate()-1 as date)
  group by agentname, CallCenterID
)
select * from stats s1 where exists (
  select 1 from stats s2
  where s2.CallCenterID = s1.CallCenterID
    and s2.agentname <> s1.agentname
)


Relationship between number of transactions and redo generation

Yes, it will.

1000 statements, each in its own transaction, will generate more redo than 1 transaction of 1000 statements.  Transaction begin/end markers do require space in the redo and archive log.

Further, Oracle writes the redo log buffer to disk as it needs to;  generally speaking, committing more than 3 times per seconds adds latency (due to serialized physical I/O required to flush the log buffer to disk) to the overall operation.


Changing Postgres 9.6 to behave more like 9.5

The issue we were having turned out to be Transparent Huge pages.  I disabled this in the kernel and on Postgres, and things went back to how they were prior to the upgrade to Postgres 9.6


Selecting rows from one table not in another table

How much RAM do you have?  If you have more than 4GB, set innodb_buffer_pool_size to 70% of available RAM.  This is the most important thing to tune.  With it set to only 8M, you are definitely I/O-bound.  Increasing the size will probably shrink the time to under 10 seconds.

Your second idea should be good (assuming the only new rows have higher ids), but let's get rid of the LEFT and use MAX:

SELECT  *
    FROM  table_a t1
    JOIN
        ( SELECT  MAX(id) AS max_id
            FROM  table_a_mod ) t2
    WHERE  t1.id > t2.max_id


Cannot reinstall SQL Server 2008 R2 after downgrade from Windows 10 to Windows 7

A few more suggestions—adding as an answer to accept more characters so I can include more. . .

BE SURE TO RUN EVERYTHING AS ADMINISTRATOR

Command prompt (as administrator) run SFC /SCANNOW - Once this completed, check the log file it points to in the window for other errors and post back that as an edit to your question, I think it's called CBS.log, so just look for error with the date and time around the time you run once it's complete
https://support.microsoft.com/en-us/mats/program_install_and_uninstall
You may want to ensure this is installed (or uninstall re-install)
https://www.microsoft.com/en-us/download/details.aspx?id=21254
If you have Visual Studio installed, you may want to consider uninstalling and reinstalling it as well
If there are any other prerequisite components installed on this system for SQL Server 2008 R2 Express, you may want to consider uninstalling and reinstalling those as well - see notes below

Note, sometimes with the SQL Server and Visual Studio and other components, software, etc. as such, you have to install them in the order based on their version or things in the Windows registry, DLL component, etc. just get out of wack.

For example, if you have some component (or software) of the 2005 and 2008 version you need, it's best to install the oldest first (2005) and then once that is complete install the newest (2008).  I've seen this too many times where someone installs some old component or software as such on top of newer software, etc. that's already installed then the system start having strange problems.

If all else fails, you can backup your important data, and then do a fresh wipe of your HD and install a fresh copy of Windows. Microsoft has no way of emulating all software on everyone computers in the whole world, so perhaps when you upgraded and then downgraded, something just got corrupt and there was something on your system that they didn't test with, etc.  I typically always do fresh system upgrades rather than in-place upgrades but I'm in a business environment mainly too.


Standalone MongoDB to MongoDB Shard Setup

How it is done

Basically, it is easy:

Set up three(!) config servers
Start a mongos query router and have the --configdb option string point to the three config servers. Note: the string has to be identical on all mongos
Restart your going to be shard with the shardsvr option set. This step isn't strictly necessary, but best practise. What this basically does is that mongod will start on a different port (27108), preventing accidental (and potentially problematic) direct access to the shard.
Connect to the mongos instance.
Add the shard via the sh.addShard() command.
Done. Start sharding the collections as needed.

Some advice

If it is foreseeable that you will have to scale out, I would go into production with a sharded cluster consisting of a single shard right away. This makes scaling out much easier and faster when you reach the limits of your single shard – simply fire up another shard, add it using the addShard command and you are done.
Always, always, always let your shards consist of replica sets. And from personal experience with one of my customers: While using a replica set with only two data bearing nodes and an arbiter is possible, I would not advice doing so. The reason for this advice is that if you are doing maintenance on one of the data bearing nodes, there is no failover possibility in case the other node fails. So your shards should consist of replica sets with (at least) three data bearing, non-hidden and non-delayed nodes with default priority.
While MongoDB is pretty easy to fire up and maintain in a standalone and even replica set configuration, things tend to get a lot more complicated when you start to shard in a production environment. Make sure you have a MongoDB specialist at hand when going into production.


I can't recommend polymorphic associations.

In my experience, this will lead to data consistency issues since you can't use a foreign key constraint in the schema. That means you're reliant on your application's writes to this part of the database being bug-free, and no one manually altering the data.

It can also make the queries which join Upvotes to >1 of the other tables more complicated/harder to understand.


How to test mirroring with a single Microsoft SQL Server license?

You can install from the same media on another server without violating licensing; as long as the mirror secondary has equal or less processors than the primary. As long as you don't use it for active read-only connections, it doesn't need a paid license. See page 11[pdf], this is for SQL 2012, but there have been similar provisions as far back as I can remember.


Insert script adds N before each varchar data

Text literals with N (or n) in front of the opening quote indicate that the strings may contain symbols from a national character set, that is, they have the data type NCHAR or NVARCHAR. Either the target columns have these data types, or SQL Developers assumes that from the database NLS settings.

You can find more details in the manual.


Stuck at optimizing "not in clause" for a medium-sized query across two tables

Consider the database that you're querying against. It contains a data model that someone designed. It likely contains indexes that someone added to improve query performance. It contains statistics maintained by the engine to improve query performance. You effectively throw all of that away when you join table variables to each other. SQL Server doesn't allow for statistics on table variables and those queries won't benefit from all of the work that went into improving your data model. The best use cases for table variables are to retain data after a transaction is rolled back or to avoid unnecessary recompiles in stored procedures that are executed hundreds of times per second or more.

Fundamentally, comparing 15000 rows to 2000 rows can be a very fast operation in SQL Server as long as you avoid fighting with the database. I mocked up some data similar to the quantities mentioned in the question:

CREATE TABLE dbo.Words (
    Id INT IDENTITY (1, 1),
    [Text] NVARCHAR(100),
    PRIMARY KEY (Id)
);

INSERT INTO dbo.Words WITH (TABLOCK)
SELECT DISTINCT LEFT(Text, 25)
FROM sys.messages;
-- (203013 rows affected)

CREATE TABLE dbo.LearnedWords (
    UserId INT,
    WordId INT
);

CREATE CLUSTERED INDEX CI ON dbo.LearnedWords (UserId);

INSERT INTO dbo.LearnedWords WITH (TABLOCK)
SELECT 1, Id
FROM dbo.Words
WHERE ID % 100 <= 7;
-- (16247 rows affected)

Suppose that you were able to create a table that contains the distinct words used in each "text". I used the following:

CREATE TABLE dbo.TextWords (
    TextId INT,
    WordId INT
);

CREATE CLUSTERED INDEX CI ON dbo.TextWords (TextId);

INSERT INTO dbo.TextWords WITH (TABLOCK)
SELECT 1, Id
FROM dbo.Words
WHERE ID % 1000 <= 9;
-- (2039 rows affected)

Finding new words can now be expressed with a single SELECT query without any table variables:

select w.[Text]
from TextWords t
INNER JOIN dbo.Words w ON t.WordId = w.Id
Where t.TextId = 1
AND NOT EXISTS (
    SELECT 1
    FROM LearnedWords l
    WHERE t.WordId = l.WordId
    AND l.UserId = 1
);

That query finishes in less than 20 ms on my machine. If I run your original code it takes 3800 ms in total. If you need to use the table variables then at the very least try adding a RECOMPILE hint to the final query. That drops the execution time to 1600 ms on my machine.

With the right data model you can meet your business requirements in a simple and efficient way. There's a bit of ambiguity in your statement about texts. Perhaps you aren't able to store unique words for each text in the database ahead of time. In that case the time taken for your procedure should entirely depend on how much time it takes to populate a temporary structure with the unique words used in that text. You don't have that code posted here so it's impossible for us to help you with that.


Changing Auto-Growth Setting to MBs vs Log Drive Huge Occupancy

Basics of Transaction Log files

Transaction Log files (TLog) are like a notepad with pages that hold all the information on anything that has changed during the day. The TLog (notepad) has a beginning and an end when it is opened. The TLog (notepad) will also know where it was before based on the LSN (log sequence number) (notepad: Post-IT Index). If deletes/inserts/updates are finished they are marked as COMMITed and (can be) removed from the TLog (notepad) when a backup occurs which results in a CHECKPOINT being set in the TLog file. In the notepad analogy the old notes get removed from where they were and because your notepad is always the same size, the removed pages get replaced with fresh white empty pages; be it at the beginning or at the end for your notepad. Why? Because your notepad only has space for 100 pages. The removed notepad pages are then stored in your central storage (binder).

Following is a simplistic view of the TLog file.

TLog after database has come online

<begin>

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 1:123456 <== This is where your Post-IT index is

<end>

Transaction Logs are Circular

Equally important: The TLog is circular in nature (round robin), so if modifications can't be stored at the end of the TLog and there is free space at the beginning, the transaction will be stored there.

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
<end>

As you can see in above example the transactions were written to the TLog and circled around in order to log further changes.

TLog Full, Now What?

Now if the TLog is full it has the following options:

crash the database because of insufficient space
Expand if the TLog allows for growth

Let's follow on with the simplistic model of my TLog file and the situation where the TLog file has to grow. Two transactions modify data.

Before

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
<end>

LSN 28 is logged for the first transaction

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
<end>

TLog has to grow

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2

<end>

LSN 29 is logged for the second transaction

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
LSN 29 : Delete page content 5:444532

<end>

How Does This Relate to Your Questions?

You should have the basics now, so let's have a look at how this all relates to your questions.

If your application is modifying data or if the maintenance task is modifying data all modifications have to be stored in the TLog file. If the TLog has to grow because there is insufficient free space in the file and if the growth settings permit, then the TLog file will grow.

Yes, But You Changed Things

  Will the above changes help my Disk utilization to be at least bit lower than before. Because expanding drive each time is becoming burden for us.

percent (%) or MB growth?

Changing the growth settings from % to MB is a good thing to do, but it doesn't eliminate the need for the database to store transactions somewhere, if the space in the TLog is all used up.

Additional reading:

SQLSkills | Category : Transaction Log (SQLSkills)
The Transaction Log (SQL Server) (Microsoft Docs)
10 most important SQL Server transaction log myths (SQLShack)

Removing Maintenance Plans

This can reduce the need for the TLog file to grow because INDEX REORGANIZE and INDEX REBUILD are no longer performed. But, ...

Adding Ola Solution

... adding the Index Optimisation Jobs from Ola will perform Index maintenance too. So you've just replaced Index Maintenance with Index Maintenance. No real gains.

Ok, So What Do You Have to Do Now?

  What else should I keep in mind along with this to optimize Log Drive Occupancy to be lower.

Adequate TLog file sizing

Don't try and shrink the TLog file too much. It will grow again. Instead try sizing the TLog file according to your observations, allow for a little growth and monitor how full the TLog file is.

Frequent TLog Backups

If you have a look at my simplistic model, you may have noted that the TLog didn't have a lot of CHECKPOINTS logged. If the database has to handle a lot of transactions modifying data, then shortening the interval between TLog backups can help keep the TLog from growing, because a TLog backup creates a CHECKPOINT which allows the data to be written to the database. Let's add an example using my over simplistic model.

Additonal Backup

A backup is performed and logged in the TLog and during the backup a user modifies data:

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -> 2
LSN 29 : Delete page content 5:444532
LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -> 'good'
<end>

We now have everything in the database (up to LSN 30) and the SQL Server could free up the TLog to look like this:

<begin>

LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -> 'good'
<end>

Great, the TLog can now continue logging modifications at the beginning again.

Optimal Virtual Log File Sizing (advanced topic)

Having an adequate VLF size (not too big, not too small) can help keep TLogs at bay.

Additional reading:

Important change to VLF creation algorithm in SQL Server 2014 (SQLSkills)
Transaction Log VLFs – too many or too few? (SQLSkills)

VLF are like subsets of pages in my simplistic model or you could look at them as groupings of pages. Having to many pages in the group (VLF) can be good or bad depending on a couple of factors. Please read up on them in the linked articles. Basically it boils down to:

Having long running transactions can block a lot of VLFs which in turn can result in TLog growth.
Having too many VLFs can have a negative impact on SQL Server performance.

Additional Resources

Why Does the Transaction Log Keep Growing or Run Out of Space (DBA.SE)
Switching to Simple Recovery - shrinking transaction logs (DBA.SE)
SIMPLE or FULL recovery model for databases? (DBA.SE)
Large number of VLF and shrinking the log (DBA.SE)
How important are transaction log VLFs for performance? (DBA.SE)


Stored Procedures Best Practices: Fenced or Unfenced?

To be more precise, NOT FENCED routines run in the same process space as the database manager itself. The engine is written in C, so calling a not fenced routine is just like calling another C function from main(). This is where all the memory corruption and performance aspects come from: a not fenced routine has access to all the same resources -- memory, files, etc. -- as the database manager process itself.
For a FENCED routine the database manager launches a separate process (db2fmp), which in turn executes the routine code. As a result the operating system protection prevents a fenced routine to access any memory area or resource that belongs to the database manager.
SQL routines cannot be fenced, strictly speaking, because they don't "run", but they are even better than not fenced -- they are bytecode that the DB2 runtime engine itself executes, so there's no separate thread or process.
C and C++ routines can be fenced, in which case they execute in separate processes, or not fenced, in which case they are loaded into the database manager process space and called as functions.
Java routines can only be fenced by the fact that they need a separate process, Java virtual machine, to execute. If you declare them as NOT FENCED, the option is quietly ignored.
Having said all that, your only choice between fenced and not fenced is with C/C++ routines. Typically you would run the fenced for safety, changing to the not fenced mode only when you are pretty sure they can't harm the database manager and they need higher performance1.

1 - Performance difference between fenced and not fenced routines comes from the overhead associated with allocating a fenced process, as well as with interprocess communications between the database manager and the not fenced routine. Even then, the fenced process is not created every time a fenced routine is called; a pool of them will be created and reused for such calls. All this means that you might see the benefit of declaring a routine as fenced only if that routine is called very frequently (as in, tens of times per second or more).


I found that I need to send the protocol version along with the user, otherwise the connection is simply dropped, as the documentation states.


Restore a Database to a New database (T-SQL)

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.


Postgres PLPGSQL 'un-stored' procedures?

I have found this answer within the great dba.stackexchange.com resource that appears to show that I'm not going to be able to get a return value out of a DO block:
Running a CTE query in a loop using PL/pgSQL
Will leave this open for a bit to see if anyone has a cool workaround.......


How to query EndDate value from ReportServer..Subscriptions.MatchData

What's throwing it off is the XML name spaces in the tags. To get around this you need to use the with XMLNameSpaces clause.

Give this query a go.
I've tested this on a test instance (2008, not 2008 r2), so you may need to change the actual namespace definition to match your xml data.

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' as rs)
SELECT
[Description]
,m.value('rs:StartDateTime[1]','varchar(30)') AS StartDateTime
,m.value('rs:EndDate[1]','varchar(30)') AS EndDate
FROM
(
    SELECT
    [description],
    CAST(MatchData AS XML) AS MatchData
    FROM ReportServer.dbo.Subscriptions
) AS P
CROSS APPLY MatchData.nodes('/ScheduleDefinition') x(m)

SQL BOL reference for the WITH XMLNAMESPACES clause:

http://msdn.microsoft.com/en-us/library/ms177400.aspx


Does Oracle 11g support CREATE ASSERTION?

Database SQL Language Reference

There is no CREATE ASSERTION in Oracle. RDBMS products do not necessarily implement all features in the SQL standards.

And to add to the above:

Check Constraints

  Restrictions on Check Constraints

  The condition of a check constraint can refer to any column in the
  table, but it cannot refer to columns of other tables.

  Conditions of check constraints cannot contain the following constructs:

  Subqueries and scalar subquery expressions


Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

You say that performance degrades with the number of executions and that "restarting the deployment" fixes it.

I'm unclear what you mean by that particular phrase but presuming that it involves stopping the loop and then restarting it after some period then one possibility is ghost records.

I created a table as below (with one row per page for easier maths)

CREATE TABLE T
  (
     X INT PRIMARY KEY,
     Y CHAR(8000)
  )

INSERT INTO T
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
                  'Y'
FROM   master..spt_values v1,
       master..spt_values v2

And assumed your DELETE process is clearing out older rows as identified by some ascending column.

SET STATISTICS IO ON;

DECLARE @i INT = 0;
WHILE @i < 10
  BEGIN
      SET @i+=1

      RAISERROR('Processing %d',0,1, @i) WITH NOWAIT

      DELETE TOP (2000) FROM T
      WHERE  X <= 50000

      /*50 MS Delay*/
      WAITFOR DELAY '00:00:00:050'

      SELECT ghost_record_count,
             version_ghost_record_count
      FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T'), NULL, NULL, 'DETAILED')
      WHERE  index_level = 0
  END

Results

+---------------+--------------------+----------------------------+
| logical reads | ghost_record_count | version_ghost_record_count |
+---------------+--------------------+----------------------------+
|          2018 |               2000 |                          0 |
|          4025 |               4000 |                          0 |
|          6033 |               6000 |                          0 |
|          8026 |               7965 |                          0 |
|         10004 |               9944 |                          0 |
|         11989 |              11920 |                          0 |
|         13977 |              13902 |                          0 |
|         15963 |              15882 |                          0 |
|         17954 |              17864 |                          0 |
|         19943 |              19849 |                          0 |
+---------------+--------------------+----------------------------+

Records aren't deleted straight away. They are marked as ghosts to begin with then cleared up by a background task. In the test above very few of them were cleaned up in the 10 iterations meaning that the tenth DELETE did 10 times as many reads as the first one.

Possibly in your environment the DELETEs are occurring at a greater rate than the ghost cleanup task processes them leading to the continual degradation of performance.


How do I shrink all files quickly for all databases?

When you do "Tasks -> Shrink" from the GUI it actually issues a DBCC SHRINKDATABASE command behind the scenes. Try it. When the dialog box comes up, don't click the "OK" button. Instead, click the "Script" button. You'll see the command in a query window. Combine that with a query on sys.databases (leave out master and msdb), and you can make a script to shrink all of the databases.

For example (taken from jcolebrand's comment):

SELECT
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
         sys.master_files mf
    JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE d.database_id > 4;

Copy the output of that query and run it to shrink all your files.


Strategy to scale a comparison table query and load time

This will probably run faster:

( SELECT * FROM Comparision WHERE product_left  = 1
           ORDER BY result DESC  LIMIT n
)
UNION DISTINCT
( SELECT * FROM Comparision WHERE product_right = 1
           ORDER BY result DESC  LIMIT n
)
ORDER BY result DESC  LIMIT n

If there is no change of fetching the same row from the two SELECTs, change to UNION ALL to be a little faster.

Have these two 'composite' indexes:

INDEX(product_left,  result)
INDEX(product_right, result)

Yes, the ORDER BY and LIMIT are duplicated.  If you also need OFFSET, then it gets more complex, but still possible.

Why?  With the indexes I gave you, each SELECT will optimally filter (WHERE) and sort (ORDER BY), thereby touch only n rows.  Then the UNION puts together the 2*n rows, re-sorts and delivers the desired n.

With just INDEX(product_left) it will gather all the 1 rows, sort them and peel off n -- slower.

Without the UNION, the query will simply scan the entire table, ignoring your indexes (or any others).  (OK, there is a chance of some index being usable, but what I provided is better.)  Use EXPLAIN SELECT ... to see what is going on.  (Ask for help it it is not obvious.)


Mongo

Mongo is a document store. It's not a great solution to model this in a document store. You need people to list days and times desks are available. I would want to use something that could model timestamp ranges, and with good support for transactions. I would use something like PostgreSQL.


DB2 equivalent for MySQL's DROP .. IF EXISTS

No.

You'll need an IF clause to check for the existence of the table and then a separate statement to actually drop it if it does exist.


Very Simple MySQL Inner Join Extremely Slow

I ended up moving the tables back to InnoDB and then increasing the innodb_buffer_pool_size to 8G.  It's still not sub 1 second like our MS SQL Server, but it is finishing in under 5 seconds, so a huge step in the right direction.


Search all columns as a single text per row

You can cast the complete row to a string (text) and then use the built-in full text search on that result:

select *
from foo
where to_tsvector(foo::text) @@ to_tsquery('full-text-query');

foo::text concatenates all columns of that table as a single large string, using the default text representation for non-text data.


Look at this bugreport: https://bugs.centos.org/view.php?id=15723

Long story short: kernel 3.10.0-957 shows much higher disk util than the previous kernel


Revoke privileges for a user on tables with like names

You need to reference the record of the cursor loop:
EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;',
               t.table_schema, t.table_name);
               ^-- here        ^-- here


When you are exporting to .csv file you should use --field/-f/fieldFile  option.

http://docs.mongodb.org/manual/reference/program/mongoexport/

Try this

mongoexport -d central -c alerts -q "{updated_at:{\$gte:new Date(1420936780121)}}" -f  --csv --out alerts_11_12_Jan_20151.csv


Optimal Number of MySQL InnoDB Buffer Pool Instances

When I go back to MySQL 5.5, I would think about this same thing.

What I learned over those years was the following: If the Buffer Pool was bigger than half the installed RAM and innodb_buffer_pool_instances was 1 (default for 5.5), the threat of swapping was always imminent.

I discussed this before : Is there a rule of thumb regarding the size and number of a buffer pool instances?. In that post, I mentioned an example of a client who had 192GB RAM on the server with 162GB Buffer Pool. When innodb_buffer_pool_instances was 1, swapping happened. When I set innodb_buffer_pool_instances to 2, things got way better.

In your case, since the Buffer Pool is exactly half, a value of 1 may be OK. I would not chance it. I would set it to 2.

Since MySQL 5.6 has a default of 8, you shouldn't have to think about it anymore.

I will say this: akuzminsky's answer has the highest principle. My answer is just shooting from hip based on past experiences (good and bad).


It's hard to say much given your need to keep your code and data confidential, but sometimes you can get better performance just by trying equivalent rewrites. The following query should return the same results but it's very likely to have a different query plan:

SELECT
A.REFERENCE,
A.MEMBERID,
B.MEMBERID AS MEMBERID_B
FROM
TRANS A INNER JOIN
TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID > B.MEMBERID

UNION ALL

SELECT
A.REFERENCE,
A.MEMBERID,
B.MEMBERID AS MEMBERID_B
FROM
TRANS A INNER JOIN
TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID < B.MEMBERID

As you said in chat, this rewrite finishes in 6 seconds which is a decent improvement over the original 20 seconds you were seeing with <>.


Match multiple components of a subquery CTE in PostgreSQL

There are several ways to rewrite the query, even with keeping the CTEs. But please consider that CTEs in Postgres are pre-evaluated, so they might cause efficiency problems.

first, the LEFT join is equivalent to an inner JOIN, as you have a column from the right-side table in the following WHERE clause.
second, the FROM user_table LEFT OUTER JOIN content_table ON 1=1 is awkward. You could use the simple FROM user_table CROSS JOIN content_table. Not any performance gain, just more simple. I think I would prefer even the (frawned upon) FROM user_table, content_table from the LEFT ... ON 1=1.
after doing that, it's easy to see that FROM a CROSS JOIN b WHERE <x> can be replace with FROM a JOIN b ON <x>
the IN (SELECT user_id, content_id FROM user_allowed_data_1) can be replaced with the more elegant IN (TABLE user_allowed_data_1)

Now, to get rid of the OR, one way would be to combine the two ctes in one:

-- query 1 --
WITH user_allowed_data AS (
    SELECT u.id AS user_id, c.id AS content_id FROM …
  UNION DISTINCT
    SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name
FROM user_table AS u
  JOIN content_table AS c
    ON (u.id, c.id) IN (TABLE user_allowed_data) ;

But since we did that, we can just use joins in the main query and not the fancy ON ... IN (...):

-- query 2 --
WITH user_allowed_data AS (
    SELECT u.id AS user_id, c.id AS content_id FROM …
  UNION DISTINCT
    SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name
FROM user_table AS u
  JOIN user_allowed_data AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c
    ON ad.content_id = c.id ;

Another way - to get rid of the OR - would be to use UNION in the main query instead:

-- query 3 --
WITH user_allowed_data1 AS (
    SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
    SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name
FROM user_table AS u
  JOIN user_allowed_data1 AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c
    ON ad.content_id = c.id
UNION DISTINCT
SELECT u.name, c.name
FROM user_table AS u
  JOIN user_allowed_data2 AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c
    ON ad.content_id = c.id ;

Any of the above of course can be written without ctes - which I stress again - can be a barrier to efficiency. So, your original query, slightly altered and without CTEs (keeping the OR):

-- query 1b --
SELECT u.name, c.name
FROM user_table AS u
  JOIN content_table AS c
    ON (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …)
    OR (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …) ;

The OR variants can also be expressed by replacing the IN with a correlated EXISTS subquery:

-- query 1c --
SELECT uu.name, cc.name
FROM user_table AS uu
  JOIN content_table AS cc
    ON EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
               WHERE (u.id, c.id) = (uu.id, cc.id) )
    OR EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
               WHERE (u.id, c.id) = (uu.id, cc.id) ) ;


Optimizing one-way syncing of large (wide) tree

It's look like that is more efficient to dump the new state of the tree into the base along with the old state and then compare them with a single CTE.

Here is an approach I've used in the MariaDB but I hope PostgreSQL allows the same with minor changes:

WITH RECURSIVE
   old_tree (dpth, id, parent_id, attr1, attr2 ...) AS (
      SELECT 1, t.*, a.*                          -- initial portion
        FROM trees  AS t                          -- the table containing all the trees
        JOIN attrs  AS a ON a.node_id = t.id      -- attributes for node
       WHERE t.id = 1234                          -- root node for the old tree
      UNION ALL
      SELECT o.dpth+1, t.*, a.*                   -- recurrent portion
        FROM old_tree AS o                        -- this is recursive CTE
        JOIN trees    AS t ON t.parent_id = o.id  -- childs of the nodes already fetched
        JOIN attrs    AS a ON a.node_id = t.id    -- attributes for node(s)
      ),

   new_tree (dpth, id, parent_id, attr1, attr2 ...) AS (
      SELECT 1, t.*, a.*                          -- all the same for the new_tree
        FROM trees  AS t
        JOIN attrs  AS a ON a.node_id = t.id
       WHERE t.id = 4567                          -- root node for the new tree
      UNION ALL
      SELECT n.dpth+1, t.*, a.*
        FROM new_tree AS n                        -- this is recursive CTE too
        JOIN trees    AS t ON t.parent_id = n.id
        JOIN attrs    AS a ON a.node_id = t.id
      )
SELECT ot.*, nt.*                     -- should be aliased to avoid ambiguity
  FROM            old_tree AS ot
  /* there is no FOJ in mysql/mariadb but I've used it to be more clear */
  FULL OUTER JOIN new_tree AS nt  ON nt.attr1 = ot.attr1 -- or any other node identifier(s)
                                 AND nt.dpth = ot.dpth   -- and by same depth of recursion
;

As a result you'll get the table like that:

+------+------+------+------+------+------+------+------+
|  oid | opid | oat1 | oat2 |  nid | npid | nat1 | nat2 |
+------+------+------+------+------+------+------+------+
| 1234 | NULL |    X |    x | 4567 | NULL |    X |    x | -- unchanged
| 1235 | 1234 |    Y |    y | NULL | NULL | NULL | NULL | -- deleted
| NULL | NULL | NULL | NULL | 4568 | 4567 |    A |    B | -- added
| 1357 | 1234 |    Q |    a | 9876 | 4567 |    Q |    z | -- modified
| .... | .... | .... | .... | .... | .... | .... | .... |

The final SELECT of the WITH can be used to filter out only changed/added/deleted nodes. Then you can modify the old tree structure or log the changes or else.


How to Set up SSRS Web Services to run under another port than 80?

The port can be configured in SSRS Configuration Manager. This will have been installed on the machine when you installed SSRS.

Check this and this BOL articles for all the details.


How to add additional tables/fields into a query with multiple inner joins?

After further tinkering, I worked it out.  If I'm correct, then it's stupidly simple.

Additional tables can be added to my scenario if:

they are added as LEFT JOINS
they are added after the INNER JOINS

I'm still trying to wrap my head around exactly how JOIN functions, but it makes sense to me that the first three joins are narrowing down the selection.  In order to expand the selection (to add my additional tables/values) I use the LEFT JOIN, which essentially says, "use everything to the left (my inner joins) and use this table too."

SELECT desired_categories.*, t1.valueX, t2.valueX, t3.valueX
FROM
    ( SELECT nid, title, additional_col_1, additional_col_2, COUNT(1) AS all_categories
        FROM node
        GROUP BY nid
    ) AS A

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories
        FROM node
    INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid
        IN (498,499,500)
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN
    ( SELECT DISTINCT nid, title
        FROM node
    ) AS desired_categories USING (nid)

LEFT JOIN table_1 AS t1 ON desired_categories.nid = t1.entity_id
LEFT JOIN table_2 AS t2 ON desired_categories.nid = t2.entity_id
LEFT JOIN table_3 AS t3 ON desired_categories.nid = t3.entity_id
LEFT JOIN table_4 AS t4 ON desired_categories.nid = t4.entity_id
LEFT JOIN table_5 AS t5 ON desired_categories.nid = t5.entity_id

WHERE all_categories = good_categories
  AND A.additional_col_1 > t1.valueX
  AND A.additional_col_2 = 'active';


PgAdmin 3 crashed when font size was increased

I have solved the issue by deleting PgAdmin3 file in the Windows registry.

Save a copy of the Registry editor, so that in-case there is any error, you can
restore the saved copy.
Double click on the "Registry editor" at C:\windows\regedit.
Click on HKEY_CURRENT_USER\Software.
 delete the folder PgAdmin 111.

Re-start Pgadmin. Then increase font size to 12 - 14 Time and it works fine.


Performance issue | MySQL Consumes more than 80%+ memory and 2000%+ CPU

It would be helpful to have SHOW CREATE TABLE for the first few tables in the slowlog.  It would also be helpful if you always tagged each column with the alias (or table) that it belongs to.  Meanwhile, I will make some guesses.

Indexes (or PRIMARY KEYs) needed:

user_registration:  (usr_username)
user_type:  (usr_type_id)
company_master:  (comp_id)
company_master:  (status, comp_id)

If my suggestions don't help enough, then provide EXPLAIN SELECT ... for the worst few queries.

Because of the implementation of information_schema (prior to version 8.0), SELECT  data_free... cannot be sped up.  But why are you doing the query?  There may be a workaround.

Including a test on status or del_status (etc) sometimes makes optimization more difficult.  There is not a one-size-fits-all remedy.

A tip about SELECT  U.*, ...: If * includes some TEXT or BLOB columns that you don't really need, there may be a performance improvement by spelling out only the columns you do need.

Those indexes should help the first few queries.  Since they comprise 99.9%+ of the activity, you should see the CPU go down significantly.

If you need another go at this, change to long_query_time = 1, clear the slowlog, and run for another day.


Unable to restore database because it exists, unable to drop because it does not exist

Disclaimer, the following solution where for a development box. Use with caution. For a production like scenario, open a PMR.

It turned out that during a previous restore the file system got full and therefore not all files necessary got created. I cataloged the database:

db2 catalog db XXXX
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

and tried to connect to it (fails). In db2diag log there's an entry showing where he files where supposed to be located, in my case:

/data/db/db2/db2inst1/NODE0000/SQL00010

As it turned out this is the same location as shown in

db2 list db directory

So what I did was:

db2stop force

and then rsynced all files from another database:

#> cd /data/db/db2/db2inst1/NODE0000/SQL00010
#> rsync -av ../SQL00011/* .
#> db2start

Before dropping the database I tried connecting (for the fun of it) and got a new error:

#> db2 connect to XXXX
SQL0294N  The container is already in use.  SQLSTATE=42730

Dropping the database was possible though

#> db2 drop db XXXX
DB20000I  The DROP DATABASE command completed successfully.

As mentioned this was performed on a developer box with no important data, in a production environment I would have opened a PMR instead.


What sometimes works in this situation is

SELECT * FROM
(
SELECT
 PROJECT.PROJECTID, REQUEST.REQUESTID,
 SAMPLE.SAMPLEID,SAMPLE.OTHERSAMPLEID,
 ILLUMINASEQPROTOCOL2.SAMPLEID
FROM
 ILLUMINASEQPROTOCOL2,
 PROJECT,
 SAMPLE,
 REQUEST,
 ANCESTORS A1,
 ANCESTORS A2,
 ANCESTORS A3
WHERE (
 ILLUMINASEQPROTOCOL2.RECORDID = A1.RECORDID AND
 A1.ANCESTORID = SAMPLE.RECORDID AND
 SAMPLE.RECORDID = A2.RECORDID AND
 A2.ANCESTORID = REQUEST.RECORDID AND
 REQUEST.RECORDID = A3.RECORDID AND
 A3.ANCESTORID = PROJECT.RECORDID)
AND  (
 (((ILLUMINASEQPROTOCOL2.RecordId > 0) AND
  ((SAMPLE.EXEMPLARSAMPLETYPE LIKE 'DNA') AND
  (REQUEST.RecordId > 0)))
 AND  (PROJECT.RecordId > 0))
) AS mytable
) ORDER BY SAMPLE.RECORDID LIMIT 0,501;

i.e. transform your original query into a subquery within the outer SELECT. Then, put your ORDER BY and LIMIT as subclauses of your outer SELECT.

This should eliminate the bottleneck with the ORDER BY and LIMIT. The reason your query slows down with the ORDER BY is that the query has to retrieve all records and then SORT them - without the ORDER BY, the system can just spit out the first 500 records.

It is puzzling that you do have indexes on the relevant fields. If what I have suggested doesn't work, you could post the table structures and the query execution plan.


Yes, CAST and CONVERT do the same thing, except that CONVERT allows more options, such as changing character set with USING.


How to Archive Only MySQL Master not the Slave.?

If you are sure the Slave is sync'd with the Master, you could do the following on the Slave:

Activate Binary Logging
Perform a full mysqldump every three months
Run FLUSH LOGS; every midnight
After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180.
The idea is to have the changes to the Master. They are stored already in the Master's Binary Logs. Those same changes can be on the Slave as well if you activate Binary Logging on the Slave. The Master never needs to feel anything throughout this process.

You could use pt-table-checksum and pt-table-sync to sync up the Slave from the Master. (I wrote a shell script to automate them using mk-table-checksum and mk-table-sync)

Doing these things will allow you to reload a snapshot of the beginning of a Quarter onto another server. Replay all logs on the Slave (using mysqlbinlog) to any datetime from those logs to view different days within the next Quarter.

If all your data is InnoDB, here is an Alternative from the Master

Binary Logging on the Master should already be enabled due to Replication
Perform a full mysqldump (using --single-transaction) every three months
Run FLUSH LOGS; every midnight
After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180.

These are just some ideas you can try to organize using the Slave or the Master.


what is the right way to restart a mariadb galera cluster?

As far as I read the right way is to use the init.d/systemd scripts of MySQL/MariaDB in order to stop a node. This will inform WSREP/Galera that the replication must stop and will save on the disk, in the MySQL/MariaDB data folder, in a file the last transaction number that the node has committed (So that next time it will boot, the nodes will compare their last committed transaction and sync).

You have to stop a node and wait a little bit (like a minute or so) before to stop the next node, to be sure the other nodes see that.

After that, it is very important to start again the nodes in the reverse ordre, so that the last node you shutdown is the first booting one, having the most up-to-date data.
Then the next nodes will be able to sync with the first one.

Using wsrep_on='OFF' doesn't sound right to me, WSREP is embedded in MySQL, so when MySQL gracefully stops, WSREP knows what it has to do.


I always truncate the log file after a bulk operation. Also, typically, production log backups are set to at least once every 30 minutes. I prefer 15 minutes, but an hour is a bit long. That helps in keeping log file in check too.


How to increase fast backup and restore of 500GB database using mysqldump?

But the link also recommends XtraBackup from Percona, which is faster, no locks on tables that are already in use and gains no time for restoration. For 500GB of data I prefer Percona XtraBackup to be quick and efficient unless you want to convert them to innodb_file_per_table model if it wasn't from the existing DB server. Below link explains on how to proceed.

http://www.percona.com/doc/percona-xtrabackup/innobackupex/creating_a_backup_ibk.html


Useful formats for the results of a database "audit"?

ER-Assistant. Great ERD, very small and compact. 620Kb total. Its basic plain black/white ERD's but some like that.

Its free and uses Crows foot notation, but windows only.

http://highered.mcgraw-hill.com/sites/0072942207/student_view0/e_r_assistant.html

Another popular choice is Workbench from my research. Here is the link.

http://dev.mysql.com/downloads/workbench/5.1.html


User databases inaccessible after physical restart

It sounds to me as if the D: drive isn't accessible when the SQL service starts. Consider telling the service to start on a delayed start, or create a process which checks for a file on D: before doing a "net start mssqlserver".


Is there any tangible difference between a unique clustered index and a clustered primary key?

One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key.

Oh, and the fact that a table can have one PK and many UK :-).

These are both differences in INTENT not in PERFORMANCE.
Otherwise, I don't think there's any difference.
Behind any PK or UK the SQL Server builds an index (depending on the request, clustered or not) and the way it's used is transparent for the source is coming from.


Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) be unique (in combination) and NOT NULL (individually). A PK is enforced via a Unique Index, though it can be either Clustered or Non-Clustered.

A Clustered Index is a means of physically (i.e. on disk) ordering the data in the table and deals with performance; it has nothing to do with data integrity. A Clustered Index can require that the key column(s) be unique (in combination), but it doesn't need to. However, since the Clustered Index is the physical order of the data, it needs to uniquely identify each row no matter what. So if you don't set it to require uniqueness, it will create its own uniqueness via a hidden 4-byte "uniquifier" column. That column is always there in non-Unique Clustered Indexes, but it doesn't take up any space when the key fields are unique (in combination). To see first hand how this "uniquifier" column works (both in the Clustered Index and the effect on Non-Clustered Indexes), please check out this test script I posted on PasteBin: T-SQL script to test Uniquifier size.

Hence, the main question of:

  would it be more efficient to add an auto-increment id field and use that in conjunction with company_id as the primary key, or would it add unnecessary overhead

is conflating those two concepts, so they need to be addressed separately, though there is definitely some overlap.

Should an IDENTITY column be added or would it be unnecessary overhead?

If you add an INT IDENTITY column and use it to create a PK, assuming it would be a Clustered PK, that adds 4 bytes to every row. This column is visible and usable in queries. It could be added to other tables as a Foreign Key, though in this particular case that won't happen.

If you don't add the INT IDENTITY column, then you can't create a PK on this table. However, you can still create a Clustered Index on the table as long as you don't use the UNIQUE option. In this case, SQL Server will add a hidden column called "uniquifier" which behaves as described above. Because the column is hidden, it cannot be used in queries or as a reference for Foreign Keys.

So as far as efficiency goes, these options are roughly the same. Yes, there will be slightly less space taken up by having the non-Unique Clustered Index due to some rows (ones with the initial unique key values) taking up 0 bytes while all rows in the IDENTITY / PK will take the 4 bytes. But there won't be enough of the 0 byte rows (especially with the small amount of rows expected) to ever notice a difference, let alone out-weigh the convenience of being able to use the ID column in queries.

INT IDENTITY Column or Hash of org_path Persisted Computed Column?

Given that you won't be looking up rows based on org_path values, then it doesn't make sense to add the overhead of the Persisted Computed Column plus needing to compute that hash in queries in order to match against the Computed Column (this was my original suggestion, available in the revision history here, which was based on the initial wording / details of the Question). In this particular case, the INT IDENTITY "ID" Column is probably best.

Key Column Order

Given that the ID Column will rarely, if ever, be used in queries, and given that the two main use-cases are to get either "all rows" or "all rows for a given company_id", I would create the PK on company_id, id. And because this means that rows are not inserted sequentially, I would specify a FILLFACTOR of 90. You will also need to make sure to do regular index maintenance to reduce the fragmentation.

Second Question

  does the fact that company_id is the primary key in another table have any effect here

No.

Trigger

Since org_path values within a company_id are unique, you should still create a Trigger on INSERT, UPDATE to enforce this. In the Trigger, do an IF EXISTS with a query that probably does a COUNT(*) and GROUP BY company_id, org_path. If anything is found, issue a ROLLBACK to cancel the DML operation and then a RAISERROR saying that there are duplicates.

Collation

In my initial answer (based on original wording / sparse details of the question, and available in the revision history here), I had suggested possibly using a binary (i.e. _BIN2) Collation. Now that we have insight into what exactly org_path is, I would not recommend using a binary Collation. Since there will be diacritical marks, you do want to make use of linguistic equivalences.


What data structure is used for MongoDB 3.x geospatial indexes?

The general index format used by MongoDB's included storage engines as at 3.0.x (MMAPv1 and WiredTiger) is B-tree, however there are more nuances in the technical implementation.

MongoDB 3.0 introduced a storage engine API which separates the concerns of storage formats (i.e. data & index representations on disk and in memory) from the core server product. For example, WiredTiger supports index prefix compression, data compression, and more granular concurrency than MMAPv1.

It is expected that alternative storage engine implementations can (and will) differ in their underlying implementation of indexes and data storage to suit different workloads. For example, WiredTiger has support for LSM (Log Structured Merge-Trees) which is expected to be available in the MongoDB 3.2 production release. There are also alternative storage engines such as RocksDB (which uses LSM) and TokuMXse (which uses Tokutek's fractal tree storage).

I'm not aware of any pluggable storage engines for MongoDB that have been specifically optimized for storage of spatial data, but it is conceivable that one may be created.


MySQL chain replication timing of writes on first slave

To answer this, you need to understand the way MySQL replication works and the difference between the IO_thread and the SQL_thread.

In a nutshell, the IO_thread pulls events from the master and stores in the relay_log. The SQL_thread pulls from the relay_log and applies them to the slave dataset.

So, the description of --log-slave-updates (emphasis mine):

  This option causes the slave to write the updates performed by its SQL thread to its own binary log

means that the MySQL is only logging to the first slave's binary log after it is being committed on that slave. And it won't be available to the second slave until it has been committed on the first slave.

So if I'm understanding you right, you should be safe in your us-ecase.

Single-threaded sql thread and slave lag

It is important to note that by default, the SQL thread is single-threaded. This means even though your master can apply changes across multiple connections very quickly, when it is applied to the first slave it is one change at a time. This can contribute to slave lag.

Luckily, in MySQL 5.6 the ability to have multiple slave thread appliers was introduced with the --slave-parallel-workers option.

However, this only helped if you had multiple schemas(databases), because MySQL would only apply in parallel if statements were in different schemas.

This limitation was removed in 5.7 to allow for parallel application within the same schema.

The main point I am trying to make, is that if you are dealing with slave lag on the first slave, the second slave will take similar time (assuming similar hardware) so will be even further behind your master. So ensure your custom application takes that into account.


Migrate SQL Server database to multiple files (shrinkfile emptyfile vs log shipping)

In a word, no.  You have to do it live.

What you'll want to do it add 3 new files.  Then simply start doing index rebuild operations.  As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files.

I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.


How check that the Oracle Database is up?

If you run the following query:

SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

It should return:

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
YOUR_DBASE       OPEN         ACTIVE


Handling partial backups from many different databases

In order to do a piecemeal restore of only the primary filegroup, you need to ensure the following conditions are met:

There can be no pre-existing target for the database restore statement.  i.e. you must DROP any existing database before you run the piecemeal restore.  This applies even if you use the REPLACE keyword in the RESTORE DATABASE statement.
Use a MOVE clause to specify the storage location of the primary filegroup for the database you're restoring.  Specifying the location allows you to ensure you don't try overwriting some other database file, which would result in an error during the restore.

I would restore the database temporarily, copy the data from the restored database into a permanent database, then drop the restored database.  From the details in your question, there is no need to keep the restored database attached to the instance once you've copied the data into the permanent location.

I wrote a blog post showing the problem with performing a piecemeal restore onto an existing database, and how to rectify the problem at SQLServerScience.com


Is it possible to have read/write race conditions in a percona cluster?

A replication setting determines which types of queries first perform a causality check, i.e. have the executing server confirm that it is up-to-date.

Check the wsrep_sync_wait variable description here:
https://www.percona.com/doc/percona-xtradb-cluster/5.5/wsrep-system-index.html

Our consultant advised setting 6 (i.e. 2 | 4), meaning UPDATE, DELETE, INSERT, and UPDATE perform a causality check. In other words, write queries. In the end, we decided to switch to 7 (i.e. 1 | 2 | 4), performing causality checks on reads as well. Although this makes reads take longer, the problem of outdated reads that you describe goes away.

Note: After weeks of success with this setting, the problem suddenly came back. It disappeared as soon as we (temporarily) started doing all reads and writes on the same server, so something seems to be up. I'm wondering if there is another setting that comes into play and needs to be fixed as well.

Updated...

I believe we mitigated the issue in the previous note, but I can't seem to remember what exactly it was.

Use a single write node

In any case, I highly recommend using only a single write node. Since all writes are replicated anyway, all servers are doing comparable writes, so write scaling is severely limited - unlike read scaling. Writing on a single node, however, does give us certain advantages.

Avoid conflicting writes

Using a cluster with multiple write nodes has an unfortunate effect: if two simultaneous writes onto different nodes conflict, they may initially appear to succeed, but then one of them will fail at commit time. At least for application development, this has a significant impact. Every place in the application that runs this risk must implement some catch-and-retry logic... or we could just use a single write node!

Of course, you'll want high availability. We use ProxySQL to load balance. If the write node goes (or is taken) down, another node becomes the write node. Yes, only during failover there might be the risk described above, but that is only a very small window. Ideally, when doing a manual failover, we should momentarily queue incoming requests and let the old write node finish its work, before assigning a new write node.

Auto-increment is not always increasing

Auto-increment is not guaranteed to be always increasing! I found this a particularly disturbing issue that was unknown to many. The greatest ID is not necessarily the latest row. Counterintuitive and harder to reason about. There a various configuration options.

Auto-increment can be controlled using wsrep_auto_increment_control & friends, having ServerA use 1, 4, 7, ..., ServerB use 2, 5, 8, ..., and ServerC use 3, 6, 9, .... This avoids contention of IDs, yes, but it also skips IDs and really creates non-incremental sequences.

Alternatively, as I understand from Percona, we can use a single write node and disable auto-increment control. The values should now always be increasing. During a failover, the new write node might fail a transaction or two at commit time if it has not replicated the latest auto-increment value yet, although I expect this should be mitigated by the causality setting discussed at the top of this answer.

I would go with the latter, and, just to be sure, instruct everyone to not rely on auto-increment being strictly increasing.

Do let us know if you can achieve a single write node and high availability with HAProxy. I have seen it done with ProxySQL, but it would be great to know if it can be done without it.


Restore Database from Database with Different Name

Are you looking to do something like a re-name swap?  Something like this:

Load DB2
Rename DB1 to DB_temp
Rename DB2 to DB1
Rename DB_temp to DB2

Then you re-load DB2 again the next time?

If so you can simply alter the name of the DB rather than deal with the time a restore takes (the file names will remain the same but that really doesn't matter in this case.)

ALTER DATABASE CurrentDBName MODIFY NAME = NewDBName;

Now on the other hand if you actually want to keep DB2 in place and just restore over DB1 it would look something like this:

RESTORE DB1 FROM DISK = 'DB2 backup file' WITH REPLACE

There are several methods you can use to improve your speed.  Probably the easiest is to stripe your backup.

BACKUP DATABASE DB2 TO DISK = 'loc1', DISK = 'Loc2', DISK = 'Loc3' WITH FORMAT
RESTORE DB1 FROM DISK = 'loc1', DISK = 'Loc2', DISK = 'Loc3' WITH REPLACE

Warning, you will need to have all of the files you stripe to in order to do your restore.  If you lose one the whole backup is useless.


502 error after restarting mongo db in remote server using fork command

I could finally get it to work by executing following command  :

sudo mongod --fork --auth --port 27017 --dbpath /var/lib/mongodb --logpath /var/lib/mongodb/mongod.log


InnoDB Table has backed up INSERT and UPDATE queries, high CPU

Many things can be done to speed things up...

Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude.  (This is one of many things to shrink the record size.)
Do not INDEX boolean values like clocked_in and public_post.  (The optimizer is unlikely to ever use the index.  And it is costly to update.)
Don't split DATE and TIME; use DATETIME instead of VARCHAR.  (Shrink and ease of use.)
Don't have a table with lots of fields, then spend most of your time INSERTing only two columns.  (Perhaps you meant UPDATE?)  Perhaps have a separate table with the log of clock-in/out?
How many different values are there of order_type?  (Consider using an ENUM or "Normalizing" that field.)

Was that the entire PROCESSLIST?  Or was something other than 2-column INSERTs operating on that table?

How much RAM do you have?  What is the value of innodb_buffer_pool_size?


How to secure SQL Agent Job that INSERTS to a linked server

SQL Express doesn't have SQL Agent, so I'm assuming you're running the job on the EE Server.

In that case, you should be able to simply use a TSQL job step on the SQL EE box
The SQL Server Agent Account on the EE server already has rights to the local SQL Server.  You can grant it access to the remote SQL Express server.  If running under Network Service or a local virtual account, you would grant the rights to the computer account of the EE server.  EG

CREATE LOGIN [MyDomain\SQLEEComputerName$] FROM WINDOWS

Pushing data over a linked server is not very fast, however.  Pulling is faster.  So you'll need to test the performance.


How can we ensure security and integrity of data stored in MongoDB?

You are mixing concepts here. Transactions are related to atomicity and guarantee that multiple operations are either executed as a whole or not at all.
Consistency, however is (simplified) the guarantee that after each successful transaction or operation applied, the database is in a a valid, usable state.
An example

Note This example is simplified for the sake of understandability.

Often people say that you can not use MongoDB for financial operations such as account balancing, since it lacks transactions. They could not be farer from the truth.
Take a simple example of two users, each with an account:
{
  _id:"act1",
  owner: "Foo Bar"
},
{
  _id: "act2",
  owner: "Bar Baz"
}

And now, we have a collection we call "transfers". Each of our accounts get $100.00 from an outside source:
db.transfers.insert({from:"Scroogey", to:"act1", amount: 100.00, date: new ISODate()})
db.transfers.insert({from:"Scroogey", to:"act2", amount: 100.00 , date: new ISODate()}})

Now, how do we get the balance of our two accounts? Say Foo Bar wants to check his balance. We have to do an aggregation for that:
db.transfers.aggregate([
  { $match:{ $or:[ { from:"act1" },{ to:"act1" } ]}},
  { $sort:{ date:1 }},
  { $group:{
    _id:"act1",
    balance:{
      $sum:{
        $cond:[
          { $eq: [ "$to", "act1" ] },
          "$amount",
          {$multiply:["$amount",-1]}
        ]}
      }
    }
  }
])

Which gives us the correct value when run against transfers:
{ "_id" : "act1", "balance" : 100 }

So far, so good. So how does this relate to atomicity and consistency?

In MongoDB, a write operation is atomic on the level of a single document, even if the operation modifies multiple embedded documents within a single document.

So if we insert a document into the transfers collection, we achieve what we want: atomic transfers from and to accounts. Say Foo Bar wants to send Bar Baz $20.25:
db.transfers.insert({from:"act1",to:"act2", amount: 20.25, date: new ISODate()})

Atomic and consistent. Now, if we run our aggregation again, we get the correct result:
{ "_id" : "act1", "balance" : 79.75 }

If Bar Baz checks his account
db.transfers.aggregate([
  { $match:{ $or:[ { from:"act2" },{ to:"act2" } ]}},
  { $sort:{ date:1 }},
  { $group:{
    _id:"act2",
    balance:{
      $sum:{
        $cond:[
          { $eq: [ "$to", "act2" ] },
          "$amount",
          {$multiply:["$amount",-1]}
        ]}
      }
    }
  }
])

he sees the correct value, too:
{ "_id" : "act2", "balance" : 120.25 }

We have an atomic system to do account balancing without transactions! And still we have a log of all transfers from and to an account which you need anyway. We just use it differently.
There is another way to do it
There is a way to achieve multi-document atomicity from the applications point of view, called Two-Phase-Commits.
Basically, it is an extension of the above. You'd have a transaction collection, in which pending operations are documented. Only if all operations are done successful, the transaction is marked as committed. There are various rollback scenarios. This is definetly more work than doing it like documented above, and should only be considered in case the data model can not be designed for the according use case. That would be the case when dealing with legacy systems, for example, in which you want to add features which would rely on transactions.
Basically you move the transaction logic from the DBMS to the application. This is not necessarily as disadvantage, since control over the various stages of a transaction which is supposed to be committed is much more granular.
Conclusion
As we have seen, there are not transactions. With careful data modeling and a bit of knowledge about the inner workings and tools provided by MongoDB, there is hardly any use case that can not be implemented with MongoDB. It might not be the best tool for any use case, however, and you should choose your persistence system carefully.
If you really need transactions, use the Two-Phase-Commit approach.


What is the difference between integrity and consistency?

I think the difference is that the two terms refer to different things:

Consistency refers to Transactions (it's the C in ACID).
Integrity refers to Databases.

A transaction is said to be consistent if it keeps the integrity of the database (i.e. if it transforms the database from a valid state to another valid state).


Assuming you are running transaction replication. This is how it works in your scenario

When initial records are inserted with replicated column value as 'N' on publisher no records will be replicated to subscriber.
Once you update the some of the records with replicated column value as 'Y' based on your logic on publisher only those 'Y' records will replicated to subscriber.
And for last question once you update any of the replicated column 'Y' value records on publisher back to 'N' in subsequent runs of your logic those records will be deleted from the subscriber.

All of these operations can be monitor by executing the EXEC sp_browsereplcmds on distribution database. In the execution results look for command column to see what exact is performed by replication process.


Bids should be two tables:

BidHistory -- containing details about each bid
CurrentBid -- Either some details (redundant) about the latest bid, or where all the details live until moved to the other table.

This adds complexity to the "make a bid" code.  But it simplifies the code that needs to know what the current bid is.  And it avoids the complex code to fetch the latest bid.

The Strawman:

There is a serious flaw in the query that you presented.  MAX(bid.id) and MAX(bids.bid_amount) do not necessarily refer to the same row.  (OK, perhaps they do in a bidding situation, but you should not assume it!)  This is where you need "Groupwise Max" solutions.  See the [greatest-n-per-group] tag.  There are a lot of discussions here and on stackoverflow.com about how to find the row that contains the max (or min) value of a particular column.  Some solutions involve a subquery.  Some solutions are slower than others.

See also http://mysql.rjweb.org/doc.php/groupwise_max


Optimizing queries on a range of timestamps (one column)

Two things that are very odd here:

The query select 300k rows from a table with 1M+ rows. For 30 % (or anything over 5 % - depends on row size and other factors) it doesn't typically pay to use an index at all. We should see a sequential scan.

The exception would be index-only scans, which I don't see here. The multicolumn index @Craig suggested would be the best option if you get index-only scans out of it. With lots of updates like you mentioned, this may not work out, in which case you are better off without the additional columns - and just the index you already have. You might be able to make it work for you with more aggressive autovacuum settings for the table. You can adjust parameters for individual tables.
While Postgres is going to use the index I would certainly expect to see a bitmap index scan for that many rows, not a plain index scan, which is typically the better choice for a low percentage of rows. As soon as Postgres expects multiple hits per data page (judging from its statistics on the table) it will typically switch to a bitmap index scan.

Keep PostgreSQL from sometimes choosing a bad query plan

Judging from that I would suspect that your cost settings are inadequate (and possibly the table statistics, too). You  may have set random_page_cost and / or cpu_index_tuple_cost too low, relative to seq_page_cost. Follow the links and read the manual.

Would also fit with the observation that cold cache is large factor, as we worked out in comments. Either you are accessing (parts of) tables that nobody has touched in a long time or you are running on a test system where the cache isn't populated (yet)?
Else you just don't have enough RAM available to cache most of the relevant data in your DB. Consequently, random access is much more expensive than sequential access when data resides in cache. Depending on the actual situation you may have to adjust to get better query plans.

One other factor has to be mentioned for slow response on the first read only: Hint bits. Read details in the Postgres Wiki and this related question:

Why does a SELECT statement dirty cache buffers in Postgres?

Or the table is extremely bloated, in which case an index scan would make sense and I would refer back to CLUSTER / pg_repack in my previous answer that you quoted. (Or just VACUUM FULL) And investigate your VACUUM settings. Those are important with many inserts and updates every day.

Depending on UPDATE patterns also consider a FILLFACTOR below 100. If you mostly update only newly added rows, set the lower FILLFACTER after compacting your table, so that only new pages keep some wiggle room for updates.

What fillfactor for caching table?
Are regular VACUUM ANALYZE still recommended under 9.1?

Schema

  campaign_id is 99%+ NULL and dt_updated is 0% NULL.

Adjust the sequence of columns slightly, to save 8 bytes per row (in the 99% of cases where campaign_id is NULL):

CREATE TABLE traffic (
    uuid_self       uuid not null REFERENCES ... ,
    uuid_partner    uuid not null REFERENCES ... ,
    id              serial PRIMARY KEY,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
);

Detailed explanation and links to more:

Configuring PostgreSQL for read performance

To measure:

Measure the size of a PostgreSQL table row