Postgresql 11 - service doesn't start I use PG Admin 4 with PostgreSQL 11 for my data. After some kind of crash yesterday, I am not able to start postgresql anymore. I've done some research, but it doesn't look like a localhost, IP address, or port issue.

The log file states (in bold what I think is relevant):
2021-03-21 06:49:36.682 AEDT [11796] LOG:  database system was shut down at 2021-03-20 14:56:05 AEDT
2021-03-21 06:49:36.682 AEDT [11796] LOG:  invalid record length at 5B1/CA7F5900: wanted 24, got 0
2021-03-21 06:49:36.682 AEDT [11796] **LOG:  invalid primary checkpoint record**
2021-03-21 06:49:36.682 AEDT [11796] **PANIC:  could not locate a valid checkpoint record**
2021-03-21 06:49:36.890 AEDT [13136] LOG:  startup process (PID 11796) was terminated by exception 0xC0000409
2021-03-21 06:49:36.890 AEDT [13136] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2021-03-21 06:49:36.890 AEDT [13136] LOG:  aborting startup due to startup process failure
2021-03-21 06:49:36.898 AEDT [13136] LOG:  database system is shut down

I have checked the postgresql.conf and pg_hba.conf files. They look fine and unchanged (allowing all users, having the right IP address, and port number).
postgresql.conf
# - Connection Settings -

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to '*'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = ''   # comma-separated list of directories
                    # (change requires restart)
#unix_socket_group = ''         # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off              # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''          # defaults to the computer name
                    # (change requires restart)

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0       # TCP_KEEPCNT;
                    # 0 selects the system default

# - Authentication -

#authentication_timeout = 1min      # 1s-600s
#password_encryption = md5      # md5 or scram-sha-256
#db_user_namespace = off

**pg_hba.conf**
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

My SSD hard drive, which contains the data directory of postgres seems fine (i.e. I can read / write and access all files). In an effort to fix my starting issue, I was trying to run pg_resetwal but unsuccessful because the program did not open although the postgres service stopped.
From the Windows logs:

Waiting for server startup...
LOG:  listening on IPv6 address "::", port 5432
LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "log".

pgAdmin gives me the following (obviouldy I can't start postgresql...):

could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP
connections on port 5432?

Do you have any idea what could be wrong here? Many thanks!

I'm sorry to have to say to you that it seems like you have corruption in your WAL files. Did you remove some WAL files? If so, copy them back to the WAL directory.
If you don't have the missing WAL files (and I see archiving is disabled, so you likely don't have them), data loss is inevitable.
My suggestion:

copy the entire cluster somewhere else (just to be able to restore should you make things worse)
use pg_resetwal to try to reset your wals (see documentation here and please read the whole page so you understand that this will lead to data loss)
try to start again
if it starts, if you don't have checksums enables (it's not the default), pg_dumpall everything (see documentation here) (redirect to /dev/null if it's too big) to check for any data corruption
if it does not start, I'm afraid you'll have to rely on backup, making the data loss even worse

I'm so sorry for the bad news...


Sample data

CREATE TABLE #Data
(
    id integer NOT NULL PRIMARY KEY,
    x integer NOT NULL,
    val float NOT NULL
);

INSERT #Data
    (id, x, val)
VALUES
    (1, 1, 8.18),
    (2, 3, 8.35),
    (3, 5, 8.51),
    (4, 7, 8.52),
    (5, 10, 8.54),
    (6, 15, 8.57),
    (7, 1, 8.35),
    (8, 3, 8.60),
    (9, 5, 8.70),
    (10, 7, 8.75),
    (11, 10, 8.76),
    (12, 15, 8.82),
    (13, 1, 8.55),
    (14, 3, 8.80),
    (15, 5, 8.91),
    (16, 7, 8.95),
    (17, 10, 8.96),
    (18, 15, 9.04),
    (19, 1, 8.75),
    (20, 3, 9.04),
    (21, 5, 9.15),
    (22, 7, 9.21),
    (23, 10, 9.24),
    (24, 15, 9.27);

Pivot

The first step is to pivot the data we have:

DECLARE @Pivoted table
(
    x integer NOT NULL PRIMARY KEY,
    val1 float NULL,
    val2 float NULL,
    val3 float NULL,
    val4 float NULL
);

-- Pivot the data we have
INSERT @Pivoted
    (x, val1, val2, val3, val4)
SELECT
    N.x,
    val1 = MAX(CASE WHEN N.rn = 1 THEN N.val ELSE 0 END),
    val2 = MAX(CASE WHEN N.rn = 2 THEN N.val ELSE 0 END),
    val3 = MAX(CASE WHEN N.rn = 3 THEN N.val ELSE 0 END),
    val4 = MAX(CASE WHEN N.rn = 4 THEN N.val ELSE 0 END)
FROM
(
    SELECT
        D.x,
        D.val,
        rn = ROW_NUMBER() OVER (
            PARTITION BY D.x
            ORDER BY D.id)
    FROM #Data AS D
) AS N
GROUP BY N.x;

Next we need to know the range of values in the table so we can fill in the blanks:

DECLARE
    @Min integer,
    @Max integer;

-- Range of values
SELECT
    @Min = PMin.x,
    @Max = PMax.x
FROM (SELECT TOP (1) P.x FROM @Pivoted AS P ORDER BY P.x ASC) AS PMin
CROSS JOIN (SELECT TOP (1) P.x FROM @Pivoted AS P ORDER BY P.x DESC) AS PMax;

Now we can find the missing rows, and the real rows on either side (for interpolation):

DECLARE @Missing table
(
    x integer NOT NULL PRIMARY KEY,
    prev_row integer NULL,
    next_row integer NULL
);

-- Find missing rows
INSERT @Missing
    (x, prev_row, next_row)
SELECT
    Missing.number,
    PrevRow.x,
    NextRow.x
FROM
(
    -- All rows that ought to be present
    SELECT SV.number
    FROM master.dbo.spt_values AS SV
    WHERE
        SV.[type] = N'P'
        AND SV.number BETWEEN @Min AND @Max
    EXCEPT
    -- Those that are present
    SELECT P.x
    FROM @Pivoted AS P
) AS Missing
CROSS APPLY
(
    -- Find the previous real row
    SELECT TOP (1) P.x
    FROM @Pivoted AS P
    WHERE P.x < Missing.number
    ORDER BY P.x DESC
) AS PrevRow
CROSS APPLY
(
    -- Find the next real row
    SELECT TOP (1) P.x
    FROM @Pivoted AS P
    WHERE P.x > Missing.number
    ORDER BY P.x ASC
) AS NextRow;

Finally, we compute interpolated values for the missing rows, and return the results:

-- Results
SELECT
    Result.x,
    val1 = ROUND(Result.val1, 3),
    val2 = ROUND(Result.val2, 3),
    val3 = ROUND(Result.val3, 3),
    val4 = ROUND(Result.val4, 3)
FROM
(
    -- Rows that exist
    SELECT
        P.x,
        P.val1,
        P.val2,
        P.val3,
        P.val4
    FROM @Pivoted AS P

    -- Plus
    UNION ALL

    -- Interpolated rows
    SELECT
        M.x,
        val1 = PrevRow.val1 + (Scale.factor * (NextRow.val1 - PrevRow.val1)),
        val2 = PrevRow.val2 + (Scale.factor * (NextRow.val2 - PrevRow.val2)),
        val3 = PrevRow.val3 + (Scale.factor * (NextRow.val3 - PrevRow.val3)),
        val4 = PrevRow.val4 + (Scale.factor * (NextRow.val4 - PrevRow.val4))
    FROM @Missing AS M
    JOIN @Pivoted AS PrevRow
        ON PrevRow.x = M.prev_row
    JOIN @Pivoted AS NextRow
        ON NextRow.x = M.next_row
    CROSS APPLY
    (
        VALUES(CONVERT(float, M.x - M.prev_row) / CONVERT(float, M.next_row - M.prev_row))
    ) AS Scale (factor)
) AS Result
ORDER BY
    Result.x ASC;

Results

╔════╦═══════╦═══════╦═══════╦═══════╗
║ x  ║ val1  ║ val2  ║ val3  ║ val4  ║
╠════╬═══════╬═══════╬═══════╬═══════╣
║  1 ║ 8.18  ║ 8.35  ║ 8.55  ║ 8.75  ║
║  2 ║ 8.265 ║ 8.475 ║ 8.675 ║ 8.895 ║
║  3 ║ 8.35  ║ 8.6   ║ 8.8   ║ 9.04  ║
║  4 ║ 8.43  ║ 8.65  ║ 8.855 ║ 9.095 ║
║  5 ║ 8.51  ║ 8.7   ║ 8.91  ║ 9.15  ║
║  6 ║ 8.515 ║ 8.725 ║ 8.93  ║ 9.18  ║
║  7 ║ 8.52  ║ 8.75  ║ 8.95  ║ 9.21  ║
║  8 ║ 8.527 ║ 8.753 ║ 8.953 ║ 9.22  ║
║  9 ║ 8.533 ║ 8.757 ║ 8.957 ║ 9.23  ║
║ 10 ║ 8.54  ║ 8.76  ║ 8.96  ║ 9.24  ║
║ 11 ║ 8.546 ║ 8.772 ║ 8.976 ║ 9.246 ║
║ 12 ║ 8.552 ║ 8.784 ║ 8.992 ║ 9.252 ║
║ 13 ║ 8.558 ║ 8.796 ║ 9.008 ║ 9.258 ║
║ 14 ║ 8.564 ║ 8.808 ║ 9.024 ║ 9.264 ║
║ 15 ║ 8.57  ║ 8.82  ║ 9.04  ║ 9.27  ║
╚════╩═══════╩═══════╩═══════╩═══════╝

These numbers are rounded to three decimal places using SQL Server's default rounding. If you need to mimic e.g. Excel rounding, you would need to use a function that implements that specific algorithm.

Tested on SQL Server 2008 R2 with compatibility level 80, as indicated in your previous questions.

db<>fiddle (SQL Server 2012, earliest version available)


Problem remotely connecting to MySQL database Good afternoon everyone.

I have 3 servers on 2 different networks, all of which run MySQL.

I can use mysql -u<user> -p -h<ip-addr> from server 1 to server 2 and back without a problem.

When I try to use that command from server 3 (which is on a differnet network) to server 1 I get the following:-

$ mysql -u<user> -p -h<hostIP>
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '<hostIP>' (146)
$

I know the same command works from server 2 to server 1 with the same password. My initial thought was that there was a network block between server 3 and server 1 but if that were the case then server 3 would not get a password prompt.

I have also looked at the users table in server 1 and the user has the same password for local connections and network connections (which is why it works from server 2 to server 1).

Is there another port other than 3306 that is used as part of the connection after that password prompt? Or, failing that, what am I missing?

Many thanks,

David

There could be multiple reasons for this but to start troubleshooting as it looks like the issue with permissions/grants.

1.Please check and assign proper grants on server 1 :

GRANT ALL PRIVILEGES ON db.* TO 'username'@'server3_ip' IDENTIFIED BY 'Password';

flush privileges;

All is not compulsory in above query.

2.Double Check Bind parameter in in My.cnf .

bind-address 0.0.0.0

Hope this helps else we can debug further.


Is there a way to force one optimization level in SQL Server? Based on Paul White's blog, there are three optimization phases in SQL Server, search 0 (Transaction Processing), search 1 (quick plan) and search 2 (full optimization). Each phase has entry and termination conditions.
My question is, can we force the optimizer to run at one optimization phase?

I think this question represents a misunderstanding of the optimisation phases and a willingness to try and override the optimizer ("I know best") when there is really no need to do so, as from my experience the optimizer gets it right most of the time.  For the odd occasion it doesn't there are more appropriate tools for experienced developers to work around, eg query rewrites, different indexing strategies, filtered indexes/stats, hints, plan guides, plan forcing.  Again from my experience on occasions the optimizer isn't quite getting it right it tends to be because of over-complexity, either in the query or schema design, although there are occasional bugs.

Phases search 0 - 2 are also components of Full Optimisation and not all queries are candidates for this.  For example simple queries are much more likely to be optimised as Trivial Plan which is cheaper to run than full cost-based optimisation.  Why force full optimisation for simple queries where there is only one path?  Additionally optimisation can drop out of these phases if a 'good enough' plan is found.

You can influence some control over the optimizer.  Here's a few examples:

give the optimizer as much information as possible so it can make good decisions, eg up-to-date statistics, foreign keys, unique indexes, constraints, appropriate datatypes
to encourage simple plans you could only write simple queries; build up resultsets eg using temp tables
prevent parallel plans - turn off parallelism at server level ( eg with sp_configure 'max degree of parallelism' ), or at query level using OPTION ( MAXDOP 1 ).  This isn't necessarily such a bad idea in an OLTP system for example.
there is a trace flag for encouraging parallel plans, but not generally recommended

So hopefully you can see it doesn't really make sense to force the optimizer to run in only one of these modes.

Do you have some specific examples in mind where you don't feel the optimiser is doing a good job?  Post them here as questions and I'm sure someone will be able to help.


Is C collation suitable for my Postgres database? I am working with 500m rows (120GB) in Postgres 9.4, and I am trying to make SELECT statements as fast as possible. I am less concerned about the speed of INSERT and UPDATE, because my data only changes once a quarter and I'll probably use a backup database and switch over when that happens.

I'm working on a Debian Wheezy server, with 32GB of RAM and 320GB of solid-state. I have set maintenance_work_mem to 16GB while setting up the database, and shared_buffers to 12GB.

I have found that creating indexes is slow, and even with indexes, SELECT queries are still quite slow. To help this, I've been recommended to use the C collation for the database.

I've been reading through the collation documentation, but it doesn't answer all my questions. So here's what I want to do with my database:

Support fast SELECT * FROM mytable WHERE col1='020406AAA' queries.
Support fast SELECT * FROM mytable WHERE col1 LIKE '02%' queries.
Allow non-ASCII characters in some columns (e.g. Ménière's disease). This isn't absolutely critical, though - I could convert these to ASCII if it would really help for speed.
Add the PostGIS extensions to the database.

Are any of these contra-indications for using the C collation?

I'm expecting all the columns where I want to use LIKE queries to consist of digits and uppercase letters only.

The data is a UK dataset, in English, for UK-only users. So I'm not expecting to have to add internationalization.

From what you're describing, it doesn't sound like anything is contraindicated related to the C collation order. In fact, Robert Haas, has a great discussion on the subject here, which seems to come to the conclusion that if you don't need locale aware sorting, just don't use it: Perils of Collation Aware Comparisons

I did notice you are on Debian Wheezy. The stock kernel for Debian Wheezy is 3.2, which if you're using that, has abysmal performance on database workloads. I would investigate that along with the collation testing, if you are not happy with the performance of your machine.

The wheezy-backports kernel is 3.16, which does not have these issues. Discussion and performance tests are here: Benchmarking PostgreSQL with Different Kernel Versions

Hope that helps. =)


Will insert and update wait while a table is being vacuumed? I want to vacuum a table in PostgreSQL version 9.1 which will take between 10 to 12 minutes.  This needs to be done in real-time, in production use.

I just want to know that if any INSERT or UPDATE query comes, will they wait for the lock to be lifted, or will they time out?

NOTE: The Java code that will fire the query has not specified any timeout.

The documentation says the following:

  ROW EXCLUSIVE

  Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

  The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other
  referenced tables). In general, this lock mode will be acquired by any
  command that modifies data in a table.

  SHARE UPDATE EXCLUSIVE

  Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
  protects a table against concurrent schema changes and VACUUM runs.

  Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE.

There is no conflict between the two lock types involved.  This means that neither INSERT/UPDATE blocks VACUUM, nor the other way around.  This means furthermore that your queries should succeed without timeouts.  Most probably the I/O activity of vacuuming won't do you any harm.


How to rotate pgstartup.log My pgstartup.log has got big. I have renamed it, and can set up logrotate to do it automatically if need be, but of course the server is still writing to the open file. I'd rather not restart the server, if I can avoid it. Is there a way to tell the server to reopen the file? I wondered if either pg_reload_conf or pg_rotate_logfile would do it, but the former only does the config files, and the latter only does the true log file (i.e. not pgstartup.log).

You can try to use next config for logrotate. I think "copytruncate" does that You need.

/var/log/postgresql/pgstartup.log {
       daily
       rotate 7
       copytruncate
       compress
       delaycompress
       notifempty
       missingok
}


How does MS SQL Server decide when it can use "Parallelism" when executing queries? How does MS SQL Server decide when it can use "Parallelism" when executing queries? I recently posted another question here that, when looking at the actual execution plan used parallelism when I had an order by on the query and did not when I didn't have the order by. So, I'm wondering how it figures out when it can use "Parallelism" and when it can't.

SQL Server makes this decision based upon cost. If you execute 'sp_configure' (make sure advanced options are enabled) you will see an entry for 'cost threshold for parallelism'. If the optimizer estimates the cost to be greater than the run_value then your query will be executed in parallel.

If the ORDER BY clause in your example results in an increased cost, then the query will go parallel.


When MySQL table gets too large I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the table takes a really long time (more than 48h) and every minute downtime is one minute too much. So whenever I need to add a new index or add a new column, the problem with the altering is there.

Are 12 billion rows too much for MySQL to handle? What would you suggest to do?

48 hours ALTER to a 12 billion rows table is not out of the ordinary in InnoDB.

You could:

Use Percona online schema change \ Facebook php online schema change tools to ALTER the table online.
Switch to TokuDB engine which does these alters online and also improves performance when scaling up from InnoDB.

Both solutions are free.


How to approach copying a schema for testing? I'm an Oracle newb.

The systems in question are strictly for developers to test against. We fire them up and then blow them away afterwards. Problem is that this takes as long as 8 minutes per build which is costing us severely in CI. Four minutes to erase and four minutes to rebuild. Also problematic is that we can only run one set of tests at a time because we only get one schema to test against.

One thing to consider is the possibility that a developer might check in something with screwed up transaction management, like something that does real commits with no transactional rollback.

What we were thinking of as a solution was to keep a copy of the schema that is kept up to date via automated means. Then do a straight copy of the entire schema to a unique name. Do the test run against the copy and then asynchronously remove it at the end.

Generally speaking, what is a good approach for this? We are working with Oracle running on our local machines in a Docker container. What I've been experimenting with is using the expdp/impdp tools. The Docker container adds a pain in the 6 O'clock, but I've been able to get limited success with this technique. In a nutshell I do the copy but then I get errors to the effect that several items pre-exist despite copying to a new name. I expect that this has something to do with the close association between Oracle Schemas and User's. I'll post examples of the errors I'm seeing as soon as I have a new test run.

The 12c Multitenant option is a nice tool for this.

Create a "master" pluggable database that is your starting point, and you can create other pluggable databases by cloning this PDB.

When you clone a PDB, you have the option to clone the PDB as a "snapshot copy". This does not actually copy the files, but uses storage snapshots, and that is what makes this feature great. For example, cloning a 111 GB database took 20 seconds in this example: Snapshot clone of a PDB using ACFS

When I experimented with it, smaller databases took even less time to be cloned. When you do not need the database anymore, you can just simply drop it.

Also you are not limited to 1 schema, because you can create many clones and use them at the same time concurrently, up to 252 PDBs.

Note that, the Multitenant option is an extra cost option for Enterprise Edtition databases. So you need the proper license to use it.

SNAPSHOT COPY


SUM several CASE results Given:

 CASE
     WHEN A IS NULL THEN 1 ELSE 0
 END AS [A]
, CASE
     WHEN B IS NULL THEN 1 ELSE 0
 END AS [B]
, CASE
     WHEN C IS NULL THEN 1 ELSE 0
 END AS [C]

How can I SUM  A+B+C being the results of 3 different CASE WHEN

You did not specify your RDBMS (SQL Server, Oracle, etc.), but if it supports Common Table Expressions, you could encapsulate your main select logic in a CTE and select/sum from it.  Here is a solution using SQL Server.

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)

;WITH _cte
AS (
    SELECT CASE
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    )
SELECT A + B + C as Results
FROM _cte

Results
2

As a comment pointed out, you don't need a common table expression.  A derived table will also serve the purpose

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)
SELECT A + B + C AS Results
FROM (
    SELECT CASE
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    ) a


Benchmarking set up over HDD+SSD combination? I have a Linux system set up (Arch) on an HDD. I have been trying to benchmark some of my queries and found some disk IO bottleneck, and thus opted to move the entire PostgreSQL database from the HDD to my SSD.

I could notice a difference, however, I can't help but wonder if my set up is still imposing some limitations: I have my Linux OS, testing scripts and interface (psql) on an HDD, and my database server is on an SSD. Can I expect much better results if I move literally everything to SSD?

I am not so familiar with how the OS/DBMS interaction is working...

If it helps, the SSD is being exclusively used for the DBMS. And my data size fits comfortably over my RAM and SSD.

For database performance it doesn't matter much on what storage device the operating system or the software is installed. What matters is the database storage and the amount of RAM.

When fixing database performance problems, the first thing to try is to improve the query performance and resource usage.


Null check condition in Where clause DECLARE @SubDesc1 nvarchar(100)
SET @SubDesc1 = null

I would like to do something like that.

Query 1

SELECT *
From Table1
where Category = 'Category1'
  AND [Description] ='ABC'
  AND SubDescription1 IS NULL
   OR SubDescription1 = @SubDesc1

Query 2

SELECT *
FROM Table1
WHERE Category = 'Category1'
  AND [Description] ='ABC'
  AND SubDescription1 = (Case WHEN @SubDesc1 IS NOT NULL
                              Then @SubDesc1
                              ELSE SubDescription1
                              END)

None of them solved if my @SubDesc1 = 'def'

However, it will get me both records where SubDescription1 Is null and SubDescription1 = 'def'

How can I do this?

If NULL value of @SubDesc1 means "return all records, do not apply filtering", then

SELECT *
FROM Table1
WHERE Category = 'Category1'
  AND [Description] ='ABC'
  AND (    SubDescription1 = @SubDesc1
        OR @SubDesc1 IS NULL)

If NULL value of @SubDesc1 means "return only those records, where the field IS NULL too", then

SELECT *
FROM Table1
WHERE Category = 'Category1'
  AND [Description] ='ABC'
  AND (    SubDescription1 = @SubDesc1
        OR COALESCE(@SubDesc1, SubDescription1) IS NULL )


Where are queries against a linked server actually processed? How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering:

Scenario 1:

A stored procedure/view relies entirely upon the linked server for a result set. Does the linked server process all of this and subsequently stream the result set back to the primary server via the network?

Scenario 2:

A stored procedure/view uses local and linked data. I'm assuming that the linked server returns a result set that the local server then executes against its own, local data. I'm also assuming that the local optimizer is not in play here because it has no knowledge of anything on the linked server until it's presented with a data set.

I don't think this is empirically deterministic. My understanding is that an entire table could be brought over and processed on the calling server instead of being processed remotely on the linked server. If both servers are SQL Server 2008+ you should see scenarios like this:

SELECT x.foo FROM linked.db.dbo.x JOIN linked.db.dbo.y ON ...

And in these cases you will see that the join, filters etc. are processed on the remote server. But this should not be construed as a blanket statement! You may see slightly different remoting decisions in the plan for a slightly different scenario:

SELECT x.foo FROM localdb.dbo.x JOIN linked.db.dbo.y ON ...

These are for SQL Server 2008+ -> SQL Server 2008+ linked servers. Other linked servers (lower versions of SQL Server, other platforms altogether) may behave differently, and it may be version-dependent, it could even be specific driver-version dependent, or query-dependent (e.g. is a function used). And it of course could depend on the permissions of the linked server account - if you can't see remote statistics / cardinality for the distributed portion of the query, the local optimizer is going to be pretty blind and may not make the decision you want.

If you want to guarantee that processing occurs on the remote server before it starts streaming data across, and you have actually tried to get it work with four-part names and the plan does not try to process remotely, you can use OPENQUERY instead of explicitly referencing four-part names. Never tried that in a view, though. :-)

If you come across a case where you expect processing to occur remotely and it's not, I'm more than happy to look at the actual plan and the stats profile to help understand why (or at the very least to pass it on to some of my connections who can help).

From my own personal experience, I've found much, much, much more reliable and predictable performance by doing whatever I can to make as many queries as possible entirely local. Linked servers are useful but they certainly don't lend themselves to real-time performance. While you may think it violates DRY in some sense, I would rather pay that cost once during a mirroring session or replication and have redundant data, than potentially pay for a sub-optimal remoting decision for every single cross-server query. And if the data can be slightly delayed there are even more options for moving data across to make it available locally (log shipping, SSIS, custom).


db journaling vs fs journaling SQLite has a pragma for journal_mode, which I assume is available across other sql database programs (MySQL, PostgreSQL, MS-SQL, etc.) so this question would also apply to all those database programs which offer journaling mode like SQLite does.

Most modern file-systems (BtrFS, Ext4, NTFS, etc) use journaling.  As databases are typically stored upon a journaling file-system, is it not redundant for the database program to also be journaling?

When SQLite journal_mode is enabled, large numbers of updates and/or inserts take an extraordinarily long time to complete compared to journal_mode being disabled.

SQLite's pragmas are specific to SQLite; you won't find them (or indeed the PRAGMA statement) in other engines.

As for database "journalling", that's just SQLite's name for its transaction log (see the entry for journal_mode in SQLite's reference). The transaction log is a very different thing to the file-system journal and generally not something you want to disable. Without a transaction log you won't be able to rollback transactions and (as the linked documentation notes): "If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt."

The file-system journal helps ensure that the file-system is consistent (shortly) after a crash; but that says nothing about whether the internal structure of files in that file-system is consistent from the point of view of an application (e.g. SQLite).

In other words: no, it's not redundant and most other databases don't provide any option to disable the transaction log. I'd suggest if your inserts and updates are going slowly, there's probably other reasons (how many changes are made per transaction, etc.)

Journaling is critical to enabling databases to provide the ACID (Atomic, Consistent, Isolated, Durable) guarantees they promise.
SQL-ite supports modes:  DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF

DELETE, TRUNCATE, PERSIST specify different ways of handling the journal file after each transaction commit.
WAL - Write-Ahead Logging allows the database file to be updated in-place.
MEMORY, OFF - Allow inconsistent state on crash, although OFF "allows the database file to be corrupted using ordinary SQL"

For performance, Write-Ahead Logging (WAL) can sometimes be helpful, or using a database designed for multiple concurrent writers.


unable to create schema on amazon rds for postgres I'm trying to create a schema on a postgres instance located on Amazon rds.

That schema would be used to import data from an existing schema built on a local db.

Issue is I do not have permissions to create schemas on RDS.

The user dw_etl I'm using to connect to the postgres instance has the role rds_superuser, which is similar to the superuser role. dw_etl should be able to create new schemas.

Questions:

Is there a way to create schemas with RDS?
If I cannot create schemas on RDS, what would be the best way to "silos" the data? Will the public schema became some kind of dump?

Thanks

RDS PostgreSQL in this sense is no different from any other PostgreSQL installation.  You can, of course, create schemas and whatever objects you want.

The problem here is missing privileges.  By default, the owner of the DB (the role that created it) has full access (CREATE, CONNECT, TEMP) on the DB, and can grant these to other roles, too.

Connect to the DB with the role that created it, then you can try to create a schema to prove the above.  To make other users able to create schemas inside this DB, do the following:

GRANT ALL ON DATABASE your_db TO andy_k;

After this, you can log in as andy_k, and create schemas.


The specified @job_id does not exist After deleting a SQL Server Agent job I continue to get notifications that the job attempted to run and failed. I am receiving the following error message.

  [000] Unable to retrieve steps for job my_job
  [298] SQLServer Error: 14262, The specified @job_id
  ('CC65FEFB-0033-442E-B55E-6209E25C5039') does not exist. [SQLSTATE
  42000]

I have checked the following tables in the msdb database and none of them have the specified job_id except the dbo.sysjobhistory table.

dbo.sysjobs
dbo.sysjobschedules
dbo.sysjobsteps
dbo.sysjobstepslogs
dbo.sysjobservers
dbo.sysjobactivity
dbo.sysjobhistory

Is there anywhere else I can look?
Also, today I have stopped and restarted the SQL Server Agent and will update this post tomorrow with the result.

Thank you in advance!

A stop and start of the SQL Agent fixed the issue.


Caching derived dataset automatically in SQL Server I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there any way to cache the output of the view, and dynamically update it whenever any of the underlying tables are updated?

A few quirks of my system:

The database only sees updates once a day, during off-hours. So a resource-intensive solution is fine, as long as the resources are consumed during these off-hours.
Due to the way the system is set up, I would prefer to not clean up the data while loading it, and would rather leave the underlying tables untouched.
I have appropriate supporting indexes set up
The tables all contain distinct records with no overlap, so I need to pull them all in

Some options I've considered:

An indexed view won't work due to the view relying on the UNION operator.
I haven't found any way to cache the output of a TVF.
I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this, since setting it as a trigger would involve refreshing the table millions of times each evening while the table is overwritten.
I could live with the poor performance, but this would negatively impact end-users.

Here's a simplified example of the view:

CREATE OR ALTER VIEW AllNamesAndAccountNumbers AS
SELECT
    x.AccountNumber
    , COALESCE(x.FullName, n.FullName) AS FullName
FROM (
    SELECT
        AccountNumber
        , FullName
    FROM TableWithNames
    UNION ALL
    SELECT
        AccountNumber
        , NULL AS FullName
    FROM TableMissingNames
) x
LEFT JOIN NameMappings n -- this join is slow due to the table being really huge
ON n.AccountNumber = x.AccountNumber

Oh - if this is an antipattern, and I should be attacking this from a fundamentally different angle, please say so. I'm concerned I'm looking at this wrong.

I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this

That's the fallback.  And it's totally normal to run an ETL job after loading staging tables.

You might be able to do something with 2 indexed views instead of one.  And, of course, you can play with indexes and columnstore tables to improve query performance.


Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server I have two linked servers and want to create a stored procedure that simply SELECTs data from one table from the first server and INSERT the data in other table from the second server.

The procedure code looks like this:

BEGIN TRAN

INSERT INTO [RI].[TEST_DB].[TEST_TABLE] (...)
SELECT ...
FROM [TABLE]

TRUNCATE [TABLE]

COMMIT TRAN

But I get the following error:

  OLE DB provider "SQLNCLI11" for linked server "RI" returned message "Cannot start more transactions on this session.".
  Msg 7395, Level 16, State 2, Procedure usp_test, Line 46
  Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server "RI".
  A nested transaction was required because the XACT_ABORT option was set to OFF.

As the error message suggests, add the following to your stored procedure:

SET XACT_ABORT ON;

See Distributed Queries and Distributed Transactions


Most efficient way to join two tables with MAX value I am optimizing a query, where I have to join two tables, let's say Product and TransactionHistory table, and return multiple columns from both table for the last TransactionDate from the TransactionHistory table for each Product in the Product table.

TransactionHistory table has around 13 Million rows and the Product table has almost 2 million rows.

Sounds pretty easy, doesn't it? but for different scenarios different query perform differently.

I am not using my actual table names here, to show the different query syntax I have tried, I am using Adam Machanic's BigProduct and BigSalesHistory Tables.

The query was originally written like....

SELECT p.ProductID
     , p.Name
     , h.Quantity
     , h.TransactionDate
FROM [dbo].[bigProduct] p
INNER JOIN [dbo].[bigTransactionHistory] h ON p.ProductID = h.ProductID
WHERE h.TransactionDate = ( SELECT MAX(TransactionDate)
                            FROM [dbo].[bigTransactionHistory]
                            WHERE ProductID = h.ProductID )
AND p.ProductID > 1317
AND p.ProductID < 1416
GO

Which resulted in some 30 scan counts and almost 300,000 logical reads.

I being a big fan of windowing functions wrote the following queries using ROW_NUMBER(), both backfired badly:

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
INNER JOIN ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
            ) c
            ON p.ProductID = c.ProductID AND rn = 1
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

AND

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = ProductID
            ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
  AND rn = 1
GO

The Scan count went into 100s and the logical reads jumped to 700,000s.

Being pretty disappointed by the windowing function's performance I rewrote the query using CROSS APPLY

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT TOP 1  h.ProductID
                          , h.Quantity
                          , h.TransactionDate
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = h.ProductID
              ORDER BY h.TransactionDate
             ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

Now this query seems to perform the best out of all the above mentioned but as soon as the OUTER Query's Range for ProductID increases this query goes horribly wrong and starts performing worse than the above two.

Also even though it reduced the logical reads down to 27000 but the CPU time and the elapsed time jumped up by 8 times.

Is there a more efficient way of writing a query like this, keeping in mind that the outer query range for ProductID varies a lot?.

Any suggestion or pointers in the right direction are much appreciated. Thank you.

You need to think about how you would like to solve this if you had a paper-based system.

Like - do you want to use each ProductID one by one and find the newest one? Then your Nested Loop that calls a Seek using TOP 1 on an Index on (ProductID, Transaction Date DESC) will be best.

Do you want to search through all your transactions and group them by ProductID and grab the max? Then that approach could be best.

But I'd suggest to you that what you'd do in real life is to do the second approach on a subset of your data (such as the most recent month), and then do a product-by-product search on the ones that didn't work. Try an outer join onto a subquery that groups by ProductID and filters by TransactionDate to "fairly recent". Then do an Outer Apply to do a Top 1, but include a Predicate in there like where m.ProductID IS NULL, so that it only does those Seeks for products that were missed in the first round. You should see a Filter with a Startup Expression Predicate, and notice that the Index Seek is executed a lot fewer times.

I show this kind of thing at http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/ - look for the plan I show, on the example with two Outer Apply bits.


Do indexes consume memory? I am just starting to learn about memory usage on SQL Server.  When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is taking up the lion's share of space in the buffer pool.  Looking further, using sys.allocation_units and sys.indexes, I confirmed this is likely caused by the heavy use of indexes in the database.  Most indexes are clustered.

Another database developer believes we are having memory issues on the server - that queries are starting to run long because there is no available memory.

My question here is - does the use of these indexes, and their existence in the buffer pool, take away memory available for other processes?

Yes, the data pages of a used index that are cached in the buffer pool will be taking up space in the data cache.  But don't let that turn you away from using indexes (first off, a clustered index is the actual table data so keep that in mind as well).  The use of indexes (properly designed and implemented, of course) is a good thing.

Your memory issues are most likely not from having indexes on your tables.  Dive into the memory issues, what exactly are the problems?  Are you having a low Page Life Expectancy?  How is your memory configured on the server?  Is the max server memory to low restricting the size of the buffer pool?

To get a breakdown of the index pages in your data cache, you can run the below query:

select
    count(*) as total_page_count,
    count(*) * 8 as total_consumption_kb,
    sum(row_count) as total_row_count
from sys.dm_os_buffer_descriptors
where page_type = 'INDEX_PAGE'
group by page_type

To get these stats by database:

select
    db_name(database_id) as database_name,
    count(*) as total_page_count,
    count(*) * 8 as total_consumption_kb,
    sum(row_count) as total_row_count
from sys.dm_os_buffer_descriptors
where page_type = 'INDEX_PAGE'
group by database_id
order by total_consumption_kb desc


Restrict use of tables I am looking to restrict SELECT/INSERT for the 60-80 tables in the database.
The database has very complex security structure, and want to restrict INSERT/SELECT for all users (and except SysAdmin, no other login should have access to those tables).
is that possible? thank you in advance.

By default, SELECT and INSERT access will not be granted. You can REVOKE any SELECT / INSERT perms as follows:
USE MyDatabase;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');
-- you can also filter on o.name to restrict to specific objects
-- and pr.name for specific prinicipals

EXEC sp_executesql @command = @sql;

As Unkush said in the comment, this will only REVOKE any existing access to that explicitly granted. access can also be implictly granted via built the built in db_datareader role and other avenues such as db_owner group and schema ownership.
To harden this further, you can place a deny on the public role (which all users fall into but this won't affect sysadmins) at the database level as follows:
USE MyDatabase;
DENY SELECT ON DATABASE:: MyDatabaseTO [public];

Here is a re-producible test:
First, set up the test db:
USE [master];

/* Create our test db*/
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'PermissionsTest')
BEGIN
    ALTER DATABASE PermissionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PermissionsTest;
END;

CREATE DATABASE PermissionsTest;
GO

USE PermissionsTest;

/* Create some tables*/
CREATE TABLE MyTable1
(
    a INT,
    b INT
);

CREATE TABLE MyTable2
(
    a INT,
    b INT
);

CREATE TABLE MyTable3
(
    a INT,
    b INT
);

CREATE TABLE MyTable4
(
    a INT,
    b INT
);

CREATE TABLE MyTable5
(
    a INT,
    b INT
);

CREATE TABLE MyTable6
(
    a INT,
    b INT
);

/* set up some users */
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin1') DROP LOGIN MyLogin1;
CREATE LOGIN MyLogin1 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser1 FOR LOGIN MyLogin1;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin2') DROP LOGIN MyLogin2;
CREATE LOGIN MyLogin2 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser2 FOR LOGIN MyLogin2;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin3') DROP LOGIN MyLogin3;
CREATE LOGIN MyLogin3 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser3 FOR LOGIN MyLogin3;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin4') DROP LOGIN MyLogin4;
CREATE LOGIN MyLogin4 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser4 FOR LOGIN MyLogin4;

/* grant some explicit permissions */
GRANT SELECT ON MyTable1 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable2 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable3 TO MyUser1,MyUser2;

/* grant some permissions via a custom role */
CREATE ROLE MyRole;
ALTER ROLE MyRole ADD MEMBER MyUser2;
ALTER ROLE MyRole ADD MEMBER MyUser3;

GRANT SELECT ON MyTable4 TO MyRole;
GRANT SELECT ON MyTable5 TO MyRole;

/* add a user to the standard db role */
ALTER ROLE db_datareader ADD MEMBER MyUser4;

Then we can see how these permissions work:
EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permision)*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permission)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (via MyRole membership)*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

Then we can run the dynamic revoke :
USE PermissionsTest;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');

EXEC sp_executesql @command = @sql;

And test (this will leave the db_datareader perms in place)
EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

We can then either place a deny on the entire database (a deny is the most robust way as there are various other avenues users can get SELECT permissions such as db_datareader, schema ownership, db_owner etc)
DENY SELECT ON DATABASE:: PermissionsTest TO [public];
Alternatively, you can supply a more granular DENY at a schema or table level:
DENY SELECT ON TABLE:: MyTable1 TO [public]
Running the Test again:
EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT


Could I use the SQL statement, SELECT * from sysobjects, with C# MySQL Connector - .NET? I would like to port a SQL statement, SELECT * from sysobjects,  written for Microsoft SQL Server  to MySQL version 5.6.

Could I use the SQL statement,  SELECT * from sysobjects, with C# MySQL Connector - .NET or do I have to modify it?

I realize that I can get such a C# MySQL Connector - .NET to compile but that is no guarantee it can be executed correctly?

If I have to modify the SQL statement, how might I do it?
  Any help is greatly appreciated.

The answer is SELECT * FROM INFORMATION_SCHEMA;


Fetch all words from column with like I have a column with description and inside it can contain # value .
what I want is all the words searched from that column with #tag

This is my query

SELECT description FROM  photo where description like '%#aw%';

and this is what I get as output

description
'nice place #awesome'
'my pc #awe'
' nice design #aw'
'my table #awesomedatabase'

and what I want is this output how to modify my query in order to get below output

 description
 #awesome
 #awe
 #aw
 #awesomedatabase

You can use the INSTR function of MYSQL to find the starting position of the word matching your delimiter '%#aw%'. And then use sub string to fetch the word of your interest.

for eg. SELECT SUBSTR(description,INSTR (description,'#'))
FROM photo where description like '%#aw%';

The problem here though is that if the word is followed by anything, it will also be a part of your output. You will need to find the position of the immediate next 'blank space' and use it as the LEN parameter (the 3rd param in SUBSTR function).


What should be the structure for a person-to-person store database I am going to create a person-to-person store with Laravel, a website whose users can share and sell their product or their mobile, TV, Computer, Shoes, Shirts, Books ,Cars, Bags, Animal etc.

I am going to create a table named Products. Other tables about product are, for example, Bugs, Animals, Cars, Shirts, Shoes or Computers.

products table:

product_id   |   cat_id     |   title    |   cost    |    timestamp

cars table:

      id     |  product_fk  |     brand   |   model   |    type    |  Fuel  | ...

Computers table:

     id     |  product_fk  |     brand   |   type     |    CPU     |  GPU    |  hard_disk  |  ...

I will create other, similar, tables.

I want to know if there is another way to do this which is better and easier?

What do you think about this structure?

There could be many way possible to store data for such scenario, as with your design attempt it seems that you are looking to have unique Id to identify each product and if that is the case the you are right with having a productstable and make that product_id an auto increment key.

Now the next thing to make sure that what ever the attributes of all these bugs, Animals, Cars, Shirts, Shoes, Computers which are common can be added in products table, attributes related to sales can be a good choice to save in this table.

Now Let say for car you will have different brand, design, Engine(Diesel/Petrol), seats, Color and so on you may need other look up tables to save these attributes similar for all the other product like Shoes, Computers etc.


PostgreSQL Copy command internals I need to know some internals about the copy command in PostgreSQL 10.
In  particular I need to know if the copy from into a remote database makes batch insertions. If so, what is  the default insertion batch size?

And the insertion will be performed with a bunch of insert queries with n records?( n is the default batch size)

As with all open source projects, the internals of the Postgres COPY statement can be easily discovered. As one can read in the source code comments, Postgres attempts to use batch inserts by default, except in the cases where it is not possible:

when there are any BEFORE or INSTEAD OF triggers on the table.
when the target is a partitioned table and there are any statement-level insert triggers.
when the target is a foreign table.
when the target has any volatile DEFAULT expressions.
when there are any volatile expressions in the query WHERE clause.

If none of the above is true, insert will happen in batches of up to 1000 rows (the default can be changed by recompiling the Postgres server).

For partitioned tables the decision to use batch inserts is made for each partition individually.

While most of the COPY processing happens on the server, the client (psql) responsibility is to send data from a local file one 8K buffer at a time, which is evident from the psql \copy command handler source.


Cannot Access New Installation of MySQL on an Amazon EC2 Instance I installed MySQL server on an Amazon EC2 instance and started it. Seems like it started, but when I try to do mysql -u root I get this error.

mysql -u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using       password: NO)

I don't have a password for the root and don't know how to go forward.
How do I access the new installation?

While others are suggesting a full reset of the MySQL root password via launching into single user mode and then reseting the password manually through a query, that should not be the case on a clean install.

And though this question is from 2015, it’s unclear what version of MySQL is being used. So that said, using MySQL 5.7, the process of getting a password on a clean install has changed. A temporary password is now set in the MySQL daemon log file which should be in this location:

/var/log/mysqld.log

But could also be in this path:

/var/log/mysql/mysqld.log

As explained here in the official MySQL 5.7 docs the password is in that error log and can be found using Grep like this:

grep 'temporary password' /var/log/mysqld.log

You can now login to MySQL using that password by running this command and entering that password when prompted:

mysql -uroot p

Or you can run mysql_secure_installation—which is good practice regardless—and enter and change the password there with that.


Query to remove duplicate calculated values I have a client running SQL Server 2008 R2 SP1, and I can't seem to write a query that has distinct calculated values. I could change the schema to store the value being calculated, but I was curious if there was a way to do this from within a query without modifying schema (for scenarios where client schema modification is not possible)?

City Table:

+-------+----------+--------------------------+----------+
|  id   |   name   |       displayName        | countyID |
+-------+----------+--------------------------+----------+
| ...   | ...      | ...                      | ...      |
| 833   | Portland | Portland, Ashley, AR     | 3323     |
| 21388 | Portland | Portland, Clackamas, OR  | 5439     |
| 21655 | Portland | Portland, Multnomah, OR  | 5462     |
| 21726 | Portland | Portland, Washington, OR | 5470     |
+-------+----------+--------------------------+----------+

County Table

+------+------------+----------------+---------+
|  id  |    name    |  displayName   | stateID |
+------+------------+----------------+---------+
| ...  | ...        | ...            | ...     |
| 5439 | Clackamas  | Clackamas, OR  | 38      |
| 5462 | Multnomah  | Multnomah, OR  | 38      |
| 5470 | Washington | Washington, OR | 38      |
+------+------------+----------------+---------+

State Table

+-----+-------+---------------+
| id  | State | StateFullName |
+-----+-------+---------------+
| ... | ...   | ...           |
| 38  | OR    | Oregon        |
+-----+-------+---------------+

Query:

   select top 100 City.id as cityID
    ,City.name + ', ' + [State].[State] as label
    ,[State].[State] as stateAbbrev
    ,[State].StateFullName as stateName
from [City]
left join [Country] on (City.countyID = Country.id)
left join [State] on (Country.stateID = [State].id)
where City.name + ', ' + [State].[State] like 'Portland, OR'

Results:

+--------+--------------+-------------+-----------+
| cityID |    label     | stateAbbrev | stateName |
+--------+--------------+-------------+-----------+
|  21388 | Portland, OR | OR          | Oregon    |
|  21655 | Portland, OR | OR          | Oregon    |
|  21726 | Portland, OR | OR          | Oregon    |
+--------+--------------+-------------+-----------+

The desired query would return only one row for distinct values of the calculated column "label". As I mentioned earlier, I'm curious if there's a solution that doesn't involve schema modification since that may not always be possible.

Converting my comment as answer (SQLFiddle):

;with cte (cityID, label, rowNum, stateAbbrev, stateName)
as (
    SELECT
    TOP 100 City.id AS cityID,
    City.name + ', ' + [State].[State]  as label,
    row_number() over (partition by cast(City.name + ', ' + [State].[State] as varchar(max)) order by City.id desc) AS rowNum, -- if you want the highest cityID then use "desc" else remove the "desc"
    [State].[State] AS stateAbbrev,
    [State].StateFullName AS stateName
  FROM [City]
  LEFT JOIN [Country] ON(City.countyID = Country.id)
  LEFT JOIN [State] ON(Country.stateID = [State].id)
  WHERE City.name + ', ' + [State].[State] LIKE 'Por%'
  order by City.id)  -- I have put in order by
select cityID, label, stateAbbrev, stateName
        from cte
        where rowNum = 1

The above will result :

 (click here to enlarge)

Note: As Aaron commented, you should remove CityID if not required in output list.


Writing a trigger for every time a user logs in I want to write a trigger which runs every time a user logs in and saves the name of each user, for example.

I have written trigger in this way:

create or replace TRIGGER LOGON_TRG
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO t_log(ID,NAME) VALUES (S1.NextVal,ora_login_user);
END;

Does anyone have any solutions for this?

I would suggest using basic auditing.  Parameter audit_trail can be set to DB, and you can audit system privileges, including CREATE SESSION.  Auditing CREATE SESSION captures (among others), database and O/S user IDs.  Views of interest are sys.aud$ and dba_audit_session.  You may not need to add a logon trigger if the information you want is already captured through auditing.  L Sallwasser


How safe is to use BACKUP LOG WITH TRUNCATE_ONLY As per the title, how much is it safe to use BACKUP LOG db_name WITH TRUNCATE_ONLY in last STEP of any SQL-job execution? Randomly found on google that this Code line can be very harmful in use. But how?

I am using my code as below in one of my SQL-job's last STEP.

BACKUP LOG  my_database with Truncate_Only
DBCC SHRINKFILE (N'my_database_Data')
DBCC SHRINKFILE (N'my_database_Log')

Let me simplify the question with below aspects:

What actually doing this code in the SQL-Job? Does it really help?
Is there anything related performance of the DB-Server?
If I remove first line of code (BACKUP LOG), how it can get effect?
Currently I am not able see SQL-Job history of same SQL-Job, is this the reason that it clears all logs?

This could be opinion based question but still let's discuss if you can share some Ideas on it.

What actually doing this code in the SQL-Job? Does it really help?

SQL is backing up the log to nowhere and then trying to shrink the data and log files of your database. All of these are bad ideas. Backing up the log to nowhere means you lose point-in-time recovery of your database because you cannot restore these portions of the log. See this link for more info.

  Is there anything related performance of the DB-Server?

Constantly shrinking your log and data files can cause performance issues because SQL Server has to regrow these files as needed, which causes waits on your queries that require the additional space.

  If I remove first line of code (BACKUP LOG), how it can get effect?

Before you can reclaim space in the log, you need to backup the data in the log. Removing this would mean your shrinking wouldn't likely free much space, however, as stated before shrinking the log is a bad idea anyway.

  Currently I am not able see SQL-Job history of same SQL-Job, is this the reason that it clears all logs?

No, this is unrelated. Most likely your SQL Agent history is limited and you have a high-frequency job filling the job history tables. See this link.


Settings for a MySQL backup only slave instance I have two instances of MySQL running one a server, set up as master and slave.  It's all working smoothly together and I'm only using the slave instance for backups to avoid locking up the live databases.

What settings would be ideal to lower the Slave instance requirements as much as possible, that aren't important for replication performance.  For example, would setting the innodb buffer to the minimal amount make sense?  Are there any other bits of MySQL that could be lowered or turned off?

Alternative backup solutions may not be an option due to having to run on Windows.

Just a suggestion, but maybe you could look at Percona's hot backup solution here? It's free and open source. It allows for the hot backing up of InnoDB tables. HTH, Paul...


How to export SSIS (SQL Server Integration System) packages to third party data source I want to export SSIS packages into a HANA databse. Please guide me through the exact steps to achieve this.

I found an article about that, the version is to HANA 4.1.

  To move data from SQL Server to HANA, you can use SAP BODS in version
  4.1 SP1 brings improved migrate with an assistant.

  Other methods such as:   Sybase replication server.


SQL Server Linked Server Permissions On a SQL Server 7 server, I have a linked server to a Sybase database server running this command:

select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE')

works fine if I'm logged in as an SA.

If I try to run it while logged in as non-SA, it fails with the error:

  Server: Msg 7302, Level 16, State 1, Line 1 Could not create an
  instance of OLE DB provider 'ASAProv.90'.

What permissions do I need to grant the user to let it work as a NON-sa account?

IIRC, try this. I think this is how I fixed it some time ago...

exec sp_configure 'Show Advanced Options', 1; reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1; reconfigure;
go

Edit, attempt 2:

Check this. I've seen this at a previous company (I wasn't sysadmin there) and can't remember what teh DBAs had to do for us to get our linked server to work (also Sybase)

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx


ORA-01113: file 1 needs media recovery -- system01.dbf The only way to recover is from media backup? I know I cannot "rebuild" system datafile, but if I don't have a backup - I know shame on me! - Is there anyway to recover database?

Without a valid database backup: forget it.
If you do have a valid backup you could use that to restore the database.
If your database is also running in archivelog mode and you have all archives created since that backup, you can also roll forward the transactions in them and doing so recover all data.

It all starts with a valid backup, or a standby database. No magic involved, just common sense.

You could take a look on jdul
 a tool that has 'reads datafiles when db is down or corrupted' in it's description. I never used it. Kurt has made more nice tools.


How to configure pgmodeler? I am having some troubles configuring pgmodeler, particularly the Connections.

I put

  Connection DB: postgres

  Host/Port      localhost           5432

  User           postgres

  Passwort

Could not connect to the database. Message returned: could not connect to server: No route to host Is the server running on host "5432" (0.0.21.56) and accepting TCP/IP connections on port 5432?

I think there is something wrong with my idea. However, I just looked for the required information on the first page of pgadmin after logging into. Can someone explain me how to get the connection from pgmodeler to my postgres DB?

I had ran into this problem and solved changing pg_hba.conf in this line

local    all            postgres        password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password

changed the word ident to password.
It worked for me!


Why does this scalar function not raise an error? My understanding is that scalar functions always return a row, but I don't understand why the first of these functions does not raise an exception while the second does. The only difference is the second accepts an additional parameter.
The first one always returns a row of NULL.
create schema app_testbed;

create table app_testbed.items(
    id serial primary key,
    name varchar
);

-- why does the "other" parameter change how it works?
create or replace function app_testbed.this_should_break_but_doesnt(
  item app_testbed.items,
  other text default null
)
returns app_testbed.items as $$
  declare
    v_item app_testbed.items;
  begin
    raise exception 'Does not raise.';
  end;
$$ language plpgsql strict volatile;

-- without the additional parameter it raises as expected
create or replace function app_testbed.this_breaks(
  item app_testbed.items
)
returns app_testbed.items as $$
  declare
    v_item app_testbed.items;
  begin
    raise exception 'Raises.';
  end;
$$ language plpgsql strict volatile;

Figured it out myself. I did not fully understand how the strict keyword works but the docs state:

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.


How to keep track of functional dependencies when performing a BCNF decomposition I have the following relation and functional dependency set:

R(A,B,C,D,E,G,H)
F = {AB -> CD, E -> D, ABC -> DE, E -> AB, D -> AG, ACD -> BE}

I am really confused about how to keep track of functional dependencies when decomposing R.

For example, if we decompose:

R1 = (ABCD)
R2 = (DEGH)

then (the easy ones):

F1 = {AB -> CD}
F2 = {E -> D}

So what I want to know is, what is the best STRATEGY of keeping track of the rest of the functional dependencies in F1, F2.

Is it enough to find the closure of all subsets of ABCD to complete F1? or should I find closures of all subsets of ABCDEGH before the decomposition occurs? I'm actually not quite sure how this would happen. for example:

Before the decomp:

D+ = DAG

After the decomp

D+ = D?

Decomposition and Functional Dependencies

In general, when a relation R(T) with dependencies F is decomposed in two relations R1(T1), R2(T2), the dependencies holding in the two relations cannot be immediately derived from the original set of dependencies F. This is because there could be dependencies implied by F, that is in F+, that hold in the decomposed relations. If we want to know all the dependencies that hold in the decomposed relations, we should consider the projection of the dependencies F over the attributes of the decomposed relations.

Here is the formal definition of projection of a set of dependencies F: Given R, and Ti ⊆ T, the projection of F on Ti is equal to πTi(F) = {X → Y ∈ F+ | X,Y ⊆ Ti }. In other words, one should calculate F+, the closure of F, and then get all the dependencies that have all the attributes in Ti.

But, since the computation of F+ is an exponential task, the computation of a projection of a set of functional dependencies is an exponential task and it is not performed in practice (a part from simple cases with very few attributes and dependencies).

What instead can be decided with a polynomial algorithm is the answer to this question: does the decomposition R1(T1), R2(T2) of R(T) with dependencies F preserves the dependencies? (see J. Ullman, Principles of Database Systems, Computer Science Press, 1983).

By definition, a decomposition preserves the dependencies if the closure of the dependencies projected over the decomposed relations is equal to the closure of the original relation. Formally, a decomposition Ri(Ti) preserves the dependencies if and only if (∪πTi(F))+ = F+.

Why we are interested in FDs of decomposed relations?

When decomposing for a specific reason, for instance to normalize a relation, we are interested in knowing if the decomposition preserves the dependencies (otherwise it is not a good decomposition).

But, while in the synthesis algorithm for the 3NF we are guaranteed that the decomposition alway preserves the dependencies, the same is not true for the BCNF. On the contrary, there are examples of relations that actually cannot be decomposed in BCNF without losing the dependencies (for instance, R(A,B,C), F={AB → C, C → A}).

Summary

When we decompose for BCNF, we should be prepared to lose some dependency (and, for this reason, in practice the 3NF is used).
The analysis algorithm of BCNF requires an exponential task to be performed, since at each step one should check if a decomposed relation is still not in BCNF, and to know this one should know the projection of the dependencies over it (or at least its cover).

A BCNF for your example

In your example, a BCNF obtained with the classical analysis algorithm is the following (starting with a minimal cover of F and producing a decomposition with a cover of the projection of the dependencies):

R2 < (A B H) , { } >

R3 < (A D G) , { D → A, D → G } >

R4 < (B C D E) , { E → D, E → B, C D → B, B D → C, B D → E } >

Note that in this decomposition the two original dependencies:

{ A B → C D
  A B C → D E }

are lost.


How many pool connection size should be set for a 1000 users Jira system? For a Jira system, if use Jira's default connection configuration as below:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://[IP]:5432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>jiradbuser</username>
    <password>{PASSWORD}</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

In postgresql's config file:

postgresql.conf

max_connections = 300
shared_buffers = 80MB

In /etc/sysctl.conf file:

kernel.shmmax=100663296

At test stage, it can work. But in the production environment, for 1000 users use this system, if one app server serve 500 users, how many pool connection size is good to set in the postgresql.conf file?
And, if use pgbouncer, how many connections it can be reduced?

The answer depends on how many cores you have on your database system, how many concurrent I/O requests it can handle and what the ratio of active versus idle time is while the application is using a connection.

I would start with a maximum of 20 sessions.


Multiple PVSCSI with SQL Server Regarding SQL Server virtualization, been trying to find info if there is a positive performance impact on separating Data devices from Log devices into different Paravirtual SCSI (PVSCSI) Adapters, similar to what is done here.

There has been a scenario on a client where an additional PVSCSI was added and log devices were separated to the new PVSCSI, showing considerable performance gains. Yet, the doubt remains if it was due to this separation or simply due to the fact that an additional PVSCSI was now present.

As it is known, Log disks typically are written in a sequential manner, while Data disks follow a more random pattern in their r/w, and there are performance benefits on placing these two different kinds of files on separate disks.

But what about the controllers? Is there a benefit also on keeping these different patterns in separate PVSCSI controllers?

Anyone has any insight on this?

Thanks in advance

I'll answer in two parts: first "why the traditional answer about separating sequential and random often doesn't apply."

Then I'll discuss the potential benefits of separating files at the Windows physicaldisk, and to adding additional vHBAs and distributing the physicaldisks among them.

Expecting benefit from separating random and sequential disk IO at the Windows physicaldisk level typically assumes HDD devices for the data storage.  It also typically assumes that separate Windows physicaldisks means separate HDD devices.  The idea is that some set of HDDs are handling mainly sequential disk IO and have very limited disk head movement (eg the HDDs hosting a single busy txlog*) while a separate set of HDDs are handling random disk IO.

Those assumptions rarely hold today - especially in a VM.  First of all, unless the VMs Windows physicaldisks are RDMs, multiple of the could be in a single datastore - or maybe multiple datastores are on a single ESXi host LUN.  So what is separated in the guest can be commingled at the ESXi host level.

But let's say that RDMs are used, or that each guest physicaldisk is on its own datastore, on its own ESXi LUN.  Even then, separate sequential from random io in the guest is often commingled at the array, because the LUNs presented to the ESXi host may be from the same single pool of disk devices.  Almost every storage array does this now - either exclusively or as an option to ease management and increase array efficiency/resource utilization.

Finally, so much storage today is either all flash or hybrid flash+HDD.  With no head movement to worry about, flash doesn't care about separation of sequential for random… doesn't even care about IO weaving.

So… those are all the reasons separating sequential from random may not be all that beneficial. Next why spreading files across physicaldisks and spreading physicaldisks across vHBAs can
still boost performance anyway.

*I purposefully mentioned a single transaction log in this HDD example.  When several separate sequential disk IO streams (eg 8 busy transaction logs) are taking place on the same HDDs - unless somehow almost all of the activity is within SAN cache - constant head movement among the sequential IO tracks leads to IO weaving.  That's a specific kind of disk head thrashing which leads to disk latency that is "worse than random".  Happens on RAID5 and RAID10, although RAID10 can tolerate just a little bit more variation in this regard than RAID5 before significant degradation.

Now - given that longwinded talk about how separating sequential from random might not help - how can spreading files across physicaldisks still help? How can spreading physicaldisks among vHBAs help?

It's all about disk IO queues.

Any Windows physicaldisk or LogicalDisk can have up to 255 outstanding disk IOs at a time in what is reported by perfmon as "Current Disk Queue".  From the outstanding disk IOs in the physicaldisk queue, storport can pass up to 254 to the minidriver.  But the minidriver may also have both a service queue (passed down to the next lower level) and a wait queue.  And storport can be told to lower the number it passes on from 254.

In a VMware Windows guest, the pvscsi driver has a default "device" queue depth of 64, where the device is a physicaldisk.  So although perfmon could show up to 255 disk IOs in "current disk queue length" for a single physicaldisk, only up to 64 of them would be passed to the next level at a time (unless defaults are changed).

How many disk IOs can be outstanding to one busy transaction log at a time?  Well, transaction log writes can be up to 60kb in size.  During a high scale ETL, I'll often see every write to the txlog at 60kb.  The txlog writer can have up to 32 writes of 60kb outstanding to one txlog at a time.  So what if I've got a busy staging txlog and a busy dw txlog on the same physicaldisk, with default VMware settings?  If both txlogs are maxing out at 32 outstanding 60kb writes each, that physicaldisk is at its queue depth of 64.  Now… what if there are also flatfiles as an ETL source on the physicaldisk?  Well… between reads to the flatfiles and txlog writes, they'd have to use the wait queue in, because only 64 can get out at a time.  For databases with busy txlogs like that, whether physical server or virtual, I recommend the txlog on its own physicaldisk, with nothing else on the physicaldisk.  That prevents queueing at that level and also eliminates any concern with contents of multiple files interleaving(which is a much, much lesser concern these days).

How many disk IOs can be outstanding to a rowfile at a time(from SQL Server's perspective, not necessarily submitted to lower levels)? There isn't really a limit in SQL Server itself(that I've found, anyway).  But assuming the file is on a single Windows physicaldisk (I do not recommend using striped dynamic disks for SQL Server, that's a topic for another time), there is a limit.  It's the 255 I mentioned before.

With the magic of SQL Server readahead and asynchronous IO, I've seen 4 concurrent queries each running in serial drive a total "current disk queue length" of over 1200!  Because of the 255 limit, that isn't even possible with all rowfile contents on a single physicaldisk.  It was against a primary filegroup with 8 files, each on own physicaldisk.

So readahead reads can be very aggressive, and can stress IO queues.  They can be so aggressive that other rowfile reads and writes end up waiting.  If transaction logs are on the same physicaldisk as rowfiles, during simultaneous readahead reads and txlog writes it's very easy for waiting to take place.  Even if that waiting isn't at the "current disk queue length" level, it may be waiting at the device queue (64 by default with pvscsi).

Backup reads against rowfiles can also be aggressive, especially if buffercount has been tuned in order to maximize backup throughput.

There's one more SQL Server io type to be aware of when considering isolating txlogs: query spill to tempdb.  When query spill takes place, each spilling working writes to tempdb.  Got a lot of parallel workers all spilling at the same time?  That can be quite a write load.  Keeping a busy txlog and important rowfiles away from that can be really helpful :-)

Now, it is possible to change the default device queue depth for the pvscsi driver.  It defaults to 64, and can be set as high as 254 which is the most storport will pass on.  But be careful changing this.  I always recommend aligning the guest device queue depth with the underlying ESXi host LUN queue depth.  And setting ESXi host LUN queue depth per array best practices.
Using an EMC VNX? Host LUN queue depth should be 32.  Guest uses RDMs? Great.  Set guest pvscsi device queue depth to 32 so it's aligned with the ESXi host LUN queue depth.  EMC VMAX? Typically 64 at ESXi host level, 64 in guest.
Pure/Xtremio/IBM FlashSystem?  Sometimes host LUN queue depth will be set as high as 256!  Go ahead and set pvscsi device queue depth to 254 (Max possible) then.

Here's a link with instructions.
https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2053145

The link also talks about requestringpages - WhatAreThose??  They determine queue depth for the pvscsi adapter itself. Each page gives 32 slots in adapter queue depth.  By default, requestringpages is 8 for an adapter queue depth of 256.  It can be set as high as 32 for 1024 adapter queue depth slots.

Let's say everything is at default.  I've got 8 physicaldisks with rowfiles on them, and SQL Server is lightly busy.  There's an average of 32 "current disk queue length" across the 8, and none are higher than 64(everything fits in the various device service queues). Great - that gives 256 OIO. It fits in the device service queues, it fits in the adapter service queue so all 256 make it out of the guest to queues at the ESX host level.

But… if things get a little busier, so an average of 64 with some physical disks' queue as high as 128.  For those devices with more than 64 outstanding, the overage is in a wait queue.  If more than 256 is in the devices' service queue across the 8 physicaldisks, the overage there is in a wait queue until slots in the adapter service queue open up.

In that case, adding another pvscsi vHBA and spreading the physicaldisks between them doubles the total adapter queue depth to 512.  More io can be passed from guest to host at the same time.

Something similar could be achieved by staying at one pvscsi adapter and increasing requestringpages.  Going to 16 would yield 512 slots, and 32 yields 1024 slots.

When possible, I recommend going wide (adding adapters) before going deep (increasing adapter queue depth).  But… on many of the busiest systems, gotta do both: put 4 vHBAs on the guest, and increase requestringpages to 32.

There are lots of other considerations, too. Things like sioc and adaptive queue depth throttling if vmdks are used, configuration of multipathing, configuration of the ESXi adapter beyond LUN queue depth, etc.

But I don't want to overstay my welcome :-)

Lonny Niederstadt
@sqL_handLe


Where can I find old versions of SQL Developer (4.x)? I need a version that works with 11.2.0.4 driver or lower.
In Oracle site there is the current version 20.4.1 and a link to the previous one. Each previous version you click has a link to the previous one, but this chain is broken.
20.4.1 points to 20.4 that points to 20.2 that points to 19.4. The last link is broken.
For some reason, there is a forgotten link to 3.2.2 version that google was able to find.
Do you guys know of any repository for archived versions?

Oracle removed the links, but the files are still there.
Example: http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-4.1.3.20.78-no-jre.zip
Of course before using that direct link, you need to go to any other public download page and accept the license agreement, but after that, it works.
Also the full clients contain some old SQL Developer versions.
11.2.0.4 client (for example: p13390677_112040_MSWIN-x86-64_4of7.zip) contains SQL Developer 1.55.
12.1.0.2 client (for example: p21419221_121020_MSWIN-x86-64_7of10.zip) contains SQL Developer 3.2.20.10.
I did not check newer versions.
You can download those files from https://support.oracle.com/, or any random site you can find with a Google search using the above filenames.


I have to create INDEX but which? I have to create INDEX but which with this query ?

SELECT *
FROM TABLE
WHERE A = 1
  AND B = '5427dbfac0a8280d74bc672c72636a4f'
  AND C = 'BIL'
  AND D = 'B'
  and E = '01'
GROUP BY F,G,E,H,I
ORDER BY B,C,E,G,H,I;

The B, C and E columns will all have constant values as they are included in the WHERE clause, so there is no need to be in the GROUP BY and ORDER BY clauses. Therefore, the ORDER BY can be written as ORDER BY G,H,I
Since you can always rearrange columns in the GROUP BY clause without altering the results, that can be written as GROUP BY G,H,I,F to be aligned with the ORDER BY clause.
No reason to have all the columns in the SELECT list - especially those that are limited by the WHERE clause. Keep only those needed.

The query now becomes:

SELECT F,G,H,I     -- with possibly other columns, as needed
FROM TABLE
WHERE A = 1
  AND B = '5427dbfac0a8280d74bc672c72636a4f'
  AND C = 'BIL'
  AND D = 'B'
  and E = '01'
GROUP BY G,H,I,F
ORDER BY G,H,I ;

and possible indexes that could help:

(A) or (B) or (C) or (D) or (E) to limit the rows. This could have tiny or huge effect on efficiency, depending on the selectivity of the column.
(A,B) or (C,A) or .... or (A,C,D,E,B) i.e. whatever combination of the 5 columns in WHERE or all them (order doesn't matter). This should be better than the previous single-column indexes as the rows need to be returned will be found via the index. The result will still have to be grouped and ordered though.
(A,B,C,D,E, G,H,I,F) Even better as the whole query (selecting, grouping and ordering) can be done with an index scan. If you don't have any more columns in the SELECT list, this is the best index you can have.
(A,B,C,D,E, G,H,I,F, <moreColumns>) If you do have more columns in the SELECT list, you can include them in the index.

Now, which index to use really depends on lot of factors like:

Is this query crucial? How often is it run and how fast does it need to run?
How many rows does the WHERE clause limit the query to? If they are not many, you may just be ok with the (A,B,C,D,E) index or even narrower.
How many rows does the GROUP BY limit the query to and are there calculations in the SELECT list (like MAX(K))?
Can any of these indexes help other queries as well? Indexes with many columns are useful for very few queries and add overhead for the write operations on the table so you wouldn't want to create too many of them.


is it "bad" to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres? pertaining to a previous question I asked:  how do you prevent dead rows from hanging around in postgresql?

If I have a table with 300k rows, and I were to update all rows in the table, setting a boolean column from false to true, is this an OK thing to do or should bulk updates over a certain size always be avoided?  I'm still a bit fuzzy on the whole Vacuum thing here, my understanding is that the MVCC in postgres causes every row to be duplicated temporarily and then auto-vacuum should come in an reclaim that space.  I am wondering, hypothetically, if these 300k rows were to have that boolean value flipped once a week, would I eventually run out of disk space, or would this not be a problem as long as there was enough time between the updates for auto-vacuum to come and keep things in check?

So, 300k rows total doesn't seem like a huge amount, I wouldn't be overly worried unless you have a particular cause for concern (e.g. your UPDATE taking way too long, holding row locks for too long, etc).

But two suggestions which may be helpful for your particular use-case:

First, make sure that your UPDATE statement does not touch rows it does not need to. If you want to set all values of some_bool_column to false, do it like this:

UPDATE mytable SET some_bool_column = false
WHERE some_bool_column IS DISTINCT FROM false;

although semantically the UPDATE above is exactly the same as if it did not have a WHERE clause (ignoring triggers), you will avoid a ton of I/O for tuples which already have some_bool_column = false.

Second, if you can, try to take advantage of Heap-Only Tuples aka the HOT optimization for your UPDATEs. So if you can avoid having an index on some_bool_column, these bulk UPDATEs will be faster and may be able to avoid contributing to index bloat. Often, you don't want to have an index on a boolean column anyway, since the selectivity will be low (or if one of the boolean values is rare, just use a partial index on that value.)


Get latest data for specific identifier I have an application that (mostly) uses only the very latest data available for a certain identifier (may consist of multiple columns/keys). However, these data may be bad or implausible. In these cases, it has to access the history of the entry to verify that value or calculate an alternative.

For the second use case I created an sorted index identifier..., time.
However, in my mind this index does not really fit the main use case, because it still have to consider n values (with different times) for each identifier.

Is there a way in mongodb to create an index on something like identifier..., $max(time)? Or isn't that necessary at all because mongodbs internal query optimization is smart enough to only look at the first/last value of the index?

Environment:

MongoDB 4.0.1
Java Applications
I have plenty of (big) collections that might need this logic (many TB of data in total)
New data are added often; roughly one entry per identifier per minute, sometimes multiple entries per second
Reads occur in roughly fixed intervals (15s) for fixed subsets of ids each (only the readers knows which identifiers he uses); only the latest data are relevant
Readers might do a cold (re-)start needing to fetch the history to better predict the future/understand the current state (also happens when the reader considers the input data as logically implausible).
Network/processing sometimes introduces delays/shuffled input values.

Alternatives considered:

Use separate tables: One for live data, one for historic data

Decreases write performance as I have to write in two collections
Requires some kind of transactional behavior, as I have to make sure that entries will always be added to both collections.
Requires additional logic that makes sure that only the latest values are in the live table. Entries might change order or might arrive multiple times due to network/processing latency.

Use separate tables with some kind of insert hook

Decreases write performance as I have to write in two collections
Introduces additional delay due to the hook processing time
Requires a new tool and thus increases complexity
Requires additional logic that makes sure that only the latest values are in the live table. Entries might change order or might arrive multiple times due to network/processing latency.
Sync/hook failures are hard to detect

Use of some kind of cache (redis)

Requires a new tool and thus increases complexity
Requires some kind of transactional behavior, as I have to make sure that entries will always be added to both tools.
Requires additional logic that makes sure that only the latest values are in the live table. Entries might change order or might arrive multiple times due to network/processing latency.

EDIT

Example:

sensor_id | time    | value
1         | 12:15   | 5.12 <-- I don't need this value or any of its predecessors
1         | 12:16   | 5.15 <-- I need this value
3         | 12:16   | 1.23 <-- and I need this value
4         | 1998 AD | 4200 <-- Not needed
4         | 1999 AD | 5000 <-- but I need this value (even if is years old)

I have an application that (mostly) uses only the very latest data
  available for a certain identifier (may consist of multiple
  columns/keys). However, these data may be bad or implausible. In these
  cases, it has to access the history of the entry to verify that value
  or calculate an alternative.

  For the second use case I created an sorted index identifier..., time.
  However, in my mind this index does not really fit the main use case,
  because it still have to consider n values (with different times) for
  each identifier.

  Is there a way in mongodb to create an index on something like
  identifier..., $max(time)? Or isn't that necessary at all because
  mongodbs internal query optimization is smart enough to only look at
  the first/last value of the index?

As per MongoDB Blog documentation here By default, MongoDB generates a unique ObjectID identifier that is assigned to the _id field in a new document before writing that document to the database. In many cases the default unique identifiers assigned by MongoDB will meet application requirements. However, in some cases an application may need to create custom unique identifiers, such as:

The application may require unique identifiers with a precise number
of digits. For example, unique 12 digit identifiers might be required
for bank account or credit card numbers.
Unique identifiers may need to be generated in a monotonically
increasing and continuous sequential order.
Unique identifiers may need to be independent of a specific database
vendor. Due to the multi-threaded and distributed nature of modern
applications, it is not always a straightforward task to generate
unique identifiers that satisfy application requirements.

Use ObjectID as a unique identifier

MongoDB database drivers by default generate an ObjectID identifier that is assigned to the _id field of each document. In many cases the ObjectID may be used as a unique identifier in an application.

ObjectID is a 96-bit number which is composed as follows:

a 4-byte value representing the seconds since the Unix epoch (which
will not run out of seconds until the year 2106)
a 3-byte machine identifier (usually derived from the MAC address)
a 2-byte process id, and
a 3-byte counter, starting with a random value.

Benefits

ObjectID is automatically generated by the database drivers, and will
be assigned to the _id field of each document.
ObjectID can be considered globally unique for all practical
purposes.
ObjectID encodes the timestamp of its creation time, which may be
used for queries or to sort by creation time.
ObjectID is mostly monotonically increasing.
ObjectID is 96-bits, which is smaller than some (eg. 128-bit)
alternative UUID implementations, which will result in slightly
smaller documents that will use slightly less disk space and RAM than
these alternatives.

Use a single counter document to generate unique identifiers one at a time

A counter document for unique identifier generation could look as follows:

{
    "_id"   : "UNIQUE COUNT DOCUMENT IDENTIFIER",
    "COUNT" : 0,
    "NOTES" : “Increment COUNT using findAndModify to ensure that the COUNT field will be incremented atomically with the fetch of this document",
}

And the unique identifier-generation document could be atomically requested and incremented as follows. Note that by default the document returned from findAndModify is the pre-modification document:

db.uniqueIdentifierCounter.findAndModify({
    query: { _id: "UNIQUE COUNT DOCUMENT IDENTIFIER" },
    update: {
        $inc: { COUNT: 1 },
    },
    writeConcern: 'majority'
})

Use a single counter document that allocates batches of unique identifiers

This approach is similar to the previous approach, with the difference being that instead of incrementing the COUNT value by 1, we may wish to increment it by a larger number that will represent a batch of unique identifiers that will be allocated by the database to the application.

For example, if the application knows that it needs 1000 new unique identifiers, then the application would use findAndModify() to atomically get the current COUNT and increment the COUNT value by 1000. The document returned from the findAndModify command would contain the starting value for the batch of unique identifiers, and the application would loop over 1000 values from that starting point.

var seq_increment = 1000;
db.uniqueIdentifierCounter.findAndModify({
    query: { _id: "UNIQUE COUNT DOCUMENT IDENTIFIER" },
    update: {
        $inc: {COUNT: seq_increment },
    }
    writeConcern: 'majority'
})