Defining a physical strategy for a table with ~110M rows I am tasked with designing a table in PostgreSQL (never used it) to load data from flat files. This table will contain ~110M rows. Each row will have a certain range for IP Addresses (e.g.: 0.0.1.0 - 0.0.2.240) and around 20 attributes.

The data needs to be entirely replaced on weekly basis and once a day an update will be done based on daily files.

The application will query this table for a given IP Address. Appropriate range needs to be found and attributes returned.

My plan is to partition the table based on the first octet of the IPs. This will give me 255 partitions. Indexes will be based on Start and End IP for the range.

Please let me know if there are any more helpful ideas for partitioning and indexing? Any good practices will also help.

ip4r Extension

  My plan is to partition the table based on first octet of IP. This will give me 255 partitions. Indexes will be based on start and end ip for the range. Please let me know if there are any more helpful ideas for partitioning and indexing? Any good practices will also help.

This is an ideal case for ip4r, excerpt from the docs.

  [...] the builtin types have no good support
  for index lookups of the form (column >>= parameter), i.e. where you
  have a table of IP address ranges and wish to find which ones include
  a given IP address.  This requires an rtree or gist index to do
  efficiently, and also requires a way to represent IP address ranges
  that do not fall precisely on CIDR boundaries. [... The default types] are variable length types (to support ipv6) with non-trivial overheads, and the authors (whose applications mainly deal in large volumes of single
  IPv4 addresses) wanted a more lightweight representation.

CREATE TABLE ipranges (range ip4r primary key, description text not null);
CREATE INDEX ipranges_range_idx ON ipranges USING gist (range);
INSERT INTO ipranges VALUES ('10.0.0.0/8','rfc1918 block 1');
INSERT INTO ipranges VALUES ('172.16.0.0/12','rfc1918 block 2');
INSERT INTO ipranges VALUES ('192.168.0.0/16','rfc1918 block 3');
INSERT INTO ipranges VALUES ('0.0.0.0/1','classical class A space');
INSERT INTO ipranges VALUES ('10.0.1.10-10.0.1.20','my internal network');
INSERT INTO ipranges VALUES ('127.0.0.1','localhost');

SELECT *
FROM ipranges
WHERE ip <<= range
ORDER BY ip, @ range;


Need advice on abnormal login auditing Recently i am facing some slow issue for my web application. And i have check my visitor report and it shows normal traffic rate as usual. And then i check my db login auditing and find below login failed logs.

Previously I have enabled remote access (Ms SQL Server > Connection > Allow remote access to this server) for personal purpose. But I have disabled remote access option at the moment but not sure whether it helps.

Does it means someone trying to hack my db? Anything else i can do in order to prevent this?

EDIT

I am getting this kind of errors in random time with each time consists of 100-500 failed attempts(last for 1-2 minutes). Random time for today as below:

01/19/2014 18:41:29
01/19/2014 16:59:44
01/19/2014 15:50:56
01/19/2014 14:59:43
01/19/2014 07:02:33
01/19/2014 05:34:45
01/19/2014 01:25:49

Eg data:

01/19/2014 18:41:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 7.
01/19/2014 18:41:29,Logon,Unknown,Login failed for user 'hbv7'. Reason: Could not find a login matching the name provided. [CLIENT: 61.138.99.12]
01/19/2014 18:41:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 5.
01/19/2014 18:41:29,Logon,Unknown,Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 61.138.99.12]
01/19/2014 18:41:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 7.
01/19/2014 18:41:28,Logon,Unknown,Login failed for user 'hbv7'. Reason: Could not find a login matching the name provided. [CLIENT: 61.138.99.12]

Most of the errors are login failed for user 'sa'

Some steps to reduce these vulnerabilities can be found here:

  Disable the login that is being attacked. In most cases, the login would be a generic sa or admin account. This will disable access to the account. However, the server may still be vulnerable to outside attacks.
  Change name of the account that is being attacked. For instance, change the sa account to sa_databasename in order to make it harder to guess a high level account name.
  Change the port from the default 1433 to a random port in order to make it less predictable to find the port that the server is listening to.
  Change the firewall settings to only allow know IP addresses to get through the security system.
  Implement a strong password policy and make sure that the passwords expire on a frequent basis.


What is the reason to "normalize your databases"? In the past, I've been told (on this site) that I should normalize the values in the database - using a lookup table instead of using direct (string) keys.
I am confused why this is so good that several people recommended this. Is it just for memory consumption? But then in my case (explained below) how much is that?
Consider I have a dictionary database for a website:
CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language text NOT NULL,
    value text,
    PRIMARY KEY (id)
)

And then insertion would happen like:
INSERT INTO public.dictionary VALUES
    ('yes_button', 'en', 'yes'), ('yes_button', 'nl', 'ja')

Or instead of 'en' I'd use 'en-us'. Now I've been told to "normalize" the database - which would mean having a lookup table that binds the string representation of a language ('en', 'nl' to a value):
CREATE TABLE languages
(
    id serial NOT NULL,
    language text
)

CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language integer NOT NULL,
    value text,
    PRIMARY KEY (id),
    FOREIGN KEY (language)
        REFERENCES  public.languages (id)
)

However, this would increase complexity quite a bit, since insertion can no longer be simple — it needs to either check the foreign table on the backend, or use some more complex SQL. So there is a real cost to updating to this design.
What are the advantages?

Is it just the storage size? An integer foreign key reference is 4 bytes anyways, and a size 2 string is 3 bytes (while a size 5 is 6 bytes - so save 2 bytes at most).

Is it speed of database? But isn't this then a micro-optimization, which is the "root of all evil"?

Is it just to make sure that each language "exists" before insertion? There are other mechanisms, and languages should be created on the fly anyway. Existence isn't based on what is in our database, but rather an external lookup to the standards, and the language is properly added once a user provides a single translation.

Examples of the idea were mentioned in the comments on my previous question Improving database design, is this a valid case for entity attribute values?:

language should an int and refer to a seperate table(normalizing your tables) – nbk Jan 13 at 16:54

@nbk interesting -> why? The ISO country + language codes are already unique so a string can just as well be the key right? Why would I use an extra integer redirection, when the textual description is as strong a guarantee of uniqueness? – paul23 Jan 13 at 17:58

@paul23 a normqalized table like in my desription or in the answer helps to reduce size and speed of the querys lets say you have 255 language and you save an int that reduce the size massively. A normalized data design helps noto to have redundant data in your tables – nbk Jan 13 at 18:20

The dictionary database is actually separate from the data database, since it is used by several applications.
If I use string keys through a normalizing table, the 'en-us', 'nl-nl'...  might very well be perfect keys themselves. They are unique on their own, so why would I use a uuid or other randomized string? In other words, 'en-us' isn't just a string, it's specifically determined by ISO 639 standard as a locale string. Thus, the amount of strings is very much limited. It very much serves as an "identifier" like "pi" is for 3.1415...
Regarding data quality, I wouldn't have to use an integer key — I could use a string — but then the string 'en-us' is the correct string to represent the "English as spoken in US" locale.

Your issue is that you are getting two different pieces of advice conflated into one and the justifications for each piece of advice are not being presented clearly.
Recomendation 1: Normalize your database
In any transactional database this is generally considered a best practice.  There are lots of reasons why you might back away from this and there are applications, like BI data warehouses, where this is not necessarily what you want.  However, for a transactional database you normalize to begin with and denormalize when necessary as a rule of thumb.
Where there seems to be some confusion is around why to normalize.  You are not alone in this confusion. A lot of people have a lot of misconceptions about the purpose of normalizing your database.
Normalization is NOT (primarily) about:

Increasing performance
Saving memory
Saving disk space
Reducing duplication (it is about reducing redundancy, but that is not exactly the same thing as duplication - more below)

Normalization IS about:

Data Quality (first and foremost)

Normalizing your database design means that you don't store a piece of data in multiple places so that if you need to update or delete it for any reason you might get yourself in trouble because the data will need to change in more than one place.  When you don't normalize you end up with the very real possibility of your data becoming inconsistent over time as changes are made inconsistently.  This is a little bit of an over simplification because there are other benefits of normalization, such as simplified query logic. However, data quality is by far the most important benefit and, ironically, it is the one which programmers without formal database design training most often fail to understand clearly.
Recommendation 2: Use Integer Surrogate Keys
This falls much more under the rubric of common custom than anything else.  Lots of people like to use a meaningless integer primary key in all their relational database tables. This in itself is actually two pieces of advice: (i) use surrogate keys in every table and (ii) use integers for surrogate keys.
Different people will give you different reasons for why they consider these best practices and all of these reasons can be argued over on a case by case basis.  The best argument I can think of for using surrogate keys is that natural keys are more likely to be changed.  Changing any primary key is a giant pain so it's best to avoid if you can.  The best argument I can think of for using integers for your surrogate key is that it's a nice simple data type which is compact and efficient.  Again, this is highly situational so people will make an argument for or against this in different cases.
What I would say about Recommendation 2 overall is this: pick a lane and stick to it so that your code is relatively consistent and diverge from this only when you have a really compelling case of critical performance or critical efficiency and you can clearly demonstrate that diverging from your usual approach has significant benefits.

In general, I avoid using natural keys, but there are times when you can get away with it, and even times when it makes better sense to use them. The question you need to ask yourself with a natural key is "Might this change in the future?"
My rule of thumb is "if a user can see it, they're going to want to change it someday". In your specific case though, language codes are set by an international standards body, so the chance that they might change are pretty slim — it would be too big of a pain.  I wouldn't hesitate in your case to use "en-us" as a key value.


What is the proper way to validate that a many-to-many relationship will not surpass a certain limit? In a Postgres DB, I have a relationship as follows:

A Group table has a column userEntranceLimits.
A UserEntrance table has a column userId and a column groupId.
The count of a UserEntrance's distinct users for a specific group may not exceed the group's userEntranceLimits.
Inside of a transaction (whose isolation level is REPEATABLE_READ) for creating a new UserEntrance, I check if the current count of UserEntrances for that group is >= the limit.
If it is not, I proceed to create a new UserEntrance.
In some rare occasions, a group appears with more UserEntrances than its limit.

I understand why this race condition exists, but I do not understand the proper way to prevent it from happening without using the SERIALIZABLE isolation level. We have many concurrent writes on this table, and when we tried to make the transaction SERIALIZABLE before, it did not go too well.
My idea right now is to do a SELECT ... FOR UPDATE and to count the number of occurrences on the application side, but that looks like a bad solution to me. What is the proper solution for this kind of problem?

SELECT ... FOR UPDATE will not work in this case. It still requires SERIALIZABLE isolation level because what you need to do is to prevent other sessions from inserting rows with the same groupId. And to do this you need to lock a key (what actually SERIALIZABLE level does). What you can try to do?:

check and adjust your indexes (to not lock more rows than you actually need)
revise your code to make your transaction as small as possible
try to emulate locks using separate table (with the list of groupids and locked flag) and updating a row for required groupid before start your transaction and after the end of it.


MYSQL: log the query executed on certain table I am writing a trigger that suppose to fire on inserts for a table and log the exact query used to insert.

However, using below

SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();

Always return in original_query variable this above query not the one firing the trigger and count in processlist is 1.

I am in MySQL 5.6.18.

This is the whole trigger deffinition

DROP TRIGGER IF EXISTS affiliate_revenue_i_log$$
CREATE TRIGGER affiliate_revenue_i_log BEFORE INSERT ON cds_affiliate_revenues
    FOR EACH ROW
    BEGIN
      DECLARE original_query VARCHAR(1024);
      SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();
      INSERT INTO cds_affiliate_revenues_log (action,id,added_date,original_query) VALUES('insert',NEW.id,NOW(),original_query);
    END$$

You could use log_output and general_log (USE CAREFUL), for those blessed with MySQL >= 5.1.12:

Steps:

I've created a new table called new_table:

CREATE TABLE new_table (
  id int(11) NOT NULL,
  text varchar(45) DEFAULT NULL,
  text2 varchar(45) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I've created a new log table called new_table_log:

CREATE TABLE test.new_table_log LIKE new_table;

ALTER TABLE test.new_table_log
CHANGE COLUMN text2 text2 VARCHAR(1024) NULL DEFAULT NULL COMMENT '' ;

Trigger:

USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.new_table_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`new_table_AFTER_INSERT` AFTER INSERT ON `new_table` FOR EACH ROW
BEGIN
    DECLARE vOriginal_query VARCHAR(1024);
    DECLARE vLAST_LO VARCHAR(15) DEFAULT 'NONE';
    DECLARE vLAST_GL ENUM('ON','OFF') DEFAULT 'OFF';
    SET vLAST_LO=@@log_output;
    SET vLAST_GL=IF(@@general_log=0,'OFF','ON');
    SET GLOBAL log_output = 'TABLE';
    SET GLOBAL general_log = 'ON';
    SELECT argument INTO vOriginal_query FROM mysql.general_log WHERE argument LIKE 'INSERT%' AND argument NOT LIKE '%NEW.%' AND thread_id=CONNECTION_ID() ORDER BY event_time DESC LIMIT 0,1;
    INSERT INTO new_table_log(id,text,text2)VALUES(NEW.id,'INSERT',vOriginal_query);
    SET GLOBAL log_output = vLAST_LO;
    SET GLOBAL general_log = vLAST_GL;
END$$
DELIMITER ;

Insert into new_table:

INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST');

Test:

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE new_table (
    ->   id int(11) NOT NULL,
    ->   text varchar(45) DEFAULT NULL,
    ->   text2 varchar(45) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test.new_table_log LIKE new_table;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE test.new_table_log
    -> CHANGE COLUMN text2 text2 VARCHAR(1024) NULL DEFAULT NULL COMMENT '' ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test.new_table;
+----+------+-------+
| id | text | text2 |
+----+------+-------+
|  1 | Lel  | TEST  |
+----+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.new_table_log;
+----+--------+-----------------------------------------------------------------+
| id | text   | text2                                                           |
+----+--------+-----------------------------------------------------------------+
|  1 | INSERT | INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST') |
+----+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql>


Lost Update Understanding https://habr.com/en/company/postgrespro/blog/467437/  gives the following example of a Lost Update:

  For example, two transactions are going to increase the amount on the same account by ₽100 (₽ is the currency sign for Russian rouble). The first transaction reads the current value (₽1000) and then the second transaction reads the same value. The first transaction increases the amount (this gives ₽1100) and writes this value. The second transaction acts the same way: it gets the same ₽1100 and writes this value. As a result, the customer lost ₽100

I read this a few times. But I don’t understand how the customer lost P100. Please explain.

There are two separate transactions (T1 and T2) that each add ₽100 to the customer's balance.

The intended outcome is:

T1 reads the current balance as ₽1000, adds ₽100, and writes ₽1100
T2 reads the current balance as ₽1100, adds ₽100, and writes ₽1200

Or the other way around (T2 then T1). The important point is that both increments of ₽100 are applied.

In the example of a lost update, something like the following occurs:

T1 reads the current balance as ₽1000
T2 reads the current balance as ₽1000
T2 adds ₽100 (to the ₽1000 it read), and writes ₽1100
T1 adds ₽100 (to the ₽1000 it read), and writes ₽1100

This way the final balance is ₽1100, not ₽1200, so the customer has lost ₽100.


Where should one put indexes in a time dimension table? After reading the Questions and Answers from this website about indexes, a question came to my mind.

What if, one is using a time dimension table with the lower level of granularity being the day. Where should one put the indexes ?

Randy Melder in the question : What does “index” means on RDBMS ? said :

  Think of an index as "table of
  contents"... that is an ordered list
  of pointers to positions in a file,
  aka offsets

In the case of the time dimension, most data research might be done either for a specific day, a specific week, a specific month or a specific quarter if the time table stores all the day for a unique year.

My question is : Should one put indexes for all those fields ?

Day is suppose to be unique so for this one I understand perfectly the use of indexes.
But a week id will have 7 occurences, a month id will have 30/31 occurences, a quarter id will have more or less 120 occurences.

Should one still put indexes for those fields ?
Will it still be useful?

I am asking you that because in the same question, David Spillett said :

  Adding too many indexes can be a bad
  optimisation of course, as the extra
  space used to store the indexes (and
  the IO-load to maintain them if your
  DB sees many write operations) may be
  a worse problem than the slightly less
  optimal read queries, so don't over-do
  it.

So what would be the best considerations  for the time dimension case ?

You likely won't run into the issues of write problems, as I assume this would be something created once (or once per year), and then not touched.

But using an index will likely be a hinderance if you're searching by week ...  The problem is, if the index is used, it might scan that first, and then grab each record out of the table individually, which when you're pulling out more than about 5-20% of the records, it's typically faster to do a full table scan, and then drop the records you don't care about.

I don't know of any major RDBMSes that don't optimize for this when it's well-distributed data.  If it's not well distributed (eg, one of the values in a column occurs 95% of the time, but there's also other possible values), you may have to compute histograms on the table and not use a placeholder for the value when searching, so that the query optimizer has the value being searched for when generating the execution plan.

I'd likely not index day of week.  I'd check my database's documentation to see what their tradeoff is for indexed reads vs. full table scans to see if I'd index the day of the month or month of the year.  I'd likely index DOY/day of year if present (which sounds like it's your unique index, anyway)


SQL Server Service Broker - CHECKDB found consistency errors, how could this happen? I encountered a weird SQL Server Service Broker consistency problem, namely I have a queue that holds messages (status=1, Ready to receive) which are lying there for a very long time.

In the meantime there was an upgrade from SQL 2005 to SQL 2012 and now when I want to do something with these messages (e.g. END CONVERSATION, even with CLEANUP) I get an error:

  The conversation handle "......." is not found.

So I tried to look for this handle in sys.conversation_endpoints, but to my surprise it's not there. This shouldn't have happened, as far as I know if there are messages in the queue with associated conversation_group_id and conversation_handle, there should be an endpoint for each one.

I ended up running DBCC CHECKDB and got messages such as:

  Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 9708, State 1:
  The messages in the queue with ID some_number are referencing the
  invalid conversation group 'some_guid'.

  Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 9705, State 1:
  The messages in the queue with ID some_number are referencing the
  invalid conversation handle 'some_guid'.

My question is: how could this happen? Are there any potential DEV/DBA errors or gotchas e.g. while performing SQL upgrade, that might lead to such corruption?

Corruptions can occur due to media corruption or memory bit-flips. Ideally, one has a backup and restores from a backup.

If you cannot restore from a uncorrupted DB, when you have a situation where you have inconsistencies between internal tables you can try to run REPAIR_ALLOW_DATA_LOSS. If repair does not fix the issues you can force a new broker in the database (ALTER DATABASE ... SET NEW_BROKER) which will wipe clean all conversations and messages. You will loose all messages pending processing. You need to exercise care if you have remote conversations (endpoints in different server or even different DB) as the partner endpoint will be left stranded and will have to be cleaned up. NEW_BROKER changes the service_broker_guid in sys.databases and this may impact routes.

If you need more detailed help you'll have to open a case with product support.

  Are there any potential DEV/DBA errors or gotchas e.g. while performing SQL upgrade, that might lead to such corruption?

I'm not aware of any, I suspect more likely to be flaky media/memory/cpu (they all can cause corruptions).


Creating a unique constraint on a PostGIS 'raster' type column I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14).

ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast);

But getting the following errors:

ERROR:  data type raster has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Kindly someone help me to fix this error up. I have no basic idea about the operator classes. Thx.

Zia.

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.:

regress=> create unique index indexname on test USING GiST(id) ;
ERROR:  access method "gist" does not support unique indexes

Data types must specifically support different kinds of indexing, and this data type doesn't seem to support b-tree indexes. So you can't make a unique constraint on this type.

Details:

There are a few index types used by PostgreSQL. Types must provide an operator class for each index type they support. For your purposes there are really two groups of interest:

b-tree - the default, what you get when you don't say the index type. Supports equality, less-than, and greater-than operations. Supports creation of unique indexes.
GiST and GIN - popular flexible index types, used heavily by PostGIS, fulltext search, etc. Does not support creation of unique indexes. Supports more kinds of comparisons than b-tree.

Most data types support b-tree indexing, which is the default, by providing a b-tree operator class.

Some data types, especially those used in GIS, don't provide a b-tree opclass, either because nobody wanted one, or because it doesn't make much sense.

This type seems to be one of them. Nobody's added b-tree support for the raster type, so you can't create a b-tree index on it. As only b-trees are supported for unique indexes, and a unique constraint creates a unique index to implement the constraint, that means you can't make a unique constraint on a raster type.

You might find that a newer PostgreSQL/PostGIS version has the desired opclass. Or might not. I haven't checked. Either way, you're out of luck in 9.1.

Lets try creating an operator class for b-tree support of the raster type. The docs on operator classes and families shows the strategy numbers for the b-tree index strategy, showing that we need support for =, <, <=, > and >= for raster. Do we have those?

regress=# select oprname from pg_operator where oprname IN ('=', '<=', '<', '>', '>=') and oprleft = 'raster'::regtype and oprright = 'raster'::regtype;
 oprname
---------
 =
(1 row)

Nope. So we can't create a b-tree operator class for raster without first implementing ordering operators for it and making sure that those operators follow the required rules about transitivity, commutivity, etc.

That's not overly surprisingly, since it's not clear what exactly raster < raster would actually mean. It does, however, mean you can't enable unique index support easily for raster.

Another possibility would be to hash the input with md5 or similar, then create a unique index on the hash. This solves the index operator type issues and the problems with maximum b-tree entry sizes.

Creating indexes on hashed values is well documented elsewhere.


When does it make sense to duplicate data for querying I have the following table design.

an Event table, with helper tables to hold the event meta data.

each event row has an event type column (regular_event, recurring_route, timed_messages, and more.)

each event type has its own tables to define different things regarding the event - including users. The thing is that it might be referenced directly by a many to many table (users_regular_events) or by a third table or forth table (events -> recurring_routes -> recurring_routes_stations -> recurring_stations -> recurring_stations_users -> users).

would it make sense to create a users_events table that duplicates relations between certain events? because, currently if I want to show each user his events I'll have to join multiple tables. Maybe I need to rethink my entire design?

My suggestion is to design the system the way it makes logical sense, and deal with problematic joins when they prove to cause performance issues. Don't prematurely optimize just because you think joins are costly.

Joins that are performance problems are usually because the queries are horrible (which could be requirements or bad query logic), there are missing indexes, or both.


Changing IP address of SQL Server 2012 I administer a Microsoft SQL Server 2012 instance on VMWare, and I need to change the IP address.  Reading similar questions here on the site I am hopeful that the change will be a simple one, but my version in particular hasn't been discussed.

Running SQL Server 2012 on Windows Server 2012 R2, are there any dangers or relevant precautions I should take before changing the IP address?

I would say that SQL Server does care when you change the IP address of your server. It is all based on the configuration of the TCP/IP settings for that given instance.

Once you change the IP, go into SQL Server Configuration Manager and then the Network Configuration. Verify that the new IP was updated properly in the list of IPs for the TCP/IP protocol. If it does not show up, you will likely see the old IP still there, just update it to the new one.

After this you will need to restart the instance.


How to rewrite subquery to return only 1 row I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please?

SELECT DISTINCT user.id, user.username, user.name, user.surname, user.avatar,
(SELECT IF(ISNULL(
  (SELECT attendant.fk_user AS u
  FROM attendant
  WHERE fk_event = 1

  UNION

  SELECT organizer.fk_user AS u
  FROM organizer
  WHERE fk_event = 1

  UNION

  SELECT invitation.fk_user_to AS u
  FROM invitation
  WHERE fk_event = 1)), NULL, 1)) AS related
  ...

Change your subquery select to count(*) e.g.

select o.OrderID, o.OrderAmount
from Orders o
where (select count(id) from Disputes d where d.CustomerID = o.CustomerID) <= 0


How many files should I create for one filegroup and 9 weekly partitions? I have 9 weekly partitions on one filegroup. How many files do I need to create?

There are 3 tables containing 46 million, 16 million and 10 million rows.

You need one file.

Unless you have specific requirements to have multiple files, you only need one file for the filegroup. The 'specific' requirements would be things like requirement to spread the filegroup on multiple disk locations or a requirement for round-robin allocation. You did not mention any such requirement, you only give information about the number of rows in the tables, which is largely irrelevant when it comes to deciding the number of files in the filegroups.


Local database vs Amazon RDS I am doing large INSERT into a local MySQL database

I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable

I expect this database to get larger and I have a lot more to insert

would using an Amazon micro instance have any benefit here?

I don't foresee my upload speed being a bottleneck, so my logic is that their distributed processing will make the database quicker.

This turned out to be more of a CPU utilization issue but would amazon's solution be better, perhaps more optimized for its more available processors? With this logic a more expensive instance would have better performance

but with so many variables involved here, does anyone have experience with this to say?

You should fully tune the MySQL Environment, particularly your InnoDB settings. (See the bottom of my Answer for tuning tips). This would be much better than fighting Amazon for elbow room in RAM/Disk. Why did I say fight?
If you just spun up an Amazon RDS instance of MySQL, you would subject yourself to whatever  constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted a Week Ago : https://dba.stackexchange.com/a/21498/877
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

The larger the model, the bigger the InnoDB Buffer Pool. Oh yeah, the bigger the budget needs to be.
What most people are unaware of is that the InnoDB Transaction Log Files are the same size for all models : 128M. Given default settings (innodb_log_files_in_group = 2), that's 256M of Transaction Log Space. If you wish to set some other InnoDB option, do this:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=<InnoDB Option>,value=???,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

That's for your future consideration.
As for your import problem at hand, the AWS RDS Documentation has many suggestions for circumventing import slowness including:

Create all secondary indexes prior to loading. This is counterintuitive for those familiar with other databases. Adding or modifying a secondary index causes MySQL to create a new table with the index changes, copy the data from the existing table to the new table, and drop the original table.
Load data in PK order. This is particularly helpful for InnoDB tables where load times can be reduced by 75-80% and datafile size cut in half.
Disable foreign key constraints foreign_key_checks=0 For flat files loaded with LOAD DATA INFILE, this is required in many cases. For any load, disabling FK checks will provide significant performance gains. Just be sure to enable the constraints and verify the data after the load.
Load in parallel unless already near a resource limit. Use partitioned tables when appropriate.
Use multi-value inserts when loading with SQL to minimize statement execution overhead. When using mysqldump, this is done automatically.
Reduce InnoDB log IO innodb_flush_log_at_trx_commit=0

I hope you are not using LOAD DATA INFILE for importing. Why ? Because, only MyISAM tables benefit from it in conjunction with the bulk insert buffer (which by default is 8M). InnoDB does not benefit from it.
If you are loading InnoDB tables via mysqldump or your own SQL, all well and good. You may need to expand the Transaction Logs as I explained if your are imposing large transactions in bulk.
As a sidenote, please do not perform any large transactions if AWS is doing a snapshot. You should make sure snapshots are properly scheduled and then commit not to do any large transactions within the snapshot window. You could disable Snapshots for the duration of your bulk transaction, but the AWS RDS Documentation has a big caveat on that:

Warning: DO NOT DISABLE AUTOMATED BACKUPS IF YOU NEED TO RETAIN THE
ABILITY TO PERFORM POINT-IN-TIME RECOVERY. Disabling automated backups
erases all existing backups, so point-in-time recovery will not be
possible after automated backups have been disabled. Disabling
automated backups is a performance optimization and is not required
for data loads. Note that DB Snapshots are not affected by disabling
automated backups. All existing DB Snapshots are still available for
restore.
Disabling automated backups will reduce load time by about 25% and
reduce the amount of storage space required during the load. If you
will be loading data into a new DB Instance that contains no data,
disabling backups is an easy way to speed up the load and avoid using
the additional storage needed for backups. However, if you will be
loading into a DB Instance that already contains data; you must weigh
the benefits of disabling backups against the impact of losing the
ability to perform point-in-time-recovery.
DB Instances have automated backups enabled by default (with a one day
retention period). In order to disable automated backups, you must set
the backup retention period to zero. After the load, you can re-enable
backups by setting the backup retention period to a non-zero value. In
order to enable or disable backups, Amazon RDS must shut the DB
Instance down and restart it in order to turn MySQL logging on or off.
Use the rds-modify-db-instance command to set the backup retention to
zero and apply the change immediately. Setting the retention period to
zero requires a DB Instance restart, so wait until the restart has
completed before proceeding.
rds-modify-db-instance AcmeRDS --apply-immediately
--backup-retention-period=0 You can check the status of your DB Instance with the rds-describe-db-instances command. The example
displays the status of the AcmeRDS database instance and includes the
--headers option to show column headings.

EPILOGUE
Here are my past posts on tuning InnoDB:

Optimizing InnoDB default settings
Capabilities of InnoDB INSERT Performance
Scaling Percona datacenters: setup and replication


Sync mysql database between 2 geo locations (datacenters) We have a database cluster built on Percona MySQL. We are going to extend our redundancy with another database cluster with a new data center located in another GEO location. So that in case of failures in one location, we can switch the service to other location.

What is the right technology to keep data in both of GEO sites synced always?

You have a 3-node Percona XtraDB Cluster now?  And all 3 nodes are in the same DataCenter?  And you want to use a second DC for HA?

Perhaps the 'common' way is to set up asynchronous replication (the old Master-Slave) between one of the nodes in the original DCs and a node of a new 3-node PXC cluster in the new DC.

Another approach is to continue to use a single PXC cluster --

4 nodes, 2 in each datacenter.
"weight" the nodes so that loss of one DC will let the other DC continue to run.  (Caveat:  That works one one direction; for full protection from DC failure, you need 3 DCs, with at least one node in each.)
Tweak some of the tunables due to the delays between data centers.
Set up some sort of Proxy to assist with the automatic routing of queries.  Such can probably coexist on the PXC nodes.

(Sorry, there are still more details to ferret out; this is only an overview.)


Query execution time is random, need to identify the root cause I have a query like below

select * from table1 where column2=123 and column3='B'

Now the issue faced by us is that this query is executing very slowing sometimes as seen in our performance tracking tool. Also from the knowledge of our application, we know that it gets executed 100s of time in a day, but we only see it being reported as slow 2-3 times a day.

When I run it in SQL Server Management Studio, it gets executed in almost 200ms consistently.

What I need to know is, what is the cause of such random slowness and what could be few strategies to identify/fix the root cause?

Also, note the table has only 3 columns.

Column1 - Primary Key, (Integer)
Column2 - Foreign Key, (Integer)
Column3 - Value, varchar(10)

Other important information as asked in comments.

Indexes on table - No index
Are statistics on this table updated? - No, how to do that?
Is there a possibility that query is blocked by
some data changes or data load? - There are chances that an edit
script might be running at the same time this query is running, but
the update script would not be updating the same row.
What is the transaction isolation level used in this DB? - Read Uncommitted.

In general, A primary key constraint creates a clustered index in the table. It's important has at least one index in the table to avoid fullscans.

As you said the table doesn't have any kind of index, nevertheless you're searching in the table with a predicate which has two columns. In my experience, I'll create a non-clustered index to support this query. There's another essential thing, you should include all columns that you're using in the statement select. Include columns are not key columns in the index, soo be careful.

CREATE INDEX idx ON MyTable (column2 ,column3) INCLUDE (Name, Address)

I've already read a lot of pages of this book and I recommend it for you.

Expert Performance Indexing in SQL Server

This another one here is good too.

SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query


SQL Server take an unencrypted full backup I have to take an unencrypted full backup of a database so that it can be sent over to other team after which they can restore it in their own environment. The other team has a different environment and a different SQL Server certificate.

My server setting for a full backup is taking an AES 128 encrypted backup. And I don't intend to share the decryption key and certificate with the other team. My version of SQL Server is Standard 2014 SP2.

I have seen encrypting the backup but this is something I haven't come across yet. Has anybody encountered this type of scenario before. Any help will be greatly appreciated.

EDIT -

In my case, all SQL server database backups are encrypted using a self signed certificate. This certificate is installed on all servers so only a single certificate needs to be managed. In the event the certificate expires it will need to be renewed as backups will not run with an expired certificate. Additionally, it is required to have the certificate and private key loaded to restore backups created with the key pair.

It was this certificate and private key which I do not intend to share with the other team. Hence, require a copy-only full backup that can bypass the certificate encryption. Is there any way, I can get through the above scenario by creating a backup which doesn't require certificate and private key to be loaded.

Adding the output of the query as asked by learning-dbadmin -

By default SQL will not encrypt backups. From what I see in your screenshot, the backup you're taking there, will not be encrypted either. (The option is just disabled)
A simple way would be to just use T-SQL to create your backup. ( Note copy_only unless you want to be starting a new backup chain) (and replace the folder with your backup dir)
BACKUP DATABASE DBase TO DISK = '\\Folder\DBase_20200109_1034.bak' WITH COPY_ONLY,STATS = 5

Check BOL if you need more parameters like compression.
Aslong as you don't add the encryption clause, your backup will remain non-encrypted.
Of course, feel free to use the GUI as well, aslong as you don't set "Back up to a new media set, ...." and click to encrypt your backups they should all be non-encrypted


Plural vs Singular Table Name How should I name my Tables when creating a new database?

Singular: Client or Plural: Clients?

Up to you. Just be consistent though.

Personally I prefer singular based on what each *row" stores: Order, Product, User, Item, etc.

This matches my modelling (via Object Role Modelling) where I use singular entities/types.

Edit:

One reason is that plural fails when you have link tables:
Orders, Products would give OrderProducts or OrdersProducts. Neither sounds correct

Or history tables (of course you can use schemas for this):
Orders -> OrdersHistory or (no!) OrdersHistories? Wouldn't Order-> OrderHistory be better?


How to prevent or notice automatic connection retry in SQL Server and OLE DB I have a c++ application that connects to SQL Server 2005 using OLE DB / Native client. If the connection gets broken or for example the session is killed from the database something causes the connection to do an automatic reconnect. The problem is that in that case for example all temporary tables created previous connection are lost.

Is there any way to prevent this from happening, or somehow notice that this has happened? That way I could do the same steps as in the original connection.

I tried to add "ConnectRetryCount=0" and "Connect Retry Count=0" to connect string, which I found from here: https://msdn.microsoft.com/en-us/library/jj730312%28v=sql.110%29.aspx but that made no difference, even when I tested the same application native client 11 and SQL Server 2014.

You can check if the temporary object exists using this:

IF OBJECT_ID('tempdb..#sometablename') IS NULL
    CREATE TABLE #sometablename ...

Another thing you could do is check the connection time for the current connection:

SELECT connect_time
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

However, I'm having a hard time believing that the client disconnects without throwing errors. I would try to catch those errors first.


Can filtering be done more efficiently with/without sql joins? I have a simple problem that has a simple solution with SQL, but would like to explore alternative ways to solve it if they turn out to be more efficient on large scale.

Let's assume that we have a system where we have users, videos and list of videos users have viewed:

CREATE TABLE `video` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rank` INT NULL,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_rank` (`rank` ASC));

CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `user_view` (
  `user_id` BIGINT NOT NULL,
  `video_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_id`, `video_id`));

CREATE TABLE `user_friend` (
  `user_id` BIGINT NOT NULL,
  `friend_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_id`, `friend_id`));

The question is: How would we find all the videos that friends of a user have not viewed?

In my solution, I would get the ids of friends (Let's assume user has 3 friends, with ids 1, 2, 3) and build a query:

SELECT
    v.id,
    uv1.video_id
FROM
    video v
    LEFT JOIN user_view uv1 ON (v.id = uv1.video_id AND uv1.user_id = 1)
    LEFT JOIN user_view uv2 ON (v.id = uv2.video_id AND uv2.user_id = 2)
    LEFT JOIN user_view uv3 ON (v.id = uv3.video_id AND uv3.user_id = 3)
WHERE 1
    AND v.id > 100
    AND uv1.video_id IS NULL
    AND uv2.video_id IS NULL
    AND uv3.video_id IS NULL
ORDER BY
    v.rank
LIMIT
    30

Above query will work, but more friends a user has, more joins we'd have to add to the query.

Let's assume we're dealing with 1 billion videos, 100 million users and on averahe user having 50 friends.

Is there a more efficient way to do this in SQL?

Is there a way to do this with non traditional SQL way? Perhaps with noSql with mongodb, cassandra, riak, redis, couchdb or anything else? I'm wondering if there is anything else more efficient purpose built for this.

Any other programming/processing technique that would prove to be more efficient?

I would really appreciate your input.

I don't understand your two choices, but I feel sure that a LEFT JOIN is involved.

Ponder something like this:  Step 1:

SELECT uv.video_id
    FROM (
        SELECT uf.friend_id    -- all your friends
            FROM user_friend AS uf
            WHERE uf.user_id = u.id
              AND u.id = 123   -- you
         ) AS f
        LEFT JOIN user_view AS uv  ON uv.user_id = f.friend_id
        WHERE uv.id IS NULL    -- that they did not view

But, beware, the output could be very long.

I don't know if you need DISTINCT after SELECT.

Step 2:  Now to peel off the 30 highest ranked ones:

SELECT v.id
    FROM ( the above query ) AS b
    JOIN video AS v  ON b.video_id = v.id
    ORDER BY rank DESC
    LIMIT 30

Unfortunately, the query must find all the un-viewed videos before it can sort to locate the top 30.


You need conditional aggregation using sum(case):

select cat_id,
   sum(case when status = 1 then 1 else 0 end) as published,  -- only count status 1
   sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0
   count(*) as totals
from tab
group by cat_id

Edit:

My crystal ball was apparently broken, but now it's fixed :-)

If you need to count each product_id only once you can use this:

select cat_id,
   count(distinct case when status = 1 then product_id end) as published,  -- only count status 1
   count(distinct case when status = 0 then product_id end) as unpublished,-- only count status 0
   count(distinct product_id) as totals
from tab join whatever...
group by cat_id


Using SPID in DB Tables (instead of Table Variable) Transactional database used for booking things...

Our vendor was asked to replace #temptables with @tablevariables (because of heavy compile locks) but instead they replaced with an actual table that adds SPID as a column to ensure the stored procedure only acts on the applicable rows.

Do you see any risk in this method of operation?  Before all transactions were isolated within their own transaction...  I worried we may end up locking this table a bunch but their opinion is that SQL uses row-level locking and this won't create more locks.

SQL Server Version: 2016 Enterprise - 13.0.5216.0

CREATE TABLE dbo.qryTransactions (
    ID int IDENTITY (0,1) NOT NULL CONSTRAINT pk_qryTransactions PRIMARY KEY CLUSTERED,
    spid int NOT NULL,
    OrderID int,
    ItemID int,
    TimeTransactionStart datetime,
    TimeTransactionEnd datetime,
...other fields
    )

CREATE INDEX idx_qryTransactions_spidID ON qryTransactions (spid, ID) INCLUDE (ItemID, OrderID, TimeTransactionStart, TimeTransactionEnd)

It seems to me using the @@SPID like that is asking for trouble.

Session ID's are reused frequently; as soon as a user connection logs out, that session ID is available to be used again, and is likely to be used by the next session attempting to connect.

To make it work at least semi-reliably, you'd need a login trigger that purges prior rows from the table with the same @@SPID.  If you do that, you're likely to see a lot of locking on the table using the @@SPID column.

SQL Server does indeed use row locking, but it also uses page locking, and table locking.  Of course, you might be able to mitigate that via good indexing, but this still looks like an anti-pattern to me.

If the stored procedure is the only method used to access the affected tables, you could investigate using an application lock, via sp_getapplock to essentially serialize access to the relevant parts.  Docs for sp_getapplock are here.  Erik Darling has an interesting post about it here.


How do I clear a table from SQL Server's cache? I have a few tables in my database that should not be cached.

How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache?

Flushing all the cache is not an option.

I'm using SQL Server 2008 and SQL Server 2008 R2.

There is no way of doing this.

DBCC DROPCLEANBUFFERS doesn't accept any parameters for a specific database or object. Internally SQL Server can do this at a database level however as when a database is AUTO_CLOSEd all corresponding pages are removed from the buffer cache.

Also internally SQL Server can mark certain pages such that they will be the first ones kicked out by the lazy writer. This is used by DMVs such as sys.dm_db_index_physical_stats to avoid flushing the buffer pool as alluded to in this article but this functionality is not exposed in any way to us (even though it might be useful to be able to specify the same if doing a one off scan of a large table for example).


Check If Value Exists In Table I am needing a way to run an If/Else statement in SQL Server.  This is what I am needing done, if my Exec sp_executesql @SQL statement returns a value (meaning it exists in the table) I want to set the value returned to a variable.  If it does not return a value, then I want to print a message saying it does not exist.
With the sample DDL Below, I would want to assign the value of 3 to the variable @placeholder since 3 would be the value returned.  How would that syntax be constructed?

Create Table #Test1 (id int,name varchar(100))
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange')

Declare @Color varchar(100), @sql nvarchar(max), @placeholder varchar(100)
Set @Color = 'Pink'

Set @Sql = 'Select id from #Test1 WHERE name IN ('''+@Color+N''')'

Exec sp_executesql @SQL

Drop Table #Test1

Your example looks quite simple and does not need sp_executesql statement (as long as your query returns only one row)...

You can set your variable in your select :

Create Table #Test1 (id int,name varchar(100))
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange')

Declare @Color varchar(100), @returned_id varchar(100)
Set @Color = 'Pink'

Select @returned_id  = id
from #Test1
WHERE name = @Color

if @returned_id is null
    print 'Error - no rows returned for ' + @Color
else
    print @returned_id

Drop Table #Test1


MongoDB | Problem inserting embedded documents into document I am trying to insert data in a nested format.

A snippet of my models (there are many, many more):

class LifetimeMode(db.EmbeddedDocument):
    meta = {'collection': 'lifetime_mode'}
    career = db.EmbeddedDocumentField(GameModeData)
    war = db.EmbeddedDocumentField(GameModeData)
    sd = db.EmbeddedDocumentField(GameModeData)
    dom = db.EmbeddedDocumentField(GameModeData)

class LifetimeData(db.EmbeddedDocument):
    meta = {'collection': 'lifetime_data'}
    mode = db.EmbeddedDocumentField(LifetimeMode)

class UserData(db.EmbeddedDocument):
    meta = {'collection': 'user_data'}
    timestamp = db.DateTimeField(default=datetime.utcnow)
    type = db.StringField()
    level = db.IntField()
    lifetime = db.EmbeddedDocumentField(LifetimeData)

class User(db.Document):
    meta = {'collection': 'user'}
    public_id = db.UUIDField()
    gamer_id = db.StringField()
    user_data = db.EmbeddedDocumentField(UserData)

When I try to create my database with dummy data I am running into an issue I cannot solve. I grab my data from an external API.

lt_mode_data = data['lifetime']['mode'] # << This is the API returned data
for mode, mode_data in lt_mode_data.items():
    game_mode_data = GameModeData()
    lifetime_mode = LifetimeMode()
    json_data = json.dumps(mode_data['properties'])
    lifetime_mode[mode] = game_mode_data.from_json(json_data)

The problem is that only the final mode is added, as if each modes data gets added but is then overwritten by the next.

Any ideas?

To solve this problem I changed the code to the following:

lt_mode_data = data['lifetime']['mode']
    mode_dict = {}
    game_mode_data = GameModeData()
    lifetime_mode = LifetimeMode()
    for index, (mode, mode_data) in enumerate(lt_mode_data.items()):
        mode_dict[mode] = mode_data['properties']
    lifetime_mode = lifetime_mode.from_json(game_mode_data.from_json(json.dumps(mode_dict)).to_json())


Where can I find mysqld error logs? I am trying to run mysql but I get this errors and I don't know what to do. Are there any more error logs where I can find more information?

/usr/local/mysql/bin# ./mysqld

150321 15:35:20 [ERROR] Can't find messagefile '/usr/share/mysql/english/errmsg.sys'
150321 15:35:20 [Note] Plugin 'FEDERATED' is disabled.
./mysqld: Unknown error 1146
150321 15:35:20 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
150321 15:35:20 InnoDB: The InnoDB memory heap is disabled
150321 15:35:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150321 15:35:20 InnoDB: Compressed tables use zlib 1.2.3
150321 15:35:20 InnoDB: Using Linux native AIO
150321 15:35:20 InnoDB: Initializing buffer pool, size = 128.0M
150321 15:35:20 InnoDB: Completed initialization of buffer pool
150321 15:35:20 InnoDB: highest supported file format is Barracuda.
150321 15:35:20  InnoDB: Waiting for the background threads to start
150321 15:35:21 InnoDB: 1.1.5 started; log sequence number 1595675
150321 15:35:21 [ERROR] Aborting

150321 15:35:21  InnoDB: Starting shutdown...
150321 15:35:22  InnoDB: Shutdown completed; log sequence number 1595675

I have no idea how it happened, but:
1) mysqld was not using /etc/mysql/my.cnf file.

2) I could not find any other my.cnf files on the machine.

3) So i changed /etc/mysql/my.cnf to point to my data files

4) And I created a symlink for socket:

ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

5) And then I run the deamon:

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf


Attach AdventureWorks Data to Existing Database (No Admin Role) I've been struggling to find a solution to this.

I have been provided with a database within a server run by our IT department.  I have full write access to that dB but no other rights on the server.

I'd like to use the AdventureWorks dB to learn SSIS, but obviously can't create the database from the .mdf file.

Is there another method I can use to create the relevant table and copy the AW data directly into my existing dB?

You know what's really funny? This is exactly the kind of thing SSIS is designed to do.

You're probably going to run SSIS on your own desktop or laptop as you learn. SSIS is part of SQL Server Developer Edition, so you can install that for free on your laptop. It also comes with the SQL Server engine, obviously.

So attach the AdventureWorks db to your local SQL Server Developer Edition, and then use SSIS to move the contents of the local tables you want over to the server run by your IT department.

Congratulations, you have your first ETL project! Bad news though: you're also the client. You're probably a terrible boss.

If you want to learn something easier, start by using SSIS to import the contents of a spreadsheet or a text file into that target SQL Server instead.


PostgreSQL maintenance I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III.

I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. Moreover, I need to update database statistics via ANALYSIS so query executor could select the best plan and I also need to REINDEX to regenerate indexes.

The windows I use for that purposes is here: http://www.pgadmin.org/docs/dev/maintenance.html

Some questions regarding the behavior and options:

if I run VACUUM without selecting any options below (FULL, FREEZE, ANALYZE) what PostgreSQL will do? Will it only free some space and that's it?
If I run VACUUM with Analyze option is it the same as running VACUUM without Analyze and then running ANALYZE separately. What is working faster?
Is it worth to do REINDEX after doing VACUUM and ANALYZE? I can see in verbose messages that indexes are affected somehow by VACUUM without options. Should I run REINDEX to make my indexes works faster?

I don't know what resources you're getting this from. Not just that PgAdmin page given some of what you're saying. The information you're relying on is either outdated or incomplete; all this is pretty much unnecessary.

Make sure that autovacuum is keeping up with the database workload and you're pretty much done. These days you should not generally need to run a manual vacuum or analyze, though it's handy after bulk loads or deletes. Manually reindexing is certainly not required as a routine operation.

See autovacuum in the docs.


mysql if else with a statement as its condition? I am trying to calculate something from the DB level and would like to know if its possible to an if/else or ifnull or other to evaluate this?  IF not what is a good approach?
Is it possible to do something this way?
I've tried several ways, but here is my latest example.
IF (SELECT markups.markup FROM markups LIMIT 1)
THEN
    SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1
ELSE
    SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"

You can use dynamic sql for this purpose
The if statement chekcm if there are markups if so it tales the firwst query into a user defined variable @sql
and the executes it (see manual)
SELECT IF(EXISTS(SELECT markups.markup FROM markups)
, @sql:=' SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1'
,@sql:='SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Mongo Shard Server Doubling as Another Shard's Replica Server I'm going to be setting up a sharded + replicated mongo cluster, with application software servers co-located with mongod instances-- so that odds are a request is being processed on the same hardware server where the necessary data lives.

But it's a bit of a drag how mongo secondary replica servers are almost dead weight compute-resources, since they can only handle reads... and must forward all writes to their primary. Therefore, there's almost no point to co-locate application servers with secondary replicas... only primaries.

Therefore I've considered a setup where each server is a shard, and also a replica for a neighboring shard. That way, no server is dead weight.

Are there any concerns with this architecture, from anyone who has tried it?

Normally network speed (read/write) is not bottleneck, so application servers can be separated from mongod primaries.

If you plan to make cluster and put application to primaries so that application writes only that shard where it's data lies it's going to be a little bit tricky to select right sharding key.

Only concern is the memory, normally you DON'T put any applications to mongod nodes, because mongod uses ALL the memory, eventually! Atleast at situation where your data+indexes are bigger than free memory (at start of machine).


Downgrading from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6 We are trying to downgrade from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6 due to some issues. But AWS RDS doesn't allow Aurora MySQL 5.7 snapshot restore to Aurora MySQL 5.6 instance/cluster. Does anyone face same problem? How can we restore it to old version?

Thanks,
Sagun

I think exporting to a SQL file first would work:

export to a SQL file (in S3 or elsewhere)
import to a 5.6 instance

See this: Amazon Aurora Can Export Data into Amazon S3, where it says:

  You can now use the SELECT INTO OUTFILE S3 SQL statement to query data from an Amazon Aurora database cluster and save it directly into text files in an Amazon S3 bucket. This means you no longer need the two-step process of bringing the data to the SQL client and then copying it from the client to Amazon S3. It’s an easy way to export data selectively to Amazon Redshift or any other application.
  ...


MySQL: How select the count of two queries while selecting another field too? I'm trying to get how many orders happened during a month, if the order has a duration of 168, it's a weekly transaction so I have to divide it by 4, and get the ceiling. If an order has a duration of 720, it's a monthly transaction.

I want the result to contain the video_title and count, which is the transactions count which is all the monthly transactions + (weekly trasactions/4)

I tried the query below, it's correct but I get an error when trying to select the video title too which comes from videos table, how to select the video title as well?

SELECT video_title,
(
    SELECT  CEILING(COUNT(*)/4) FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =168
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
        )
        +
        (
    SELECT  COUNT(*)
    FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =720
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
        )
       as count
       GROUP BY video_title ORDER BY count ASC"
ORDER BY count ASC

And also what indexes are the best for such a query?

In your query you are adding the results (SELECT COUNT(*)....) from two queries together. There is no reference to a video_title in this count. I added this reference to the video_title which makes sure the count is done for every video_title.

The left join makes sure the every video is selected, and that's why IFNULL(..) is added. If the link to a subquery fails, the count is NULL, the function IFNULL replaces that with 0.

SELECT
   v.video_title,
   ISNULL(c1.count,0)+ISNULL(c2.count,0) as count
FROM videos v
LEFT JOIN
   (
    SELECT  video_title,CEILING(COUNT(*)/4) as count FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =168
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
    GROUP BY video_title
   )  c1 on c1.video_title = v.video_title
LEFT JOIN
   (
    SELECT  video_title, COUNT(*) as count
    FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =720
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
    GROUP BY video_title
   ) c2 on c2.video_title = v.video_title
ORDER BY count ASC

Because no input data was given, this is only a guess on what the desired query should look like


How I can force the TSQL-Developers to use not deprecated TSQL? I want to avoid the using of deprecated code.

Like the new feature Nr. 8
http://msdn.microsoft.com/en-us/library/ms178053.aspx

I want to force them to use "WITH" for example, or a semicolon at the end.

Is there an easy way to force this with ease?

Bjorn,

There is nothing out of the box that will force the developers to use non-deprecated features (as they are deprecated, not removed). The most tailored solution that could be created is with DDL trigger(s), but note that they can be tricky... especially if not extremely familiar with them.

I would suggest PBM, but it has limitations that won't necessarily capture everything you're looking for. As already discussed, you can use extended events (in the previous link I had) to check for the use of the deprecated items.

Here is an example that will stop the use of fn_get_sql() and give a helpful message while stopping the use. Note that multiple checks can be made in the same trigger, this is just a trivial example.

/* Create a test datbase */
CREATE DATABASE No_Deprecated_Items;
GO

USE No_Deprecated_Items;
GO

CREATE TRIGGER Stop_Deprecated_Items
ON DATABASE
AFTER CREATE_FUNCTION, ALTER_FUNCTION, CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER
AS
BEGIN
    DECLARE @Command NVARCHAR(MAX)

    SELECT @Command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    IF(PATINDEX('%fn_get_sql%', @Command) > 0)
    BEGIN
        RAISERROR('fn_get_sql is deprecated, use sys.dm_exec_sql_text() instead!', 16, 1)
        ROLLBACK
    END
END
GO

/*
This is created as it passed the constraints in the trigger
*/

CREATE PROCEDURE Good_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM sys.dm_exec_sql_text(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is created
SELECT * FROM sys.procedures
GO

/*
This will be rolledback as it fails the trigger logic.
*/
CREATE PROCEDURE Bad_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM ::fn_get_sql(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is NOT created
SELECT * FROM sys.procedures
GO

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

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

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


Not enough server storage is available to process this command - RESTORE DATABASE is terminating abnormally First a little description of the system that I'm seeing the problem on:

My firm has a regularly scheduled restoration of a database from backups of another, I won't go into the business reasons for this.

The database being restored is on an instance of SQL Server 2014 with SP2-CU1 updates which is running on a Windows Server 2012 R2 VM.

The drive on which the mdf/ndf/ldf files are, has 1.19TB free space, and the database occupies some 863GB. The .bak files themselves are held There are also some other database files on this drive leaving 244GB free, which I assume is enough for it to do a restore without any issue related to insufficient working space.

Over the weekend we do a restoration from a full backup, and every other day is from a differential. We sometimes see the restoration fail multiple times in a row, at present I've had some 8 or 9 consecutive failed attempts since Monday. This usually occurs when restoring from the full backup which leads me to believe that it's a performance issue with SQL Server when handling large databases, or it might be something funny with the VMs?

The error I am seeing when it fails is below. This can occur at seemingly any point during the restoration, for instance the last attempt it got up to 76% complete on restoring the full backup before keeling over.

Msg 3203, Level 16, State 1, Line 2
Read on "\\VM\Backups\TheDatabase.bak" failed: 1130(Not enough server storage is available to process this command.)
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 4319, Level 16, State 5, Line 3
A previous restore operation was interrupted and did not complete processing on file 'TheDatabase'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

There is a project to do partitioning so we need only backup and restore the hot portion of the DB, which should hopefully resolve this, but I am looking for anything in the interim which could potentially reduce the occurences of this or stop this from happening entirely. Let me know if more info is needed to diagnose this.

Any help would be greatly appreciated!

The error message is misleading you, it's not talking about disk storage on the machine where you are restoring.

The windows error message states:

  1130(Not enough server storage is available to process this command.)

When you check that error message you will notice that it's not an error during the restore operation but rather during the file copy operation over the network as documented here

  The two causes of this problem are related. The more frequent cause is listed first:
  More files are open than the memory cache manager can handle. As a result, the cache manager has exhausted the available paged pool memory.
  The backup program has tried to back up a file whose size is larger than the backup API can access on that version of the operating system. This has the same result (that is, the paged pool is exhausted).
  Note This second issue is more likely to occur on a Microsoft
  Windows NT 4.0-based computer.

  The resolution for each problem differs depending on whether you
  experience the problem in Windows Server 2003, in Microsoft Windows
  2000, or in Windows NT 4.0.

Or here

  When you access shared items on a Windows NT server from a Windows NT
  client, you may receive the following error message:   Not enough
  server storage is available to process this command.

So as you say, you likely have enough storage to restore the database but you are running into issues retrieving the file over the network.
In any case you need to be looking at some SMB issue

Had you been running into disk space problems during the restore the error message would have been:

  112(There is not enough space on the disk.)

As @Nic correctly pointed out in the comments you should probably try to copy the backup file over using an unbuffered file copy (use ESEUTIL or XCOPY) and restore from there to either solve or at least isolate the issue.

If you still run into issues copying over the file that way I'm not sure that still fits the scope of dba.se.


MySQL: Show full processlist has empty Host name. Why? I am executing SHOW FULL PROCESSLIST and from the response I get the "Host" field is empty.

Quoting from the docs here

  Host

  The host name of the client issuing the statement (except for system
  user where there is no host). SHOW PROCESSLIST reports the host name
  for TCP/IP connections in host_name:client_port format to make it
  easier to determine which client is doing what.

Who is a system user? The root user?

I have permitted remote access to the root user.

The same case on my another system where there a root user with the remote access and executing SHOW FULL PROCESSLIST shows localhost:<thread-id> in the host field.

How to know in which case MySQL shows the host name in SHOW FULL PROCESSLIST`?

The system user is the user defined to execute MySQL Replication.
There are two DB Connections dedicated to performing MySQL Replication

IO Thread : This thread is responsible for downloading the latest Binary Log Entries from the Master and Storing those Entries in the Slave's Relay Logs.
SQL Thread : This thread is responsible for processing the relay logs like a FIFO queue. It basically reads the next available Relay Log Entry and processes the SQL of that Entry.

The system user is referred to as a non-client thread in the MySQL Documentation about SHOW PROCESSLIST;
As far seeing localhost:#####, that number after the colon is a really a port number within mysqld assigned to localhost.
UPDATE 2012-04-27 18:00 EDT
Questions from your comment

Can I rename the system user? Or any other properties of the system user? Also, is the system user is dedicated only for replication? Or any other MySQL processes are spawned by System User? I understand that system user cannot be accessed by a client, its an internal process spawned by MySQL.

Answers to Questions from your comment
No, you cannot rename the system user. It is dedicated to handle MySQL Replication only. The only way to manipulate properties of of the system user would be throught the GRANT command issued to create the replication user.
For example, when you setup a replication user, you issue a command like this:
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to 'repl'@'%';

When START SLAVE is issued on a Slave, the Master authenticates the DB Thread coming from the Slave and assign one thread on the Master. The thread on the Master will ship binary log entries to the I/O Thread on the Slave. The I/O Thread on the Slave is assigned to system user for handling communcation between Master and Slave. The SQL Thread on the Slave is also assigned to system user for handling intracommunication of local relay log entries to be processed FIFO (Frist In, First Out) by mysqld running on the Slave. No direct access is permitted via the MySQL Client on the Slave except for

STOP SLAVE; (Kills both I/O Thread and SQL Thread)
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
START SLAVE; (Creates both I/O Thread and SQL Thread)
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;

Of course, you could issue KILL ####; where #### is the process ID of either the I/O Thread or SQL Thread. You would be totally respsonsible for reestablishing replication at the risk of losing the correct log file and position if the KILL command misses any communication because of an unnatural stoppage of a replicaton thread.


Txn Log backups - does it backup empty space? If I have a transaction log that is 1.5GB and only 5% used, will the transaction log backup only the 5% of the 1.5Gb (~250MB)?  I always assumed so, but I am seeing constant log backups of 250MB (every 10 minutes) even in the middle of the night when there shouldn't be any activity in the database.  This observation has led some to believe it is backing up the entire 1.5GB and with compression of the empty space making it smaller.

Does a transaction log backup only what it needs to, or does it do the entire .ldf file?

Log backups only backup actual data and do not backup empty space.

It sounds like there are still processes running overnight.  Got any index rebuilds or any maintenance tasks (aside from DB backups) that may be running overnight?  Just because you think the server should be quiet, doesn't always mean that it is.  There could be overnight batch jobs, miscellaneous tasks, all sorts of stuff.


Optimizing the query with 2 distinct counts in select This SQL works perfectly but it is pretty slow for me.
I did all performance improvement in my database. More complex queries works faster. ( Postgresql 10.x )

How can I optimize or make this query faster?

SELECT
  COUNT(DISTINCT a.message_id) :: DECIMAL / COUNT(DISTINCT b.order_item_id) :: DECIMAL
FROM messages a
INNER JOIN order_items b
  ON a.product_id = b.product_id;

Aggregate  (cost=53169.94..53169.96 rows=1 width=32) (actual time=10281.758..10281.758 rows=1 loops=1)
  ->  Nested Loop  (cost=0.29..14266.71 rows=7780646 width=8) (actual time=0.015..4054.449 rows=7811063 loops=1)
        ->  Seq Scan on messages a  (cost=0.00..775.82 rows=15582 width=8) (actual time=0.007..4.527 rows=15582 loops=1)
        ->  Index Scan using order_items_product_id on order_items b  (cost=0.29..0.73 rows=14 width=8) (actual time=0.005..0.174 rows=501 loops=15582)
              Index Cond: (product_id = a.product_id)
Planning time: 0.450 ms
Execution time: 10281.813 ms

There is a many-to-many relationship between messages and order_items through products, as a product can appear in many order_items and many messages and also a message (or an order_item) can have many products.

This results in the query producing a variation of a CROSS JOIN, and thus the need for using DISTINCT in the counts and consequently the poor performance.

I'd suggest you try adding composite indexes on both tables, on (product_id, order_item_id), (product_id, message_id) and see if they are used.

Another approach would be to rewrite the query so it doesn't use DISTINCT:

WITH
  cm AS
    ( SELECT COUNT(*) AS count_messages
      FROM messages AS m
      WHERE EXISTS
            ( SELECT 1
              FROM order_items AS o
              WHERE m.product_id = o.product_id
            )
    ),
  co AS
    ( SELECT COUNT(*) AS count_order_items
      FROM order_items AS o
      WHERE EXISTS
            ( SELECT 1
              FROM messages AS m
              WHERE m.product_id = o.product_id
            )
    )
SELECT cm.count_messages :: DECIMAL / co.count_order_items :: DECIMAL
         AS result
FROM cm, co ;


Permissions granted are insufficient for this operation I am using Report Builder 3.0 and have been trying to run reports using AdventureWorks 2014 in SQL Server Express 2014. My report Server is http://John-PC/ReportServer_SQLExpress.

When I click Run in Report Builder I get a message saying The permissions granted to User ‘John-PC\John’ are insufficient for performing this operation.

I have confirmed that there is a login for John-PC\John and that it is assigned to user John-PC\John. I have tried to give permissions using, among others: exec sp_addrolemember 'db_owner', 'John-PC\John' which resulted in command completed successfully but not in changing the error message.

When I connect to SQL Server my own username is John-PC\John; I suppose that is related but my own permissions seem fine.
I’ve been fighting this for about 3 weeks.

Can someone give me a different approach or correct the one I’m using so that I can run reports in Report Builder ?
Thank you very much.
John

Report builder works on different permissions than the raw SQL Server permissions which enable you to log into the SQLOS and execute code against the DB engine.  Report builder needs you to identify users in other areas.

Microsoft has some documentation on the matter here.  If you are running native mode (non sharepoint) you can check:

https://msdn.microsoft.com/en-us/library/ms365173.aspx

You will need to add users which can be found here on MS docs:

https://msdn.microsoft.com/en-us/library/ms156034.aspx

To add a user or group to a system role:

Start Report Manager (SSRS Native Mode).

Click Site Settings.

Click Security.

Click New Role Assignment.

In Group or user name, enter a Windows domain user or group account in this
format: \. If you are using forms authentication or custom
security, specify the user or group account in the format that is correct for your deployment.

Select a system role, and then click OK.

Roles are cumulative, so if you select both System Administrator and System
User, a user or group will be able to perform the tasks in both roles.
Repeat to create assignments for additional users or groups.


Informix Specific Table export I'm busy trying to migrate data between informix databases using the dbexport and dbimport utilities.

I know that in PostgreSQL I can use the following

pg_dump –a –t table database > /destination/table.sql

Which I can then import using

psql database < /destination/table.sql

Is there an equivalent for the informix dbexport and import utilities?

The dbimport tool assumes that it must create the database (and won't work with an existing database).  There isn't a utility that migrates data like you're trying to do.

You have multiple options.  One is to use one of the replication schemes (ER, HDR, RSS, etc) to create a clone of the original database.  Another is to create the relevant SQL yourself and run that via DB-Access or another tool.  You might find external tables or HPL relevant for getting good performance on the loading.

What do you do with the data that already exists in the target database?  Delete it before importing the new?  Save it in case the new doesn't import cleanly?  Do you have constraints — primary key, foreign key — that will complicate any data loading?  Do you need to defer those constraints while all the loading takes place?  Do you want to rebuild the tables from scratch?

What size of database is under discussion?  Megabytes, gigabytes, terabytes, petabytes?  How many tables?  How big are the biggest tables — row size, number of columns, number of rows?

Incidentally, if the PostgreSQL tools simply export the data from one table and import into another, then that can be done with my SQLCMD program — a program which pre-dates Microsoft's johnny-come-lately program of the same name by over a decade.  SQLCMD has two aliases, sqlunload and sqlreload.  You could use:

sqlunload -d old_database -t old_table |
sqlreload -d new_database -t new_table

You can save the data in a file, of course:

sqlunload -d old_database -t old_table -o old_table.unl
sqlreload -d new_database -t new_table -i old_table.unl

And there can be file transfers, etc, as required in between the unload and reload operations.  If you need to truncate the new table before loading, that's a separate operation:

sqlcmd -d new_database -e 'truncate table new_table'

And so on.  By default, sqlreload (you can also use sqlcmd -R … for that, and sqlcmd -U … for sqlunload) will batch the loading into sub-transactions of 1024 rows, but you can configure that, of course.


Performance issue on SQL Server 2008 R2 hosted on Windows Server 2008 R2 Our client is using SQL Server 2008 R2 SP3 on Windows Server 2008 R2 Enterprise SP1. I download their MDF.

Their server is:

Intel XEON E5-4620 2.20Ghz 128 GB RAM (SQL Server using 64GB, 20% free).
64 cores on 4 processors.
RAID SSD I/O system (but doesn't matter because physical reads is 0).

My computer is:

Intel i7 5700HQ 2.7GHz 16 GB RAM (SQL Server using 8GB).
4 cores on one processor.

I query simple Select * from StokKart. Table has 68k rows and 20 columns.

Client server results

(68648 row(s) affected)
Table 'StokKart'. Scan count 1, logical reads 3854, physical reads 0, read-ahead reads 0,
 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 640 ms,  elapsed time = 4013 ms.

My laptop results

(68600 row(s) affected)
Table 'StokKart'. Scan count 1, logical reads 3847, physical reads 0, read-ahead reads 0,
 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 187 ms,  elapsed time = 1742 ms.

I installed a fresh SQL Server on my machine using the client's backup and Windows 7. Client's Server always 4-4.5 seconds. Windows 7 machine under 2 seconds. Client always complains about a slow application. All indicators are normal. No I/O bottleneck, full RAM, or high CPU usage.

I tested the server at night when nobody was using the system. I use SQL Server Activity Monitor and Windows Resource Monitor for monitoring.

Query plans are exactly the same.

Server:

My Notebook:

I tried SQL Server 2014 on same server. Still 4000 ms and mine 1600-1700 ms.

Changing the query to Select Count(*) from Stokkart, the results are:

Server : CPU time = 16 ms,  elapsed time = 11 ms.
My Laptop : CPU time = 0 ms,  elapsed time = 3 ms.

I tested the server using its own local SSMS (local); my laptop, my SSMS, so not a network issue.

As mentioned in comments on the question:

  sp_BlitzErik: If your CPUs have higher clock speeds why wouldn't they read data from memory faster?
  Sean Gallardy: So, you don't think a CPU that has a 59.1% faster clock speed (turbo boost up to 3.5Ghz) would have anything to do with a difference of 57.5% performance in a purely cpu/memory workload?
  Simon Hellings: If the query is not going parallel then the core speed matters. Core count is irrelevant at that point. If one car is travelling at 40mph and one at 70mph which one gets across the line in a straight line race?  Same thing with clock speed...

...the difference you are seeing in executing this query almost certainly down to the clock speed differentials between the two machines.

If the query doesn't generate a parallel execution plan, it's only going to be using one core no matter how many cores you have, or what settings you have for MAXDOP.

Effectively, this test is one 2.2GHz processor vs. one 3.5GHz processor. This is why you aren't seeing high overall CPU usage figures for the server: most of the cores are just sat there twiddling their thumbs.

Where the server is going to win out over your laptop is when it's not just one query running but many queries simultaneously.

I'm ridiculously over-simplifying here and I'm going to ignore all other possible resource contention (RAM etc.), but if 4 instances of this query hit the engine at the same time then your quad core laptop can execute all four together, but if we up that number of queries to 5 then one is going to have to wait for a core to become available. If it's 10 queries then 6 are going to have to wait their turn. On the 64-core server however, it can run those ten queries simultaneously, and another 54 for good measure!

Also, which edition of SQL Server 2008 is it? Your mention of it only using 64GB makes me wonder if it's Standard Edition, in which case it's worth bearing in mind that it will only use four physical processors anyway (32 of the servers cores) see Features Supported by the Editions of SQL Server 2008 R2 for details.

You mention that the client is complaining of a slow application, which I'd imagine is really what you'd like to get a handle on. I think the results from your test query have just led you down a blind alley. If you want to better address the performance issues the client is complaining about then you need to see what's actually happening on the server during use.

I'd start by installing Adam Machanic's excellent sp_whoisactive stored procedure and running that during times of high load to see what is going on. Even better might be to log the results to a table so you can get a more general picture.

Having mentioned Brent Ozar's site, I'd highly recommend downloading their FirstResponder kit as the tools in there can be invaluable in finding where the problems lie.

It's also worth having a look at the server's wait stats to see where the bottlenecks are - if you run the query found in Paul Randal's Wait statistics, or please tell me where it hurts, it will tell you what sort thing the server is waiting around for.


Should I separate frequently updated columns? I have a users table that contains users' information and a column named credits that is frequently updated.

On index page I'm showing list of users with basic user information but I don't need credits.
On details page I'm also showing credits with user information.

Two main operations are dense in project. The first operation is the SELECT operation on index page. The second operation is the UPDATE operation on credits column. credits column is not indexed but since it's frequently updated, will it effect the indexed columns on users table? If so, I think I should separate credits column as a table and join it only when required. Doing by this, can I improve SELECT performance on index page?

Also, I'm using MySQL. What kind of table structure should I use for frequently updated columns?

It seems what you want is a so-called Index-Only Scan for the query on the "index" page. That means that you have all the data (columns) that you select on the index page in the index you are using for this query. This makes it unnecessary to go to the table when executing this query.

Example:

SELECT name, basic, information
  from users
 where filter = something

(I just suppose you are using some filter).

If you create the following index:

 ALTER table users add index (filter, name, basic, information);

MySQL can execute the above query without actually reading anything from the table (just the index—hence, Index-Only Scan). In other words, the number of columns in the table does not affect the performance of this query.

You can recognize an Index-Only Scan by the presences of "Using Index" in the Extra column of the execution plan (explain).

If you update columns that are not part of this index, the index doesn't need to be updated.

Some links to my site http://use-the-index-luke.com/

Index-Only Scan (aka. covering indexes)
Update performance
Getting MySQL execution plans & Operations


MySQL: replicating to a different table engine type According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say about drawbacks, data consistency implications (apart from the CASCADE corner case) and recommended settings for enabling such a replication configuration. So just a number of questions come to my mind regarding this setup:

is statement-based replication preferred over  the row-based variant or vice-versa?
does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?
are DDL executions on the master handled on the slave in a sane manner?
how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?
Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?
any other caveats one should know about?

I will answer each question as follows:

  1: is statement-based replication preferred over the row-based variant or vice-versa?

If you are replicating to a Slave with all MyISAM tables, statement-based is more sensible because the SQL statement would be more compact to log and ship.

If the Slave has all InnoDB, row-based is better because of the granular changes. Just be willing to live with bloated binary logs.

  2: does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?

Data consistency falls out of the picture with MyISAM. Measures for crash-safe replication always falls in favor of InnoDB. MySQL 5.6 supports storing log file and position in an InnoDB table.

  3: are DDL executions on the master handled on the slave in a sane manner?

Yes

  4: how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?

Run it like this:

SET SQL_LOG_BIN=0;
ALTER TABLE blah ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

  5: Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?

Yes, I have recommended this before : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

  6: any other caveats one should know about?

All the caveats you needs are in this post : Using MyISAM for reading and InnoDB for writing data


Unique clustered index on indexed view fails because of aggregates I want to create an indexed view similar to this (slightly simplified):

Create VIEW dbo._v1
WITH SCHEMABINDING
AS
    SELECT
         COUNT_BIG(*) as CB
        ,[ID]
        ,TITLE]
        ,dbo.getXmlTranslation1(acat.[TITLE], 'de-DE') as T
    FROM dbo.[ArticleCategory] acat
    Group by
         [ID]
        ,[TITLE]

Creation of a unique clustered index on this fails:

CREATE unique CLUSTERED INDEX __CLIX
ON dbo._v1([ID]);

  Cannot create the clustered index '__CLIX' on view 'db._v1T' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

I searched for a solution but can't find one. If I remove the GROUP BY (I don't really need here), I am told that the Unique Clustered index can't be created because of multiple [ID] results.
I added the scalar function call in order to workaround the XML operation within the indexed view that is not allowed as well.
I checked if the scalar funtion is deterministic, what is the case.

Your issue is definitely your function, and you will have to do something to make that work differently.  Here is a very basic example to illustrate the problem.

First create your test data.

CREATE TABLE dbo.testResults
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,col1 VARCHAR(200)
);
GO
INSERT INTO dbo.testResults
        ( col1 )
VALUES  ( 'test1' ), ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test4' );
GO

Now we will create a view (that works correctly) and add the clustered index to that view.

CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1;
GO
--Works great
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1);
GO

Next we will add a very simple and basic function that does nothing more than very basic string manipulation (left 3 characters of a given string).

CREATE FUNCTION dbo.left3 (@str varchar(200))
RETURNS varchar(3)
WITH schemabinding
AS
begin
RETURN LEFT(@str, 3)
END;

Now we will get rid of our previous view add this column to the view.

DROP VIEW dbo.vwResults;
GO
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,dbo.left3(col1) AS left3col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1, dbo.left3(col1);
GO

So far, so good.  But then by adding even this simplistic function to the view and nothing else, we are no longer able to index the view.

/*FAILURE!!!!!!*/
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1);
GO

So for this example there is a pretty simple solution where I can fairly easily create a computed column and then everything works.   Here is how I would do that.

ALTER TABLE dbo.testResults
ADD left3col1 AS LEFT(col1, 3);

After doing this, I can create the same view and this time I can add the unique clustered index as follows.

DROP VIEW dbo.vwResults;
GO
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,left3col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1, left3col1;
GO
/*IT WORKS!!!!!!*/
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1, left3col1);
GO

This might not be an option for your scenario, but basically your function is the problem and you need to work through some alternatives (depending on what it does) if you want to make an indexed view work for your scenario.