MySQL shows CPU usage over 40% though total CPU usage is less than 30% as per TOP - How to debug this issue?

Interpreting the readings from top can be confusing and the details are a bit off-topic for here (perhaps ask questions on https://superuser.com/ if you need more information than you can find elsewhere) so I'll not delve too far, but...
Firstly: the readings in top and similar programs can be misleading as they are only samples. It takes "how many cycles used" for processes at the start of the loop, waits, then takes those counts again, and uses the differences to work out how much each process has used in that second (or longer if you have it set to update slower). There are various issues with this: it will completely miss short lived processes that start and stop in the intervening time, on a really busy system it can miscount because its loop to read the state of each process takes a little while to run through (so the reading for process 123 is not taken at the same instant as that for process 124, etc.). This also means that the total CPU used if you tot it up from the list could be quite different sometimes to the amount used shown in CPU summary lines, for instance indicating more is used than the summary suggests. tl;dr: take any figure presented for a process as a sample or estimate, not an absolutely correct & precise fact.
The CPU used per process is not "% of total" but "% or a CPU core". So that sample says mySQL used 41.2% of available CPU time in the period things were counted for which is 10.3% or your 4-core processor. The rest of that 26% will be other processes doing work or waiting in "IO wait" states. The CPU time it used could be all from one core, or more likely spread around them all. You will sometimes see processes taking more than 100% because they are used more CPU time than is offered by a single core during the sampling period. tl;dr: make sure you know what are per-system, per-CPU, and per-core figures.
You don't say where you get the 26% figure from - that isn't displayed in your screenshots. If you are reading that from another program then it probably isn't based on the same time period as the figures shown in top so the comparison is invalid anyway.
tl;dr: the CPU % figures you have may no mean anything useful =.

is MYSQL not configured correctly?

What makes you think that it might not be, with regard to mySQL's behaviour not just these figures?
Is something running slowly?
Are you expecting it to be busy at that moment so using more CPU time?
If you are not currently asking it to do much work, it won't be using much CPU time. It won't use it just because it is available.


Can I use my DR node as a "backup" in case something went wrong instead of restoring my database using our last daily backup and then procedurally restore the entire transaction log backup from that day?

No.  All changes, including ones you might like to undo, are automatically written to your AG secondaries.  You should take log backups on exactly one replica and all the backups should go to a single location.  You can configure a Backup Preference to help make sure that 1) backups are being taken, and 2) they are taken on your preferred node if it is available.


You can use extended events on Azure to capture deadlock details.

Following article explains in details.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/21/lesson-learned-19-how-to-obtain-the-deadlocks-of-your-azure-sql-database/

Based on the comment I am adding this section.  I just tested this and it works.

You need to run the following query in Master database (Not user database where your deadlock happened) in order to get the XML of the deadlock.

WITH deadlockcte
     AS (SELECT Cast(event_data AS XML) AS [target_data_XML]
         FROM   sys.Fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL
                ))
SELECT
target_data_xml.value('(/event/@timestamp)[1]', 'DateTime2')
AS Timestamp,
target_data_xml.query('/event/data[@name="xml_report"]/value/deadlock')
AS deadlock_xml,
target_data_xml.query('/event/data[@name="database_name"]/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM   deadlockcte

Which will have the details about the deadlock processes and you can get a deadlock graph by:

Copy the deadlock_xml column results from the previous query and load into a text file. If more than one row is returned, you will want to do each row result separate.
Save the file as a ‘.xdl’ extension, (e.g. deadlock.xdl) which can be viewed in tools such as SQL Server Management Studio as a deadlock report/graphic.

Based on the last comment I am adding my test code which does show text in inputbuffer.

 /*

Deadlock simulation code was copied from:
http://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server

*/

--Simulating deadlock

IF OBJECT_ID('Employees') IS NOT NULL
    DROP TABLE Employees

CREATE TABLE Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

IF OBJECT_ID('Suppliers') IS NOT NULL
    DROP TABLE Suppliers

CREATE TABLE Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO
INSERT INTO Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

/*
--Window 1
BEGIN TRAN;
UPDATE Employees
SET EmpName = 'Mary'
WHERE empid = 1

--Window=2
BEGIN TRAN;
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1

Window 1
UPDATE Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1

window 2
UPDATE Employees
SET phone = N'555-9999'
WHERE empid = 1
--commit
*/

DROP TABLE Employees
GO
DROP TABLE Suppliers
GO


Show processlist / Max concurrent connections seem to max out at 131

I once wrote about the kind of things to tweak when seeing output from mysqltuner.pl : Understanding mysqltuner Recomendations w/ Unused Catalogs

Here is the kind of output it generates

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6M (Tables: 30)
[--] Data in InnoDB tables: 359M (Tables: 1206)
[!!] Total fragmented tables: 21

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 15h 35m 57s (30K q [0.036 qps], 9K conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 322.0M global + 5.4M per thread (15 max threads)
[OK] Maximum possible memory usage: 402.6M (6% of installed RAM)
[OK] Slow queries: 0% (0/30K)
[OK] Highest usage of available connections: 13% (2/15)
[OK] Key buffer size / total MyISAM indexes: 2.0M/921.0K
[OK] Key buffer hit rate: 100.0% (19K cached / 8 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 6 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 34% (10 open / 29 opened)
[OK] Open file limit used: 0% (19/2K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)
[!!] InnoDB data size / buffer pool: 359.3M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 359M)

From the output, you should keep watch on

Maximum possible memory usage: 402.6M (6% of installed RAM)
Total buffers: 322.0M global + 5.4M per thread (15 max threads)
Highest usage of available connections

If you see the per thread value, remember that is is made up of

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size

added together and then multiplied by the max_connections. That can have a bearing out how much memory can be consumed. Since you are hitting the 131 connection limit, perhaps you can multiple the per thread value by 131. Once you do, look back at how much RAM is consumed. Remember to reason on the fact that you are in a VM environment. From the OS point of view, you could be swapping or be the victim of process "steal" as seen in %st in top.

Keep in mind that RAM becomes a premium commodity in a VM, and MySQL will adhere to it without question.

I hope this wild brainstorming of mine helps ...


This happens when you restore the backup with a different version of Oracle software.
Use the same version of Oracle software.
Or you can upgrade the database before opening it normally. For that, use alter database open resetlogs upgrade;, and continue with upgrading the database.


How do we get an hour, minute and second difference between two datetimes?

CURRENT_TIMESTAMP returns the current datetime something like 2017-03-09 15:19:53.770.

CONVERT(VARCHAR(10), CreatedDate, 108) returns a string with only time 15:19:53.

The second parameter of DATEDIFF takes a datetime so your string is implicitly converted to a datetime. In lack of something better to use for a date SQL Server uses 1900-01-01 so your second parameter ends up to be 1900-01-01 15:19:53.000 and the function returns the number of minutes since the beginning of the previous century.

Remove the convert in the second parameter and it should work just fine for you.


How can I capture error thrown by SET_LOCKTIMEOUT in ola Hallengren

As you're already doing, @LogToTable = 'Y', should log errors. However, I tested this and I'm not getting an error logged to the commandlog table, either, but it does get logged to an output file. I'm using the latest version of Ola's script and table on SQL 2019. If you add @LockMessageSeverity = 10 (the default is 16), the job will at least run and you won't get alerted. You may want to check your stats, though, because the update stats statement being blocked will be skipped. You could submit this as a possible bug through Ola's github.


Catching slow queries with prepared statements

It's In There

Extended events has the rpc completed class:

SELECT * FROM sys.dm_xe_objects AS dxo WHERE dxo.name = 'rpc_completed';

If you need to show the parameters used, you may need to try one of these events:

CREATE EVENT SESSION what_do_i_get
    ON SERVER
    ADD EVENT sqlserver.module_end
    ( SET collect_statement = ( 1 )
WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.rpc_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sp_statement_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sql_batch_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sql_statement_completed
    ( WHERE ( sqlserver.session_id = ( 55 )))
    ADD TARGET package0.event_file
    ( SET filename = N'what_do_i_get' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = ON,
           STARTUP_STATE = OFF );
GO

The sql_text column won't help you though, you'll wanna use the statement column I believe, depending on how the statements are executed.

As a side note, you may want to be careful if you're using sp_prepare.

See my post here:

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance


Replicating tables for reporting purpose

I have implemented solution 2 multiple times in the past and it has proved extremely effective.

You are right in being concerned about the performance impact of replicating tables though.  You will have to invest time in making sure replication isn't causing issues.  Things to consider would be:

Where you will host the distributor - the DML and DDL performed on the replicated tables are passed from the publisher (where the original tables are) to the distributor.  The actual commands are effectively copied into a set of special tables in a separate "distribution" database.  The distributor has to be powerful enough to deal with this load.  This could be the same instance as the publishing database, but with high volume OLTP or OLAP you may want to scale that out to another machine.
How often do you make schema changes to the tables you are wanting to replicate?  Schema changes are possible, but they can be a nightmare in a replication topology.  This will make schema changes something you will need to be much more careful with in future.
How much data is inserted/updated/deleted in the replicated tables?  Replication can keep with with very high loads, but your underlying systems will need to be able to deal with that (it is very much a system architecture question).  As replication is effectively copying the DML and DDL into a second database you can basically see it as creating double the data (and I/O etc.) compared to the same DB without replication.

There are many more things to consider, but this should give you a quick intro to what needs considering if you went with solution 2.


ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND on

Autoextensible datafiles still have a size limit.

select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';

Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:

1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:

alter database datafile 15 autoextend on maxsize 20G;

Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.

If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:

2) add a new datafile to the tablespace, for example:

alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;


How to monitor backup jobs on Oracle 11g?

I might have found a couple of answers on my own. Posting them here in case others need to know:

Google (or my google skills) seems to be getting increasingly less reliable.
Searching the forums at https://forums.oracle.com/ I found this thread: https://forums.oracle.com/forums/thread.jspa?messageID=9956411, which references http://www.morganslibrary.org/reference/dba_best_practices.html. Here is the code mentioned:

SELECT start_time, end_time, input_type, input_type, status
FROM v$rman_backup_job_details
ORDER BY 1;

and
SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status
FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd
WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time
AND vrbjd.input_type <> 'ARCHIVELOG'
ORDER BY 2,1;

As for why the query in Enterprise Manager picked older jobs I learned a nice trick from someone at work: You can read recent queries from the view v$sqlarea. Something like select sql_text from  v$sqlarea order by last_active_time desc should do the trick.


Can Postgres restrict usage of the SET and SHOW commands?

There are no session parameters in PostgreSQL; SET changes the value of a global parameter for the current session.  If you use it to set a custom parameter (with a name containing .) that does not yet exist, a so-called placeholder parameter is created. There is no way to restrict permissions to view such a placeholder parameter.

What you can do is write a module in C that creates a real parameter. That module has to have a _PG_init function that calls DefineCustomStringVariable (if it is a string you want). That module would then be added to shared_preload_libraries, so that the parameter is defined when the server starts.

Such a parameter can be defined as GUC_SUPERUSER_ONLY, which means that only a superuser (or a member of the pg_read_all_settings role) can see its value.


Determine which SQL drivers an application uses to connect to SQL server

The article mentioned is almost what you want, i think

If you join sys.dm_exec_connections with sys.dm_exec_sessions, you get what you need from columns program_name and client_interface_name:

    SELECT c.connect_time
        ,s.login_time
        ,s.host_name
        ,s.login_name
        ,c.protocol_type
        ,driver_version = CASE SUBSTRING(CAST(c.protocol_version AS BINARY(4)), 1, 1)
            WHEN 0x70
                THEN 'SQL Server 7.0'
            WHEN 0x71
                THEN 'SQL Server 2000'
            WHEN 0x72
                THEN 'SQL Server 2005'
            WHEN 0x73
                THEN 'SQL Server 2008'
            WHEN 0x74
                THEN 'SQL Server 2012+'
            ELSE 'Unknown driver'
            END
        ,s.program_name
        ,s.client_interface_name
        ,s.is_user_process
    FROM sys.dm_exec_connections AS c
    JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id


What joins are available in Informix 9.2?

Ok, at least an outer join works just with different syntax: just using OUTER with a comma after the first table and a WHERE instead of an ON.

Edited to add an example from http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls679.htm

SELECT c.customer_num, c.lname, o.order_num FROM customer c,
   OUTER orders o WHERE c.customer_num = o.customer_num


Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server?

Unfortunately, the group is not presented in the LoginName column => it is the individual user account (DOMAIN\user).

You can get the groups for a given member by executing:

EXEC XP_LOGININFO 'domain\groupname','members'
EXEC XP_LOGININFO 'domain\groupname','all'

This may be able to be wired up using a user-defined event.


Do user database settings affect maintenance job times for system databases?

Those changes will have no affect on the amount of time system backups take to run.

As a first step I would change the job schedules so that the Integrity checks and System backups are not running at the same time.

Also make sure that there are no other jobs running at the same time as either of these.

If you don't have monitoring in place, jump on the server when the backups are running, have a look and see what load the server is under. You can also try moving the Fulls back by an hour or so and see if the time change makes a difference.


How can I check if one specific login has any database users mapped on it?

use this query:
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
    DROP TABLE #results;

CREATE TABLE #results(
    LoginName sysname
    ,LoginType nvarchar(60)
    ,IsMustChange bit
    ,DatabaseName sysname NULL
    ,DatabaseUserName sysname NULL
    ,DatabaseRoleName sysname NULL
    );

EXEC sp_MSforeachdb '
USE [?];
INSERT INTO #results
    SELECT
        sp.name AS LoginName
        ,sp.type_desc AS LoginType
        ,CAST(LOGINPROPERTY ( sp.name , ''IsMustChange'' ) AS bit) AS IsMustChange
        ,DB_NAME() AS DatabaseName
        ,dp.name AS DatabaseUserName
        ,r.name AS DatabaseRoleName
    FROM sys.server_principals sp
    LEFT JOIN sys.database_principals dp ON
        dp.sid = sp.sid
    LEFT JOIN sys.database_role_members drm ON
        drm.member_principal_id = dp.principal_id
    LEFT JOIN sys.database_principals r ON
        r.principal_id = drm.role_principal_id
    WHERE sp.name = ''SomeLogin''';

SELECT * FROM #results;

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


Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED

When a process commits, the locks it held are immediately released to all.  But the rows it inserted are not visible to other snapshots which started before it committed.  So the just-unlocked rows are eligible for someone else to lock, when that someone else is not able to see the inserted rows that would cause them not to select the just-unlocked row.  When you select a row FOR UPDATE, the anticipation is that you will update that row.


SQL Server Management Send Mail as Part of a Job

Here are some steps to troubleshoot the situation. After executing them you're probably be able to identify what's causing your problem. Execute one of them at a time to eliminate the possible problems:

Replace @attach_query_result_as_file = 1, for
@attach_query_result_as_file = 'Danchat',. The idea is to cause an
error to see if causes the job to fail or if it goes unnoticed too;
Clean the job step and leave only a simple mail command like this:
EXEC msdb.dbo.sp_send_dbmail    @profile_name = 'MailProfile', @recipients = 'myaccount@emailcom',     @subject = 'Test'; and try to
run the job. Don't use begin/end or anything else on the step, just a
simple code like that;
To find if the problem comes from the parameter of the @query
variable run the complete code of your question on the job, but use
this @query = N'SELECT 1;' instead of your original query. If the
email arrives, it means you only have to correct that internal query
of yours, if the email is not sent, you should start removing the
other options until the email is sent to find the culprit.
Try using this: @execute_query_database = 'YourDatabase',   @query = N'select * from Timecards WHERE Date >= ''20210418 00:00:00.000'' AND Date < ''20210419 00:00:00.000'' AND (DailyHours > 24.1 OR DailyHours < 0 )', assuming this interval contains any data. If not,
query your table for an interval that has at least one row to see if
it works properly.
Finally, if the mail is not sent only the moment you specify a table
on your query, it's possible the problem is related to privileges
(even though you are using an admin account - maybe some change in
context is happening). Try configuring an extended event session to
capture requests and you will be able to verify that.

Solution:
From chat discussion we were able to figure out the problem was in fact a permission problem and he was able to capture the actual user running the internal query following my last suggestion about an XE Session.


Can we store the queue data in a durable manner and then fetch tasks
  from specific queues based on business needs? (I assume yes, but just
  confirming.)

Yes.

  How reliable is it for 100+ queues? I recall that the older versions
  of the Service Broker were quite unstable.

A queue is a table.  SEND is an insert.  RECEIVE is a delete.  There is some functionality to process queues from inside SQL Server, but you can always just use external clients.

See eg https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316 for a sample of how to process a queue from an external application.

  Can we create new queues dynamically / programmatically?

Yes.  But like creating tables dynamically, it's got bad code smell.

  Can we create min-priority queues (i.e. as tasks are pushed onto the
  queue, it is sorted based on priority)?

Yes.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-broker-priority-transact-sql

  What is the impact in terms of performance and load?

A SEND is a single-row insert into a clustered-index table with a single non-clustered index.  A RECEIVE is a delete of one row.

  Is it true that we need to setup some kind of "conversation" between
  queues in order for it to work?

Every SEND and RECEIVE is in the context of a "conversation" which is a potentially long-lived session between two "services".  conversations can live forever and be reused, and are the source of much confusion for getting started with service broker.

  Are there any other differences / pros/cons that we need to be aware
  of considering one versus the other?

Service broker is quite complex, as all messages are sent on potentially-long-lived duplex conversations, and you must program against it in TSQL.  Once you grok the design, and the strange troubleshooting a lot of the TSQL is boilerplate.

The best thing about Service Broker is that it's not a separate repository and your messages are have the same HA/DR and Backup as your other data.  And it's trivial to enlist SEND or RECEIVE in a transaction involving other application data.

If you don't want in-database messaging, consider Azure Service Bus is much simpler to use, more functional, is operated for you in the cloud, and is able to handle that level of scale quite easily and cheaply.

Also for many scenarios you can just use a simple table as a queue.  See eg http://rusanu.com/2010/03/26/using-tables-as-queues/ from Remus Rusanu, who used to be a SQL on the SQL product team that owned Service Broker.  You don't get blocking reads, but if your latency requirements can tolerate a reader in a polling loop, it can be a good option.


No. This is not possible. You can change the ownership of any object in PostgreSQL only if you are Superuser or if you are the owner of same object.
You should ask your DBA or owner of the schema to do change the owner.


I think the first question is how much RAM the system has.  If you have enough that the database fits comfortably in RAM then this is not a major problem.  Basically the OS disk cache is very fast, and if the db is sitting there effectively, then you don't have anything to worry about.  If you are on a system with, say, 4GB RAM and not a whole lot else running, I wouldn't worry.

The problem happens when this is not true, and then the shared buffers try to hold the most frequently used information.  I would expect that caching would be ok there too. What you have to remember is that the PostgreSQL cache is slower than the OS cache because it provides features aimed at keeping frequently used data in the cache.

Of course all of the above depends highly on your query patterns.


Single Database Upgrade (From App side) which is part of Always On

Not sure I’m following why you need downtime at all. Aside from isolation and schema locks, changing a table is no different from changing data - it gets logged, sent to secondaries, etc. This is never instantaneous, but it can often be so close that there’s no way your app would notice.

  Unless both the replicas are in sync we are not allowed to keep the app running. Also we are a bit worried on the database size. If it was for a small database we would have gone straight as you mentioned. So, it will be fine to go ahead without breaking the AG?

It is 100% impossible to update both the primary and the secondary at the exact same moment in time, so let's throw that requirement out the window straight away. The app may require downtime in that case, which you can accomplish in other ways, but I meant database downtime. I don't think a schema change warrants pulling a database out of an AG. Synchronous should be very fast unless you have other configuration / latency issues already, and can you explain exactly why the rule about the replicas in sync and the app running? Surely your schema changes are backward compatible in any case?

  So you mean the Steps I mentioned is fine? Yes I meant the same in terms of downtime. Also This is set for Auto Failover which is why we want to stop the AG before doing any changes.

No, I still don't know why your steps include removing the database from the AG and putting it back in with a fresh backup. Failover seems unrelated, unless your schema changes are potentially so bad that they will cause a failover while you are deploying the changes. Also, once you add the database back into the AG, then the secondary will be out of sync until it catches up again, so this still seems to break your unnecessary rule about keeping the app running.

  Got your Point Aaron. And I totally agree. This is a critical vendor managed app and we are not sure on what kind of schema changes will happen during the upgrade. We are in discussion to finalize a full proof plan to go without any hiccups. In case they want to roll back, the secondary can save us. This is another reason we want to go that way.

Secondary can't help in your current plan, of course. If you pull the database out of the AG, there is no secondary.


The simple and fast solution are two indexes on (a, id) and (b, id). Be sure to have a respectively b as leading column:

Working of indexes in PostgreSQL

The added id is not going to help for your particular query, you could as well just create them on (a) and (b) to get the bitmap index scans that @shx explained. But the index for two integer is exactly as big as for one column. There may be other use cases that actually benefit from the added id.

Is a composite index also good for queries on the first field?

If your actual SELECT list is narrow like in your example and some preconditions are met, I would go for index-only scans.
Create indexes on (a, b, id) and (b, a, id).

This equivalent query (given that id or at least (id,a,b) is unique) helps Postgres choose the query plan with index-only scans:

EXPLAIN
SELECT id,a,b FROM t WHERE a = 10
UNION
SELECT id,a,b FROM t WHERE b = 10
ORDER  BY id;

Sort  (cost=110.45..112.92 rows=988 width=12)
  Sort Key: t.id
  ->  HashAggregate  (cost=51.42..61.31 rows=988 width=12)
        Group Key: t.id, t.a, t.b
        ->  Append  (cost=0.42..44.02 rows=988 width=12)
              ->  Index Only Scan using t_ab_id_idx on t  (cost=0.42..17.07 rows=494 width=12)
                    Index Cond: (a = 10)
              ->  Index Only Scan using t_ba_id_idx on t t_1  (cost=0.42..17.07 rows=494 width=12)
                    Index Cond: (b = 10)

Performance depends on data distribution, write patterns and value frequencies, among other things. In my tests on pg 9.5 I see similar performance to the solution in @shx's answer - as long as we select whole rows (i.e. heap tuples are not much bigger than index tuples).

Typically, there are additional columns in the underlying table - which would not impair performance of this query at all, while the alternative loses ground since it has to read more pages for wider rows in the underlying table.

Answer to added question

  Is there any use of the fact that we always look for same value in columns a and b? Can we create an expression index for that?

I can't think of a way to capitalize on that for the table as is. An index entry can only reference a single table row. Theoretically, a GIN index on (ARRAY[a,b]) might work, but I could not get useful results with it (nor did I expect to).

You would need two rows per row in the base table (except where a = b) to enable a single pass on a b-tree index for the job. Actually possible with the help of a MATERIALIZED VIEW. The added overhead and maintenance cost only seems reasonable if you have much more read than write activity and you need to optimize performance for your given query. You need to understand MVs and know when to refresh.

CREATE MATERIALIZED VIEW mv_t AS
SELECT a AS x, id, a, b FROM t
UNION  -- eliminate dupes
SELECT b AS x, id, a, b FROM t
ORDER  BY x, id;

x is the unified search key. Rows in the base table are listed once for every distinct value in [a, b]. This query returns the same result as your original, but faster:

SELECT id,a,b FROM mv_t WHERE x = 10 ORDER BY id;

The query can benefit from a single pass on a single index now. Plus, rows in the MV are sorted physically like they are returned, which helps the approach with a simple index:

CREATE INDEX mv_t_x_idx ON mv_t (x); -- simple

Sort  (cost=88.01..90.48 rows=990 width=12)
  Sort Key: id
  ->  Index Scan using mv_t_x_idx on mv_t  (cost=0.42..38.75 rows=990 width=12)
        Index Cond: (x = 10)

Also ideal for a BRIN index (Postgres 9.5+) for huge tables:

Speed up creation of Postgres partial index

You can go for index-only scans again:

CREATE INDEX mv_t_full_idx ON mv_t (x, id, a, b);  -- covering index

Index Only Scan using mv_t_full_idx on mv_t  (cost=0.42..33.75 rows=990 width=12)
  Index Cond: (x = 10)


archiving tables that are related to other tables with FK

We faced exactly the same scenario.  Our solution was to have a SQL Server Agent job with a steps to call stored procs co-ordinating the delete/archive.

To make our lives easier we had the application that persisted the data in the first place write a DateTimeCreated field in each related table with exactly the same date/time.  For example an ProductEnquiry record and associated ProductResults record would get exactly the same date/time.

Our clustered index was on that DateTimeCreated so DELETE FROM ProductResults WHERE DateTimeCreated BETWEEN... made use of the clustered index.

As the database grew we found that the purge jobs had to have a pre/post step to disable/re-enable the FK constraints.

We also had to start using a loop so we purged 50,000 records at a time and kept going until there were no more qualifying records to delete.

When we move to Enterprise edition we started using partition switching which had a dramatic performance improvement on purge activity and massively reduced IO on our SAN.


Which query performs better in MongoDB ($or or $facet)?

$facet - Index Use says:

The $facet stage, and its sub-pipelines, cannot make use of indexes,
even if its sub-pipelines use $match or if $facet is the first stage
in the pipeline. The $facet stage will always perform a COLLSCAN
during execution.

$or Clauses and Indexes says:

When evaluating the clauses in the $or expression, MongoDB either
performs a collection scan or, if all the clauses are supported by
indexes, MongoDB performs index scans.

And, your query's $match stage:
$match: {
    $or: [
        { type: 'Event' },
        { type: 'Task' }
    ]
  }

can be simply written as:
$match: { type: { $in: [ 'Event', 'Task' ] } }

See $or versus $in says:

When using $or with  that are equality checks for the
value of the same field, use the $in operator instead of the $or
operator.

Using the $or with proper indexing is likely to perform better than that of using the $facet stage.


Field order in a composite index order with high selectivity and low selectivity fields

As an alternative to @AaronBertrand's solution (if you can't or don't want to create an indexed view), I would recommend you to create an index on (Enroll_Date, UserID). If this type of question is very common on your table, this should probably even be your clustered index.

I would not generally recommend high-selectivity indexes as a general "best practice", but rather look at what index will give your query the best performance.

An index on (Enroll_Date, UserID) will give your query a highly optimized, non-blocking query plan with Stream Aggregates.

"Non-blocking" in this context means that the query doesn't need to buffer any significant amounts of data (like, for instance, a sort or hash aggregate would), which means it (a) starts returning rows immediately, and (b) consumes practically no working memory.


Database normalization, 3 mistake [stucked] - Homework

Note: my university days are long behind me - I'm coming at this from a real-world perspective. Doesn't mean the answers are the expected ones, but may help give you an argument for your choices. A good instructor would consider such thing....

Agreed - a customer logically would be in a country, not a continent.
While TOTAL_AMOUNT is presumably the sum of QUANTITY_SHIPPED * PRICE for all  Items in an Order, I wouldn't consider it a normalization issue. It's a stored calculation. In a real system, the total amount could be impacted by discounts or coupons, and (in the other direction) by sales tax. So, it's arguably appropriate.
First, the CITY fields in customer, country, and division wouldn't necessarily match each other anyway. Second, a given city can have multiple zip codes, and a given zip code can cover multiple cities (at least in the United States). If you repeated the customer's address in the order, that would probably be considered a normalization error. (Arguably - I have had Amazon orders shipped to several different addresses....)
While a person certainly can have multiple email addresses, unless the assignment specified that you had to account for multiple email addresses, I would leave that be.
I assume each customer has one and only one sales rep, whereas each sales rep (worker_id) might have multiple customers; and, that each worker belongs to one and only one division. No need for a table in between, then. Many-to-many relationships are where you normally need some sort of mapping table to define the connections.

I assume that the tool you are using points the connections at essential random points on each table's box; if so, it's hard to be certain all the relationships are indeed set up with the correct directionality; if you haven't, I'd double-check that.

You have a salesrep_id in both customer and order. Again, real-world experience tells me that's wise (Real sales reps often move from one territory to another, and, if customers have real sales reps, you might be very interested in just who sold Customer X what, years later). However, in the context of your assignment, that might be considered a denormalization.

The only other thing that leapt out at me as I looked at the diagram was that there was no product table. I find that somewhat surprising. If there was a separate product table, then of course some of the fields in the items table would move there.


Cassandra upgrade/repair issues in migration

It turns out that the original cluster had different DC and rack names in it's topology, I changed the names and copied the data again and it works.


SSRS report expression FormatDateTime changes Nulls to '1/1/0001'

Below will work :

=IIf(IsNothing(Fields!Temperature_Control_Inspection_Date.Value), "", FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value, DateFormat.ShortDate))

Above will have null dates as blank. Also, it will remove the time and just get you the date portion.


How to send an email with embedded values

You have to use dbname.schema.objectName e.g. dbname.dbo.MYTABLE in your tsql

EXEC msdb.dbo.sp_send_dbmail
        @recipients='MYMAIL',
        @body='100 Daily documents downloaded',
        @subject ='100 Daily documents downloaded from user',
        @query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100'''

EDIT:

You only want to send email when there are 100 Daily documents downloaded from user, so you can add

if exists (
        select 1
        from dbName.schema.MYTABLE
        where MYCOUNTER > '100'
        )
begin
    exec msdb.dbo.sp_send_dbmail @recipients = 'MYMAIL'
        ,@body = '100 Daily documents downloaded'
        ,@subject = '100 Daily documents downloaded from user'
        ,@query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100'''
end
else
    print ' the user has not downloaded 100 docs yet'

Note: Why are you storing integers as chars ? e.g. 100 is stored as '100' ?


pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible

So I have tried to use Dropbox and other services, but I had the same error until the tenth attempt. Here is what I did:

I logged in to my with my Heroku cli from my terminal on the master branch so I don't have to redefine the app. (specify the path to your damp. I have mine on the same directory) e.g:

[murph@ComputerX languageandcode]$
the I run

[murph@ComputerX languageandcode]$ heroku pg:psql < langandcode.sql

Then I became happy as pushing started and completed successfully.


moving large PostgreSQL/PostGIS database

I would recommend dumping the 9.1 database from your new 9.3 server like this:

pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump

I recommend using the 9.3 pg_dump as pg_dump is always backwards compatible, but not forward compatible. In other words, the newer pg_dump will take care of any syntax changes that the new server requires that the older utility doesn't know about.

Be sure to make sure your pg_hba.conf and listen_addresses in postgresql.conf are set up to allow you to remotely connect and dump appropriately as well.

If you wanted to try a dump and restore in one step, you could try this as well:

pg_dump -h remotehost -U remoteuser remotedbname | psql -U localuser localdbname

Hope that helps. =)


How do I recover 350 Gigabytes of unallocated space

Do you know everything that goes on with this app / database?

Imagine this scenario:

There's a Very Important Person at your company that needs to get (daily | monthly | quarterly | annual) financial reports
These reports are generated by a scheduled job
This job generates hundreds of GBs of data in the database - creating work tables, crunching numbers, etc
Afterwards, the job "cleans up" after itself by dropping (or truncating + rebuilding) the work tables

This results in a lot of unused space in the database - but the size of the files doesn't change

One day, a DBA comes along and shrinks this database
Some day in the future, this reporting job tries to run - but it grinds to a halt as the file has to grow over and over and over
Maybe the job even fails, or is delayed so long that it starts to affect performance during normal business hours
Very Important Person is mad, because they don't have their important reports, and everyone is complaining to them about the system being slow

You don't want that to happen, do you?

All that being said, if you're sure that the database:

really doesn't need this space, and
will never use this space because it doesn't gain data that quickly, and
you have an actual need for the space on this drive (paying by the GB to a cloud vendor, expensive SAN that other apps need space on, etc),

then by all means, shrink it.

Maybe there was a typo in the filesize when this database was first created (someone tacked a 0 onto the end of "37" - poof, 370 GB database file).

Maybe there was a one-time data load / deletion that will never happen again that caused the DB to grow to this size.

Make sure to give some thought to the "target size" you use with SHRINKFILE if you do this.  You say it's only 10 GB of used space, but I wouldn't immediately set it to 10 GB - you are putting some data into this database, right?


Fetch wikipedia articles query optimization in mysql wikipedia Database

OFFSET must locate, but skip over, the rows.  If you can "remember where you left off", performance may be significantly better.

But there is a worse problem with your query.  Since there is no ORDER BY, the optimizer is free to provide the rows in any order it likes, thereby leading to missing or duplicated rows.

Even if you add an ORDER BY, OFFSET can give miss or duplicate rows if the table is being added to or deleted from.

Read my blog on this problem, then start over on your query.


Concatenation behaves differently on two servers

Most probably the two servers have different SQL_MODE settings.

One has PIPES_AS_CONCAT defined and the other not. The server that hasn't this setting, treats || ("pipes") as a synonym to OR and not as the string concatenation operator.


SQL Server Database Migration - Backup/Restore while Original DB still in Production

First step is to change the recovery mode to FULL.

Take a full backup (with compression), and restore the full backup to new server with NORECOVERY. This will allow you to restore additional log backups.

Now configure logshipping from the primary (old) to secondary (new) server.

During the time of cutover, take a tail log backup and bring the secondary server online by restoring the log backup WITH RECOVERY.

make sure you have instant file initialization enabled on both servers to cut down the restore times.

Refer to my answer for Moving Databases to New Datacenters for more detailed steps.


How to block SSMS from accessing the internet?

Instead of fully impersonating your privileged account, start SSMS with runas /netonly.  That will cause the SSMS Windows process to run as your logged-on account, and only use the privileged account credentials when accessing domain resources using NTLM authentication.
Http requests to internet resources will not use NTLM for resources outside of your trusted domain.
Alternatively you can configure the credentials in the Windows Credential Manager.
This is the same technique used to connect using Windows Integrated Authentication to a SQL Server from a machine not joined to a domain trusted by the SQL Server's domain.  See eg: https://www.mssqltips.com/sqlservertip/3250/connect-to-sql-servers-in-another-domain-using-windows-authentication/


Referring to function parameters inside a Postgres function

Crosstab just sees a string, so send a good string.

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
        BEGIN
                RETURN QUERY
                WITH t AS (
                    SELECT p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
                      FROM crosstab (
                        format(
                          $$
                            SELECT postcode::text, indicator::int, value::float
                            FROM my_data
                            WHERE (indicator = %s or indicator = %s)
                              AND date = '%s'
                            ORDER BY 1,2
                          $$,
                          p2,
                          p3,
                          p1
                        )
                      )
                      AS ct (
                        "postcode" text,
                        "price" float,
                        "rent" float)
                )
                select * from t where result is not null;
        END
$func$
LANGUAGE plpgsql;

Btw, if you're going to used named params, p1, p2, p3, etc are a bad idea. They're already $1, $2, $2. I would suggested at least something a little easier to follow like indicator1, indicator2


oracle-mysql fast refresh materialized view

A fast refresh requires (among many other things) materialized view logs on source tables.

Refresh types

  You can perform fast refreshes of materialized views only when the
  master table or master materialized view has a materialized view log.

That functionality is specific to and implemented in Oracle, and not available in MySQL. It is not like no other database can provide the logically similar functionality, but this is the Oracle implementation, that works with Oracle databases.


ORA-27101 while trying to login after shutdown immediate

I think you did the following steps and caught this error.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

You tried to start SQL*PLUS and starting the database.

[oracle@myserver oracle]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 00:20:59 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -688980181
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

Enter user-name: sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -688980181
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

The problem is the database has not started to do the authentication for these supplied usernames.

You could connect to the idle instance using as as sysdba as the username to start the instance.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  729808896 bytes
Fixed Size          8797056 bytes
Variable Size         452985984 bytes
Database Buffers      260046848 bytes
Redo Buffers            7979008 bytes
Database mounted.
Database opened.
SQL>


how does CXPACKETS relates to CXCONSUMER wait types?

CXCONSUMER occurs when a consumer thread waits for a producer thread to send rows.
CXPACKET occurs when trying to synchronize the query processor exchange iterator or getting a required buffer.

CXCONSUMER waits are generally benign, encountered as a normal part of parallel(CXPACKET) execution.


NULLs in a composite primary key - SQL Server

There is a lot of info missing in your question

Can a pet have multiple owners? eg we have a pet, not I have a pet
Do you expect more pet types (eg Rabbit, Bird, Fish)?

The problem with your current model (as shown) is the Pets table: it adds no value currently. Of course, your simplified design now hides information that probably makes it useful...

I'll throw in some ideas with assumptions though.

Note: This is correct design, whether EF can deal with it or not. That is, you design the model, implement the database, then make your client work with it

Option 1:

Assuming an owner can have 0..n dogs and/or 0..n pets, then you'd need 3 object tables and 2 link tables. You don't need Pet.

Owner, Cat, Dog
Link tables for CatOwner and DogOwner

This allows multiple owners per Dog and Cat. You can of course just have an OwnerID in Dog and Cat to restrict to one owner.

You can have different attributes for Cat and Dog here.

As you add more types, this becomes unwieldy (no matter how you model owners)

Option 2:

Do you need to distinguish Cat or Dog as separate entities?
Isn't this just a type attribute of Pet?

**Pet
PetID, PK,
PetType, FK,
PetName
OwnerID, FK (optional, see below)
...

You can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

This requires each Cat or Dog (or Rabbit, Snake etc) object to have the same attributes.

Option 3:

The final way is superkey/subtype.

This is is useful because if combines elements of options 1 (different attributes per Cat and Dog) and option 2 (keeps the idea of Pet)

This defines one Pet object that has exactly one sub type of Dog, Cat, Rabbit, Snake etc. The super key comes from the extra UQ1 in Pet to enforce subtype. Pet as a table also stores common attributes.

**Pet
PetID, PK, UQ1
PetType, UQ1, 'Dog' or 'Cat'
PetName
OwnerID, FK (optional, see below)
...

**Dog
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Dog'
Breed  -- per Dog,not per Pet
CatsChased
...

**Cat
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Cat'
Breed -- per Cat, not per Pet
MiceEaten
...

Note: UQ1 is a unique constraint used by the FKs in Cat and Dog. It is a "super key" on Pet that allows sub-types via the FK

Again, you can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

Note, PetType can be a constant derived column in Dog and Cat

Edit: added tables for Pet, Cat and Dog.
I assume Owner is obvious...

CREATE TABLE PetSpecies (
    PetSpeciesID tinyint NOT NULL IDENTITY (1,1),
    Species varchar(100) NOT NULL,

    CONSTRAINT PK_PetSpecies PRIMARY KEY CLUSTERED (PetSpeciesID),
    CONSTRAINT UQ_PetSpecies UNIQUE (Species)
);
INSERT PetSpecies (Species) VALUES ('Dog');
INSERT PetSpecies (Species) VALUES ('Cat');

CREATE TABLE Pet (
    PetID int NOT NULL IDENTITY (1,1),
    PetSpeciesID tinyint NOT NULL,
    PetName varchar(100) NOT NULL
    OwnerID int NOT NULL,
    ...

    CONSTRAINT PK_Pet PRIMARY KEY CLUSTERED (PetID),
    CONSTRAINT FK_Pet_PetSpecies FOREIGN KEY (PetSpeciesID)
                                 REFERENCES PetSpecies (PetSpeciesID),
    CONSTRAINT FK_Pet_Owner FOREIGN KEY (OwnerID) REFERENCE Owner (OwnerID),
    CONSTRAINT UQ_SuperKey UNIQUE (PetID, PetSpeciesID)
    );

CREATE TABLE Dog (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    CatsChased int NOT NULL,

    CONSTRAINT PK_Dog PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Dog UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 1 /*dog*/),
    CONSTRAINT FK_Dog_Pet FOREIGN KEY (PetID, PetSpeciesID)
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

CREATE TABLE Cat (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    MiceEaten int NOT NULL,

    CONSTRAINT PK_Cat PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Cat UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 2 /*cat*/),
    CONSTRAINT FK_Cat_Pet FOREIGN KEY (PetID, PetSpeciesID)
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

Notes:

The PK of cat and dog can be PetID or PetID, PetSpeciesID. The latter is better to match the foreign key.
The unique constraint on cat and dog  is optional, really, as the index is contained within the PK and PetID is unique anyway if PetSpeciesID is constrained to a single value.


You say that the hierarchy gets modified.  Presumably while this operation is running, there is some amount of blocking which is taking place then?

Even if the hierarchy is changing, are the roots for items changing?

Have you looked at the time it would take to just make the mapping table from root to item and index it?

I would like to see the execution plan to see what is happening - the CTE should get spooled, but as a manually materialized and indexed table it might perform better in the later steps.

Even with heavy activity, it would seem to me that someone has to be blocked if DML operations are changing data which this process is reading.

So I'd strongly consider taking a snapshot of the hierarchy.

In addition, you have a number of other INNER JOINs - you should review whether it is, in fact, the CTEs at all and whether there are any indexes which are missing to make those joins effective.  The execution plan should tell you that.

You appear to have quite a few things in the WHERE clause which might help reduce some operations (and determine which indexes might be the best)), but it's hard to tell without looking at the execution plan or the indexes.


How to apply a theme to SQL Server Management Studio 2008?

I used this approach and theme provided here, it worked for me in SSMS 2008:

Beautify Your Management Studio 2005/2008

  To install, simply backup your [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FontAndColors{A27B4E24-A735-4D1D-B8E7-9716E1E3D8E0}] registry key, and load in this new set (standard disclaimer about being careful with your registry, make sure you know what you’re doing, don’t blame me, etc). You can then either restart SSMS, or go to the Tools, Options, Environements, Fonts and Colors and simply click OK.

There is also a comment which provides the proper registry location for changing the theme in SSMS 2012, which only seems to differ by the version number.


With your original code as such:

    SELECT COUNTY                                  COUNTY,
       DECODE(RATE,'115A','R','C')             R_C,
       SUM(MTH_CHG)                            MO_CHG,
       SUM(YTD_CHG)                            YR_CHG,
       COUNT(CUST)                             TOTAL_CUST
    FROM RPT_BLOC_VW,
       (SELECT SRAT_CODE               RATE,
        NVL(billed_chg,0) + NVL(adj_chg,0) MTH_CHG,
         0                             YTD_CHG,
         BLOC_CODE
        FROM RPT_REV_CON_ETL,
          (SELECT DISTINCT
           CUST_CODE,
           PREM_CODE AS CUST
    FROM   RPT_REV_CON_ETL
    WHERE PROCESS_DATE BETWEEN '28-OCT-16' AND '29-NOV-16' AND DESC = '115'
    )
          WHERE PROCESS_DATE BETWEEN '28-OCT-16' AND '29-NOV-16'
        AND DESC = '115'
     ) DATA
     WHERE DATA.BLOC_CODE = RPT_BLOC_VW.BLOC_CODE(+)
 GROUP BY COUNTY, DECODE(RATE,'115A','R','C')
 ORDER BY COUNTY, DECODE(RATE,'115A','R','C') DESC ;

I start by rewriting it to (this helps me think about it a little differently, which is weird, I'm sure, but this is how I approach it)

SELECT county            COUNTY,
       Decode(rate, '115A', 'R', 'C') R_C,
       SUM(mth_chg)      MO_CHG,
       SUM(ytd_chg)      YR_CHG,
       Count(cust)       TOTAL_CUST
FROM   rpt_bloc_vw,
       (SELECT srat_code                            RATE,
               Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG,
               0                                    YTD_CHG,
               bloc_code
        FROM   rpt_rev_con_etl,
               (SELECT DISTINCT cust_code,
                                prem_code AS CUST
                FROM   rpt_rev_con_etl
                WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16'
                       AND desc = '115')
        WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16'
               AND desc = '115') DATA
WHERE  DATA.bloc_code = rpt_bloc_vw.bloc_code(+)
GROUP  BY county, Decode(rate, '115A', 'R', 'C')
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC;

If I rewrite your query to use CTEs like so:

WITH DistinctCustomers AS (
    SELECT DISTINCT cust_code,
                    prem_code AS CUST
    FROM   rpt_rev_con_etl
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
),
DATA as (
    SELECT srat_code                            RATE,
           Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG,
           0                                    YTD_CHG,
           bloc_code ,
           cust.CUST
    FROM   rpt_rev_con_etl etl
    JOIN   DistinctCustomers cust ON cust.cust_code = etl.cust_code
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
)
SELECT county COUNTY,
       Decode(rate, '115A', 'R', 'C') R_C,
       SUM(mth_chg) MO_CHG,
       SUM(ytd_chg) YR_CHG,
       Count(cust) TOTAL_CUST
FROM   rpt_bloc_vw rpt
JOIN   DATA d on d.bloc_code = rpt.bloc_code(+)
GROUP  BY county, Decode(rate, '115A', 'R', 'C')
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC;

It looks like, if I read this correctly, that you are first getting the distinct customers that had any processes between the date ranges.

Then you go back for the rate codes and charges for those customers. You could probably just do a join there and not need the distinct (which was probably added because it wasn't written correctly, by my eye, but I don't write a lot of Oracle SQL, just MS SQL).

That gives us this:

WITH DATA as (
    SELECT srat_code                            RATE,
           Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG,
           0                                    YTD_CHG,
           bloc_code ,
           prem_code as Cust
    FROM   rpt_rev_con_etl etl
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
)
SELECT county COUNTY,
       Decode(rate, '115A', 'R', 'C') R_C,
       SUM(mth_chg) MO_CHG,
       SUM(ytd_chg) YR_CHG,
       Count(cust) TOTAL_CUST
FROM   rpt_bloc_vw rpt
JOIN   DATA d on d.bloc_code = rpt.bloc_code(+)
GROUP  BY county, Decode(rate, '115A', 'R', 'C')
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC;

Since I don't have a table schema, I'm just assuming that what I've done so far is correct. Pretty sure I got this under control, but see the first query to tell me what I did wrong if I got it wrong.

So then this can be reduced even further if we just do some joins across things like so:

SELECT county COUNTY,
       Decode(etl.srat_code, '115A', 'R', 'C') R_C,
       SUM(Nvl(etl.billed_chg, 0) + Nvl(etl.adj_chg, 0)) MO_CHG,
       SUM(0) YR_CHG,
       Count(etl.prem_code) TOTAL_CUST
FROM   rpt_bloc_vw rpt
JOIN   rpt_rev_con_etl etl on etl.bloc_code = rpt.bloc_code(+) AND etl.process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
GROUP  BY county, Decode(etl.srat_code, '115A', 'R', 'C')
ORDER  BY county, Decode(etl.srat_code, '115A', 'R', 'C') DESC;

But I'm pretty sure I got something wrong on that last one, I don't always trust my own reductions. I've tried to show all my steps here starting with the first edit I did to your post on your original SQL code.

Can you tell me how the output of the last SQL I wrote compares to your original query?


Is there any way to run the definition of multiple stored procedure at a time?

Try right clicking on the database in Management Studio and selecting Tasks > Generate Scripts. The wizard that runs will let you create scripts for any objects in the database. In your case you'll want to select all "Stored Procedures".

In SQL Server 2008, you can click the "Advanced" button and change the value of the "Script DROP and CREATE" property to "Script DROP and CREATE" if you want to generate both scripts.

In SQL Server 2005, you may need to generate one set of DROP scripts and then another set of CREATE scripts (sorry I don't have an instance handy to test with).

See this link for more info:
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/


Msg 14027, Level 11, State 1, Procedure sp_MSrepl_addarticle [table] does not exist in the current database

Answer generated from a comment left by the question author

It was determined that it's not possible to replicate tables that are using the same names as system tables/views such as dbo.syscolumns or dbo.sysobjects.


drop table causes ORA-08103 with another one

AskTom (answered by Connor McDonald) : ORA - 08103:Object No Longer Exists and an even more detailed explanation by Connor McDonald: A cool thing with EXCHANGE PARTITION (recommended by AskTom here).

TRUNCATE TABLE is a DDL statement, not a DML statement like delete. But what happens is a phenomena that is similar to a phenomena that can be caused by delete or update  statements.

If   delete or update statements are involved then we have

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

There is also an AskTom article for ORA-01555

What happens:

Assume in your transaction you start a SELECT statement on a table. During this  select statement another transaction manipulates the data:

Either it deletes or updates  the table data (DML) and commits
or it truncates a partitions or the table or  exchanges a partition (DDL)

In this case your session that executes the select statement cannot find the data on its original place because the table has already changed. But it  is necessary to retrieve the table data in a consistent way, this means not need the data from the table from the point in time when the query started.
YOur session  it may be able to find the data in a rollback segment (in case of a DML  statement) or in the table whose segment you have exchanged with the partition (in case of exchange partition) or it is a segment that is not assigned to a table anymore but is still available and unaltered on disk (DDL statement).
If Oracle finds all the data it can continue with your select statement. If it does not find the data, either because the rollback segment where  already overwritten (DML  case)  or the segment was already modified or deleted (DDL case) then Oracle will throw an error.
in the DML case Oracle will throw an ORA-01555, in the DDL case it will throw a ORA-08103.

Connor McDonald writes in the first article referenced:

  Partitioned tables are the most common scenario to see this error, but by no means the only way.

  Anything that physically alters an underlying segment that is being queried can create the issue. Examples:

  truncate table
  alter table move
  alter index rebuild

Both articles are very instructive and I recommend you to read both (the second one is a video)

So how can you handle this problem?

simply repeat the action in case of this error (or rollback the transaction and repeat it)
maybe it helps if you wait some time before you drop the exchanged partition. Maybe this will decrease the frequency of this error.


Should I use utf8_unicode_ci or utf8_slovak_ci?

I am not going to answer you because I do not know utf8_slovak_ci nor the slovak language, but I will tell you how I would test it myself:

Take an extensive list of words containing "problematic" (as in, non-existant in the English alphabet) letter representations (as far as I know, letters in your language containing ˇ, ´, ¨ and ˆ, maybe others)- Please forgive my ignorance of your language.

Sort them and compare them using the 2 potential collations, like this:

SELECT word FROM test_table ORDER BY word COLLATE utf8_slovak_ci;
SELECT word FROM test_table ORDER BY word COLLATE utf8_unicode_ci;

SELECT word FROM test_table WHERE word like 'ž%' COLLATE utf8_slovak_ci;
SELECT word FROM test_table WHERE word like 'ž%' COLLATE utf8_unicode_ci;

Check for differences between them, and with the common orthography rules.

If you get similar results, they should be equivalent, and you should think whether you want to also use the collation rules of the other languages (even if your site is only Slovak, I may register there as "jynus from Logroño", which contains a "problematic" character). In that case, use the unicode version, and remember that at any time you can change the collation of comparisons for particular queries. For example:

mysql> SELECT 'a' = 'á' COLLATE utf8_unicode_ci, 'a' = 'á' COLLATE utf8_bin;
+------------------------------------+-----------------------------+
| 'a' = 'á' COLLATE utf8_unicode_ci  | 'a' = 'á' COLLATE utf8_bin  |
+------------------------------------+-----------------------------+
|                                  1 |                           0 |
+------------------------------------+-----------------------------+
1 row in set (0.01 sec)


Problem passing rows results to columns names

I haven't done the actual brainwork to articulate what the bug is, but I believe the following code may solve your problem.

with PvtData ( ALUMNO, TIPOEVALUACION, AvgNOTA ) as
    ( select
        ALUMNO
        ,[TIPOEVALUACION]
        ,avg( nota ) as AvgNOTA
    from ALUMNOSEVALUACIONES
    group by ALUMNO
        ,[TIPOEVALUACION] )
select
    *
from PvtData
PIVOT
(
    sum(AvgNOTA)
    FOR [TIPOEVALUACION] IN ([PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4])
) AS PIVOTTABLE

Pre-aggregating the average appears to return the correct Data. I hope this helps you!


You can query the system views and look at metadata.

SYS.ALL_OBJECTS gives you a list of all the objects that are accessible by the user
SYS.DBA_OBJECTS is similar and list all the object in the database
SYS.USER_OBJECTS lists all the object owned by the user

You will get a list of tables using this view:

SYS.ALL_TABLES = tables accessible by the user
SYS.DBA_TABLES = all tables in the database
SYS.USER_TABLES = tables owned by the user.

For a list of columns, you can join the TABLES list with the column views:

Table Columns SYS.ALL_TAB_COLUMNS, SYS.DBA_TAB_COLUMNS and SYS.USER_TAB_COLUMNS

There are similar views for:

Constraints = SYS.ALL_CONSTRAINTS, SYS.DBA_CONSTRAINTS and SYS.USER_CONSTRAINTS
Columns used by constraints = SYS.ALL_CONS_COLUMNS, SYS.DBA_CONS_COLUMNS and SYS.USER_CONS_COLUMNS

You can then join some of them and write such a query for a list of columns involved in referential integrity contraints:

SELECT ...
FROM SYS.ALL_CONS_COLUMNS col
JOIN SYS.ALL_CONSTRAINTS con con ON col.owner = con.owner AND col.constraint_name = con.constraint_name
JOIN SYS.ALL_CONS_COLUMNS fk on fk.owner = con.owner and fk.r_constraint_name = con.constraint_name
WHERE con.constraint_type = 'R' AND col.table_name = '...'

With R being (referential integrity) for constraint_type.


The password policy applies to restrictions around Windows password policy, including things like:

An uppercase character (A-Z).
A lowercase character (a-z).
A digit(0-9).
One of the non-alphanumeric characters, such as a    space, _, @,
*, ^, %, !, $, #, or &.

It does not handle expiration checks, you need to have the check expiration set for that to happen.

Reference MSDN Create Login


Preventing PostgreSQL from starting on boot in Ubuntu

Ubuntu or Debian can run multiple instances of PostgreSQL and provide a specific way to autostart/stop/start each cluster.

There should be a file named start.conf inside /etc/postgresql/9.2/main (or more generally /etc/postgresql/<version>/<clustername>) with these self-explanatory contents:

# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
#         pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
#           circumvented and is only meant to be a small protection for
#           accidents).

auto

If you replace auto by manual, you could start this PostgreSQL instance only when desired with the command:

sudo pg_ctlcluster 9.2 main start

As for looking at the console, what you should want instead is having this run in a terminal when you work with the database:

tail -f /var/log/postgresql/postgresql-9.2-main.log


sum only different id's values with any aggreagte function in postgresql

If entrance is always the same per barcode and ID you could first group by barcode and id taking the max entrance and then get the sum grouping by barcode.

SELECT x.barcode,
       sum(x.entrance) entrance
       FROM (SELECT t.barcode,
                    t.id,
                    max(t.entrance) entrance
                    FROM elbat t
                    GROUP BY t.barcode,
                             t.id) x
       GROUP BY x.barcode;


Convert a datetime column to number of seconds

Create a new column (ALTER TABLE) then run an UPDATE on it

UPDATE
  MyTable
SET
  NewIntColumn = DATEDIFF(SECOND, '19000101', MyDateTimeColumn)

19000101 is the SQL Server epoch. You can use 19700101 for Unix epoch for example


What are the top 3 performance Issues that you encounter with your SQL Servers?

Off the top of my head - top 3 query problems:

Implicit conversions
Bad indexing strategies (too many or not enough or the wrong kind)
Using SELECT * instead of naming just the fields you need

There's a lot more around server-level config problems, database schema problems, hardware problems, etc.  I wrote a script to quickly analyze servers looking for these kinds of problems:

http://www.brentozar.com/blitz/


How does one know when to stop normalizing?

When designing a database, normalization is a process undertaken when producing the logical model i.e. the relational data model. Performance is an attribute of the physical model; the implementation of the logical model on a particular DataBase Management System.

You cannot "over-normalize" a relational data model. Either the relational data model is in fifth normal form  or it is in a lower normal form and update anomalies exist.

The normalization process is a well-defined procedure for analyzing the functional, multivalued and join dependencies of the relations within the model and taking projections of these relations to eliminate any dependencies not implied by the candidate key.

Assuming you have a relational data model in fifth normal form which has been implemented on a particular DBMS. At some point the performance of this implementation has become unacceptable. How you decide to address this issue will depend on the DBMS that is being used and the features that DBMS makes available.

Having looked at the features available to you for the DBMS being used you may decide to take some of tables in the DBMS and re-factor them so that they are in a lower normal form. (Note: the logical model has not changed, you have changed the physical model to address a performance issue with the implementation.) By re-factoring these tables you have introduced the possibility of update anomalies and, because your relational data model is in fifth normal form, you can determine precisely where these may occur. Having determined these you may choose to ignore them, or add some additional processing to either identify them and/or address them. (Note: by adding additional processing you may impact performance such that the perceived benefits of "denormalizing" these tables is actually negated.) In any case you are able to make an informed analysis of any action you may take.


Missing Polybase enable configuration on SQL Server 2017

SQL 2017 is not good with Polybase. Currently, I am using SQL 2019 for this and all is good.


I've never attempted to block traffic at the SQL Server level.  In my experience that is handled via a hardware firewall, Windows Firewall, or both.  I find it interesting that you allow connections to SQL from unknown IP addresses.  I would focus creating a white list of valid, known IP addresses and block everything else as others suggested.

To answer your question:  I did some searching and here is a linked response on writing a SQL query used as a SQL Agent job to block traffic after X number of failed login attempts.  StackOverflow Answer


Go back in time from a PostgreSQL backup using xlog

No. You can't.
PITR can let you go forward in time, not backward.

You have to have a backup taken before your WAL were created and you must have each WAL between your backup and the time you want to reach.

You can find this documentation helpfull.
https://www.postgresql.org/docs/current/static/continuous-archiving.html


ODBC Calling Fill - Unexpected closed connection after 2 hours

This must be the TCP keepalive timeout: if there is no traffic on a TCP connection, the kernel will automatically start sending TCP keepalive packages after a while. If there is no response to those, it determines that the other side has hung up without closing the connection and reports an error message. The default time until the kernel starts sending these packages is two hours, which matches your observation.
Since PostgreSQL is not in the habit of hanging up on the client (look into the PostgreSQL log for a corresponding message), you must have a misconfigured firewall or router that cut off connections after a while. If you cannot fight that problem at the source, you might be able to work around it by reducing that keepalive idle time substantially.
One way to do that is to change the PostgreSQL parameter tcp_keepalives_idle to something like 120, which will cause it to send keepalive packages after two minutes of no traffic on the connection. Depending on which client you use, you might alternatively change the keepalive settings on the client side.


MYSQL Query - Unexpected result in text messaging database

Your SELECT clauses may not be optimal. I suspect that Count(Messages.SenderID = People.ID) equals the total number of rows in the result set: for each row, Messages.SenderID = People.ID equals either 1 or 0, and MySQL simply counts all the 1s and 0s, and it ends up with a total number of 54 (6 times 9). Try using SUM(Messages.SenderID = People.ID) instead.

By the same logic, I would replace SUM(Case When Recipients.RecipientID = People.ID THEN 1 ELSE NULL END) with SUM(Recipients.RecipientID = People.ID).


Assuming you are licensed to use the AWR, you could query either the gv$active_session_history view for more recent data or dba_hist_active_sess_history for older data.  gv$active_session_history will have a snapshot of what every active session was doing at the top of each second and will hold that data for a few hours.  dba_hist_active_sess_history will have the data from, essentially, every 10th snapshot in gv$active_session_history but it will be retained for much longer (whatever your AWR retention period is).

Both of these views will have a BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, and BLOCKING_INST_ID that will tell you which session was holding the lock that the blocked session was waiting on.  You can then look too see what queries the blocking session was running at around the same time-- the blocking session may well not be active in the particular snapshot you're looking at but it probably was in the snapshot a second or two earlier.


Filter Table by a Historical 'Last Day of the Month'

To subract a month from a date you can use dateadd().

For example

SET @MaxDate = dateadd(month, -1, @MaxDate);

would subtract one month from your @MaxDate. That would keep the day of the month though. (Unless this day doesn't exists for the month, then it's the last day of the month, so e.g. the 31th day might become the 30th.)

To get the date of the last day of a month a date is in eomonth() can be used.

SET @MaxDate = eomonth(@MaxDate);

would set @MaxDate to the last day of the month @MaxDate was previously in.

eomonth() optionally takes a second argument, which is an integer to add to the month. So to set @MaxDate to the last day of the previous month of @MaxDate you might use

SET @MaxDate = eomonth(@MaxDate, -1);


Is it ok to increase `innodb_stats_sample_pages` up to large number?

Changes to the statistics has plagued InnoDB since the beginning.  Fortunately it affects only a small percentage of queries.

Upgrade to a recent 5.6.  There is a significant improvement wherein the stats are no longer recomputed whenever you sneeze.  It is now essentially possible to set them once and forget them.  The number of "random dives" and which algorithm is used is now less important.

As mentioned, MariaDB (and eventually 5.7) has fancier statistics.


This is Vertical Partioning, as Michael Green states. I think it really depends on how frequently you need the Description field for Books and Movies. If it's rarely accessed/requested, then split it out as you described above. You would have two additional tables though, like BooksMeta and MoviesMeta, instead of the one Meta table. Then add your keys to the meta table.


Oracle: Quick way to list all database links

These views are helpful for working with DB links in Oracle:

DBA_DB_LINKS  - All DB links defined in the database
ALL_DB_LINKS  - All DB links the current user has access to
USER_DB_LINKS - All DB links owned by current user

See more in Oracle documentation


How to determine the biggest float value (in terms of used bytes) with SQL in Oracle?

VSIZE

  VSIZE returns the number of bytes in the internal representation of
  expr.

select max(vsize(mycolumn)) from mytable;


MySQL Query One Table/Column With Value While Another Table/Column Empty

What you need is a LEFT JOIN

SELECT tbl2.* FROM
tbl2 LEFT JOIN tbl1
ON tbl2.package2 = tbl1.package1
WHERE tbl1.package1 IS NULL;

Note how I specify tbl1.package1 IS NULL checking the condition of the JOIN, not the data.


How do I uniquely update values of a text column before applying UNIQUE constraint?

Ended up writing a nested loop which will select all duplicate entries in table dup_table and add a dash to each duplicate dup_name field until no duplicate records exist.

DO $$DECLARE
  tmpRec RECORD; tmpInt INTEGER;
BEGIN
    LOOP
      tmpInt := 0;

      FOR tmpRec IN
        SELECT x_id FROM (
          SELECT x_id, ROW_NUMBER()
          OVER (PARTITION BY dup_name) AS "dup"
          FROM dup_table
        ) x WHERE "dup" > 1
      LOOP
        tmpInt := 1;

        UPDATE dup_table SET dup_name = dup_name || '-' WHERE x_id = tmpRec.x_id;

      END LOOP;

      EXIT WHEN tmpInt = 0;
    END LOOP;
END$$;


In General if you have the credentials for accessing MySql from Machine B.

You should have MySQL user name , host name and Password.Then You can write the Command for taking backup

On Machine B Write

mysqldump -h Your_host_name -u user_name -p password --all-databases > backup.sql

If You Face issues there may be some network issue try some things as

Use ping with ip address to check that DNS isn't broken. eg. ping Machine A
Use mysql client to connect from B to A. eg mysql -u user -pPASS --host=Host_Name --port=3306 (substitute whatever port you are connecting to the master on)


Is it possible to issue an INSERT / UPDATE / DELETE and specify for it not to be relayed to attached slaves in mysql?

Here is a quick and dirty answer:

Simply run SET SQL_LOG_BIN=0; before each SQL command. It prevents subsequent SQL from entering the Master's binary log upon the completion of any SQL statement afterwards. Thus, it cannot be shipped forward to any Slave. However, there will be no record of the SQL being executed in the Binary Logs.

Within the given DB Connection, if you want to start recording again, just run SET SQL_LOG_BIN=1; before each SQL command.

Opening a new connection will having binary logging enabled within it by default.


ORA-01654: unable to extend index  by 128 in tablespace

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a great post about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move;

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
You're not specifying a tablespace when creating the index and it's not using the one you think, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace:

select * from dba_users where username = 'MY_USER'

If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table
    on my_table ( my_column )
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use DBMS_STATS.GATHER_TABLE_STATS() and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.


Very poor insert performance MySQL / Percona Server

OBSERVATION #1

Since your buffer pool is 6G (6144M), the innodb_log_file_size should be 1536M (25% of 6G)

OBSERVATION #2

You have sync_binlog set to 1. This provides the safest ACID compliant setup. It can also slow things down dramatically. You say it seems as though its writing each row 1 at a time to disk. That's the case because each completed DML (INSERT, UPDATE, DELETE) and DDL (ALTER TABLE) statement gets written to the binary logs. The default for sync_binlog is 0. That let's the OS be responsible for flush binary log changes to disk.

OBSERVATION #3

You have innodb_io_capacity set at 10000. That's really 10000 IOPs you are expecting of mysql. Try lowering it.

There are some things to do in this respect

When it comes to sync_binlog

Comment it out of my.cnf
Set sync_binlog to 100 or 200 to pace the flushing of binary logs

You should make sure data disk has a battery-backed cache (with a good battery)

RECOMMENDATION

STEP 01) Set these options in my.cnf

[mysqld]
innodb_fast_shutdown = 0
innodb_log_file_size = 1536M
sync_binlog = ( you choose 0 or 100 )

STEP 02) Remove innodb_io_capacity. Let it be set to default.

STEP 03) Run this command in mysql

mysql> SET GLOBAL innodb_fast_shutdown = 0;

STEP 04) Apply settings

cd /mnt/Storage/mysql
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile2 ib_logfile1.bak
service mysql start

This should speed things up


No.

The rman command 'BACKUP FULL ...'  makes a backup of the datafiles but does not backup any archivelogs. If you want to backup archivlogs you have to make an archivlog backup too. This can be done by adding the 'PLUS ARCHIVLOG' clause to the 'BACKUP FULL' command or by issuing a 'BACKUP ARCHIVELOG' command after the 'BACKUP FULL' command.

Online backup of datafiles are useless if you do not have an archive log backup that covers the time of the online backup.
If I understand your scenario description, you have lost all archivelogs/redologs that cover the time after 01:30 and you haven't yet made a backup of these archives. Then you can only restore the all datafiles from a backup finished before 01:30 (maybe in the previous night) and then apply all archivelogs from the beginning of this backup until 01:30.
(I assume that you have a backup of all the archives generated in this period of time).

So you can recover the database until 01:30.

--

some consequences:
 1 ) datafiles and archivelogs should be placed on different disks. If the data disk crashes you still have all archives even if you haven't backuped them to tape.
 2) redo logs should not be placed only on data disks. Duplex them because they are so important. Place at least one copy to other disks than data disk.
3) backup your archive logs regularly. Even during other back operations like a long during fullbackup make the archivelog backup parallel to these operations.


What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?

They are both the same function but one was specifically designed for IBM PureData System for Analytics, which is the regexp_extract() function. The other function REGEXP_SUBSTR() is for DB2 only and doesn't exist on PureData System for Analytics.
Use the one that correlates to whichever system you're working in (seems like DB2).


Exist on table from linked Server giving Error 4104

The 4104 error occurs because you are using 5 part naming in your where clause.

For example:

SELECT TOP 100 *
FROM [ServerA].[MyDB].[dbo].[Table1]
WHERE [ServerA].[MyDB].[dbo].[Table1].[foo]=5;

Will throw the error:

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "[ServerA].[MyDB].[dbo].[Table1].[foo]"  could not be bound.

Solution: using aliases

SELECT top 100 *
FROM [ServerA].[MyDB].[dbo].[Table1] AS T1
WHERE Exists( SELECT * FROM [ServerB].[TheirDB].[dbo].[TableA1] AS T2
              WHERE T1.[foo] = T2.[bar]);


You should increase your bulk_insert_buffer_size to 512M because it accommodates bulk loading of MyISAM tables. According to the MySQL Documentation on bulk_insert_buffer_size:

  MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

In MySQL 5.6, the max value for bulk_insert_buffer_size is 4G.

If your MyISAM table has TEXT/BLOB data, I would also increase the max_allowed_packet. What is a MySQL Packet used for ? See my SuperUser Post What does the MySQL “max_allowed_packet” setting actually control?

UPDATE 2014-02-15 19:49 EST

Your last comment was

  The table has only fixed size data (char[60], int, ...) One line takes 126b. About the bulk_insert_buffer_size, since I'm using INSERT ... ON DUPLICATE ..., would it help ? Also, why did you choose 512M, and not a slower value, similar to the max_allowed_packet ? (or maybe 128M)

My choice of 512M was arbitrary. You can set it to whatever you are comfortable with. Just don't leave it at the default value of 8M.

Since you gave the row size, let's to the math.

10,000 rows X 126 bytes/row = 1,260,000 = 1.2 MB

OK, bulk insert buffer may not be an issue.

OBSERVATION

I don't think MySQL likes INSERT IGNORE  combined with ON DUPLICATE KEY update. Why ?

INSERT IGNORE says INSERT but reject the incoming row if the PRIMARY KEY already exists.
ON DUPLICATE KEY says INSERT but perform some UPDATE on specific columns if the PRIMARY KEY already exists.
Logically, this does not make sense to use both of them. Which one do you want?

IGNORE duplicates
UPDATE columns on duplicates

The INSERT should either be

INSERT IGNORE

or

INSERT ... ON DUPLICATE KEY

Since you have a meaningful ON DUPLICATE KEY, ditch the word IGNORE.

You could also change INSERT ... ON DUPLICATE KEY into REPLACE INTO if you are replacing all non PRIMARY KEY columns.


In theory and in the general case in practice for OLTP it would be bad practice to have the extra key: you are duplicating information that can be derived from elsewhere. You have the extra key value to store (and presumably index) and if a PeripheralUnit could move from one area to another then unless the area values on the relevant device records are updated your data is inconsistent, meaning you need extra logic (either in your business layer or in the DB) to ensure that this inconsistency does not creep in.

In some cases denormalising in this way is useful for efficient reporting. Here you would be trading off that extra coding (to maintain the extra relationship data consistently) and insert/update time (as said code is run on each create/change, and results in extra I/O activity) against greater reporting ease. IMO this does not look like a situation where the data is large enough or the model complex enough that this trade off is going to be beneficial.


Is it possible to rename the columns of a resultset?

If you have a fixed set of properties and there will be at most one value for each entity then you can do:

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
LEFT OUTER JOIN
       table_files AS t ON t.id=o.id AND t.type = 'TEXT'
LEFT OUTER JOIN
       table_files AS i ON i.id=o.id AND i.type = 'IMG'
LEFT OUTER JOIN
       table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

or if there will always be a row for each property (i.e. the row will exist with a NULL value if there is no value, instead of not having a row for the property at all) for each entity using inner joins may be more efficient:

SELECT o.id, o.version, o.description
     , t.file_name AS text
     , i.file_name AS img
     , a.file_name AS audio
FROM   table_overview AS o
JOIN   table_files AS t ON t.id=o.id AND t.type = 'TEXT'
JOIN   table_files AS i ON i.id=o.id AND i.type = 'IMG'
JOIN   table_files AS a ON a.id=o.id AND a.type = 'AUDIO'

If there might be more than one of a property for any given entity (for example if there could be multiple image files for one overview row) then this will get more complicated.

Using GROUP BY and aggregates can change the performance for the better too, as mentioned by Raymond, depending on how many properties there are, what other filtering clauses are in your queries, and how your tables are indexed. In many cases it will be better, but in some it will be noticeably worse (particularly when filtering on one of the transposed properties or picking a few properties from a bag that can contain many per item) due to causing scans where a small number of seeks may otherwise be sufficient:

SELECT o.id, o.version, o.description
     , MAX(IF(type = 'TEXT' , file_name, NULL)) AS text
     , MAX(IF(type = 'IMG'  , file_name, NULL)) AS img
     , MAX(IF(type = 'AUDIO', file_name, NULL)) AS audio
FROM   table_overview AS o
LEFT OUTER JOIN
       table_files AS f ON f.id=o.id
GROUP BY o.id, o.version, o.description

You'll need to test/benchmark your application to see if this method is faster or not.

In other databases like SQL Server there are PIVOT features that make this sort of thing less hassle, but IIRC mySQL doesn't yet have such features.

If you don't have a fixed set of properties that you need to transpose then you are forced to use ad-hoc SQL either generated by your application layer or via stored procedure (see http://buysql.com/mysql/14-how-to-automate-pivot-tables.html for one example of the latter). This is usually the case even in DBs with PIVOT/UNPIVOT/equivelant too as such features are generally implemented with fixed properties/columns in mind.


Is it bad practice to add columns to an existing table?

ALTER TABLE is there for a reason.  More seriously, unless you plan to have a really big data set, you shouldn't worry about adding new columns on demand.  Dropping and recreating the table is only viable until you have no (important) data, meaning you have to use ALTER TABLE ... ADD COLUMN later in any case.
When I mention 'really big' above, it's about aligment and padding of the different data types used for the column definitions.  For example, a row of a table with columns (in this order) (smallint, integer, smallint) will be slightly (2 bytes) wider than one with (smallint, smallint, integer).  This starts to make a difference only with tables of 10s (on decent hardware maybe 100s) of million rows or when the table has many columns and a lot of rows.  More about this in Erwin Brandstetter's excellent answer.
When adding a new column in pre-11 Postgres versions, beware the trap of ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ....  As ALTER TABLE ... ADD COLUMN requires a heavy lock that prevents access to the table for concurrent sessions, you have to keep the transaction as short as possible.  If you manage to do this, adding a new column will be hardly noticeable performance-wise to the other processes.


pg_upgrade issue from postgres 9.3 to 9.6

I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.

I am running PostgreSQL in Windows 10.

I received the same error message.

I was confused about running cmd.exe under user: postgres.  My system doesn't have an account with that name.

I did two things.

pg_hba.conf file were modified for both versions to add the "method" "trust".  I did not add extra lines as you might have found in other references.  However, comparing my entries with the original poster, I had the default localhost entries as per what they added.  I would comment md5 lines temporarily:

I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6
Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user.  Extra consideration  may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.

In my case, my setup has the PostgreSQL application files stored in C:\program files directory and data is stored in C:\APPS\PostgreSQL\version\data

Referencing my setup, I ran the following command:

-c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.

-U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:

The -U option allows you to specify a different postgreSQL user account for the command to use.  This is where I specified the user to be postgres.  Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option

This is the result I received after running modified command:

Hope this helps.


You can use the "WITH ESTIMATEONLY" with your dbcc command.

DBCC CHECKDB WITH ESTIMATEONLY

This should give you an aprox. space in tempdb to run it. .


It is certainly possible to delete data from a random access file.

Realistically, though, virtually any database will do a soft delete and mark data as deleted rather than physically deleting data.  At some point, something else will then reuse the space that the deleted row had been using (what operations reuse the space will depend on the database among other things).  And different databases may provide tools to automatically or manually reclaim that space.

Practically, this is basically the same thing that the operating system does when you delete a file.  The operating system removes the file system's pointer to the file just like the database removes the reference to the row from the index.  The actual data for the file still exists on the drive.  At some point, some other file system operation will eventually reuse that space.  Some operating systems provide a "defrag" utility that will go through and make all the free space contiguous and move the allocated data to the "beginning" of the drive.


PostgreSQL hot standby promotion, multiple hot standbys

I don't think copying pg_xlog after you've lost the master is going to allow you to make ServerB or ServerC the new master.  If you might need to remaster, you really need to add log-shipping archives as well (this is a current limitation in Postgres, at least through 9.2).

Also, if the master has been shut down cleanly, you don't need to copy anything.


Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;

SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)