How to optimise this query? Script to create Tables Person and Company

USE optimisationTesting

CREATE TABLE Person(Id int not null, Name nvarchar(60), StreetAddress nvarchar(60)
                  , Height int, CompanyId int)
CREATE TABLE Company(Id int not null, Name nvarchar(60))
GO

ALTER TABLE [dbo].[Person]  ADD CONSTRAINT [PK_Person]  PRIMARY KEY CLUSTERED ([Id] ASC)
ALTER TABLE [dbo].[Company] ADD CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ([Id] ASC)
GO

DECLARE @i int = 0

WHILE @i < 100000 BEGIN

    INSERT INTO Person(Id, Name, StreetAddress, Height, CompanyId)
    VALUES (
        @i,
        CONVERT(nvarchar(60), newid()),
        CONVERT(nvarchar(60), newid()),
        140+rand()*60,
        rand()*100)

    SET @i = @i + 1
END

SET @i=0
WHILE @i < 100 BEGIN

    INSERT INTO Company(Id, Name) values (@i, convert(nvarchar(60), newid()))

    SET @i = @i + 1
END

-------------------------------------------------------------------------------

set statistics time on

select *
    from
    Person p
    where p.Name like 'a%'

set statistics time off

The average elapsed time after three execution was 363ms.
How can this query be further optimized to find out the names of people that starts with A?

Misread your post and wrote up a post telling you how to optimize the inserts, which seemed like an odd activity to do. The real query to optimize is this:

select *
from
Person p
where p.Name like 'a%';

That query gets back around 6100 rows out of 100000 rows for me. Even if you create a simple index on the name column it is unlikely that the query optimizer will use it.

create index Person__name ON Person (name);

select *
from
Person p  WITH (INDEX (Person__name))
where p.Name like 'a%';

Disclaimer that I'm only using that hint for testing purposes. You don't want to use something like that in production unless you really know what you're doing. That hint forces the optimizer to use the index but I get worse results than not using it. If you truly need that query to be faster my only ideas are that you'll need to create a covering index, you'll need to change the structure of the table, or maybe there are some small gains to be had by writing the filter condition in a different way.

Here is what I mean by a covering index:

create index Person__name ON Person (name)
INCLUDE (StreetAddress, Height, CompanyId)

That index avoids the key lookups that you get with a simple index because every necessary column is included in it. However, the size of that index will be similar in size to the table. Any DML on the table will be slower because the index needs to be maintained.

You could change the clustering index from ID to name. That would have significant impacts on DML performance and on other queries, but it would make your query faster.

Perhaps instead of LIKE there is another string operator that you can use. I doubt that such changes would have a significant impact on your query's runtime.


Tool to find CPU utilization for a particular query in Postgres I just want to know is there any free tool to identify CPU utilization for a particular query in PostgreSQL.

You can use the CPU monitor of your operating system (e.g. top in Linux or ProcessExplorer in Windows).

Each connection to the database will create a new process on the server. The process id related to the connection is reported in the column pid of the system view pg_stat_activity.

With that process ID you can monitor the CPU usage of that process which is essentially the CPU usage of your query (while it's running of course)

More details in the manual: http://www.postgresql.org/docs/current/static/monitoring.html  and the Postgres Wiki: https://wiki.postgresql.org/wiki/Performance_Analysis_Tools


Design a many-to-many table for scale I am thinking about scaling issues with many-to-many join tables.

I have a constructed example as following:

Many users have many wishlists.
On every wishlist are many items.

If I scale this table to million of users the wishlist_items table
will get very big:

1000000 users
100 wishlists for every user
around 1000-10000 items on every wishlist (bad example, but you get the point)

Therefore table size for the join table will be:

users * wishlist_per_user * items_per_wishlist

I think this is a very common situation in RDBM design. I am afraid that this users * wishlist_per_user * items_per_wishlist relation will grow out of bound.

Are there any similar experiences with such big tables and how to query them fast? How would I design my tables to prepare for better scaling?

I think using separate wishlists for separate users will be your best option. It will be efficient and will not contain millions of rows.


Oracle pagination with order by field not unique performance Usually, we will write pagination SQL like this if order by field is unique:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' ORDER BY L_DATE
    ) XX
    WHERE ROWNUM <= 500
) XXX
WHERE RN > 0;

but in my case the L_DATE field is not unique, even maybe null too. I don't want to put more fields after L_DATE (actually, this field is dynamic coming), so I put the order by after RN, like this:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q'
    ) XX
    WHERE ROWNUM <= 500
) XXX
WHERE RN > 0 ORDER BY L_DATE;

In this way the page data is correct no matter whether the "order by" field is unique or not, but performance is 3 times slower than first one.

Any suggestions?

Right now, my solution is:

select *
from ( select xx.*, rownum as rn
       from (select * from  T_LOG ) xx ) xxx
where rn >0 and  rn <=500;

This way, data is always coming to the right way no matter order by fields is unique or not, even don't order by field.

The below way seems wrong:

select *
from ( select xx.*, rownum as rn
       from (select * from  T_LOG ) xx
       where rownum <= 500 ) xxx
where rn >0;

It's getting repeated data after some pages, if statements had order by unique field, it's work correctly.

It's also wrong, because now you are getting 500 rows (could be ANY 500 rows) and then sorting them.  If L_DATE is not sufficient to give a deterministic answer, you do need to add more columns.  The most obvious choice would be a primary key column:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' ORDER BY L_DATE, SOME_PK_COL
    ) XX
    WHERE ROWNUM <= 500
) XXX
WHERE RN > 0;

...so that in the absence of good information in L_DATE (or whatever column is nominated by the user), you will be sorting on something deterministic.

Also: If you do select ... from table where rownum <= 10 then the 10 rows you get back is not deterministic over time.  There is no predefined order to the rows.


Is it safe to disable sync_binlogs, innodb_flush_log_at_trx_commit and innodb_doublewrite on a site-redundant galera cluster? On a site-redundant galera cluster, a commit should only return after a quorum of database nodes have accepted the transaction.  If one database node goes down, all commits will be preserved by the rest of the cluster, and the downed database node will get in sync with the rest of the cluster when starting up the database again.  If the database should have become corrupt, one can always nuke the mysql data dir and start with an empty database, it will eventually catch up with the rest of the cluster.  Hence it sounds like we could tweak performance by turning off options for ensuring local ACID-compliance.

So the question here is really ... "What could possibly go wrong?"

:-)

Based on the comments, I'll give some information about our particular setup:

The cluster consists of three nodes.  Two of them are actively used in production, one of them more heavily used than the other.  The third node is only used for quorum and backup purposes.
Site-redundant means that the nodes are in different server centers.  I find it hard to think of anything that would cause two nodes to go down simultaneously - except a serious mysql bug, how likely is that?  Admittedly, two of the nodes are situated less than 10 km apart (backup/quorum node is separated by several hundred km plus a national border).  A medium-sized nuclear bomb could potentially take out two of the nodes simultaneously ... then again, "we have problems with the database" is probably the least of our concerns in such a scenario.  Could a solar storm possibly take out both or all of the servers simultaneously?
Our performance problem is primary due to the write cache on our SAN going full from time to time.  We're working on mitigating this issue, but we can never guarantee that it won't happen again.  Every now and then we experience "hicups" where we have transactions waiting for some 10-30 seconds.
In this particular setup, a 30s delay may actually be a matter of life and death.  Well, most likely not, but if the customer perceives it that way, then it's bad enough.  Reasonably low latency and quick recovery should the whole cluster fall down is the most pressing priority.  Losing some few transactions may be bad enough, but it's not a matter of life and death.
The performance problem we're seeing is write-transactions being stuck in "wsrep in pre-commit"-state.  This is not a flow-control issue, it's only one node having the problems.  I did a bit of research on it, apparently it's so that all local write queries are waiting for locks while the node is writing remote changesets to the database.  This problem should be fixed in galera 4, but upgrading is currently not an option.
Our performance problem is only on one node, so it's only on one node I'm considering to turn off those things.  I would add to our documentation that whatever happens, said node should never be used for bootstrapping the cluster.
We're not doing sharding, and we do not intend to do any sharding.  Except for those hic-ups we don't have any performance problems.

I've done some research on this now, so I'll answer on my own question.

Summary first

If the performance problems are observed only on one node, it's perfectly safe to turn off innodb_doublewrite and innodb_flush_log_at_trx_commit on the affected node - just remember to use one of the other nodes for bootstrapping should the whole cluster fail.

# echo "[server]" >> /etc/mysql/conf.d/temp_perfomance_hax.cnf
# echo "innodb_doublewrite = 0" >> /etc/mysql/conf.d/temp_performance_hax.cnf
# echo "innodb_flush_log_at_trx_commit = 2" >> /etc/mysql/conf.d/temp_performance_hax.cnf
# systemctl restart mysql # innodb_doublewrite cannot be set run-time

Also, in a true site-redundant galera cluster, it should be reasonably safe to put innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0 on any number of nodes.  If one is experiencing performance problems, if uptime and performance is important, and if one lost transaction probably isn't a matter of life and death - then no hesitations needed, just go ahead and do it.

set global sync_binlog = '0';
set global innodb_flush_log_at_trx_commit = 2;

and one can probably go ahead and turn off double write as well if performance is more important than ACID-compliance.  The probability of disasters is pretty low.

I can see two reasons for sticking to the "safe" settings:

Preparedness for the worst-case-scenario: what if the whole cluster melts down simultaneously?
More robustness on the nodes

Node robustness: keep innodb_doublewrite turned on

innodb_doublewrite can be safely turned off at one node without losing ACID-complience, but it may still be better to have it turned on.  According to When is it safe to disable InnoDB doublewrite buffering? turning off innodb-doublewrite may cause corrupted data, in that case it may be needed to wipe the data partition on the downed node and start it up again.  This comes with three costs:

manual sysadmin intervention
elevated levels of IO on the cluster while the corrupted node is performing a SST
the cluster will have to run with reduced redundancy until the node is fully operative again.

(As far as I can understand, a missing flush on log files shouldn't cause problems starting a db node - i.e. for innodb_flush_log_at_trx_commit the manual explicitly states that  "InnoDB's crash recovery works regardless of the value")

Reasons why the whole cluster may burn down at once

Hard explosions

Consider innodb_flush_log_at_trx_commit to be set to 2, binlog_sync to be set to 0 and innodb-doublewrite-buffering set to 1, this may give performance gains, and as far as I can understand the only failure mode it won't cover is if a quorum of nodes are suffering a power-loss or kernel panic.  For a true multi-site-setup, those seems rare enough that one may want to neglect them.

If the nodes are located nearby, a local problem, i.e. power outage, flooding, fire, etc can knock all of the nodes out in the same time.  I've multiple times witnessed a whole server site going down for the weirdest reasons even though everything was supposed to be redundant.
Could solar storms affect the whole cluster simultaneously?  Especially if the cluster is located in the same city and in polar regions?  I've seen two laptops crashing simultaneously, being in the polar region during a solar storm, and with no other plausible reasons (different OS'es, it wasn't under a leap second, etc).
If all nodes runs the same OS, some OS kernel problem could potentially cause all nodes to crash hard with a kernel panic in exactly the same time.

Software crashes

I believe that if innodb_flush_log_at_trx_commit is set to 0 or innodb_flush_log_at_trx_commit is set to 0, then a mysql crash affecting all nodes simultaneously could cause data loss.

Has anyone ever observed galera crashing in such a way?

A system admin or buggy script doing "kill -9" simultaneously on mysql on all the nodes too ... but if the system admin wants to cause data loss, he will probably find a way regardless of those settings.

Cluster going down for other reasons

In those cases, those settings shouldn't matter:

Sudden and total network isolation between all three nodes, or leaving the cluster fragmented so no quorum can be found.  One would need to bootstrap the cluster after such an incident, and if one really want to be 100% sure that no transactions are lost, one needs to manually investigate binlogs (see more on that below) - but since neither mysql nor the OS has crashed, all state will eventually be synced, no matter the sync settings.
Bad timing - i.e. one node going down and while it's down another node could go down or there could be some network isolation.  Afterwards, the cluster needs to be bootstrapped.  However, everything that the nodes have received will be written to disk, so the above mentioned settings will not affect anything.  (We've had this coming over us once).

Manual intervention is needed on cluster failures

No matter the settings, to be 100% sure that no transaction is lost after a catastrophic cluster failure, it's actually needed to manually inspect/merge binlogs.  We need to bootstrap the cluster using only one node, but we don't know if this node got all the changesets, this info is only available by investigating the binlogs from all nodes (with log_slave_updates turned on, one can probably do with binlogs from a quorum of the nodes).

This means that you need to make a decision in advance - what should be the priority if we encounter a catastrophic cluster meltdown?  Is the priority to get the cluster up and running again as fast as possible, or is it to make 100% sure no transactions have been lost before bootstrapping the cluster?

Your mileage may vary

If some downtime is acceptable under extreme circumstances (a full catastrophic db cluster meltdown is extreme) and if it's paramount to ensure all transactions are in place before returning to production mode, then one needs to make 100% sure that the binlogs are synced and kept, and that the backup regime is good.  Syncing innodb logs and double write is then non-critical and can be turned off if that gives performance benefits.

If "getting the cluster up and running again as fast as possible" is important, then it's important to set innodb_flush_log_at_trx_commit = 1 and set innodb_doublewrite = 1 at a quorum of the nodes; if the cluster should go down then it can be quickly bootstrapped from any of the servers with those settings turned on.  (Some consideration may be needed.  On our previous meltdown node C fell down and got up again.  Network glitch between node A and node B before node C got properly joined into the cluster, resulting in a shutdown.  Obviously, we can bootstrap from A or B without any data loss, but not from C).


How identify tables with millions of entries On a debian server with apache and mysql, how can I find out if any one table gets spammed?

I have lots of different blogs, wordpress, wikis,... on my server of different customers.

It seems like some PHP aplications are not protected against spamming, so some tables get really big and slow down the whole server.

I would need a script, that monitors all tables. Or is there a simple tool I could install to get a report if something weird happens?

Since the tables you want to check are MyISAM, you can use just one query. For any given table or tables using MyISAM, here is that query:

SET @SecondsAgo = 60;
SELECT
    table_schema,table_name,
    data_length MYDSize,index_length MYISize,
    table_rows,update_time
FROM information_schema.tables
WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','performance_schema','mysql')
    AND update_time > (NOW() - INTERVAL @SecondsAgo SECOND)
ORDER BY update_time DESC;

For each MyISAM table, you will get the following:

table name and database
.MYD size
.MYI size
number of rows in the table
last time the table was updated

You can set @SecondsAgo to choose how granular to scan for changes.

If many tables or the same set of tables appear from this query, you will see which tables are experiencing the most frequent writes and the most growth.

I will leave it to your to determine the weirdness from the results.


MYSQL INNODB Recover table data My web application is built using PHP Laravel framework, and I accidentally run php artisan migrate:fresh which is a command that drops all the tables and recreates them again.

I have done some research and it seems possible to recover the old table structure along with its data but I think my case is different than what I saw on other posts: I have the .frm and .ibd files, but I am not sure how to use them and if these are still useful.

I just want to know if I still can recover all the lost data.

if your data is mission critical and you really cannot move forward without it there are some very specialist tools on the market for recovery. There are only small amount of experts that know how to do this properly. I spent some years working at a consultancy that could do this with varying degrees of success and it was very expensive in terms of $ and time. Take a look on https://recovery.twindb.com/ to see if there's anything there that can be useful.

Don't forget to set your backups up to avoid this situation again in the future. There are plenty of tools out there that make this simple for you.

https://www.percona.com/downloads/XtraBackup/LATEST/

https://www.binlogic.io/


How to install Oracle's sqlplus on a Mac? I need steps how to install Oracle's sqlplus on a Mac.

Download Basic Package and SQL*Plus Package zip files from Oracle
Add a local repository to brew from where the Oracle packages will be installed: brew tap InstantClientTap/instantclient
Copy both Oracle's zip files to /usr/local/Homebrew/Library/Taps/instantclienttap/homebrew-instantclient
Install the basic package: brew install instantclient-basic
Install the SQL*Plus package: brew install instantclient-sqlplus
Test sqlplus by connecting to an existing database: sqlplus user/password@server:port/sid

My intention was to improve Joost van Wollingen's article published here.


No of rows to be displayed I have 100 records. I want to display only 10 rows of that record default or by specifying the number by using procedure

You can specify the number of records to return by using TOP

SELECT TOP 10 * FROM table


Check for blocked queries periodically and cancel them I have series of update queries which are executed every 5-10 seconds. Sometimes it happens that one of these will block all the pending update queries as for some reason it just freezes.

Since I can afford to lose an update is there are way to periodically check what queries are pending in the database, say for example for more that 1 minute, and subsequently cancel them?

You can do this by querying pg_stat_activity on newer PostgreSQL versions that record the query start time.

See pg_stat_activity in the PostgreSQL manual.

Use pg_cancel_backend to cancel the query, or pg_terminate_backend to close the connection running the query.

However, I strongly recommend that you instead fix the problem so that your queries don't get blocked. Look at pg_locks when it's stuck, determine why it's getting stuck, and fix the problem. Read the PostgreSQL manual chapter on explicit locking, and the wiki entries on lock monitoring.


Oracle 11g XMLType Experience We are currently evaluating possibilities to store generic data structures. We found that at least from a functional point of view Oracle XMLType is a good alternative to the good old BLOB. Because you can query and update single fields from the xml and also create indexes on XPath expressions.

We are a bit worried about the performance of XMLType. Especially the select performance in interesting. We have queries that select multiple data structures at once. These need to be fast.

Such a query looks something like this

SELECT DOC_VALUE.getClobval() AS XML_VALUE FROM XML_TABLE WHERE d.ID = IN ('1','2',...);

Our XML documents are 7 to 8 KB in size. We are on Oracle 11g and create the XML column with type XMLTYPE.

Do you have experience about the performance of selects on xml type columns? What overall experiences do you have with XMLTYPE? Is this a robust and fast Oracle feature? Or is it rather something immature and experimental?

Regards, Mathias

I have worked with Oracle Xmltype for the last 4 years, starting with Oracle 10.2.0.4 and now on Oracle 12c. I find it very reliable. We have used it not only for storage, but also XML manipulation. In that regard Oracle has chosen XQuery above XSLT.

The performance is dependent on the way you define the storage of these XMLs. You can store them as CLOB, as object-relational storage or (in 12c) as binary XML. The choice that you make depends on different parameters. CLOB keeps the white spaces in the XML. Object-relational storage is the smallest and binary XML (the default in 12c) is somewhere in between.

Regarding Query speed, I prefer binary xml, but all 3 solutions are very much workable in my experience. I didn't see any performance differencies between CLOB and Xmltype (stored as clob). When the XMLs are stored in a more compressed format, you could even gain speed.


Mysql preventing truncate and drop table statements for a specific table I have a MySQL table that serves as an immutable event store. I added triggers to prevent rows from being updated or deleted;

CREATE TABLE t2 (
  str VARCHAR(32),
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)
);

CREATE TRIGGER stop_update BEFORE UPDATE ON t2
FOR EACH ROW
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UPDATE not allowed!';

CREATE TRIGGER stop_delete BEFORE DELETE ON t2
FOR EACH ROW
  SIGNAL SQLSTATE '45007' SET MESSAGE_TEXT = 'DELETE not allowed!';

That part works fine.
But I am unable to prevent the TRUNCATE TABLE or DROP TABLE statements on that specific table.

MySQL doesn't support DDL triggers. Currently I have revoked the drop and truncate permissions for the web app user.

But I would like to add some sort of warning/notice/tripple confirmation dialog thing for the root user as well, just in case someone down the lane accidentally/mistakenly/intentionally tries to clear/drop the table.

Is it possible to add some kind of stored procedure [or anything] in MySql that can prevent a drop/truncate on a specific table? Or at least can warn that this is a special table and should not be removed or cleared.

Consider putting things like that is a separate database.  Then GRANT users access to their database(s) with 'full' permissions (or whatever) and GRANT very limited access to this db:

GRANT SELECT ON Specialdb.* TO ...

If you find you need to let them do something less safe, create a Stored Procedure with security=Definer.  The definer could be root and you can do whatever you want.  However, since you control the proc, you control what things they are allowed to do.


MySQL: "CREATE DATABASE mydb" command hangs On my MySQL 5.6 server, when logged in as root, the command CREATE DATABASE hangs. No error message - nothing. The MySQL error log doesn't show anything special either.

What could I look into to troubleshoot the problem?

A short time afterwards I noticed the database was created, and further commands CREATE DATABASE succeeded.  Most likely, the issue was caused by a database lock that was eventually released later.


ceil() function behavior in postgresql While using the ceil() in postgresql, I found some strange behavior of it, Please see SQL :

Select
     *,
     ceil( (550*0.5)/100 ),
     ceil( (58*5)/100 ),
FROM
   XYZ

If I focus on the output of ceil functions it is strange :

ceil( (550*0.5)/100 ) Returning 3 ( Which converts 2.75 to 3 )
ceil((58*5)/100 ) Returning 2 ( Which converts 2.9 to 2 )

Why 2nd statement returning 2 if it is closed to the greater integer 3 ?

Description
  The PostgreSQL ceil function returns the smallest integer value that is greater than or equal to a number.

And according Postgres Docs:

  Except where noted, any given form of a function returns the same data type as its argument.

As you can see it works properly, in fact, if you use integers the returned value is one integer (58 * 5) / 100) returns 2 but if you forces it to decimal (58.0 * 5.0) / 100.0 then it returns 2.9.

select
     (550 * 0.5) / 100 as C1,
     ceil((550 * 0.5) / 100) as CEIL_C1,
     (58 * 5) / 100 as C2,
     ceil((58 * 5) / 100) as CEIL_C2,
     (58.0 * 5.0) / 100.0 as C3,
     ceil((58.0 * 5.0) / 100.0) as CEIL_C3
;

                c1 | ceil_c1 | c2 | ceil_c2 |                 c3 | ceil_c3
-----------------: | ------: | -: | :------ | -----------------: | ------:
2.7500000000000000 |       3 |  2 | 2       | 2.9000000000000000 |       3

dbfiddle here


Is a trade-in its own purchase order, or an adjustment on a sales order? Let's say you're a car dealer, selling cars.

When a customer buys a new car, he often trades in his existing car.

What data model makes more sense?

A) create a sales order record for the sale, and a purchase order record for the trade-in, and link them together (related_order_id)?

B) create a sales order record for the sale, and a sales order adjustment for the trade-in?

C) something else?

I would recommend modeling it as a sale with an adjustment for the trade-in, assuming that as a car dealer you accept trade-ins without a related sale of a car.

Therefore within your system you re-use the purchase features, and can later report on metrics like how many sales are linked to trade-ins, what values of sales are linked to trade-ins, whether trade-ins boost sales, etc


SQL SSDT Database Projects: Source Control Lookup Data Does SQL Server SSDT Visual Studio 2017 Database Projects support Source control data for lookup tables, or do we still have to utilize messy Post-Deployment Merge Scripts for multiple tables?

Redgate has a more finesse, solution. Wondering when MS will catch up.

Related

Manage lookup data using SQL Server Data Tools (SSDT) (Benjamin Day Consulting)
Pre-Deployment And Post-Deployment Script - SQL Database Project (C# Corner)

There is no specific built-in support for lookup data in SSDT at this time.  As you noted in the "related" links in your question, the currently accepted practice is to script out lookup data in post deploy scripts using MERGE statements.  This can become unpleasant as the size of lookup data grows.

Microsoft actually mentions "reference data" as a primary example of the use of post deployment scripts.  See How to: Specify Predeployment or Postdeployment Scripts:

  These scripts can be used for many purposes. For example:
  ...

  You can insert reference data that must exist in a table in a post-deployment script

You could potentially go outside of SSDT and introduce SSIS packages with flat file sources.  This might be a better experience depending on the preferences and expertise of those on your team.


Strip longest common suffix and concat in Postgres Given a table t:

id | name
------------
1  | abcfug
1  | deffug
1  | hijfug
2  | etc

How can I do something like:

select string_agg(strip_lcs(name), ', ') from t where id = 1

returning:

abc, def, hij

NB I wrote an aggregate function to return lcs if that helps:

CREATE FUNCTION lcs_iterate(_state TEXT, _value TEXT)
RETURNS TEXT
AS
$$
        SELECT  RIGHT($2, s - 1)
        FROM    generate_series(1, LEAST(LENGTH($1), LENGTH($2))) s
        WHERE   RIGHT($1, s) <> RIGHT($2, s)
        UNION ALL
        SELECT  LEAST($1, $2)
        LIMIT 1;
$$
LANGUAGE 'sql';

CREATE AGGREGATE lcs(TEXT) (SFUNC = lcs_iterate, STYPE = TEXT);

Given that you have an aggregate that finds the longest common suffix

WITH x AS(
    SELECT left(name,length(name)-length(lcs(name) over ())) AS s
    FROM t WHERE id = 1
)
SELECT string_agg(s,', ') FROM x;


ms sql differential backup size jumps We are doing a full backup once a week and a daily diff between that.  The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a sudden jump to 40Gb almost every week.  Why would this happen?  How do I investigate?  I'm reasonably sure that the actual db usage is uniform w/o spikes.

Perhaps you've got a maintenance job - such as reindexing - that occurs once a week - that would cause a massive jump in diff backup file size. Or perhaps it's a once-a-week application job that imports or changes data?

If you're also taking regular transaction log backups (say once an hour), if you look at the file size of those backup files it may give you a clue as well as to when the change activity is.


Maximum sum() of overlapping ranges Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the maximum sum() of applicable overlapping ranges. A simple example:

create table event (
  event_id int primary key,
  event_type_id int not null,
  period tstzrange not null,
  quantity int not null
);

insert into event (event_id, event_type_id, period, quantity) values
(1, 1,'[2016-01-06 09:00:00+00,2016-01-08 17:00:00+00]',1),
(2, 1,'[2016-01-07 09:00:00+00,2016-01-07 11:00:00+00]',1),
(3, 1,'[2016-01-07 13:00:00+00,2016-01-07 17:00:00+00]',1),
(4, 2,'[2016-01-07 12:00:00+00,2016-01-07 17:00:00+00]',1);

Given a query with the following clauses:

select ...
where event_type_id = 1
and period && '[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange
group by event_type_id

The desired result would be: 3, i.e. the maximum sum(quantity) where the ranges of the same event_type_id overlap within a given timestamp range.

The task as I understand it
Pick rows from a table where the period overlaps with a given time frame. Determine distinct ranges of overlapping periods within that set and return the greatest sum(quantity) from any range.
Requires Postgres 9.2+, since there are no range types in older versions.
Assumptions

"Overlapping" is meant in a cascading manner, like tiles on a traditional roof: those are "overlapping" (rainproof), though the highest tile does not directly overlap with the lowest.

All values in period have inclusive bounds ([]). Else you have to adjust for exclusive bounds. (The range of the input parameter can still have arbitrary bounds.)

We filter for exactly one event_type_id. Else you have to add PARTITION BY event_type_id to the window definition.

quantity is an integer. Else you have to adjust for the type in calculations.

Quantities for overlapping periods are counted fully, even if parts of the period are outside your given time frame.

Even works for duplicates on (event_type_id, period).

Best performance with a single subquery
This should be dynamite.
SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
FROM  (
   SELECT lower(period) AS p_start
        ,(sum(quantity)                      OVER w)::int AS running_sum
        , lead(lower(period), 1, 'infinity') OVER w
                        > max(upper(period)) OVER w AS range_end
   FROM   event
   WHERE  event_type_id = 1
   AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange
   WINDOW w AS (ORDER BY lower(period))
   ) sub
WHERE  range_end
ORDER  BY 1 DESC
LIMIT  1;

All three window functions in the subquery can use the same window. This avoids additional sort operations and should be fastest.
Verbose CTE variant with more explanation
Same query, just more verbose and slower, since CTEs materialize derived tables and pose as optimization barriers.
WITH cte1 AS (
   SELECT quantity
        , lower(period) AS p_start
        , upper(period) AS p_end
   FROM   event
   WHERE  event_type_id = 1
   AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange
   )
,    cte2 AS (
   SELECT (sum(quantity)               OVER w)::int AS running_sum
        , lead(p_start, 1, 'infinity') OVER w               -- next start ..
                          > max(p_end) OVER w AS range_end  -- .. after last end
        , p_start, p_end
   FROM   cte1
   WINDOW w AS (ORDER BY p_start)
   )
SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
                    -- subtract the previous sum to get the sum of this range
     , p_end::text
FROM   cte2
WHERE  range_end    -- only rows at the end of each range
ORDER  BY 1 DESC    -- biggest sum first
LIMIT  1;           -- only return the winner

sqlfiddle for Postgres 9.3
db<>fiddle here for Postgres 12
You need an index for this to be fast with big tables. The best option would be a GiST index on (event_type_id, period). Details:

Optimizing queries on a range of timestamps (two columns)

Explanation
Filter rows that match your conditions, then sort by the start of the time range (lower(period)) and calculate:

A running sum of quantity (running_sum).
The start of the next period: (lead(lower(period), 1, 'infinity')).
Defaults to 'infinity' for the last row to include the last range.
The latest end of any period so far max(upper(period)).

If 2. is later than 3. it's the end of a (sub-)range (range_end).
In the outer SELECT filter rows with range_end and subtract the previous total to get the sum for the range. ORDER BY that result and return the first (LIMIT 1) greatest sum_quantity. Voilá.
Aside
To select all of Jan 7th, 2016, the clean expression is:
'[2016-01-07 00:00:00+00,2016-01-08 00:00:00+00)'::tstzrange
Not:
'[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange
Details:

PostgreSQL splitting time range into days

Since the default precision of timestamp values is 6 decimal digits (microseconds resolution), you could also use:
'[2016-01-07 00:00:00+00,2016-01-07 23:59:59.999999+00]'::tstzrange
But that's messy and depends on an implementation detail that might change (even if unlikely). It's not subject to rounding errors, since timestamps are stored as integer values in modern Postgres:

Ignoring timezones altogether in Rails and PostgreSQL


Strategy-design of a database for medication I am working on a database where it contains 3 main tables.

The first table is a list of medications with auto increment IDs.

id   name

1    paracetamol 500mg

...

The second table will detect how many paracetamol box we have with a foreign key of id from the first table:

q_id   id   quantity

12     1    5

The third table will indicate each box how much pills does it contains and the barcode of each box:

p_id q_id nbr_pills_per_box barcode

100  12   30                32445332223

101  12   35                ...

102  12   30                ...

Does these 3 tables make a relational database or should I change the design and even the concept ?

I would suggest you merge the 1st 2 tables, since every box will contain a single type of medicine. This way querying your database would be faster as well.

Eg. You want to get the qty of medicines of a particular type, you will have to query by joining these 3 tables. Instead, add the medicine name column in the second table and for medicines with no stock you can get the data from a single table, instead of querying 2 tables.


Shard key selection for both write scaling and query isolation? From the mongod documentation, I know that when choosing shard keys the key should always have high cardinality, so write operations are distributed to different shards (a kind of write scaling). But for query isolation, better query performance will be gained if a query is routed to a single shard server (one single mongos instance).

For write scaling, choosing a shard key which has high cardinality seems easy. But for query isolation, how can I choose a shard key which will route the query to one single shard to the maximum extent when I don't know the detailed sharding algorithm?

The documentation provides an answer through kind of a back-door. As you mentioned, for shards:

For write optimization, choose a shard-key that has high cardinality.
For query isolation, choose a shard-key that allows the mongos instance to route the query to as few shards as possible.

It goes into a few more details, which are relevant to what you're looking for.

When attempting query isolation, always include the full shard-key in queries as it allows mongos to provide optimization. If you don't, it will query all shards.
If your preferred shard-key is not sufficiently cardinal, adding another field that does have sufficient cardinality to create a compound key will allow more efficient write-spreading.

The easiest gain here is to include the shard-key in all queries, even if you don't need the data in the query. This allows you to get the benefits of optimization without having to rewrite your query system to include cluster metadata.

You certainly can write a query-optimizer that uses cluster metadata. It is very possible to get the number of chunks in a specific collection and even get the chunk boundaries. You'll have to track the state of chunks in your collection since they will update from time to time, but it would allow you to write queries that only hit one shard at a time.

However, that's a lot of work and the gains you get may not be worth the extra engineering effort. Only you can tell if that's an optimization that's worth it to pursue.


SQLite JOIN ... USING - rowid works, but then it doesn't This works.

SELECT *
FROM Demographics
JOIN MeasuresofBirthAndDeath
ON Demographics.rowid = MeasuresofBirthAndDeath.rowid;

This does not.

SELECT *
FROM Demographics
JOIN MeasuresofBirthandDeath
USING (rowid);

Why?

It seems that such query works fine for explicit primary keys.
I run this code:

CREATE TABLE Demographics (rowid int PRIMARY KEY);
CREATE TABLE MeasuresofBirthAndDeath (rowid int PRIMARY KEY);

insert into Demographics VALUES (1);
insert into MeasuresofBirthAndDeath VALUES (1);
insert into MeasuresofBirthAndDeath VALUES (2);

SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid);

And get correct result: 1

In case of using implicit rowid it really doesn't work. Let's see code:

CREATE TABLE Demographics (name text);
CREATE TABLE MeasuresofBirthAndDeath (name text);

insert into Demographics VALUES ('demo');
insert into MeasuresofBirthAndDeath VALUES ('birth');
insert into MeasuresofBirthAndDeath VALUES ('death');

SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid);
Error: cannot join using column rowid - column not present in both tables

We get error because rowid is not real column (https://www.sqlite.org/lang_createtable.html#rowid):

  Except for WITHOUT ROWID tables, all rows within SQLite tables have a
  64-bit signed integer key that uniquely identifies the row within its
  table. This integer is usually called the "rowid". The rowid value can
  be accessed using one of the special case-independent names "rowid",
  "oid", or "rowid" in place of a column name. If a table contains a
  user defined column named "rowid", "oid" or "rowid", then that name
  always refers the explicitly declared column and cannot be used to
  retrieve the integer rowid value.

In first select we explicit add condition to join and it works

ON Demographics.rowid = MeasuresofBirthAndDeath.rowid

But in second select there are no column in both table to apply USING to it.

You can ALTER your tables and create INTEGER PRIMARY KEY columns which would be alias for rowid and use JOIN USING on these new column:

  if a rowid table has a primary key that consists of a single column
  and the declared type of that column is "INTEGER" in any mixture of
  upper and lower case, then the column becomes an alias for the rowid.
  Such a column is usually referred to as an "integer primary key". A
  PRIMARY KEY column only becomes an integer primary key if the declared
  type name is exactly "INTEGER". Other integer type names like "INT" or
  "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
  key column to behave as an ordinary table column with integer affinity
  and a unique index, not as an alias for the rowid.


Temporary permissions for DDL I was hoping to use user impersonation to setup a user that has permissions to modify DDL, and only when I need to deploy DDL changes would I impersonate that user.  This way I can use my windows integrated account to query data as needed, but cannot modify the database except when I explicitly impersonate that other user.

According to the MSDN article on impersonation, you can only execute impersonation if you are a sysadmin.

So the challenge is, if my default Login, we'll call it LoginAaron, is a sysadmin of the database, how do deny myself DDL write permissions, so that I cannot execute DDL without explicitly impersonating another user with ddl_admin role(we'll call user UserDdlDeploy)?

Or is there another elegant way to accomplish this?

This is not so much a security procedure, as it is more of a safety/precautionary procedure.

A sysadmin can't demote themselves temporarily / most of the time / etc. What you want to do is create a SQL auth login that you use most of the time, and it only has the privileges to do the limited things you want to do. Then when you want to perform DDL operations, log in as your Windows login.

There are probably ways to do this if your primary login is not a sysadmin, but I think they will be self-defeating. You'd have to log in as someone else any time you want to do anything (including things you want your lower-privileged login to be able to do, but didn't foresee when you set up the permissions). As a quick example, here are two database principals, one of which can impersonate the other:

CREATE USER blat WITHOUT LOGIN;
CREATE USER floob WITHOUT LOGIN;
GRANT IMPERSONATE ON USER::blat TO floob;

EXECUTE AS USER = 'floob';
GO
PRINT USER_NAME();
GO
EXECUTE AS USER = 'blat';
GO
PRINT USER_NAME();
GO
REVERT;
GO
PRINT USER_NAME();
GO
REVERT;

Results:

floob
blat
floob

(Now, impersonation is not perfect - there are issues with using this when you need to access resources across database boundaries, for example.)

So, imagining those are real users hooked to real logins, you could add only blat to the ddl_admin role, and log in as floob most of the time. When you needed to, you could simply impersonate blat, do your DDL things, then revert. My suggestion is to just stay away from trying to diminish the privileges of your Windows account / sysadmin, and just get used to logging in a different way so that you can be a peon most of the time, and only elevate when you need to. I'll repeat, though, that this won't eliminate mistakes you would make in any case anyway - it only prevents you from accidentally running DDL, or running the wrong DDL, which could happen when you've knowingly impersonated, too.

And, because the notion of elevating your own privileges only when you need to, in order to protect yourself because you think you'll only make mistakes when you're not elevated, seems weird to me, and because I can't help it (credit xkcd, of course):

Here is a more concrete example, that uses a real SQL authentication login.

CREATE LOGIN peon WITH PASSWORD = 'peon', CHECK_POLICY = OFF;
GO

CREATE DATABASE playground;
GO

USE playground;
GO

CREATE USER peon FROM LOGIN peon;
GO
ALTER ROLE db_datareader ADD MEMBER peon;
-- of course grant all the permissions you want here
GO

CREATE USER ddldude WITHOUT LOGIN;
GO
ALTER ROLE db_owner ADD MEMBER ddldude;
-- doesn't have to be db_owner, just a valid example
GO

GRANT IMPERSONATE ON USER::ddldude TO peon;
GO

Now, create a new session, logging in directly using peon/peon, and run this code:

USE playground;
GO
CREATE TABLE dbo.foo(id INT);
GO

Breaks, right? Yes, of course:

  Msg 262, Level 14, State 1
  CREATE TABLE permission denied in database 'playground'.

Now, you can impersonate another user, without being a sysadmin, so you should file a bug against whatever documentation told you that:

EXECUTE AS USER = N'ddldude';
GO
CREATE TABLE dbo.foo(id INT); -- works
GO
REVERT;

Don't forget to clean up:

USE master;
GO
ALTER DATABASE playground SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE playground;
GO
DROP LOGIN peon;
GO


How to store user's language knowledge (150+ languages) in mysql DB Users in my website must be able to select languages they know (English, German, etc), so I have a table languages with 150 languages (id, name) and table users (id and a lot of other fields).

Website's visitors must be able to search users by their language knowledge. For example, they might be looking for someone good at English, German and French.

I'm unable to solve this. If I make a table language_knowledge with fields id, language_id and user_id, then I will be able to find users who know one specific language (lets say English), but I need to find users who know, for example, BOTH English and German.

How do I do this?

This is a classic situation where you use a joining table (a fiddle based on this can be found here. It was done much later than the original answer - better candidates for PRIMARY KEY of the language table!.

CREATE TABLE user
(
  user_id INT,
  user_name VARCHAR(25),
  PRIMARY KEY (user_id)
);

CREATE TABLE language
(
  iso_code CHAR(2),  -- or whatever you want the PRIMARY KEY to be.
  language_name VARCHAR(30),
  PRIMARY KEY (iso_code)
);

CREATE TABLE user_language
(
  ul_user_id INT,
  ul_iso_code CHAR(2),
  PRIMARY KEY (ul_user_id, ul_iso_code),
  FOREIGN KEY (ul_user_id) REFERENCES user (user_id),
  FOREIGN KEY (ul_iso_code) REFERENCES language (iso_code)
);

I forgot to mention that the FOREIGN KEYs must point to a PRIMARY KEY or a UNIQUE KEY (and not some other arbitrary field) in the referenced table.

And for completeness, you should put a PRIMARY KEY on your two joining fields. This will help query response time and avoid multiple entries of the same data. Whatever your app does, always try to enforce RI (Referential Integrity) in the database.

This query shows how to get the user id and name for anyone who knows both English and German.

SELECT ul_user_id
    , user_name
FROM user_language ul
    INNER JOIN language l ON ul.ul_iso_code = l.iso_code
    INNER JOIN user u ON u.user_id = ul.ul_user_id
WHERE l.language_name IN (
    'English'
    , 'German'
    )
GROUP BY ul_user_id, user_name
HAVING COUNT(*) = 2;

The HAVING COUNT(*) = 2 clause limits the results to only people who know both two languages.  If the IN (...) clause had 3 different languages, you'd need to modify the HAVING clause to reflect that, like:  HAVING COUNT(*) = 3


Full outer join does not work for two small tables, error 1064 folks.

I am currently exploring the capabilities of MySQL while doing simple exercises with diverse queries.

I was reading the following article that explains how table JOIN works:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

However, I am stuck at the FULL OUTER JOIN example (comprised of tableA and tableB):

tableA:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> select * from tablea;

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Pirate    |
|  2 | Monkey    |
|  3 | Ninja     |
|  4 | Spaghetti |
+----+-----------+

tableB:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> select * from tab

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Rutabanga   |
|  2 | Pirate      |
|  3 | Darth Vader |
|  4 | Ninja       |
+----+-------------+

I am trying the same as proposed in the website:

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

and receive error 1064.

Does anyone have an idea what the reason could be?

Thank in advance for the assistance.

The simple reason is that MySQL has not implemented FULL outer joins, only LEFT and RIGHT ones.

You can simulate the FULL join with a UNION of a LEFT and a RIGHT outer join:

SELECT TableA.*, TableB.*
FROM
  TableA LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name

UNION

SELECT TableA.*, TableB.*
FROM
  TableA RIGHT OUTER JOIN TableB
    ON TableA.name = TableB.name ;

or (for improved performance) using UNION ALL:

SELECT TableA.*, TableB.*
FROM
  TableA LEFT OUTER JOIN TableB
    ON TableA.name = TableB.name

UNION ALL

SELECT TableA.*, TableB.*
FROM
  TableA RIGHT OUTER JOIN TableB
    ON TableA.name = TableB.name
WHERE
    TableA.name IS NULL ;

Both queries (UNION and UNION ALL) will return same results. Unless the result of the join is not unique (if it produces 2 or more identical rows). Since this question has SELECT *, so SELECT a.*, b.*, for this to happen, the tables should have duplicate rows, which is rather unlikely (a table with no primary or unique constraint.)


Should I be using this to export the data using the SQL Developer 'Data Pump Export Wizard' connected as a DBA? I am trying to learn more about Oracle databases in my spare time.  I have Oracle Enterprise available in a Docker Container.  I am a developer rather than a DBA.  I have plenty of experience with SQL Server.
I am trying to use the data pump feature.  So far I have done this:

Created a user called dummy and created a table (Dummy.table1).
Added a few rows to table1.
Created a user called admin and given this user dba access.
Login to SQL Developer as admin and select: View/DBA.
Connect to my database and use Data Pump Export Wizard to create a dump file.
Generate a .sql file from the .dmp file.

I have tried all four options (Database; Tablespaces; Schemas and Tabeles) under: 'Types to be exported in data pump', selecting 'Data and DDL' each time, however I cannot see the INSERT statements, which should be created for Dummy.table1.  Why is this?
The only thing I can think of is that there is a menu item called: Tools\Database Export.  Should I be using this to export the data? I would of thought that the data should be exported when using the Data Pump Export Wizard; connected as a DBA?

Data Pump stores definitions and data in dumpfiles with a binary format specific to Oracle. Data Pump can be used to generate DDL SQL statements (create user, table, view, trigger, etc.), but not for the data in tables.
If you want INSERT statements, then yes, use the Database Export feature of SQL Developer instead of Data Pump.


trigger in MySQL to disallow multiple row deletions at a time on a table How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query?

delimiter \\
create trigger not_more_than_2_rows
before delete on  employee
begin
declare count int default 0;
if (count>1) then
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
set count = count+1
end \\
delimiter ;

but this does not seem to work, the count variable is getting initialized for each row.
I could see that one way to overcome this is to initialize variable count once per trigger execution instead of once for each row....

Any other solutions?

This goes against all that righteous and good in triggers but here we go...
Create a table that keep a count of attempted deletes per connection
CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MyISAM;

or to reduce disk I/O make it a MEMORY table
CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MEMORY;

Now get the count from that table and check it
delimiter \\
create trigger not_more_than_2_rows
before delete on  employee
begin
declare count int;
insert ignore into trigger_happy_employee_deletes (conn_id) values (connection_id());
select delete_count into count
    from trigger_happy_employee_deletes
    where conn_id = connection_id()
;
set count = count + 1;
update trigger_happy_employee_deletes
    set delete_count = count
    where conn_id = connection_id()
;
if (count>1) then
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
end \\
delimiter ;

After your issue a DELETE command, you will have to remember to clear the count like this:
delete from employee where ... ;
update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;

or you count clear it before:
update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;
delete from employee where ... ;

Give it a Try !!!
POTENTIAL DRAWBACKS
Here are the pitfalls of doing this:
Pitfall #1
Using an arbitration method like this (via a MyISAM table) will cause a major bottleneck because a MyISAM performs a full table lock on each INSERT. Using a MEMORY table will not fare much better because although there is far less table access on disk, there is still nominal disk I/O hitting the MEMORY table's .frm file that can cause a slight bottleneck when given enough database traffic.
You cannot make trigger_happy_employee_deletes InnoDB because triggers do not work intelligently with InnoDB:

Jun 21, 2011 : Using MySQL triggers or transactions?
Mar 21, 2011 : Dynamic SQL in MySQL stored routines

Pitfall #2
From a coding perspective, you will have to remember to setup this mechanism across your code. If you forget to do this in all necessary places, you will have undesired deletions.
Pitfall #3
If you want this same mechanism for another table, you would either make another trigger_happy table for deletes or be tempted to merge all delete triggers into a common trigger_happy table, which would make maintenance a total nightmare, not to mention increased bottlenecks by an order of magnitude. Even worse, you may be tempted to merge update and insert triggers into the common trigger_happy table.
Pitfall #4
The trigger would have to communicate with another table outside the triggered table. That's more internal read traffic just to maintain a count.


Show data point values in the legend section Is it possible to show the values in the legend section in a chart component of SSRS? I'm using SQLserver 2008.

Something similar to the image below

That would be really fancy, but I don't think it's possible.  You may be able to constrain the width of your bars of the chart and the width of your columns in your table as well as the placement of both to "fake it" though.


How is the number of records on a select determined as high or low by optimizer? What is the threshold between using the index and doing an direct access to the database?
I assume this decision is related to the number of index access (and subsequent access to the tables). If this number is really low the index should be used. But if the number is high I guess a direct access would be better in terms of performance. But how is high defined? Is there e.g. some variable that specifies this? E.g. in terms of percentage of the table size?

MySQL 5.7 Reference manual contains the following:

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:
The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.
There are no usable restrictions in the ON or WHERE clause for indexed columns.
You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.2, “How MySQL Optimizes WHERE Clauses”.
You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.
For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:
Use ANALYZE TABLE tbl_name to update the key distributions for the scanned table. See Section 13.7.2.1, “ANALYZE TABLE Syntax”.
Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:

SELECT *
FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name = t2.col_name;

Start mysqld with the --max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that no key scan causes more than 1,000 key seeks.

See the original page here:  https://dev.mysql.com/doc/refman/5.7/en/how-to-avoid-table-scan.html


MySQL database stops working after log enabled I have this strange issue. I have MySql workbench, which I use to write queries for MySQL database (xampp 3.2.1). Everything works fine, but recently I found myself having to profile the queries my app sends, so in the config file (my.ini) I added this this line:

general_log             = 1

After adding this line, my server starts, I can connect to it via workbench, but when I try to get the data from my database

SELECT * FROM applicantsDB.Applicants;

I get

  Error Code: 1146. Table 'applicantsDB.Applicants' doesn't exist

While the same query succeeds if the logging is off. I can still get data from my other databases. What might be causing this?

EDIT now it doesn't even work when logging is off (other dbs are fine). Tried creating a new db and importing the schema from applicantsDB. Worked fine until I decided to restart the server. If I do the same without logging on, everything is fine (across restarts too) but when I enable logging, everything breaks.

EDIT V2
sample error log:

2014-04-17 10:20:26 1878  InnoDB: Error: table 'applicantsdb/applicants'
InnoDB: in InnoDB data dictionary has tablespace id 50,
InnoDB: but the tablespace with that id has name applicantsDB/applicants.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2014-04-17 10:20:26 1878  InnoDB: Error: table 'applicantsdb/files'
InnoDB: in InnoDB data dictionary has tablespace id 52,
InnoDB: but the tablespace with that id has name applicantsDB/files.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

EDIT V3
when I create the database with the name in lower case everything works fine. Still, this does not explain why enabling logging can break a database

You certainly have a problem but that does not look related to enabling general logging.  Are you sure it's not that those errors have been filling your error log previously and you only just noticed them after an investigation stemming from enabling general_logging?

Enabling general_logging can and cause your server seemingly hang.   General logging will log every single query thrown over the fence to mysql (even one resulting in syntax errors).

This can cause the file size to grow a lot more rapidly than expected.  Run

show variables like 'general_log_file';

Then check the disk usage for that path from the shell command line

df -h /var/log/mysql/mysql.log

If that reports 0 available and 100% use then it is very much expected mysql would hang.

It's the defined behavior of mysql when any log (general, binary, error) cannot be further written to because space has been exhausted.   The idea is it will just hang out until space is made available.

To remedy you want to either more the log to a filesystem with a filesystem with enough space, or just delete it if you don't care.  Then either restart w/ the general_log variable pointing to a file system with enough space for your traffic loads or add more space to the existing file system.


Help understanding InnoDB Deadlock Detection I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but from the status output it seems not. It would be a great help if anyone could provide an explanation of what the output meaning:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-27 16:19:43 7f4860dbe700
*** (1) TRANSACTION:
TRANSACTION 1305986614, ACTIVE 4 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 8 row lock(s), undo log entries 3
MySQL thread id 1681095, OS thread handle 0x7f48610ca700, query id 2133831389 127.0.0.1 root executing
Update Resources.ResourceAllocations as ra Set
                        ra.Act_Arr = Date_Add( ra.Sch_Arr, interval (deplate - ra.CumulativePerformanceAllowance + cpa) second ),
                        ra.Act_Dep = Date_Add( ra.Sch_Dep, interval (deplate - ra.CumulativePerformanceAllowance + cpa) second ),
                        ra.Arr_Late = (deplate - ra.CumulativePerformanceAllowance + cpa),
                        ra.Dep_Late = (deplate - ra.CumulativePerformanceAllowance + cpa),
                        ra.ActualType = 'F'
                    Where ra.ResourceTrainID = tid and (ra.Sch_Arr > pdate or ra.Sch_Dep > pdate) and  ( Select @updateMoveIDs := concat_ws( ', ', ra.ID, @updateMoveIDs ))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986614 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1305986516, ACTIVE 5 sec fetching rows
mysql tables in use 3, locked 3
36316 lock struct(s), heap size 3765800, 3874929 row lock(s)
MySQL thread id 1674629, OS thread handle 0x7f4860dbe700, query id 2133828216 10.22.33.17 stuart Sending data
Create table TrainsToRetrieve (Index (ResourceTrainID))
            SELECT ra.ResourceTrainID, ra.CIFSequence, Simplifier.ReportingLocations.SimplifierSequence
            FROM Resources.ResourceAllocations as ra
            Inner Join Simplifier.ReportingLocations ON ra.Tiploc = Simplifier.ReportingLocations.Tiploc
            inner Join Simplifier.SimplifierNames as SimplifierNames On SimplifierNames.ID = Simplifier.ReportingLocations.SimplifierNameID
            Where ra.DiagramDate >= date_sub(d, interval 1 day) and ra.DiagramDate <= date_add(d, interval 1 day) and Simplifier.ReportingLocations.SimplifierNameID = simnameid and (
                ( ra.Sch_Dep >= date_add(d, interval (SimplifierNames.StartHour) Hour) and ra.Sch_Dep < date_add(d, interval (SimplifierNames.EndDay* 24 + SimplifierNames.EndHour) Hour) ) or
                ( ra.Sch_Arr >= date_add(d, interval (SimplifierNames.StartHour) Hour) and ra.Sch_Arr < date_add(d, interval (SimplifierNames
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986516 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112726 page no 40423 n bits 144 index `PRIMARY` of table `Resources`.`ResourceAllocations` trx id 1305986516 lock mode S locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Your (2) transaction holds S-lock on table Resources.ResourceAllocations. (1) and (2) transactions wait for X-lock for that table -> deadlock.

What happened:

Trx(2) got S-lock on ResourceAllocations with the select statement (because of create table ... select)
Trx(1) needs X-lock to make update on ResourceAllocations -> wait for X-lock
Trx(2) needs X-lock (for unknown reason) on ResourceAllocations -> wait for X-lock

As the result Trx(1) waits for X-lock from Trx(2) (because Trx(2) has S-lock) and Trx(2) waits for X-lock from Trx(1) (because it is behind Trx(1) in a queue for X-lock).

It's hard for me to say what can be done because I do not see other queries in transactions, but the solution definitely would be: do not require for X-lock in transaction (2).

In simple words: commit transaction(2) after create table statement or do not update tables in this statement from any other transactions until it is done.


du of directories in a diskgroup in Oracle ASM In Linux, you could run the below on a directory to find the size of each sub-directory:

du -sh *

When you're in asmcmdin the +FRA disk group for example, how can I retrieve the size of each directory in the disk group in one list, instead of running du +FRA/directory on each and every single directory?

du of asmcmd doesn't support this, just put it in a loop.

$ dg=fra; for dir in $(asmcmd ls $dg); do echo $dg/$dir; asmcmd du $dg/$dir; done


Replication from Amazon's RDS to Postgres via DMS errors out I have a transactional replication from SQL Server 2008 to RDS SQL Server 2016 in 2014 compatibility mode. This works okay. I now want to replicate this RDS database via DMS to Postgresql. This errors prematurely, the Cloudwatch entries:

  Database instance is not enabled for REPLICATION: Applying
  enablement... (sqlserver_log_queries.c:2752) 2018-08-02T13:09:29
  [SOURCE_CAPTURE ]E: Failed to execute statement: '' [1022502]
  (ar_odbc_stmt.c:4422) 2018-08-02T13:09:29 [SOURCE_CAPTURE ]E:
  RetCode: SQL_ERROR SqlState: 42000 NativeError: 21089 Message:
  [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Only members of
  the sysadmin fixed server role can perform this operation. Line: 1
  Column: -1 [1022502] (ar_odbc_stmt.c:4428)

The user used for replication has all the possible rights on the database and RDSinstance. Any idea What to try next?

Solution: When I first create the replication via the DMS, and only then create the replication to the RDS, it works. BEWARE: in the errorsituation, the database CANNOT BE DELETED. There's a warning that you need sysadmin rights when you try. I had to drop and recreate the instance before i could implement the solution.


Running a query on an improperly indexed, 600gb table This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data.

Originally this data was supposed to come in for one state, but data from the entire country has been storing here for a long period of time.

I have been put in charge of running a query to isolate a LATITUDE/LONGITUDE bounding box in this momentous data set within a certain date range.

To make things even worse, the database is located on an old server which runs on a quad p3 processor with only about 4gb of ram so it's incredibly slow.

Query I would like to execute, but for obvious reasons cringe just looking at:

INSERT INTO gpsdata.vehicle_location_wa SELECT * FROM gpsdata.vehicle_location
WHERE LOCATION_TIMESTAMP BETWEEN '2014-03-20' AND '2014-07-20'
AND LATITUDE BETWEEN 46.86275036 AND 47.80149551
AND LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

Table structure:

CREATE TABLE `vehicle_location` (
`SEQ_ID` decimal(10 , 0 ) default NULL,
`DEVICE_ID` varchar(100) collate utf8_unicode_ci default NULL,
`DATA_TYPE` varchar(80) collate utf8_unicode_ci default NULL,
`DATA_DESC` varchar(180) collate utf8_unicode_ci default NULL,
`SPEED` decimal(6 , 3 ) default '0.000',
`SPEED_UOM` char(3) collate utf8_unicode_ci default NULL,
`DIRECTION` decimal(3 , 0 ) default NULL,
`DURATION` decimal(10 , 0 ) default '0',
`LATITUDE` decimal(16 , 13 ) default NULL,
`LONGITUDE` decimal(16 , 13 ) default NULL,
`GPS_STATUS` tinyint(4) default NULL,
`LOCATION_TIMESTAMP` datetime default NULL,
`TIMEZONE` tinyint(1) default NULL,
`DST` tinyint(1) default NULL,
`STATUS` tinyint(1) default NULL,
`MILEAGE` decimal(15 , 2 ) default '0.00',
`QUEUE_TIME` datetime default NULL,
`OBSERVATION_TIME` datetime default NULL,
`ID` BIGINT NOT NULL auto_increment,
PRIMARY KEY (`ID`),
KEY `DEVICE_ID` (`DEVICE_ID`),
KEY `Location_Timestamp` (`LOCATION_TIMESTAMP`),
KEY `Data_Type` (`DATA_TYPE`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_unicode_ci

Some things I thought about doing:

Selecting only by date range into a new table with two extra indexes: LATITUDE and LONGITUDE [Seems like best option]
Moving database onto a much faster machine with loads of ram (This has its own issues, the whole database is 3.5 tb of data on a raid)
Adding an index to table (gasp)

I'm hoping someone with a bit more experience can help guide me in the right direction or affirm some of my thinking before I start executing queries that could take days to process.

Thank you for your help!

You have a big problem for one reason: BTree indexes (which are the only format available for InnoDB) are highly inefficient for filtering on more than 1 range.

There is one thing that you must understand: in general, using more than 1 index per table access is not possible/efficient (there are some cases where union_merge is faster, but that is an exception)- in general, for AND conditions you want a single index over several fields.

So, no problem then, isn't it? We just index (LOCATION_TIMESTAMP, LATITUDE, LONGITUDE) right? Sorry, no. As you are using a range for the conditions on all the columns, only the first column of the index will be used. The reason is easy to see, but difficult to explain without drawing a tree, so I hope you can trust me on this and I can later explain you why.

There is an exception to this, which is a new feature found in 5.6 called Index condition pushdown (that doesn't solve the problem completely, but makes the 3-column index more useful than a 1-column one).

So what are the options?

Convert some of the conditions to the ref type, that means, convert them into equality checks. That is something that can be done with the timestamps. It may sound a bit weird, but indexing (LOCATION_TIMESTAMP, LATITUDE) and having a query like this:

WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND
LATITUDE BETWEEN 46.86275036 AND 47.80149551;

may actually be much faster. Of course, this depends on the selectivity of the clauses, etc.

Can we do the same for LONGITUDE. In same cases, yes, you could create an additional field called LATITUDE_INT with floor(LATITUDE), and do something like:

WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND
LATITUDE_INT IN (46, 47) AND
LATITUDE BETWEEN 46.86275036 AND 47.80149551 AND
LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

The 3 columns of the indes will be used in this case. But to be frank, that starts to become a bit crazy.
Use the geospatial extensions. MySQL has support for spatial types like POINT, though precisely for geographical applications, and with its own indexes (R-trees), which can index in several dimensions at te same time -so they speed up multirange searches- and have its own, optimised set of bounding box functions (MBRContains() is what you want).

This is great, isn't it? Bad news: it is only available for MyISAM in 5.5 and 5.6, and it will only be available for InnoDB in 5.7. The change is also not very transparent (not as easy as just add an index).

So, the fact that you have an unindexed table is possible due that your options are very narrow, specially for 5.5. If you are a bit flexible about the table structure (partitioning, as you suggested) or performing other tricks, there are ways to walkaround it, but it needs some care.

Some people end up combining MySQL with external tools precisely for this reason, and we will not have a real solution until 5.7.

Finally, there is the problem of the large table. This can become a problem. InnoDB is only efficient when most of its non-leaf parts of the primary key fits into memory. As ypercube says, a 3.5TB table on a 4GB server is something scary. Inserting to that table must be slow. You will need more ram and thinking about partitioning it, for example, as you said, by state (if that is ok for your queries) of by timestamp and get advantage of the manual or automatic prunning.


SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015? Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system.

Interested to hear from others who have found usage cases where performance value was delivered by trace flag 8048 (promote query memory grant strategy from per-NUMA node to per-core), trace flag 8015 (SQL Server ignores physical NUMA), or SUMA (interleaved sufficiently uniform memory access, a BIOS option on some NUMA machines).

Trace flag 8048
http://blogs.msdn.com/b/psssql/archive/2011/09/01/sql-server-2008-2008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048.aspx

Trace flag 8015
http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx

Gory details of system workload, gathered metrics from troubled system, and gathered metrics from the system after intervention follow.

Trace flag 8048 was a 'fix', but was it the best fix? Would SQL Server ignoring physical NUMA due to trace flag 8015 have accomplished the same thing? What about setting the BIOS to interleave memory, leaving the server with SMP-imitating SUMA behavior instead of NUMA behavior?

Peace!
tw: @sql_handle

About the system:
 - 4 hex core Xeon E7540 @ 2.00GHz, hyperthreaded
 - 128 GB RAM
 - WS2008R2
 - MSSQL 2008 R2 SP2
 - maxdop 6

About the workload:
 - 1000s of Batch scheduled/queued reports driven from 2 report application servers.
 - 3 flavors of batches: daily, weekly, monthly
 - All report application servers connections to SQL Server are made as a single service account
 - Maximum report concurrency = 90

Key findings on the troubled system:
- From Perfmon, 15 second intervals
- - System remains at 95%-100% CPU busy
- - SQL Server buffer page lookups < 10000 per/second

From wait and spinlock DMVs, 5 minute intervals

High CMEMTHREAD waiters and wait time

High SOS_SUSPEND_QUEUE spins and backoffs

Bob Dorr's CSS Engineer Blog post on trace flag 8048 indicates that systems with more than 8 cores per NUMA node can run into similar symptoms due to bottleneck in query memory grant. Trace flag 8048 will change the strategy to per-core instead of per-NUMA node.

The Intervention

MSSQL was restarted with -T8048 in place. The difference was immediately evident: buffer page lookup rate rose over 1 million and spike to 8 million per second. The troubled batch workload, which previously couldn't complete in 24 hours, completed in less than 4 hours. Another batch workload which was not the focus of investigation or intervention was submitted as part of validating the corrective value of trace flag 8048 (and ensuring that its unwanted side effects were minimal). This report batch previously completed in 2 hours; with trace flag 8048 in place the report batch completed in roughly 20 minutes.

Nightly ETL also encountered a benefit. ETL time dropped from roughly 60 minutes to 40 minutes.

Pulling together information from several places, I speculate that the high degree of report queuing, the concurrent report count greater than hardware thread count, and the single user account for all reports combined to put pressure on one NUMA node until worker thread pressure caused it to be disfavored for the next incoming connection request for the same user account, at which point the next NUMA node would get some number of connections near instantly. Each NUMA node would end up with a high probability of stressing the query memory grant bottleneck.

Opening more lanes for query memory grant removed the bottleneck. But, I'm not sure the cost. Bob Dorr's CSS post makes it clear that there is additional memory overhead with trace flag 8048. Is that overhead within the single-page allocator region governed by MSSQL 2008 R2 max server memory? If so, I guess the system will just have some number fewer database pages in the buffer pool cache. If not, should max server memory be lowered to accommodate?

This is an awesome post.

To answer your final question, I'd speculate that your answer is "yes".

That said, I probably would have pursued soft numa before resorting to the trace flags.  I think you are right about the numa node allocation and that's could be at the root of your problem.  Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading.  Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.

On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.

Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds.  Makes for some interesting analysis when workload changes or trace flags are implemented:

SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'

and

SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' )
GROUP BY wait_type
ORDER BY COUNT (*) DESC


Ample disk space with error: "1034 Incorrect key file for table 'table_name'; try to repair it" I'm attempting to alter a 1.6 GB InnoDB table with the following query:

ALTER TABLE `table_name` ADD `field_id` int(11) unsigned DEFAULT NULL

The query fails with the following error:

1034 Incorrect key file for table 'table_name'; try to repair it

Nearly every thread on this topic (SO and elsewhere) suggests that this is due to the tmpdir running out of space.

While this seems to be the problem for many, this doesn't seem to be the case on our system.

tmpdir is /tmp:

/tmp is far from full:

I'm totally stuck.  Thoughts?

Well, I'm a big dummy.  When I went to check the size of the database via the method requested by @a_vlad, I realized that previously I was checking the free space on /tmp on the wrong server.

Gah.  Executing the command again while tracking the free space on /tmp on the correct server reveals that lack of free space is, indeed, the problem.

...and here's the slice of humble pie I'm eating right now:


slave server is down,master server query halts (postgresql)? I have my master and slave server in sync.

I have stopped the slave server and tried to perform insert query in master server. The execution is in progress and doesn't give any error message. There is no proper information in logs.

I understand that in synchronous replication master waits for slave to reply but:

Is there anyway to avoid master server being halted?
I have stopped my master server and checked the logs that data is locally committed, is this the behaviour of synchronous replication? commit should not work until all data is committed to master and standby?

In synchronous mode, your primary needs to wait for the result to be acknowledged by the secondary to answer.

From the documentation (links below):

  When requesting synchronous replication, each commit of a write
  transaction will wait until confirmation is received that the commit
  has been written to the write-ahead log on disk of both the primary
  and standby server. [...] Waiting for confirmation increases the
  user's confidence that the changes will not be lost in the event of
  server crashes but it also necessarily increases the response time for
  the requesting transaction. The minimum wait time is the round-trip
  time between primary to standby.

That's why, except for someone who can afford downtime (it happens and it's not so rare), we add other asynchronous secondaries that can become synchronous secondaries if needed.

  The best solution for high availability is to ensure you keep as many
  synchronous standbys as requested. This can be achieved by naming
  multiple potential synchronous standbys using
  synchronous_standby_names.

  In a priority-based synchronous replication, the standbys whose names
  appear earlier in the list will be used as synchronous standbys.
  Standbys listed after these will take over the role of synchronous
  standby if one of current ones should fail.

  In a quorum-based synchronous replication, all the standbys appearing
  in the list will be used as candidates for synchronous standbys. Even
  if one of them should fail, the other standbys will keep performing
  the role of candidates of synchronous standby.

You'll may find useful to read that documentation page :
https://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION


Does any MariaDB engine have the option to declaratively or configuratively abstract highly-redundant data? I have an application which is architected in a "NoSQL" style around one fully denormalized "main table" which currently just holds a primary key and one JSON-valued column. For reasons which are outside the scope of this question, I want to retain this architecture: I do NOT want to go full relational and create a proper normalized data model with entities, foreign keys, etc. I would be willing to parse out the JSON fields into their own columns, if that helps with the question below.

The data has a very high level of redundancy, e.g. some long-text column values such as names, addresses, and descriptions may be repeated 20 or 30+ times across different rows.

Is there a MariaDB engine which can declaratively or configuratively deal with this type of redundancy by internally de-duplicating the values across rows?

I imagine different ways this might be implemented in MariaDB would involve an extension to the BLOB or TEXT data types to leverage a content-based hash along with reference-counting or garbage collection.

What is meant by declaratively: it can be accomplished via a single "ALTER TABLE" statement. What is meant by configuratively: it can be accomplished by modifying one or more system variables.

I have read about ColumnStore, TokuDB, Mroonga, Parquet, and MyRocks but I'm not quite sure this is what they do, as the documentation is very sparse.

This question is specifically about a storage engine to help internally optimize the storage of redundant data. Please refrain from telling me to rearchitect, redesign, or refactor the application.

In any RDBMS, "normalization" is an explicit task that the programmer does.

Having stuff buried inside JSON strings makes that task more difficult.


ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full. The mysqldump is 3.2GB. I assue that it will create an issue while restoring this DB.

  1. Can we analyse the Dump / backup inconsistancy without restoring (testing), if so how ?
  I can aware that this error is occurring due to one table named user_analysis.

Even a single table error leads to dump / backup incosistancy which leads to restoration failure. In my case daily backup is completing successfully. This issue might also cause due to insufficient space. But i had aroung 82GB free space in the SAN mounted partition. I could also see innoDB free space in the table.

  blade_server:master_db> show table status like "user_analysis"\G
  *************************** 1. row ***************************
                   Name: user_analysis
                 Engine: InnoDB
                Version: 10
             Row_format: Compact
                   Rows: 1312878
         Avg_row_length: 33
            Data_length: 43581440
        Max_data_length: 0
           Index_length: 19447808
              Data_free: 0
         Auto_increment: 1312171
            Create_time: 2011-11-25 03:00:41
            Update_time: NULL
             Check_time: NULL
              Collation: utf8_general_ci
               Checksum: NULL
         Create_options:
   Comment: InnoDB free: 13190144 kB; (`user_id`) REFER `master_db/users`(`user_id`)
      1 row in set (0.01 sec)

      blade_server:master_db> select (13190144) /1024/1024;
      +-----------------------+
      | (13190144) /1024/1024 |
      +-----------------------+
      |           12.57910156 |
      +-----------------------+
      1 row in set (0.00 sec)

    Where we had around 12.57 GB InnoDBfree space.

 InnoDB is per file :

   mysql> show variables like "innodb_data_file_path"\G
   *************************** 1. row ***************************
      Variable_name: innodb_data_file_path
      Value:

ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M

 2. What could be the root cause to this error ? I dont have any diskspace issues.
    Where can we pin point for this error ?

 3. Will table is full" triggering in logs due to too low maximum size
    for"innodb_data_file_path".

You have set aside, 12000M for your InnoDB DataFiles (ibdata1...ibdata12). The only possible way for you to have this error is if all 12000M of InnoDB DataFiles have no more room to accommodate new rows into it. How is that possible?

There are four types of information that reside in InnoDB DataFiles

Table Data Pages
Table Index Pages
Table MetaData
MVCC Data

MVCC is Multiversion Concurrency Control. This facilitates ACID Compliance and Transaction Isolation for every SQL transaction, whether it is a single SQL statement or a block of SQL Statements. Whenever you run SQL against InnoDB Tables, that will definitely involve transaction control thus introducing new MVCC Data. Even if you do not execute START TRANSACTION...COMMIT/ROLLBACK paradigms in your application, AUTOCOMMIT is on by default. That will cause InnoDB to write MVCC Data around any data you are reading and/or writing. If there is enough MVCC in the InnoDB DataFiles, it could potentially block InnoDB row data of a certain length from being written.

You have three options to make this go away:

OPTION 1 : Add one or more InnoDB DataFiles to innodb_data_file_path

innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M;ibdata13:1000M;ibdata14:1000M

OPTION 2 : Add autoextend to the laste InnoDB DataFile in innodb_data_file_path

innodb_data_file_path=ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M:autoextend

OPTION 3 : Cleanup the InnoDB Infrastructure

This would be the most enduring solution because there is an option to keep Table Data Pages and Table Index Pages from ever entering the InnoDB DataFiles. You would have to set this option in my.cnf

[mysqld]
innodb-file-per-table

This creates a separate tablespace (.ibd) file for each InnoDB table create after you restart mysql with this new option. Just putting in the option and restart mysql will not create the tablespace file. The added bonus for doing this is that you can collapse the innodb_data_file_path to the default:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

I wrote up instructions on StackOverflow on how to do this.


Does MySQL Enterprise Backup work across Operating Systems? The problem with testing is that one can never be sure (it may seem to work and then you find some thing changed later). A definitive statement from the vendor would definitely be helpful (I cannot seem to find any).

I am thinking about running MySQL Enterprise Backup between MacOS and Liunx OSes

MySQL Enterprise Backup (MEB) is designed to create a point-in-time snapshot of data but only backs up data locally. If the dataset is relatively small, try setting up an NFS share and run MySQL Enterprise Backup with the NFS share as the Target. Of course, there may be some risk involved.

Please check out these links on this subject for further research on MEB

http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/meb-backup-streaming.html
http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/meb-backup-single-file.html
http://forums.mysql.com/read.php?28,409106,410496#msg-410496

Using MEB with data on server with MacOS and Linux OS on the Target Server? One can only guess. Personally, if I had to port mysql data from one OS to another I would recommend this:

Run mysqldump from MacOS to sqldump.sql
Install mysql on Linux
Load sqldump.sql into Linux
Setup replication between servers

MacOS running mysql as Master
Linux running mysql as Slave

From here, just install MEB on the Linux Box (For free version, use XtraBackup) and run your MEB backups locally on the Linux.


Finding discrepancies between soft link parents and children In our system relationships between tables are often (but not always) defined in the application code rather than the database (annoying I know). There's a naming convention which outlines any soft (application side) links.

Here's an example:

On table Oddie, ODD_ID is the Primary key it's got a soft link to table Michael which is called MIC_ODD_ID. There's no Foreign Key constraints between Oddie and Michael as it's defined in the application.

Because of this foreign keys to tables can be created with different amounts of data precision from the "parent" table. So from the previous example, ODD_ID could be a NUMBER(8) and MIC_ODD_ID could be a NUMBER(10).

This is obviously not ideal so I've been tasked with finding all the offending columns. I've been having trouble putting a query together to do this. I'm looking on the DBA_TAB_COLUMNS table and using a substring to outline which columns are similar using this:

SUBSTR(COLUMN_NAME, -0, 6)

Which pulls back the last 6 characters of the COLUMN NAME which should be the same across tables. Using the example from above this would pull back ODD_ID from the Oddie and Michael tables.

I'm having trouble then comparing these strings against each other to find where the data precision isn't the same.

An ideal output would be the offending (Not the same precision) strings and their DATA_PRECISION's along with their OWNER and TABLE_NAME's.

Thanks in advance to anyone who tries to help.

something like below query? adjust code to suit your needs. if you have one column name with multiple definitions, you need to concentrate on those. You didn't mention oracle version so I am going to assume you have access to LISTAGG function in your environment. if not, make suitable amends but this should get you closer. You may optionally want to add views for this comparison as well, but I have only chosen to select tables.

with st as (select c.OWNER ,c.TABLE_NAME, COLUMN_NAME, case when C.NULLABLE = 'N' then 'NOT NULL ' end ||
case when C.DATA_TYPE in ('CHAR','VARCHAR2') then C.DATA_TYPE || ' (' || C.DATA_LENGTH || case when c.char_used = 'C' then ' char' end || ')'
     when C.DATA_TYPE = 'DATE' then 'DATE' when C.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || c.DATA_SCALE || ')'
     when C.DATA_TYPE = 'NUMBER' and C.DATA_PRECISION is null then 'NUMBER'
     when c.DATA_TYPE = 'NUMBER' and c.DATA_PRECISION is not null then 'NUMBER (' || c.DATA_PRECISION ||',' || NVL(c.DATA_SCALE,'0') || ')'
end Col_def
from DBA_TAB_COLUMNS C join DBA_OBJECTS O on (O.OWNER = C.OWNER and O.OBJECT_NAME = C.TABLE_NAME)
where O.OWNER in (<list your schema owners here>)
and O.OBJECT_TYPE in ('TABLE') order by 2,3,1)
select column_name,col_def, listagg(table_name,',') within group (order by table_name) tlist
from st group by column_name, col_def order by 1,2;


Postgresql 9.0 adding new database to streaming replication postgresql 9.0  streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i restore to that cluster? can standby server have all the data?

Hz.Root, your question has many open ends but I will try to answer.

  What is the best way of doing this?

Just create a new database in master server and populate it.

  I have a backup file about 20GB. should i restore to that cluster?

Yes. To be precise, you restore it to the database, not to the cluster.

  can standby server have all the data?

Standby server MUST have all the data - that is it's purpose. Every database, and every other object that is in primary PostgreSQL cluster, will be replicated to secondary server.


syscacheobjects is cleared randomly Microsoft SQL Server Web Edition (64-bit)

Version 10.50.1600.1

SQL Server 2008 R2

I have noticed that we are getting a lot of execution plan re-complilcations - first executions quite slow, subsequent calls very fast as you would expect.

However, periodically (after around 10 minutes or so), performances returns to that of the first query i.e. execution is slow again, then fast.

I have read many articles on what could cause this; I have changed auto update statistics to off as suggested.

What I have noticed is that syscacheobjects will build up nicely, with usecounts into the 1,000's - then suddenly the table will be empty, causing all the queries to recompile.

Does anyone know why the cache may be getting flushed like this?

Many thanks

Paul

A couple of things to check into. These two you could capture with a server-side trace or extended events (though I haven't used the latter for this specific task):

explicit calls to DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE
certain explicit calls to RECONFIGURE (or RECONFIGURE WITH OVERRIDE)

If you see these calls happening often, you'll want to figure out where they're coming from and why, because they absolutely flush the procedure cache.

Also check the error log for events containing the text "cachestore flush":

EXEC xp_readerrorlog 0, 1, N'cachestore flush';

If you see these events make sure the database in question does not have the option AUTO_CLOSE on. You may want to make sure this isn't the case anyway, and it is often turned on with Web Edition since this edition is typically used to hold a lot of infrequently-used databases at a web host.