drillchinaPlease go chenliangryan.wordpress.com for updates.
Struggled almost 30 minutes to get through all the steps. It seems the read me was disconnected with current build.
Here is the steps I tried out:
Here is complete commands for medium size build (I extracted the file to [Downloads] directory):
cd /home/ryan/Downloads/ds2/pgsqlds2/data_files/cust
gcc ds2_create_cust.c -lm -o ds2_create_cust
chmod +x ds2_create_cust_med.sh
./ds2_create_cust_med.sh
cd ../orders
gcc ds2_create_orders.c -lm -o ds2_create_orders
gcc ds2_create_inv.c -lm -o ds2_create_inv
chmod +x ds2_create_orders_med.sh
./ds2_create_orders_med.sh
./ds2_create_inv 100000 0 > ../prod/inv.csv
cd ../prod/
gcc ds2_create_prod.c -lm -o ds2_create_prod
./ds2_create_prod 100000 0 > prod.csv
cd ../../
./pgsqlds2_create_all.sh
Hope it can save your time.
vPostgres is another distribution of PostgreSQL from VMware. There is a little bit different to install pgpool with it. You are supposed to see error messages like “libpq is not installed or libpq is old” in the configuration phase or “error libpq-fe.h no such file or directory” in the make phase.
Have you ever tried change the default path of your data files and log files?
One scenario you can step into this case is manually failover. You mounted the data drive and log drive, but unfortunately the log drive unable to be mounted as original drive letter. So after you start up the instance. You found all your original databases were under the recovery pending status, because SQL Server can’t find the log file for your user databases. So you need to do something to detach and attach these databases, with a log folder path.
Here is a simple script which will save your wrists and mouse from massive clicks:
:setvar newDataPath “D:\SQL Server\Data”
:setvar newLogPath “E:\SQL Server\Log”
set nocount on
declare @databaseID int
declare @databaseName sysname
declare @dbStmt nvarchar(1024)
declare curOFLDB cursor for
select d.database_id, d.name from sys.databases d
inner join sys.database_recovery_status r
on d.database_id = r.database_id
where r.database_guid is null
open curOFLDB
fetch next from curOFLDB into @databaseID, @databaseName
while @@FETCH_STATUS = 0
begin
set @dbStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ SET OFFLINE;’
select @dbStmt
declare @logicalName sysname
declare @physicalName nvarchar(260)
declare @type tinyint
declare @moveStmt nvarchar(1024)
declare curFiles cursor for
select f.name, f.physical_name, type from sys.databases d
inner join sys.master_files f
on d.database_id = f.database_id
where d.database_id = @databaseID
open curFiles
fetch next from curFiles into @logicalName, @physicalName, @type
while @@FETCH_STATUS = 0
begin
if @type = 0
begin
set @physicalName = RIGHT(@physicalName, CHARINDEX(‘\’, REVERSE(@physicalName)) – 1)
set @moveStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ MODIFY FILE’
set @moveStmt += ‘ (‘
set @moveStmt += ‘ name = ‘ + @logicalName + ‘, ‘
set @moveStmt += ‘ filename = ”$(newDataPath)’ + @physicalName + ””
set @moveStmt += ‘ )’
end
if @type = 1
begin
set @physicalName = RIGHT(@physicalName, CHARINDEX(‘\’, REVERSE(@physicalName)) – 1)
set @moveStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ MODIFY FILE’
set @moveStmt += ‘ (‘
set @moveStmt += ‘ NAME = ‘ + @logicalName + ‘, ‘
set @moveStmt += ‘ FILENAME = ”$(newLogPath)’ + @physicalName + ””
set @moveStmt += ‘ )’
end
select @moveStmt
fetch next from curFiles into @logicalName, @physicalName, @type
end
close curFiles
deallocate curFiles
set @dbStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ SET ONLINE;’
select @dbStmt
fetch next from curOFLDB into @databaseID, @databaseName
end
close curOFLDB
deallocate curOFLDB
Just tried out how to change the data folder of vPostgres. We all know that separate data folder and log folder is one of the deployment best practice, for better performance. It is not difficult to do this for native PostgreSQL, just run the initdb command, or change the PostgreSQL.conf. But it’s not the same story for vPostgres, which is a distribution from VMware, part of its vFabric ecosystem.
Short version of the new storage: you must change a variable called “PG_DATA_DIR” in pgvm.conf, instead of postgresql.conf. You can find the pgvm.conf at /opt/vmware/vpostgres/current/etc/.
So if you want install vPostgres in your own machine, with the package downloaded from VMware, and you want separate the data folder and log folder. You need use following steps:
Have to say I don’t like this change, especially the official document never mentioned it. Tried several times then found this pgvm.conf.
And if you are using a virtual appliance. The steps shall be very close. Just try the value in second section, of course with
We all knew that unique index can prevent duplicated values in the table. Does a column with unique constraint allow NULL values? The answer is positive. But only one. This is what this post for.
There are chances that we might need the value in a column to be unique, or be NULL. Take an example, you have a user table for your online social application. The user can be validated with their identities (PID) or be linked with another external ID like Live ID (now calls Microsoft Account) in the real world. Otherwise he can choose to stay as not validated. To avoid duplicated registration, you might need the PID to be unique. At the same time, user can also choose to keep their privacy, and leave the PID value to be empty.
For the scenario above, you will find some difficulties to define the unique constraint. There will be impossible to save two unverified user, because their PIDs are all NULL.
To resolve this problem, you can use filtered index which was first introduced in SQL Server 2008. You can define the unique index as
CREATE UNIQUE INDEX uix_users_pid ON Users (PID) WHERE PID IS NOT NULL
It is always a good practice to put a unique constraint in a table, no matter an auto-incremental primary key or a composite unique index. The reason is not only it can produce some performance benefits, but also make you easier to remove duplicated rows.
Let’s look at one sample here. You have a table with log information. The table has three columns, log_date, log_source, and log_text. The problem is developer forgot to put a unique index there, although he can put one on the columns log_date and log_source. And one day the log relay-agent middleware had some problems and pushed several log entries into the table several times when it retried to save the log into the database. (Maybe you can find some more realistic scenarios.)
Now as a dba, you need to find out those duplicate rows and delete them. You can try the undocumented pseudo column %%physloc%%, or %%lockres%% in SQL Server 2005.
For SQL Server 2005
DELETE FROM logs
WHERE %%lockres%% NOT IN
(SELECT MIN(%%lockres%%) FROM logs GROUP BY log_date, log_source)
For SQL Server 2008 or later version
DELETE FROM logs
WHERE %%physloc%% NOT IN
(SELECT MIN(%%physloc%%) FROM logs GROUP BY log_date, log_source)
I found I often need to find out those heap tables when I do quality control of the database schema in some projects. Usually those heap tables will be requested for justification. So here is the simple script used to make this task easier.
select ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ from sys.indexes i
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
where index_id = 0
And this script can also be changed to find out those heap tables with non-clustered indexes.
select ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ from sys.indexes i
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
group by s.name, t.name
having sum(index_id) = 0
Something that we do frequently in an analytics application is loading data from one table to another. Personally I recommend SSIS for such tasks. But there are chances SSIS is not suitable, then we usually use INSERT INTO … SELECT.
I don’t recommend SELECT … INTO because you can’t control which file group the destination table goes to. And it is very important when I have to use table partition.
So if we need use T-SQL to move data from one place to another one. It is recommended to use TABLOCK option on the destination table. With a few other constraints, the query can be executed with minimal logging.
The few constraints are:
Maybe it is difficult to match the second and third constraint. But we have an alternative which is partition switch. We can partition the destination table. Load the data from source into an empty table with same schema except the index objects (usually I keep the clustered index). After load, we can do a partition switch.
Here is two sample queries:
INSERT INTO dbo.FactDevicePerformance_History
([TimeKey], [DeviceKey], [reads per sec], [read hits per sec],
[seq reads per sec], [seq read hits per sec], [writes per sec],
[write hits per sec], [Kbytes read per sec], [Kbytes written per sec])
SELECT h.* FROM AIR.dbo.FactDevicePerformance_History h
INNER JOIN AIR.dbo.DimTime t ON h.TimeKey = t.TimeKey
WHERE t.[Date] BETWEEN ‘2011-11-1’ AND ‘2011-11-30’
INSERT INTO dbo.FactDevicePerformance_History WITH (TABLOCK)
([TimeKey], [DeviceKey], [reads per sec], [read hits per sec],
[seq reads per sec], [seq read hits per sec], [writes per sec],
[write hits per sec], [Kbytes read per sec], [Kbytes written per sec])
SELECT h.* FROM AIR.dbo.FactDevicePerformance_History h
INNER JOIN AIR.dbo.DimTime t ON h.TimeKey = t.TimeKey
WHERE t.[Date] BETWEEN ‘2011-1-1’ AND ‘2011-1-31’
You can find the only difference is query hint “TABLOCK”. But first query runs 21 seconds, the second one runs only 8 seconds. First query increase the log file from 0.5MB to 672MB. The second one increase the log file from 0.5MB to 3.37MB. There are 2.3 million records moved in the query.
This is what called minimal logging since SQL Server 2005. Anyway you still need to compare these two approaches before you implement. Because with minimal logging SQL Server will force data pages to be flushed to disk before the transaction commits. So if the IO system for data file is not very fast, or the data pages affected by the operation are not sequentially arranged, you might see worse performance with minimal logging.
I had a database which stores huge amount of performance counter values. The table with counter values has a clustered index with column timekey in the first sequence. And there is time dimension table with column timekey as its clustered index.
One day I issued a query to find out the earliest date in the table, which looks like:
SELECT MAX(Date) FROM dbo.FactPerformance_History p
INNER JOIN dbo.DimTime t on p.TimeKey = t.TimeKey
I found the result was not returned immediately. So I checked the execution plan. I was quite surprised the execution plan showed two big tables FactPerformance_History and the DimTime were joined first then be aggregated for the max value.
It looks like query optimizer didn’t notice two tables were joined with two physical sorted columns. It decided to start the aggregation after the entire merge join was finished. Actually the query only needs the first match record with the two tables merged reversely.
Anyway, the query optimizer didn’t do it. So I can only rewrote the query:
SELECT Date FROM (SELECT MAX(TimeKey) AS TimeKey FROM Array.[FactDevicePerformance_History]) AS p
INNER JOIN ETL.DimTime t on p.TimeKey = t.TimeKey
Two execution plans here:
First query
Second query
It took 12 seconds to return with the result the first query, but only 6ms the new one.
OK. I agree semantics of the two queries are not exact the same. But if query optimizer can make the merge and stream aggregate run in asynchronously, then I believe the execution time of two plan above will be same.
drillchina 