How is it technically possible for PostgreSQL to keep small tables "in RAM"? I used to ask about performance and people would tell me that PostgreSQL keeps small-enough tables entirely in RAM automatically, as to not stress the HDD and thus enable super-fast reading/writing of "small tables", that is, with very few rows which more than easily fit in the available RAM (or what's assigned to PG).
This made sense to me, and made me very happy as I can efficiently communicate between my parallel-running scripts without stressing my poor HDD/SSD and clogging up all the resources.
But then it struck me: How is this possible? How can PG possibly keep a table all in RAM, regardless of how small it is, without losing data integrity?
If it doesn't write to the permanent storage all the time, how can it possibly recover if the power is suddenly cut, or there's a software crash, or some other disaster happens while it's running? It doesn't add up to me the more I think about it.
The only answer I can think of is: No, it doesn't actually do this. I don't see any possible way that PG could avoid losing data if it never (or rarely) writes the data to the permanent storage.
If this is possible, but only after enabling some kind of flag for the table, or setting, I'd like to know about that flag/setting. I'd like to enable this for tables which only contain "internal communication" data of the kind which has no value besides as a "common data storage" for actively running scripts.

This is possible due to Write-Ahead Logging (WAL):

Briefly, WAL's central concept is that changes to data files (where
tables and indexes reside) must be written only after those changes
have been logged, that is, after log records describing the changes
have been flushed to permanent storage. If we follow this procedure,
we do not need to flush data pages to disk on every transaction
commit, because we know that in the event of a crash we will be able
to recover the database using the log: any changes that have not been
applied to the data pages can be redone from the log records.

It's a central component of Postgres' architecture and turned on by default. You can configure a couple of parameters like instructed in the manual chapter WAL Configuration.
Also consider basics on Reliability in the manual.


Any impact of upgrade db2 from V10.5 to V11.5? Good day,

Due to the DB2 v10.5 will end of support on next year April. I would like to change my application database version to v11.5.

Before apply this to production, I do it in my local VM first. The following are the steps I have done:

backup original database.
Install DB2 v11.5, because its same environment, do I cant have same user call db2inst1, so I create another instance call db2inst2.
log in as db2inst2, run restore command to restore the image file I done in step 1 :

db2 restore database mydb from /home/db2inst2 on /home/db2inst2

After this, I configure my Jboss server to point to this new database, application can log in and working fine.

I am thinking that, is there anything I need to take care? or anything I need to avoid ? I am worry that there are something issue unforeseen will happen.

If you will need a Solaris Client, you won't get a client for the last version. IBM drop the support for Db2 in that platform.

Remember to rebind packages.

Also, update Db2 clients and JDBC clients to have everything in the same version.


EMStagePatches_$ORACLE_SID is using up space We have this directory:

$ORACLE_HOME/EMStagePatches_$ORACLE_SID

We are running out of space and this directory is using up a lot of space. Is it safe to delete it? I think it is something to do with the Enterprise Manager patches. How can I stop Enterprise Manager from downloading stuff into that directory?

We are using Oracle Database 11g Enterprise Edition Release 11.1.0.6 running on Red Hat Enterprise Linux Server release 5.2.

Just to make sure this is not used by your OEM
- rename the folder to $ORACLE_HOME/EMStagePatches2_$ORACLE_SID and restart your OEM if nothing bad happens i think is fine ti assume that is just a staging folder for the uploaded patches


Index partitioned table to prevent Postgres from doing a sequential scan I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries.

I have defined a partitioned database based on the first letter of the "identifier" - this is split into 37 sub-tables, [0-9, a-z, default (catchall for everything else)].

The database is very simple and straight forward, and is defined below.

create table entries (
  id                bigserial,
  identifier        text null,
  password          text null,
  additional_fields jsonb
)
  partition by list (lower(left(identifier, 1)));
ALTER DATABASE credentials SET constraint_exclusion=on;

CREATE TABLE entries_0 PARTITION OF entries for values in ('0');
CREATE TABLE entries_1 PARTITION OF entries for values in ('1');
CREATE TABLE entries_2 PARTITION OF entries for values in ('2');
CREATE TABLE entries_3 PARTITION OF entries for values in ('3');
...
CREATE TABLE entries_z PARTITION OF entries for values in ('z');

ALTER TABLE entries_0 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '0');
ALTER TABLE entries_1 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '1');
ALTER TABLE entries_2 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '2');
ALTER TABLE entries_3 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '3');
...
ALTER TABLE entries_z ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = 'z');

CREATE INDEX ident_idx on entries(identifier);

However, when I run an EXPLAIN it says that is it is still doing a sequential scan.

EXPLAIN SELECT * FROM entries where identifier = 'some_identifier_from_subtable_s' LIMIT 1;

Output:

Limit  (cost=0.00..140.92 rows=1 width=104)
  ->  Append  (cost=0.00..43418531.72 rows=308113 width=104)
        ->  Seq Scan on entries_0  (cost=0.00..23239.38 rows=2 width=68)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_1  (cost=0.00..150187.81 rows=6 width=68)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_2  (cost=0.00..94694.38 rows=4 width=67)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_3  (cost=0.00..81656.71 rows=3 width=67)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)

        ... etc.

        ->  Seq Scan on entries_z  (cost=0.00..579207.95 rows=13 width=69)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_default  (cost=0.00..15582.36 rows=4 width=69)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)

What am I doing incorrectly? The intelligent partitioning should be able to redirect the query to just the entries_s partition, should it not? And then the CREATE INDEX ident_idx on entries(identifier); should make the query go through the index?

After adding the explicit query the new plan looks like this:

FROM   entries
WHERE  identifier = 'some_identifier_from_subtable_s'
AND    lower(left(identifier, 1)) = 's'  -- 1st letter of above identifier
LIMIT  1;

Output:

Limit  (cost=1000.00..2583053.76 rows=1 width=71)
  ->  Gather  (cost=1000.00..2583053.76 rows=1 width=71)
        Workers Planned: 2
        ->  Parallel Append  (cost=0.00..2582053.66 rows=1 width=71)
              ->  Parallel Seq Scan on entries_s  (cost=0.00..2582053.66 rows=1 width=71)
                    Filter: ((identifier = 'some_identifier_from_subtable_s'::text) AND (lower("left"(identifier, 1)) = 's'::text))

It's still doing a sequential scan on entries_s. Does declaring the index with CREATE INDEX ident_idx on entries(identifier); not propagate it down to all partitions?

Try adding the partition key explicitly (redundantly!). Like:

SELECT *
FROM   entries
WHERE  identifier = 'some_identifier_from_subtable_s'
AND    lower(left(identifier, 1)) = 's'  -- 1st letter of above identifier
LIMIT  1;

This should allow Postgres to understand it can prune all other partitions from the query.

Can be derived from $1 of course:

AND    lower(left(identifier, 1)) = lower(left($1, 1))

You'll still see a sequential scan on the one partition, unless you create an index like you had in mind:

CREATE INDEX ident_idx on entries(identifier);

That works in Postgres 11 or later because, quoting the manual:

  When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes.

In Postgres 10 or older you have to create indexes per partition.

You may have to run ANALYZE on the table after creating the index if you follow up with the  query immediately, before autovacuum had time to kick in.


Mongo DB Upgrade 3.0 to 3.2 Verification of Upgrade We have a few environments with MongoDB 3.0, these are standalone servers.
These servers are already using WiredTiger Storage engine.
I am trying to upgrade them to 4.2.

As per the upgrade documentation you can't go from 3.0 to 4.2 but have to make incremental upgrades from 3.0 to 3.2 and so on to 4.2.

As this is something that is going to be repeated on multiple env's I am trying to automate it where in a script does the upgrade but at certain points in the upgrade gives out diagnostic info that someone can look at and confirm that a step in the upgrade process has been successful.

Doing this on EC2 instances with Amazon Linux 2.

To test this out I did a manual upgrade from 3.0 to 3.2. The processes came up fine, I was able to connect to the db and query a collection and everything "seems" fine.
Across various DB's the total size of the data is approx 2.3TB, and I was surprised that after the upgrade the DB came up in a few seconds.

I was looking at diagnostic commands to figure out if I can verify in the script that the upgrade happened without a hitch. (Other than the mongod process starting correctly).
The only thing I could find was this.
https://docs.mongodb.com/v3.2/reference/command/serverStatus/#asserts
And am not sure if this is good enough.

So TL;DR; Can I programatically verify that the upgrade was successful by looking at any metadata from the DB itself.

Note: a tangential question, YAML is current preferred config file format, as it is supported even with 4.2 any issues continuing with it?

Can I programatically verify that the upgrade was successful by looking at any metadata from the DB itself?

Yes, you can use shell scripts to redirect input/output of a mongo shell.

Beware that scripted mongo shell behaves differently than the interactive mongo shell: https://docs.mongodb.com/manual/tutorial/write-scripts-for-the-mongo-shell/#differences-between-interactive-and-scripted-mongo

a tangential question, YAML is current preferred config file format, as it is supported even with 4.2 any issues continuing with it?

From https://docs.mongodb.com/manual/reference/configuration-options/ :

  Changed in version 2.6: MongoDB 2.6 introduced a YAML-based
  configuration file format. The 2.4 configuration file format remains
  for backward compatibility.

  MongoDB configuration files use the YAML format.

With YAML being the official format, there is no issue. The old format was deprecated, it should still work, but I wouldn't bet that this will never be removed in the future.

About the upgrade procedure (as described here), I imagine you should evaluate the result of rs.status(), but as you are running standalone, I see why you use asserts from db.serverStatus(). In your case, I would be more worried when restarting the mongod process, an exit code was returned.


Why does the service broker require a master key after restoring to a different server? I've been moving some databases which use the service broker to new servers recently via backup and restore.  I've noticed that once I do this, these databases start throwing the database master key required messages in the log.  Encryption isn't being used in with the broker, and no keys are set in the original server and database, so why does it require one once I restore it? Is it maybe using the service master key if a database master key isn't set? My searching hasn't turned up specifics of what happens if you don't set one originally, it just seems to be assumed you have one set.  It's been mentioned that there are issues if it is not set, but we haven't had any.

Also, does it actually require a key? I'm starting to wonder if this is just an incorrect message, and if I just do the UID update with the alter statement, if that will resolve it, or do I have to set the key when moving to a new server?

Thank you!

When you restore a database, TRUSTWORTHY is automatically set to OFF.  For Service Broker, if you don't use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON.

Try

ALTER DATABASE MyDB SET TRUSTWORTHY ON

...for all restored databases involved with Broker.


How to find a SQL Job details in Production environment? There is a stored procedure being executed daily which inserts a record in a specific table. Our Production database servers has lot of SQL jobs scheduled to run daily at different intervals.

How to find out which job is running this particular stored procedure? Is there a way to find using SQL scripts?

You can use this query directly to get all the names of jobs that include specific text (for example "MyText"

SELECT j.job_id, j.[name], s.command
FROM sysjobsteps s
right join dbo.sysjobs j on s.job_id = j.job_id
WHERE command LIKE '%MyText%'

** Execute query on multiple SQL Servers can be done using SSMS by creating a local server group or a Central Management Server and one or more server groups

Open SQL Server Management Studio -> View  -> click Registered Servers
In the "registered Servers" windows you can create groups and add servers.
To execute query on a group simply right-click a server group, point to Connect, and then click New Query

** Execute query on multiple Azure SQL Databases can be done using Azure Elastic Job (you can search a recording of my lecture about Elastic Pool and Elastic Job).


Why doesn't a delete increase unused space in a table? If I populate the following table:

IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL
    DROP TABLE dbo.CleanTableTest;
GO
CREATE TABLE dbo.CleanTableTest
    (FileName nvarchar(4000),
    DocumentSummary nvarchar(max),
    Document varbinary(max)
    );
GO
-- Populate the table with data from the Production.Document table.
INSERT INTO dbo.CleanTableTest
    SELECT REPLICATE(FileName, 1000),
           DocumentSummary,
           Document
    FROM Production.Document;
GO

exec sp_spaceused CleanTableTest
go

Results:

name           rows  reserved  data    index_size  unused
-------------- ----  --------- ------- ----------- ------
CleanTableTest 13    456 KB    440 KB  8 KB        8 KB

But then if I delete all the rows:

delete dbo.CleanTableTest
go

exec sp_spaceused CleanTableTest
go

Results:

name           rows reserved data   index_size unused
-------------- ---- -------- ------ ---------- -------
CleanTableTest 0    88 KB    80 KB  8 KB       0 KB

Why after the delete process of all rows in table the unused space have not changed? It stays at 0 KB.

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.


sql server right join with condition returns rows that do not match condition I'm having problem with my right join. Below is my query;

select a.maintitle as TITLE,
       a.id AS ID,
       a.a_auto_produ AS HOUSENO,
       c.CARRIERGUID,
       substring(d.FILENAME,1,15),
       d.CARRIERGUID
  from a
         inner join c on a.id = substring(c.FILENAME,1,15)
                         and  c.CARRIERGUID='xxx'
         right join d on a.id = substring(d.FILENAME,1,15)
                         and d.CARRIERGUID='production'  -- <<< here
;

with the condition indicated. I expect to have carrierguid = Production returned. However the returned rows also contain carrierguid other than production as you may see below;

really appreciate any help on this

To elaborate a bit on sp_BlitzErik's comments above:

Your RIGHT JOIN says the following:

  I want all rows from d, with the data from any rows from the a-c join where a.id = substring(d.FILENAME,1,15) and d.CARRIERGUID='production'.

If d.CARRIERGUID = 'production', then you still take the row from d - you just exclude any rows from the a-c INNER JOIN that might otherwise match.

If what you really meant was:

  I want all rows from d where d.CARRIERGUID='production', with the data from any rows from the a-c join where a.id = substring(d.FILENAME,1,15).

then you would need to take d.CARRIERGUID='production' out of the RIGHT JOIN ON clause, and make it the WHERE clause for the query as a whole.


Fragmentation in PostgreSQL 9.1 How can I check for fragmentation in PostgreSQL 9.1?

Fragmentation is often called bloat in PostgreSQL. It relates to its implementation of MVCC where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are then made visible or invisible depending on the transaction looking at the data. You can start looking at the wiki Show database bloat for more information on fragmentation issues. Bearing in mind that this query will not give you exact numbers, it's a useful tool to have:

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
      (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+COUNT(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::NUMERIC) AS bs,
          CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;

Once you know where bloat is happening, you can take measures to clean the table. The operation is called VACUUMing and can be launched on demand. It is also usually done in the background with the autovacuum worker, so please do not disable it.

As noted in https://www.postgresql.org/support/versioning/, PostgreSQL 9.1 has been unsupported for one year, consider upgrading.


How to restore SQL Server bak files in Access? I backuped some SQL Server 2008 R2 databases using .bak files within SSMS, I sent them to a client and they're willing to open them within MS Access.

Can you please tell me if this is possible and if limitations exists to perfmrom this procedures?

My bak files are 40GB aprox

Thanks in advance

It is not possible to restore a SQL Server backup into MS Access.


How to handle multiple Oracle homes? On my desktop I have an Oracle client and and Oracle ODAC installed in different homes. I need ODAC because there ADO components off the Client installation don't work correct with CLOB data.

I think I can't go without the client installation, because I need some odbc components.

Now I have to maintain to copies of the tnsnames.ora file. The nest thing I know to do is carefully keep the both tnsnames.ora in sync.

Are there better solutions for a scenario like this?

Assuming that you don't want multiple tnsnames.ora files, the simplest option would be to set the TNS_ADMIN environment variable to point at the one tnsnames.ora file you wanted to maintain on the machine.  All the Oracle Homes on the machine would then use the tnsnames.ora file in the directory specified by the TNS_ADMIN setting before looking for an entry in the local Oracle Home's tnsnames.ora file.


How can I SELECT an 'instance' in PostgreSQL? I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code:

CREATE TRIGGER cdc_table ON db.table AS
DECLARE @OPER, @INST, @TAB, @PK, @ID
Select @TAB=table of altered tuple
Select @PK= primary key of @TAB
Select @INST= instance in which @TAB belongs
Begin
IF(@OPER = DELETE)
Select @ID= ID from tuple before deletion;
ELSE
Select @ID= ID from tuple after insertion or update;
ENDIF
Insert into log table @ID, @PK, @TAB, @INST, @OPER;
END;

In the line 5 they are asking me to SELECT the instance of the table I am altering. How can I do that?

The log table is defined as follows:

log id – an integer type field, having a sequential
number, that stores the execution order of the
operations;
instance – a text type field, stores the name of the
instance whose tuple was altered;
table – a text type field, stores the name of the table
whose tuple was altered;
operation – stores the type of operation that was
executed: INSERT, UPDATE or DELETE;
pk column – a text type field, stores the name of the
column that was used as a unique identifier in the
table whose tuple was altered;
tuple id – value of the unique identifier of the altered
tuple.

Thanks!

This is a really bad idea to begin with, you're recreating logical replication. Just refer to the PostgreSQL guide to logical replication.

That said, what you want is the PostgreSQL plpgsql Trigger "special variables.

TG_TABLE_NAME Data type name; the name of the table that caused the trigger invocation.
TG_TABLE_SCHEMA Data type name; the name of the schema of the table that caused the trigger invocation.


How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE? I have the following SELECT statement:

SELECT * FROM "Users" u
INNER JOIN "Calendars" c ON (u."UserId" = c."UserId")
INNER JOIN "Actions" a ON (c."CalendarId" = a."Calendar_CalendarId")
WHERE u."AuthenticationStatus" = 0 AND u."CreationDate" < now()::date - 7;

This properly selects all rows I want from the three tables "Users", "Calendars" and "Actions". It does this by matching the "UserId" of the "Users" table with the foreign key "UserId" of the "Calendars" table, and the "CalendarId" of the "Calendars" table with the foreign key "Calendar_CalendarId" of the "Actions" table.

I want to convert this to a DELETE statement. What I tried is:

DElETE FROM "Users" u
    USING "Actions" a, "Calendars" c
WHERE u."UserId" = c."UserId" AND
      c."CalendarId" = a."Calendar_CalendarId" AND
      u."AuthenticationStatus" = 0 AND
      u."CreationDate" < now()::date - 7;

But this says:

  update or delete on table "Calendars" violates foreign key constraint "FK_public.Actions_public.Calendars_Calendar_CalendarId" on table "Actions"
  DETAIL:  Key (CalendarId)=(2) is still referenced from table "Actions".

Does anyone know how I convert this query into a DELETE statement?

You're interpreting the semantics of the delete statement incorrectly. When a using clause is used, it doesn't mean that records will also be deleted from those tables. Instead, those tables are purely used to join to in order to determine which rows need to be deleted from Users.

You basically have three choices:

deleting child rows in a before delete on Users trigger.
on delete cascade constraints.
execute multiple delete statements on the various tables involved, in the right order.

My preference, in certain cases, is actually for the on delete cascade constraints, but I don't use them everywhere: just for the situation where it makes sense to be able to remove all of the children of a given parent in one go. I might use it for "invoices" and "invoice_lines".

When you take this approach you need to be sure that only users who really need to be able to delete from the parent table have that privilege -- no users or applications logging in as table owners!


Which files in the “MySQL-5.0.27-win32” folder do i need to backup? Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7).

Here is the current structure of of mysql-5.0.27-win32:

In "mysql-5.0.27-win32" folder:
bin
data
Docs
Embedded
examples
include
lib
mysql-test
scripts
share
sql-bench
my-huge.ini
my-innodb-heavy-4G.ini
my-large.ini
my-medium.ini
my-small.ini

In "data" folder:
mysql
mydatabase
...some-other-database that I don't touch...
my-PC.err
my-PC.pid
ib_logfile0 ---> this is a file, not a folder
ib_logfile1 ---> this is a file, not a folder
ibdata1 ---> this is a file, not a folder

the "mydatabase" folder has file such as .frm, .MYD, .MYI for each of its table,
"mydatabase" also has "db.opt"

Ok, here is the scenarios. I finished developing my WebApp using mysql-5.0.27-win32in my Local Desktop PC. Now it is a time to deploy my app.

To deploy, I copy the whole mysql-5.0.27-win32 folder (which contains the current DB structure + some basic data) into Virtual Server.

So, there will be 2 same folders the original mysql-5.0.27-win32 in the Local PC & the copy  mysql-5.0.27-win32 in the Virtual Server. At the beginning, these 2 folders are exactly the same. They have the same DB structures (ie same number of tables & columns in the tables) & same data stored in the tables.

From now on, my website will store user data directly to the copy mysql-5.0.27-win32 folder in Virtual Server (VPS). But if the VPS got shutdown then I will lose all data.

Thus I want to back up daily the "mydatabase" from the copy mysql-5.0.27-win32.

So which file in the "MySQL-5.0.27-win32" folder do i need to backup so that when I put them into the original mysql-5.0.27-win32, then I can have exactly the same info as I have in the copy mysql-5.0.27-win32?

Supposed that we know while data file to copy then:
is there any problem for this solution “Backup mysql database by copying the data file even when MYSQL server is running”?

(pls see the subsequent question: "Backup mysql database by copying the data file even when MYSQL server is running" solution! Is there any problem for this backup solution?)

You are using a very old, end-of-life, and unsupported version of MySQL.

There is not exactly a correct answer to your question, because it is impossible in many cases to safely back up or clone a MySQL server that is still running.

The only absolutely safe way to copy data using this method is by stopping the mysqld process, gracefully, and copying all of the files, for all of the databases.

If you are using MyISAM, which, again, you almost certainly should not, it is possible to copy a single database by copying all of the files in that database's named directory, but the tables have to be locked and flushed, or the backup will either not contain the most recent changes, or will be corrupt.

If you are using InnoDB, it is almost impossible to copy only one database from one server by copying files, unless you use the "transportable tablespaces" feature introduced in MySQL 5.6.

The correct answer to the question is "don't do it this way."

The correct solution is to use mysqldump to make the backup, which does not copy files at all.  Instead, it creates a file containing the SQL statements necessary to recreate your tables and reinsert the data that was in those tables when the backup was made.

The output of mysqldump is also human-readable, so you can actually examine what's in the backup.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html


Contention on tempdb due to high concurrency temp table creation from multiple databases? I have an environment with ~100 databases, all with the same schema.  There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds or so per user, and there are 1000+ users). It's doing a lot more than just this: We are loading potentially thousands of rows into this temp table and just dumping the whole thing out, basically it aggregates a bunch of data.

Since all the databases create the same temp table, are they all competing with each other?  Or does each database get its own version of the temp table in tempdb?

It seems like they are having contention because I see a large amount of PAGE_LATCH waits, all on the same page in tempdb (2:3:1041580), which is not a GAM, SGAM, or PFS page, and 90% of the waits seem to be from this same stored procedure across all databases.  These waits are 90% of the overall waits on the server and are causing blocking.

I did a DBCC PAGE, and it appears to be sysobjvalues (from the object_ID 60 in the header).  I ran:

SELECT OBJECT_Name(object_ID), *
FROM sys.dm_db_database_page_allocations(2,60,NULL,NULL,'DETAILED')

I get 153 rows, not sure exactly what I'm looking at here.  Looks like it's IN_ROW_DATA and LOB_DATA in allocation_unit_type_desc.

I have 8 tempdb files on a separate volume from the other files.  I have 16 cores but my understanding is that since it's not an allocation page it might not help to increase the number of files.  Using SQL Server 2017 Enterprise.

I feel like the best bet is to just tell the developers to remove the temp table here but that will require reworking the logic and will take some time.  Is there anything else I can do to avoid these page latches while I wait for the dev fix?

We are loading potentially thousands of rows into this temp table and just dumping the whole thing out, basically it aggregates a bunch of data.

Temporary tables can be a big performance and reliability win, when used correctly.

For example, it can be very useful to store a relatively small (and narrow) intermediate result set in a temporary table so SQL Server can compute automatic statistics on the data, or so the user can add specific indexing.

Temporary tables are most useful when they allow the optimizer to produce a better sequence of plans overall than a single monolithic query.

It is also possible to use temporary tables poorly. Simply storing rows in a temporary table before "dumping the whole thing out" sounds counter-productive. Always bear in mind that temporary tables are not free, and can contribute to contention.

Aside from using temporary tables in a sensible way, try to arrange things so you take advantage of temporary table caching. See my article SQL Server Temporary Object Caching for details.

Ensure you are running the latest cumulative update, since there have been several performance optimizations and bug fixes employed, for example:

PFS page round robin algorithm improvement in SQL Server 2016 and 2017 (2017 RTM CU7)
FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017 (2017 RTM CU5)


Write a slow query to test slow query logging? Is there a simple query that would take > 2 sec so that I can test the slow query logger?

I am looking for something like a generic recursive or iterative statement.

A simple query would be:

SELECT SLEEP(2);

You want to iterate it?

DELIMITER $$
DROP FUNCTION IF EXISTS `iterateSleep` $$
CREATE FUNCTION `iterateSleep` (iterations INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE remainder INT;

    SET remainder = iterations;

    read_loop: LOOP
        IF remainder=0 THEN
            LEAVE read_loop;
        END IF;

        SELECT SLEEP(2) INTO @test;
        SET remainder = remainder - 1;
    END LOOP;

    RETURN iterations;
END $$
DELIMITER ;

-- TO TEST IT OUT
mysql> SELECT iterateSleep(2);
+-----------------+
| iterateSleep(2) |
+-----------------+
|               2 |
+-----------------+
1 row in set (4.01 sec)

Alternatively if you just want to test your slow_query_log, change 'long_query_time' to 0 (to log all queries):

SET long_query_time=0;


JSON MySQL 5.7 query I have innodb table with a column in JSON format

My table products:

id_products      int
name_products    varchar --> fx-6350<br>
feature_products json    --> {"cache":{"l1":"128k","l2":"6Mb","l3":"8Mb"},"frequency":"3.9GHz","socket":am3+"}

How can I make a query with result "frequency" and "3.9GHz" and query for everything?

If I've understood you correctly, you want something like this

SELECT * FROM products WHERE JSON_EXTRACT(feature_products, "$.frequency") = '3.99GHz';

I created a table "products" - note that the last record corresponds to the one we want.

SELECT * FROM products;

id_products name_products   feature_products
1   blah    {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.97GHz"}
1   blah2   {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "4.7GHz"}
1   result  {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.99GHz}

Running the query above

SELECT * FROM products WHERE JSON_EXTRACT(feature_products, "$.frequency") = '3.99GHz';

gives:

id_products name_products   feature_products
1       result      {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.99GHz"}"

Which, as we can see, is what was required -  result "frequency" and "3.9GHz" and query for everything

I found this reference helpful. And, if all else fails, one can always consult the documentation here :-). Also, the MySQL server team blog was useful.


Mysql 5.0 on Windows 2008 I have a problem with MySQL on Windows Server 2008 x64.

This is the first time I've installed MySQL 5.5 with performance problems.  I decided to go back to the older version that worked well with my server on Windows Server 2003 x86.

Now the MySQL service is slower than before when used on Windows Server 2003.

What would be the reason for the lower performance ?

I have a server with 2 processors (4 cores), 12 GB RAM and hard disc in RAID 5.

my my.ini

port=3306
default-character-set=latin1
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="D:/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
max_connections=160
query_cache_size=200M
table_cache=939
tmp_table_size=103M
thread_cache_size=16
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=339M
key_buffer_size=316M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=4096M
innodb_log_file_size=512M
innodb_thread_concurrency=10
log-slow-queries="D:/MySQLLog/SlowQuery/SlowQuery.log"
log-error="D:/MySQLLog/Error/error.log"

Thanks in advance!

There are three(3) aspects you need to consider
ASPECT #1
You may find this astonishing but did you know that some older versions of MySQL can outperform newer versions ? You must tune MySQL 5.5 properly. In fact, InnoDB has new options introduced into the InnoDB Plugin.
I have earlier articles in the StackExchange on this

Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
Feb 22, 2012 : MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast)
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

ASPECT #2
Looking at the my.ini, something caught my attention: you have innodb_thread_concurrency=10. That's a definite no-no, especially with MySQL 5.5. I say this because MySQL 5.x thrives on setting the innodb_thread_concurrency to 0. At Percona Live NYC 2011, Ronald Bradford explicit told me as I sat in the audience not to set this to any other value than 0. By setting it to zero, it lets InnoDB figure out the correct number of threads it needs create.
ASPECT #3
You need to activate multiple cores for InnoDB. MySQL 5.0/5.1 are not designed to use multiple cores. The InnoDB Plugin (from MySQL 5.1.38 has new options for doing so. Those options are fully present in MySQL 5.5. Such include

innodb_read_io_threads
innodb_write_io_threads
innodb_io_capacity


294 : Failed with error 'aborted', from rs4 to rs0 Regarding failed with error aborted will we lose the chunks

In logs I found this:

2017-09-14T01:35:21.533+0900 W SHARDING [conn38309]
Chunk move failed :: caused by :: ChunkRangeCleanupPending:
can't accept new chunks because there are still 4 deletes from previous migration

2017-09-14T01:35:11.439+0900 I COMMAND  [conn38309] command admin.$cmd
command: moveChunk { moveChunk: "transam_db.panelist",
shardVersion: [ Timestamp 762000|1, ObjectId('590b4564c5ac1ee3b6e9050d') ],
epoch: ObjectId('590b4564c5ac1ee3b6e9050d'),
configdb: "configReplSet/mgdb07:27019,mgdb08:27019,mgdb09:27019",
fromShard: "rs1", toShard: "rs4",
min: { panelist_id: 407157 },
max: { panelist_id: 416836 },
chunkVersion: [ Timestamp 762000|1, ObjectId('590b4564c5ac1ee3b6e9050d') ],
maxChunkSizeBytes: 67108864, waitForDelete: false, takeDistLock: false }
exception: can't accept new chunks because there are still 4 deletes from previous migration
code:200 numYields:75 reslen:278 locks:{ Global: { acquireCount: { r: 161, w: 3 } },
Database: { acquireCount: { r: 79, w: 3 } },
Collection: { acquireCount: { r: 79, W: 3 } } }
protocol:op_command 2622ms

Due to this, it's unable to move the chunks. Please give a solution.

Answer is NO.

At 3.4 mongodb we got multi threaded chunk balancing and it is "little bit" stupid that way that it can try to move chunk (from or to) shard what is currently already participating in some other chunk move operation. Of course, that is not possible and then that new move operation is aborted.

If you start seeing huge aborted values, like thousands, check log files of those shards whose name is most listed on error list. Like rs4 there. From log files you see what was "reason" why move was aborted. There can be be cursor with no timeout and previous move operations remove is hanging there.

But, aborted chunk move does not lose data, ever. Data is never removed until it is copied to the new location and then mark to be there.

To find a possible reason for those aborts, all (primary) mongod.log files must be checked. Without them, it's impossible. There is always from node and to node, both log files have information about "that" movechunk operation what was aborted.


JOINing 3 tables with ORDER, LIMIT and WHERE I am trying to join these 3 tables by key organization_id but I had no success.
What I'm trying to achieve: I know ipo and I want to get some values from name_entries and address_entries.
First query gives me organization_id, which is used in second and third query.
Is this even possible in 1 query?

My current solution is calling these queries separately and then joining them in my code.

SELECT * FROM "rpo"."organization_identifier_entries" WHERE ipo = '********' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_address_entries" WHERE organization_id='########' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_name_entries" WHERE organization_id='########'  ORDER BY effective_to DESC NULLS FIRST LIMIT 1;

Using LEFT JOIN LATERAL

I think what you want is something like,

SELECT *
FROM rpo.organization_identifier_entries
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_address_entries
  ORDER BY updated_at DESC
  FETCH FIRST ROW ONLY
) AS oae
  USING (organization_id)
LEFT JOIN LATERAL (
  SELECT *
  FROM rpo.organization_name_entries
  ORDER BY effective_to DESC NULLS FIRST
  FETCH FIRST ROW ONLY
) AS one
  USING (organization_id)
WHERE ipo = '********'
FETCH FIRST ROW ONLY;

Oh, and btw, never double-quote things in PostgreSQL unless you want to enable a very stupid capability to make identifiers caps-sensitive. This is a bad idea, unless that third party is also doing it and they have capital letters in their identifiers.


Insert String Value To Binary(64) Column I am attempting to use a stored procedure to insert data into a table.  The DDL of my table is like this:

CREATE TABLE [dbo].[data1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fname] [varchar](100) NOT NULL,
    [lname] [varchar](100) NOT NULL,
    [phone] [varchar](10) NULL,
    [company] [varchar](100) NOT NULL,
    [email] [varchar](100) NOT NULL,
    [pass] [binary](64) NOT NULL,
    [registrationdate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[data1] ON
GO
INSERT [dbo].[data1] ([ID], [fname], [lname], [phone], [company], [email], [pass], [registrationdate]) VALUES (1, N'Bob', N'Tomato', N'5555555555', N'Test Company', N'test1234@test.com', 0x477265656E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000, NULL)
GO
SET IDENTITY_INSERT [dbo].[data1] OFF
GO

And this is the stored procedure that I am attempting to execute:

Declare @FName varchar(100) = 'aa'
Declare @Lname varchar(100) = 'bb'
Declare @Phone varchar(100) = 'cc'
Declare @Company varchar(100) = 'dd'
Declare @Email varchar(100) = 'ee'
Declare @Pass binary(64) = 'redsocks'

INSERT INTO dbo.appinfo (fname, lname, phone, company, email, pass)
VALUES (@FName, @Lname, @Phone, @Company, @Email, CONVERT(VARBINARY(64), @Pass, 0))

but I get this error:

  Msg 257, Level 16, State 3, Line 20
  Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

Is this statement not converting the value appropriately?

CONVERT(VARBINARY(64), @Pass, 0))

You need to apply the convert in the declare line, not during the insert. The error happens on the declare line, so the insert is never reached.


Attaching Database Incorrect name in sys.master_files I have been moving some databases from SQL 2008 to SQL 2012 using detach and attach method.  I correctly map up all the files and the physical and logical names appear correctly in the properties of the database.

However if I run sys.master_files I can see that all my files have been given the same file name in the Name column of the DMV and this is making our alerting system go crazy.

Has anyone seen this before or know how I can fix it?

Thanks

Well, for one, you really shouldn't be moving database files around using detach/attach:

Bad Habits : Using MDF/LDF Files as "Backups"

The problem probably happened because you reused MDF/LDF files for new databases with different names - the files keep the properties of the original database even if the new database has a different name and the files have different filenames/locations.

But this is an easy "problem" to fix:

ALTER DATABASE dbname MODIFY FILE (NAME = N'old_name', NEWNAME = N'new_name');

(Another way to fix it is to stop your alerting system from even looking at this, never mind complaining about it - other than OCD, there is no reason why two different databases can't have the same logical file names - they're unique per database, not per instance.)


Change tracking SQL Server 2008 My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table.

Due to some specific reasons I have to use change tracking as I was told not to use triggers and Change Data Capture.

Right now I am able to fetch current data and which column is modified. But in addition I want to track time of modification and old data.

But if anyone can help me to get any one of these two will be very helpful for me.

If you can't add any extra storage for the data (as noted in your comment) then you simply can't do this.  SQL Server doesn't track the old and modified rows for you.

There's a duct-tape way of doing it temporarily: you can read the transaction log.  When combined with the full chain of backups, you can build the old & new data together.  Third party data recovery products like Quest LiteSpeed do this - for any transaction, they'll show you the old and new data by reading through the full backup and transaction log backups.


How can I improve query performances using Using temporary; Using filesort I am very new to sql and query optimization, I am sorry if its inappropriate to ask such as may be asked previously. But I need help and some thoughts where I can look and start ...

EXPLAIN
SELECT DISTINCT node.nid AS nid,
                node.title AS node_title,
                votingapi_cache_node_points_VotingNumber_sum.value AS votingapi_cache_node_points_votingnumber_sum_value,
                node.created AS node_created,
                votingapi_cache_node_hot_VotingNumber_reddit.value AS votingapi_cache_node_hot_votingnumber_reddit_value,
                'node' AS field_data_field_url_node_entity_type,
                'node' AS field_data_field_images_node_entity_type,
                'node' AS field_data_title_field_node_entity_type,
                'node' AS field_data_field_topic2_node_entity_type
FROM node node
LEFT JOIN votingapi_cache votingapi_cache_node_hot_VotingNumber_reddit
    ON  node.nid = votingapi_cache_node_hot_VotingNumber_reddit.entity_id
    AND (votingapi_cache_node_hot_VotingNumber_reddit.entity_type = 'node'
    AND votingapi_cache_node_hot_VotingNumber_reddit.value_type = 'hot'
    AND votingapi_cache_node_hot_VotingNumber_reddit.tag = 'VotingNumber'
    AND votingapi_cache_node_hot_VotingNumber_reddit.function = 'reddit')
LEFT JOIN votingapi_cache votingapi_cache_node_points_VotingNumber_sum
    ON  node.nid = votingapi_cache_node_points_VotingNumber_sum.entity_id
    AND (votingapi_cache_node_points_VotingNumber_sum.entity_type = 'node'
    AND votingapi_cache_node_points_VotingNumber_sum.value_type = 'points'
    AND votingapi_cache_node_points_VotingNumber_sum.tag = 'VotingNumber'
    AND votingapi_cache_node_points_VotingNumber_sum.function = 'sum')
INNER JOIN taxonomy_index taxonomy_index
    ON  node.nid = taxonomy_index.nid
LEFT JOIN field_data_title_field field_data_title_field
    ON node.nid = field_data_title_field.entity_id
    AND (field_data_title_field.entity_type = 'node'
    AND field_data_title_field.deleted = '0')
WHERE ((    (node.status = '1')
        AND (node.type IN  ('link'))
        AND (taxonomy_index.tid = '1')
        AND (field_data_title_field.language IN  ('sv'))
        AND (votingapi_cache_node_points_VotingNumber_sum.value >= '1')
      ))
ORDER BY votingapi_cache_node_hot_votingnumber_reddit_value DESC
LIMIT 10 OFFSET 0

Explain shows like below image ie: using Using temporary; Using filesort etc

Please suggest !
Thanks!

For queries this complex, you may have to think outside the box. In this case, I mean think outside the query itself.
ASPECT #1 : Join Buffers
Please note what the MySQL 5.7 Documentation says about join_buffer_size:

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.
The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).
For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

You have four(4) JOIN statements in this query, which means you have four JOIN buffers. There is a lot of nested loop processing going on. You could speed this up by increasing the join_buffer_size. Be careful, though. If the join_buffer_size is 1M, this query requires 4M before the query can even start.
ASPECT #2 : Sort Buffer, Temp Tables
In the comments above, Using temporary; Using filesort was mentioned. You could eliminate this somewhat by by increasing the following values:

tmp_table_size
max_heap_table_size
sort_buffer_size

The idea is to lower the number of times sort tables and temp tables go to disk.
For example, you can set them globally for incoming connections:
SET SESSION tmp_table_size = 1024 * 1024 * 64;
SET SESSION max_heap_table_size = 1024 * 1024 * 64;
SET SESSION sort_buffer_size = 1024 * 1024 * 64;

Then, try the EXPLAIN plan and run the query. Hopefully, it will work.
Once you know it worked for you, you can set them globally for incoming connections:
SET GLOBAL tmp_table_size = 1024 * 1024 * 64;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 64;
SET GLOBAL sort_buffer_size = 1024 * 1024 * 64;

Then, go back to my.cnf and add them.
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 64M

Give it a Try and let us know !!!


How to check if Alter view/stored proc script has run successfully For the database deployments, I call all the db schema changes in a batch file and run them on the target database.

Sometimes we have scripts for altering the views or stored procs. I'm wondering what is the best way to control whether the view/sp has been altered successfully or not and report it via a message in the output log file.

For example below is the pseudo code of what I'd like to implement:

IF
(
ALTER VIEW vw_abc
.
.
.
.
) Failed THEN

PRINT 'ALTER view vw_abc Failed'.
ELSE
PRINT 'ALTER view vw_abc Completed Successfully'
END

The output message will be recorded in the script log file which is generated by the batch file that has run the script.

If there any error code that we can check to do that? Any idea?

Thanks.

Check sys.objects for the row that has the right value in name and see if the modify_date column is newer than when your script started.

SELECT SYSDATETIME() AS ScriptStart
INTO #scriptstart;
GO
ALTER VIEW ...
GO
ALTER PROC ...
GO
SELECT name
FROM sys.objects
WHERE modify_date > (SELECT ScriptStart FROM #scriptstart);

This will list all the objects that have changed since your script started. You could join to sys.schemas if you care enough... :)


In what cases is using ClickHouseDb and the like a neccessity? An open source for website analytics - https://github.com/plausible/analytics
They use Postgresql and ClickHouseDb.
When it comes to web analytics, there're tons of events going that need to be tracked. From the point of view of database, why is using ClickHouseDb in this project neccessity? Why wouldn't Postgresql, which is relational database, alone do?
Yes, ClickHouseDb has been created specifically for analytical processing. But still, why wouldn't Postgresql alone do? Are Postgresql, MySql and the like uncapable of handling lots of inserts that occurr simateneously?

Postgres and its ilk were written as general-purpose OLTP database servers. Can it handle multiple inserts and reads concurrently? Yes it can, but it saturates at a much lower level than a specific purpose-built analytics engine will. Clickhouse is still a relational database - it presents its public API as tables of pre-defined tuples, and is interacted with via SQL - just one that is highly optimized internally for answering aggregate queries on large datasets. Postgres could answer the same queries but would take longer and require more disk, memory and CPU.
A good analogy is with cars. Postgres is like the family car. It can comfortably get the kids, the dog and all the gear to a camping trip, without using too much fuel, or costing too much to purchase or maintain. Depending on the model it can go faster or slower, carry more gear or less, be more fuel efficient or thirstier. It will not, however, win an F1 race. Nor will it carry 12 tonnes of ore out of a quarry. Nor does it come with a stretcher and bottled oxygen for ambulance work. If your workload has these needs you talk to a niche vendor to acquire a very specific product that will do one job and do it really well, and pay the corresponding purchase and maintenance costs.
The complementary question is why not use Clickhouse exclusively? It's because that product is not good for a mixed random read/ write workload where Postgres excels. An ecommerce site run off Clickhouse would be awful!


Cast NTEXT to INT for calculation and comparison I need to convert an NTEXT column to an INT so I can perform some checks on it.

The first check would be is value = 0, and the second would be if it was an even or an odd number.  I've got this mostly figured out except the conversion of the NTEXT to INT. So far what I have is this:

cast((cast(a.[Value1] as nvarchar(max))) as int) = 0

The column data looks like this '0'.  When I run my query I get:

Conversion failed when converting the nvarchar value ''0'' to data type int.

What are my options to get around this; is there a way to make it ignore the quotation marks?

See comment by Martin Smith and https://stackoverflow.com/q/2133946/327165 for overview of why not to use ntext.

In order to get numeric records only, including records with numbers that have quotation marks try this: SELECT REPLACE(CAST(TextValue AS NVARCHAR(MAX)),'''','') FROM Table a WHERE ISNUMERIC(CAST(a.[Value1] AS NVARCHAR(MAX))) = 1


Are MySQL transactions under-utilised? I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related.

Scenario : Suppose I have a series of queries which creates a user - inserts a user info record, creates an empty profile record and initialises a few other records etc..

If for whatever reason the script were to fail half way through this, what do I need to do to ensure that everything to do with the "creating a user queries" gets undone?

From what I understand reading the MySQL documentation, it as simple as using START TRANSACTION and COMMIT statements.

If that is so, why do so many CMS's neglect to do so? I suppose a common argument against it might be that it is unnecessary for short scripts dealing with only a few record updates, but am I not right in thinking that it is also highly beneficial when you are dealing with replication environments too?

In MySQL's InnoDB, you may have to start using SavePoints.
SAVEPOINT
Sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.
ROLLBACK TO SAVEPOINT
Rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.
RELEASE SAVEPOINT
If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists:

ERROR 1305 (42000): SAVEPOINT identifier does not exist

The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.
CAVEAT
All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.
A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.
EPILOGUE
Given this explanation of using SavePoints, you answer may be as simple as issuing a SavePoint in appropriate places:
START TRANSACTION;
SQL to Add User
SAVEPOINT addedsuersalready
SQL to Add Other Things
SAVEPOINT addedsuersalready
SQL to Add Additional Things
COMMIT;

This may help mitigate some issues with ROLLBACKs.
Why do most CMS products not allow for such mechanisms? Most CMS products use the dreaded MyISAM storage engine.
For example, Drupal and WordPress usually install MyISAM and plugins usually expect MyISAM tables. I have written posts about recommending the switch to InnoDB.

Apr 22, 2011 : Wordpress Database Slow - should I switch to InnoDB?
Apr 15, 2011 : What would be the optimal MySQL configuration for a Drupal 7 site?

What's even worse is that more recent plugins for both WordPress and Drupal still make table changes and leave them as MyISAM. Any CMS that uses FullText Indexes cannot convert tables from MyISAM to InnoDB in MySQL 5.5 and prior. MySQL 5.6 just become GA on Feb 05, 2013 and does support FullText Indexing. Give MySQL 5.6 some maturation time before diving in.


Oracle 11G R2 install using separate instances or multiple schemas in a single instance I am in the middle of a server upgrade, and have finally come to the conclusion that I need a very important question answered. I would like to inform everyone that I am a novice to Oracle Administration but am learning quickly and still have a lot to learn.

The company I work for has many customers that use their own web portal which is connected to it's own database schema. We have isolated 3 customers to a single database instance and if the instance goes down then generally those 3 customers go down as well.

My goal for this server upgrade is to prevent any interruption from one customers issues from another customers issues. I want complete isolation, which means installing separate database instances per customer. However, I am not sure if this is a great way of handling it.

So let me ask:

Is it best to isolate the schemas to their own instances if I want to avoid having multiple customers crash, instead of a single client?
Would Automatic Memory Management be beneficial or would Automatic Shared Memory Management be beneficial for each database instance installed on the same server?

What I understand is that ASMM would be great for having multiple schemas in a single database instance.

Currently we have memory issues because SGA and PGA were set manually and when one schema runs out of memory we would have to bounce the database instance, killing each customers connections. I want to avoid this no matter what route I take, ASMM or AMM.

PLease let me know which is best and how to best accomplish this task.

One instance should be able to do the job.  I'd recommend simply using AMM and ASMM.  If your DB is on a server dedicated as the DB server (i.e. not running web services or other software), you might try setting:

memory_target = memory_max_target = sga_max_size = 50% to 60% of your server's physical memory.  If this is less than 1500M, you're probably going to have pain. If the server is Unix or Linux, you could push this even to maybe 75%.
sga_target = pga_aggregate_target = db_cache_size = shared_pool_size = shared_pool_reserved_size = large_pool_size = java_pool_size = 0. Setting these to 0 allows Oracle to size them as needed.  Oracle will steal from db cache when it needs more memory for cursors, for example.

If one of your problems is that one of your customers floods the database with too many connections, you would do best to find a way to limit this behavior, ideally at the app server if you can configure db connection pool max sizes.  If you can't change this on the app server, then it can be done at the DB side.  For example, you could allow each schema/user to have maximum 200 connections to the DB.  Beyond that limit, the offending schema gets denied additional sessions, but the other schemas remain healthy.


How do I find the median value of a column in MySQL? I can only imagine doing this with two database queries.  The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 where X is half the number of rows.

Is there a simple way to do this with only one query?

Right now I am using averages in my calculations, but I think the mean would be better; there is no upper bound to the values and they are bounded from below by 0.

EDIT: yes, I meant to say 'median' but was having some brain error & searched for 'mean'. I have now found the answer over at stackoverflow

There is quite a bit of discussion here on calculating median values from a MySQL table. Just search the page for 'median'.

As an aside, it strikes me as remiss that there is no built-in function to do this. Median is often more descriptive of central tendency than mean. Access/VBA has the same hole in its function list.


Can't connect to local database Today I created a local database on my computer.
Then I installed SQL Developer. But I can't connect.

It gives me this error:

  "Status : Failure -Test failed: Locale not recognized"

My database version: 11.2.0.1.0

Now I have only one  instance (2 databases deleted before)

According to Oracle Support Community this is likely due to a JDBC / Oracle JDBC driver issue; are you using an unusual Locale on your PC?  Is the ojdbc*.jar file on your PC that SQL*Developer is using the correct version for your database?


How to remove job history for a TIME PERIOD in SQL Server I am trying to purge / delete history for all jobs that ran for a week. How could I achieve this?
Please note that I've read this article that talks about deleting history for a SPECIFIC JOB (I have 200 jobs and can't use that).
Someone tried to manually delete it from table but apparently that can have bad consequences.
I also wanted to add that Purging UP TO A DAY is NOT acceptable. I need to delete a range (say Jan/04 - Jan/15). All days up to Jan 4 and after Jan 15 must remain.

You could just delete from the sysjobhistory table - as this is essentially what sp_purge_jobhistory does under the covers anyway:

DELETE msdb.dbo.sysjobhistory
 WHERE run_date BETWEEN '2019-01-04' AND '2019-01-15';

Though, as you have pointed out - it isn't officially supported and could have unintended consequences. With that in mind, there is an official supported way to do it using the JobServer class of SQL Server Management Objects:

The below is in C# because that is just what I prefer, but the documentation also has examples of doing the same in PowerShell:

// connect to the local instance
Server server = new Server();

// get the SQL Server Agent associated with the local instance
JobServer jobServer = server.JobServer;

// create a new job filter based on the criteria needed
JobHistoryFilter jobHistoryFilter = new JobHistoryFilter()
{
    StartDate = new DateTime(2019, 1, 4, 0, 0, 0),
    EndDate = new DateTime(2019, 1, 15, 23, 59, 59)
};

// now purge all data returned by the filter
jobServer.PurgeJobHistory(jobHistoryFilter);

And that's it! You could also condense it all into one call chain:

new Server().JobServer.PurgeJobHistory(new JobHistoryFilter()
{
    StartDate = new DateTime(2019, 1, 4, 0, 0, 0),
    EndDate = new DateTime(2019, 1, 15, 23, 59, 59)
});

I haven't put any error handling's in here but you should note that it could fail if you do not have permission to connect to the SQL Server, the SQL Server Agent, or if you do not have permission to query/modify msdb.

Here are some more useful links (and they also have PowerShell examples on them as well):

JobServer.PurgeJobHistory

JobHistoryFilter

Administrative Tasks in SMO

JobServer


Sql server database synchronization on 2 different server I need advice on setting up synchronization between 2 SQL Server 2016 databases on standalone servers in different sites. What techniques should I best use and how should I go about setting up synchronization?

Currently I have a SQL Server at one site and a second server at a different site.

The requirement is to have database replication from the first site to the second site only.

You can go in three ways:

Use replication. This is a large area of database engineering. I recommend you get started with types of replication.
Use log shipping. This is usually used to create an emergency database (hot backup). Pay attention: The secondary server will read-only.
The easiest option. Run backup on the primary server and restore this file on the secondary server. it is a good idea If the restore procedure takes a little time. There are paid cloud services like SqlBak and IBM Cloud Backup that will do it for you.


How "frequently used data" and cache work in databases I know that the databases can store the most used data in the RAM memory for fast access, but when, and what data ?

1- The query is executed multiple times in this table

id, name, gender
-- ----- -------
1 , Bill, Male.
2 , Todd, Male.

SELECT * FROM people WHERE gender = 'Male';

What is the minimum value it can save in memory ( column, row, page, table ) ?
Having PK or FK make any difference when its can be save ?
If i UPDATE the table or row, the memory have to be read again ?

2 - And a query with JOIN in tables like this ?

people
id, name, gender_id
-- ----- -------
1 , Bill, 2
2 , Todd, 2

people_gender
id, gender
-- -------
1 , Female.
2 , Male.

SELECT id, name, gender FROM people p JOIN people_gender pg ON p.gender_id = pg.id

The database read once and save the value of people_gender.id, then it can read only 4 bytes more 2 bytes in people.gender_id = 6 bytes OR it have to JOIN and read every row, even when the value is well know (1+4+1+4 = 10) ?
The JOIN is worth of it or the first case is better for performance ?

The true base can be in Sql Server up to 16 GB RAM or Postgresql down to 1 GB RAM, it have a column that save a state of an event like WORKING, WAITING, STOPPED and CANCELED ( actually not in English, five states with 8-10 chars each ), its so common updating and/or returning this data in the table Event, that i starting by testing performance things here.

I working in a software, that growing a lot in last years and now is facing performance problems, this is work for the DBA ... but we do not have one :D.

My first question in the dba meta, sorry any mistaking, any tip is welcome.

Thank you in advance.

Caching with these DBMS is at the block/page level. Data must be in memory to be used. The high-level process is that the DBMS first looks in cache, and if not present, reads the needed blocks/pages into memory for use. Implementation details vary greatly by DBMS but generally buffers are reused by a LRU algorithm with the intent to keep hot data in cache and evict less frequently used data so that the greatest benefit is realized by limited memory resources.

To address performance, perform query and index tuning so that the minimal amount of data must be retrieved from storage and add as much memory as you can for caching.


How to avoid hardcoding a database link name in a package body I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a single table causes me lots of headache. It is a char(1) field that is used as a bit vector. Because the encodings are different, it gets reencoded and scrambled, so I have to update it using DBMS_HS_PASSTHROUGH:

C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@remotedb1;
DBMS_HS_PASSTHROUGH.PARSE@remotedb1(C, 'SELECT PKEY, HEX(FLAGS) FROM TABLE1' ) ;
LOOP
   NR := DBMS_HS_PASSTHROUGH.FETCH_ROW@remotedb1(C);
   EXIT WHEN NR = 0;

   DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 1, M_PKEY);
   DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 2, M_FLAGS);

   UPDATE TABLE1
   SET FLAGS = M_FLAGS
   WHERE PKEY = M_PKEY;

END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@remotedb1(C);

Now I have a specific db-link name, remotedb1, in my package body, while I need to retarget it to another db-link rather often. I tried creating a synonym for a db-link, but that wouldn't work. Is there another way around it other than using a &&variable and recompiling from source? Will wrapping each call in EXECUTE IMMEDIATE work?

Changing each call of a component of the DBMS_HS_PASSTHROUGH to a dynamic sql statement (EXECUTE IMMEDIATE) is a possibility but I would prefer the following approach.

For tables and views of the remote database one creates views on the local database that wraps this object.
For packages, functions and procedures one creates  packages, functions and procedures on the local database that wrap these object.

You already implemented this approach by creating the views for the remote tables.
Conceptually the dbms_hs_passthrough package is a package on the remote database. Therefore its component are called with the database link appended and not with the database link supplied as string paramter.
So you should write a wrapper package for the dbms_hs_passthrough package. The definition of the dbms_hs_passthrough-components can be found in the Database PL/SQL Packages and Types Reference or you can get it from the database dictionary (e.g. by issuing a describe command in sqlplus). If you change the database link name you only have to change your views and the small body of the wrapper package.


Which major database platforms support TTL? I need to store some data in a database that will be hosted on Amazon Web Services, and I have complete freedom over choosing which database platform to install, based on what I think will best suit the particular application.

One thing to note, however, is that AWS has the following four databases essentially "built-in" to their RDS service:

Oracle
MySQL
PostgreSQL
SQL Server

But on top of that, it is also possible to spin up an EC2 instance and install anything else; the only trouble there is then I have to install and maintain the database myself whereas some of that work is farmed out if I go with one of the options above.

The particular application I'm working on will store a lot of data that, for all intents and purposes, looks like logs / audit history. And there's a lot of it. Each day, we might insert a few 100K rows into this table. In general, we'll only be operating on the data that has been inserted that same day, though we would want to keep data around for at least a week or two.

But after that point, we'll need to essentially "expire" (aka delete) the old data so that we don't run out of disk space. So I'm looking for a platform where it's relatively easy to "expire" data.

I saw that MongoDB has built-in support for this on a row level by setting an index with the expireAfterSeconds option. But I'm not sure about any of the SQL options I've listed above, or any others really.

Do any of the above SQL databases have any mechanism for automatically pruning old data, or treating a table as a "rolling log" with a cap to the file size or row count? Or do you have any other recommendations?

I know that I could simply write this on the application level itself, by having an indexed "created date" field and running a DELETE query at midnight. But I have concerns about the transaction log getting exponentially bigger as a result of doing things this way.

If you need true TTL functionality, the only real choice I know of is using an object DB.

Mongo does TTL quite well;
Redis fully supports TTL;
LevelDB has an add-on which supports TTL;
MS Azure DocumentDB also has TTL support.


How to connect external database to AppEngine? Are there any ways (better than using UrlFetch) to connect to any external database from AppEngine?

I already know about hosted SQL databases in App Engine for Business, but they are not free and will be avialable in Q2 2011, so it's not an option right now.

I like datastore very much, but customers don't like vendor lock or have other reasons.

Roughly speaking, no.

The big demand of GAE is the integrated, scalable, cloud backend. Without the backend hardware, it's functionally Django on bigtables. If the customers (quite rightly) don't like vendor lockin, GAE is not for them.

What features of GAE would you keep, if not for the backend?


COMMIT after EXPLAIN ANALYZE of a read-only query In PostgreSQL, what effect could produce issuing a COMMIT after executing an EXPLAIN ANALYZE with a read-only query?

It will result in either a

  WARNING:  there is no transaction in progress

or committing a previously started transaction (done by the BEGIN; command or a version of it), which might have included some non-readonly statements, too.

Before running the COMMIT;, you can check if you are in an open transaction from an other connection, issuing

SELECT * FROM pg_stat_activity WHERE usename = 'your_user';

You'll get (at least) two rows, one reporting exactly this query, and the other your other session.  If the state of the latter says idle in transaction (on version 9.1+), then you are in an open transaction there.  It is hard to tell what was run there beforehand, but you may be able to scroll back through your previous commands stored in your client's history.


Delete using another table with index I'm trying to delete rows using another table and wondering why postgres doesn't use an index, here are the steps, on pg10.5:

CREATE TABLE parent (id int);
CREATE TABLE child (pid int);

CREATE INDEX parent_idx ON parent (id);
CREATE INDEX child_idx ON child (pid);

INSERT INTO parent SELECT * FROM generate_series(1, 100000);
INSERT INTO child SELECT * FROM generate_series(1, 100000);
VACUUM ANALYZE parent; VACUUM ANALYZE child;

EXPLAIN DELETE FROM child c USING parent p WHERE p.id = c.pid AND p.id < 10000;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Delete on child c  (cost=464.31..2383.65 rows=10134 width=12)
   ->  Hash Join  (cost=464.31..2383.65 rows=10134 width=12)
         Hash Cond: (c.pid = p.id)
         ->  Seq Scan on child c  (cost=0.00..1443.00 rows=100000 width=10)
         ->  Hash  (cost=337.64..337.64 rows=10134 width=10)
               ->  Index Scan using parent_idx on parent p  (cost=0.29..337.64 rows=10134 width=10)
                     Index Cond: (id < 10000)

I would have expected Postgres to pick an Index Only Scan using parent_idx followed by an Index Only Scan on child_idx. What am I missing?

It can't do an index-only scan in this case.

It needs the actual table row, not just the index entry, of the parent row.  If you do an EXPLAIN VERBOSE, you will see the p.ctid getting exposed.  I believe that is because if the delete encounters a locked row in the child table, it will block until the transaction locking that row finishes.  But once that happens, it has to re-check the parent row to check if it was changed by the same transaction that had just changed the child row.

And of course it can't do an index-only scan on the child, because how would you delete a row in the table without accessing the row in the table?

But anyway, why would doing one index-only scan plus 10,000 individual index scans be faster than one hash join?  Hash joins are pretty awesome.


Interpret the CPU usage in SQL trace file During our performance evaluation, we run one load simulation with SQL trace on. And while analyzing the trace file I want to calculate the CPU usage. How to do it? (Please refer the screenshot attached)

So for the physical read, we observed a CPU spike but don't know how to interpret it.

Tha value showing for 53,682,888 reads, the CPU utilized is 1,254,911. Is it in a microsecond or in cycles?

These values are represented in terms of time. This value could either be in millisecond or microsecond. For CPU - it would be number of milliseconds used by this particular process.

This has been explained by Mr. Brad McGehee in a very detailed way with examples.

CPU

  This data column shows the amount of CPU time used by an event (in
  milliseconds). Obviously, the smaller this number, the fewer CPU
  resources were used for the query. Note that CPU data column is only
  captured for the RPC:Completed, SP:StmtCompleted, and the
  SQL:BatchCompleted events.

Reads

  This data column shows the number of logical page reads that occurred
  during an event. Again, the smaller this number, the fewer disk I/O
  resources were used for the event. Note that Reads are only captured
  for the RPC:Completed, SP:StmtCompleted and the SQL:BatchCompleted
  events.

Writes

  This data column shows the number of physical writes that occurred
  during an event and provides an indication of the I/O resources that
  were used for an event. Again, Writes is only captured for the
  RPC:Completed,  SP:StmtCompleted and the SQL:BatchCompleted events.

Please find the below notes from Microsoft on this:

Note

The server reports the duration of an event in microseconds (10^-6 seconds) and the amount of CPU time used by the event in milliseconds (10^-3 seconds). The SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

Same can be summarized here:

I hope above helps.


Returning rows where all non-NULL values are equal Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:

WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL

this would be the equivalent of the COALESCEs of all the permutations to be equal - but there is no non-aggregating MIN/MAX function

An alternative for 3 columns is:

WHERE (
COALESCE(a, b, c) = COALESCE(b, c, a)
AND COALESCE(a, b, c) = COALESCE(c, a, b)
AND COALESCE(a, b, c) = COALESCE(b, a, c)
AND COALESCE(a, b, c) = COALESCE(a, c, b)
AND COALESCE(a, b, c) = COALESCE(c, b, a)
)
OR COALESCE(a, b, c) IS NULL

i.e. "All non-NULL columns a, b, c must be equal or all columns may be null"

Obviously a PIVOT/UNPIVOT implementation could be done or a complex CASE statement, but I'm looking for something relatively simple.

In this case, the columns are all integers, so I guess there is probably a math trick I could figure out.

The most compact syntax I can find is:

SELECT *
FROM @T AS t
WHERE EXISTS
(
    SELECT ISNULL(ISNULL(a, b), c)
    INTERSECT
    SELECT ISNULL(ISNULL(b, c), a)
    INTERSECT
    SELECT ISNULL(ISNULL(c, a), b)
);

Based on an idea from one of my old blog posts that describes how to use INTERSECT and EXCEPT to replace comparisons like a <> b OR (a IS NULL AND b IS NULL) with NOT EXISTS (a INTERSECT b).


Does size of one particular column in a database affect query performance? I have a table user_list

CREATE TABLE IF NOT EXISTS `user_list` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `email` varchar(40) NOT NULL,
  `userdata` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000265 ;

The queries I perform on this table are

SELECT email WHERE id = XXXX
SELECT userdata WHERE id = XXXX
SELECT id WHERE username = XXXX
UPDATE user_list SET userdata WHERE id = XXXX (used pretty
frequently)
UPDATE user_list SET email WHERE id = XXXX
(These are the only I queries I am going to perform on this table)

Now the problem is userdata field stores all the information about the user and is relatively large.Its text generally contains about 2000 characters. Say I am going to insert some 10 million+ entries in the database.

Does the size of this userdata field affect the performance of the queries I mentioned above?

If I store the userdata text as a separate file and just store the path in the database, will there be a significant performance difference?  Since queries 1,3 and 5 do not involve the userdata field, will their performances be increased significantly since I have removed the userdata field from the table?

Is having a separate table user_data_list with the fields for the userdata

----------id----------userdata-----------------

provide any advantages over how it is now?  Does having such a separate table have any disadvantages?

A short answer would be: Yes, it affects performance.

A database stores it's data inside formatted database blocks. These blocks are the basis of all caching mechanism, so caches will store database blocks. Querying the database will move some read blocks into the query cache.

You can imagine, the smaller a block is, the more blocks can stay safely inside the caches without having to drop them for new blocks from new queries.

However, I'd say that in many scenarios that store data this way, performance differences of the kind I described might not be the most prominent factor for the underlying system. A benchmark might find differences, but for a system to react noticeably faster you need at least 10% faster responses (the number differs from study to study), and I'm not certain excluding the text column to it's own table will give you that.

On the other hand, if the column would be indexed, that might make matters different. But as I see no index other than the primary key, I see no reason to refactor here - other than that such a column is considered bad practice.


MySQL Select all clients that searched for two different items or more I have a table like this :

serial,ip,term
1,1.2.3.4,bag
2,2.3.4.5,shampoo
3,1.2.3.4,handbag
4,3.4.5.6,clothes
5,2.3.4.5,battery
6,4.5.6.7,lamp

I want to select all IPs that did search for multiple different items while excluding the term 'bag' from the selection (in this case I am looking for 2.3.4.5)

Thank you very much

SELECT ip
FROM my_table
WHERE UPPER(term) NOT LIKE '%BAG%'
GROUP BY ip
HAVING COUNT(ip) > 1;

Normally I would recommend against using a function
in a search (UPPER()), but since the wildcard is there,
the expression is non-sargable in any case!

The db-fiddle is here!


Error 909 restoring a database on Standard Edition I ran into a problem with SQL Server 2012 Standard Edition (64-bit).
I have rebuilt a system with the exact same specifications for software as it had been built previously (Windows 2012, SQL Server 2012 Standard Edition).  Loading the majority of the databases seem to cause no issues, with the exception of just a few that cause the following error when attempting to restore:

Database 'xxx' cannot be started in this edition of SQL Server because part or all of object 'Country' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'xxx' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

The previous version of SQL Server was Standard as well. Short of uninstalling and re-installing the Enterprise version, is there a solution? I did some searching, and tried the following, but it doesn't really want to do anything to the database other than to set it to "emergency mode".
Recovering a SQL Server Database from Suspect Mode

According to the error message, your database contains Enterprise Edition features in the table Country. First of all, check the following on the original (old) server:

SELECT SERVERPROPERTY('Edition');

That should tell you if you're running on Standard, Enterprise or Developer Edition. Developer Edition supports all of the Enterprise features (with different licensing terms). Could that be the issue?

Check if compression, partitioning or vardecimal is enabled on any object:

SELECT s.[name]+'.'+o.[name] AS [object], i.[type_desc] COLLATE database_default+ISNULL(' '+i.[name], '') AS index_name,
       (CASE WHEN COUNT(DISTINCT p.partition_number)>1 THEN 'Is partitioned' ELSE '' END) AS [partitioned?],
       ISNULL(MIN(NULLIF(p.data_compression_desc, 'NONE'))+' compression', '') AS [compressed?],
       (CASE WHEN ISNULL(OBJECTPROPERTY(p.[object_id], 'TableHasVarDecimalStorageFormat'), 0)=0 THEN '' ELSE 'vardecimal' END) AS [vardecimal?]
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.[object_id]=i.[object_id] AND p.index_id=i.index_id
INNER JOIN sys.objects AS o ON i.[object_id]=o.[object_id]
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
GROUP BY p.[object_id], s.[name], o.[name], i.index_id, i.[type_desc], i.[name]
ORDER BY s.[name], o.[name], i.index_id

Finally, see if there are any partition functions or partition schemes that you haven't cleaned out:

SELECT [name] FROM sys.partition_functions;
SELECT [name] FROM sys.partition_schemes;

You may also consult the following link (originally provided by Shanky in a comment):

How to identify compressed tables before restoring/migrating a database to any edition other than Enterprise Edition of SQL Server 2008


How password can be prompted in percona xtrabackup How password can be prompted while using percona xtrabackup.--ask-pass option is not working with xtrabackup. Thanks in advance.

I donot think there is --ask-pass in xtrabackup! You might want to write a wrapper shell script which will

echo enter mysql password
read -s pw

and then pass it as --password=$pw.


How do I show the number of tables in all databases? I am looking for a query that return all the databases in my MySQL database (i.e. like SHOW DATABASES; but includes the number of tables in the tabular result.)

Something that outputs:

+--------------------+--------+
| Database           | Tables |
+--------------------+--------+
| information_schema |     40 |
| db1                |     97 |
| db2                |    135 |

etc.

Is there a simple query or command that can be typed from the mysql command line that produces that.

You can use the information_schema.tables

select table_schema, count(1)
from information_schema.tables
group by table_schema


How to ORDER a big table? I have a simple but big table (tens of millions of rows). The performance is quite good except when I need to order the table by the column with format datetime.

A simple SELECT query to fetch the last 10 updated rows takes tens of seconds.

Is there a trick to order big table in a fast way?

MySQL recommends using indexes for a variety of reasons including elimination of rows between conditions: How MySQL uses Indexes.

This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query. I'm not sure how many rows are generated in 30 days, but as long as it's less than about 1/3 of the total rows it will be more efficient to use an index on the column.

Your question about creating an efficient database is very broad. I'd say to just make sure that it's normalized and all appropriate columns are indexed (i.e. ones used in joins and where clauses).


We use MongoDB 3.6 community version, How can i get the number of hits on single collection as well as bandwith usage? Is it possible to get the number of hits on single collection as well as bandwith usage in mongodb ?

There is a documentation section for Analyzing Performance, another for Monitoring, and one specifically for free monitoring.

You can also run the command db.serverStatus() to retrieve server statistics.


Count percentage of column with GROUP BY Consider the following query:

SELECT
  country
  , count(id) AS "count"
FROM
  lead
WHERE
  date_part('year', lead.since) = date_part('year', CURRENT_DATE)
GROUP BY "country"
ORDER BY "count" DESC
;

It returns something like:

country count
fr  3456
us  569
sc  248
…

How can I add a third column with the percentage of the total count?

SELECT country,
       COUNT(id) "count",
       COUNT(id) / SUM(COUNT(id)) OVER () * 100 percent
FROM lead
WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE)
GROUP BY country
ORDER BY "count" DESC;


Retrieving auto-generated keys for an INSERT statement What is the best way to retrieve an  auto-generated keys after an INSERT statement ?

After searching online for DB2 I found this https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0057053.html

Is there a way to do it without using java ?

Preferably I would work using python so ibm_db.

Can such a thing be done on a postgres database ?

There are several ways of doing this in Db2 for LUW.

Firstly, there is a function IDENTITY_VAL_LOCAL() that returns one most recently assigned identity value; it can be used when inserting single records:

insert into parent_tab (some_column) values ('foobar');
insert into child_tab (parent_id, some_other_col)
  values (identity_val_local(), 42);

assuming parent_tab in the example has an identity column id.

Another option is to use a data change table:

with parent_res (parent_id) as (
  select id from final table (
    insert into parent_tab (some_column) values ('foobar')
  )
)
insert into child_tab (parent_id, some_other_col)
select parent_id, 42 from parent_res;

The data change table will contain as many rows as are affected by the corresponding DML statement.

In Postgres a similar result can be achieved by using the RETURNING clause of the INSERT statement:

with parent_res (parent_id) as (
  insert into parent_tab (some_col) values ('foo') returning id
)
insert into child_tab (parent_id, some_other_col)
select parent_id, 42 from parent_res

Since the Postgres IDENTITY (formerly known as SERIAL) columns are backed by SQL sequences, you can also use the LASTVAL() function to retrieve the most recently assigned identity (sequence) value in the session.


Percona-Server: slow TokuDB queries after upgrading from 5.6 to 5.7. ANALYZE TABLE doesn't resolve the problem After upgrading from Percona-TokuDB 5.6.29-76.2 to 5.7.19-17 we see some very slow queries on some tables without primary keys, but multiple non-unique indexes. The box we migrated to is pretty well equipped (768 GB RAM, PCIe SSDs). We used mysql_upgrade after migration.

After investigating Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen we tried ANALYZE TABLE, even with RECOUNT_ROWS, REPAIR TABLE, ALTER TABLE *** FORCE without any effect.

Typical table structure:

CREATE TABLE `letter_archiv_12375` (
 `user_id` int(12) unsigned NOT NULL DEFAULT '0',
 `letter_id` mediumint(6) unsigned NOT NULL DEFAULT '0',
 `crypt_id` bigint(12) unsigned NOT NULL DEFAULT '0',
 `mailerror` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `unsubscribe` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `send_date` date NOT NULL,
 `code` varchar(255) NOT NULL DEFAULT '',
 KEY `crypt_id` (`crypt_id`),
 KEY `letter_id` (`letter_id`),
 KEY `user_id` (`user_id`)
) ENGINE=TokuDB

A simple query like that takes 4 seconds on a table with 200m rows.

UPDATE hoovie_1.letter_archiv_14167
                          SET unsubscribe = 1
                        WHERE letter_id = "784547"
                          AND user_id   = "2881564";

The cardinality values are correct. EXPLAIN will result in:

id  select_type table   partitions  type    possible_keys       key     key_len ref rows    filtered    Extra
1   UPDATE  letter_archiv_14167 NULL    range   letter_id,user_id   letter_id   3   const   1   100.00  Using where

The only solution is to remove and re-create at least one index. After dropping and re-creating the index letter_id the table will perform well (in 0.01 s).

The EXPLAIN will change to

id  select_type table   partitions  type    possible_keys       key     key_len ref rows    filtered    Extra
1   UPDATE  letter_archiv_14167 NULL    range   user_id,letter_id   user_id     4   const   99  100.00  Using where

We have some thousands of TokuDB tables in production - a performance loss of factor 300-500 is a problem.

So we are unsure to migrate to 5.7 - this behaviour could occur even after re-creating all indexes again.

Any ideas?

Add this composite index:

INDEX(letter_id, user_id)  -- in either order

Do you deliberately not have a PRIMARY KEY?


Mixing sharding and Replica in MongoDB cluster We know there are two ways to create a mongoDB cluster :-

1.) Sharding

2.) Replication

My question is can we combine both sharding & replication like we have in elastic cluster?

In MongoDB 3.6 and newer, shards must be deployed as a replica set. Replication provides data redundancy and high availability for each shard; sharding enables a collection to be distributed across multiple shards.

For more information see Sharded Cluster Components and the Deploy
a Sharded Cluster tutorial in the MongoDB documentation.


sql server listen on multiple ports I have a push subscriber in transactional replication (and the distributor is the publisher), in this subscriber I configured sql server port on 1433, and I have a web application which connect to this subscriber database, and I want to use any port but not 1433, Can I configure SQL server to listen to two different ports one will be used in replication from distributor IP (which stay 1433) , and the other for web request (from web server IP), and how to do that ??

In case of a single port, If the default port of sql server(1433) is changed this will affect on replication cause subscriber and publisher listening on the same port, is this right ??

Yes, SQL Server can be configured to listen on multiple IPs and multiple ports. Read Configure the Database Engine to Listen on Multiple TCP Ports.


SET IDENTITY_INSERT OFF - Caused Table Lock I Executed SQL on database that looked like this:

SET IDENTITY_INSERT table1 ON
GO
BEGIN TRANSACTION
GO
INSERT INTO table1 SELECT ... FROM table2
GO
DELETE FROM table2 ...
GO
COMMIT TRANSACTION
GO
SET IDENTITY_INSERT table1 OFF
GO

Problem is my table was locked on last statement SET IDENTITY_INSERT table1 OFF, all previous statements took around 6 hours, but my table is locked on this last statement for 1 day now.

In Activity Monitor it has connection that dont execute any command but is head blocker of locking chain, i killed it but nothing changed only that is in rollback state but it's still eating all I/O.

My question is what this session is doing and how to avoid it? It looks like IDENTITY_INSERT is almost costly like full table recreation for some reason, i don't know how this could cause any downsides on performance side.

Additional info: there was not identity collision during insert, bcs i insert operated on lower identity values than initial set on table1

Table design:

CREATE TABLE [dbo].[table1] (
    [id_table1] BIGINT     IDENTITY NOT NULL,
    [id_table3]              INT        NOT NULL,
    [some_id]    BIGINT     NOT NULL,
    [some_time2]           DATETIME2   NOT NULL,
    [some_time]            DATETIME2   NOT NULL,
    [some_value]                FLOAT (53) NOT NULL,
    [some_value2]               FLOAT (53) NOT NULL,
    [some_value3]            TINYINT    NULL,
    CONSTRAINT [FK_table1_table3] FOREIGN KEY ([id_table3]) REFERENCES [dbo].[table3] ([id_table3]),
    INDEX [IX_table1] CLUSTERED ([id_table3] ASC, [some_time] ASC, [id_table1] ASC) WITH (DATA_COMPRESSION = PAGE)
) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX [IX_table1_some_time] ON [dbo].[table1]
([some_time] ASC, [id_table1] ASC) INCLUDE ([id_table3], [some_value], [some_value2], [some_value3]) WITH (DATA_COMPRESSION = PAGE)
GO

SET IDENTITY_INSERT is just a session option allowing you to insert identity values explicitly in a subsequent statement. It doesn't do anything to the transaction log. I presume your first transaction wasn't actually committed and now is rolling back.

This could happen if you have implicit_transactions on

Or a possibly more likely scenario is that you were already in an open transaction unknowingly.

This could happen if your original script contained an error between the begin tran and commit. Executing could then terminate execution leaving an open transaction (If you had used SET XACT_ABORT ON any error would have rollbacked the transaction rather than leaving it open.)

Then after fixing the error and rerunning the script in the same session the begin tran would be called again and increment @@trancount and the COMMIT would just decrement it down to 1, leaving the transaction still open.

Rather than killing the session and causing transaction rollback the correct thing to do in that case (assuming you were happy that everything was OK and the original error condition hadn't caused changes that you wouldn't want to commit) would have been to check @@trancount and commit again as many times as needed (I.e. in the event that there were multiple executions before the error was fixed) until that reached zero.