Below is a command to check Vertica cluster status via admintools.
admintools -t view_cluster
Cheers!
Below is the command to view the number of ROS containers per projection per node. A high number (over 500) of ROS containers indicates a problem with the PARTITION BY clauses of your tables. It can also indicate that the merging process is not functioning.
For more information, see Vertica Partitions: The FAQs in the Vertica Knowledge Base.
SELECT node_name, projection_schema, projection_name, SUM(ros_count) AS ros_count FROM v_monitor.projection_storage GROUP BY node_name, projection_schema, projection_name ORDER BY ros_count DESC;
In this blog post, I will demonstrate how to partition your tables. I have created a sample database called deptstore.
There are 4 general steps in partitioning tables in SQL Server.
You can create two types of partition: RANGE partition and LIST partition.
In this post, I will only enumerate the procedures in creating RANGE partition but the process is essentially the same for creating LIST partition.
1. Create FileGroups for each of your partition.
USE [master] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q12008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q12008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q12008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q12008] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q22008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q22008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q22008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q22008] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q32008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q32008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q32008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q32008] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q42008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q42008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q42008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q42008] GO
2. Create a Partition Function. This defines how your table is going to be partitioned. You have to specify the data type that your column is dealing with. Also you have to specify the partition/dividing points.
CREATE PARTITION FUNCTION date_part_func (DATETIME) AS RANGE RIGHT FOR VALUES('20080401','20080701','20081001')
Note: The word RIGHT means that if data we are dealing with is exactly 01 July, then it will go to the right partition. In other words it will go to the partition that is in the 3rd Quarter.
If we say LEFT values, then the records will go to quarter 2.
The screenshot below shows you where you can find the create partition function in SSMS.
3. Create a Partition Scheme. This will bind particular paritions with the appropriate FileGroups.
CREATE PARTITION SCHEME date_part_scheme AS PARTITION date_part_func TO (q1,q2,q3,q4)
Below is where you can find the created partition scheme in SSMS
4. Create a table that is going to use that Partition Scheme. Ensure that the table you are creating has a column with the data type that you mentioned above (DATETIME).
CREATE TABLE invoices ( id INT NOT NULL ,inv_date DATETIME ,balance NUMERIC ) ON date_part_scheme(inv_date) --specify partitioning column
To test, insert records to the table
INSERT INTO invoices VALUES(1,'20081204',5000) INSERT INTO invoices VALUES(2,'20080202',3000) INSERT INTO invoices VALUES(3,'20080803',5000)
Check which records went in which partition.
SELECT $PARTITION.date_part_func(inv_date) partition, [id], inv_date, balance FROM invoices;
Note: If we insert data that is not in year 2008? That data will just go to the last partition. It is considered best practice to have your partitions on the left side and right hand (both sides) to be like a catch all partition.
Check partition info
SELECT pf.name AS pf_name , ps.name AS partition_scheme_name , p.partition_number , ds.name AS partition_filegroup , OBJECT_NAME(si.object_id) AS object_name , rv.value AS range_value , SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows ELSE 0 END) AS num_rows FROM sys.destination_data_spaces AS dds JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id AND dds.destination_id = CASE pf.boundary_value_on_right WHEN 0 THEN rv.boundary_id ELSE rv.boundary_id + 1 END LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id AND si.index_id = p.index_id AND dds.destination_id = p.partition_number LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id AND p.partition_id = dbps.partition_id GROUP BY ds.name , p.partition_number , pf.name , pf.type_desc , pf.fanout , pf.boundary_value_on_right , ps.name , si.object_id , rv.value ORDER BY p.partition_number GO
SPLITTING PARTITIONS
Before we split any partitions, we need to actually create new FileGroups to hold the new partition.
USE [master] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q12009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q12009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q12009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q12009] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q22009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q22009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q22009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q22009] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q32009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q32009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q32009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q32009] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q42009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q42009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q42009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q42009] GO
Alter the partition scheme. Tell it which partition to use next. And then you are going to alter the Partition Function to specify the new split value.
ALTER PARTITION SCHEME date_part_scheme NEXT USED q12009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20090101') ALTER PARTITION SCHEME date_part_scheme NEXT USED q22009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20090401') ALTER PARTITION SCHEME date_part_scheme NEXT USED q32009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20090701') ALTER PARTITION SCHEME date_part_scheme NEXT USED q42009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20091001')
NOTE: It is considered a best practice to have your right and left most partitions to be empty. This is so that when you split it, it will not be a very intensive operation. In other words, the end partitions you want to have those empty so that the split can happen very quickly.
Insert records to the table to see if it goes to the new partitions
INSERT INTO invoices VALUES(1,'20091204',5000) INSERT INTO invoices VALUES(2,'20090202',3000) INSERT INTO invoices VALUES(3,'20090803',5000)
Cheers!
Problem:
We encountered the Error 9002 ansaction Log Full Due to ‘CHECKPOINT’.
Solution:
I was able to resolve it by rebuilding the log. Below are the scripts that I used.
ALTER DATABASE DatabaseName set EMERGENCY ALTER DATABASE DatabaseName REBUILD LOG ON (NAME='DatabaseName_log',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseName_Log.LDF') ALTER DATABASE DatabaseName set ONLINE ALTER DATABASE DatabaseName SET MULTI_USER
Below is a script to get the logical name and filepath.
SELECT DB_NAME ([database_id]) AS [Database Name] ,[file_id] ,name ,physical_name ,type_desc ,state_desc ,CONVERT(bigint, size/128.0) AS [Total Size in MB] ,CONVERT(decimal(18,2), size/131072.0) AS [Total Size in GB] FROM sys.master_files WITH(NOLOCK) WHERE [database_id] > 4 AND [database_id] <> 32767 OR [database_id] = 2 ORDER BY DB_NAME ([database_id]) OPTION(RECOMPILE)
Cheers!
In this blog post, I will enumerate the steps to upgrade MySQL 5.5 to 5.7.
This is also known as the Logical Upgrade which involves exporting SQL from the old MySQL instance using a backup utility (mysqldump), installing the new MySQL server, and applying the SQL to your new MySQL instance.
1. Backup all your databases
mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > /backup/data-for-upgrade.sql
Note: Use the --routines
and --events
options with mysqldump (as shown above) if your databases include stored programs. The --all-databases
option includes all databases in the dump, including the mysql
database that holds the system tables.
2. Stop MySQL 5.5 instance
service mysqld stop
3. Move old datadir
mv /var/lib/mysql/ /var/lib/mysql-55
4. Install MySQL 5.7
5. Start MySQL
service mysqld start
6. Login to mysql with new root password
# Get temporary root password grep 'temporary password' /var/log/mysqld.log # Login to MySQL mysql –u root –p # Change root password ALTER USER 'root'@'localhost' IDENTIFIED BY 'password’;
7. Restore databases
mysql -u root -p < /backup/data-for-upgrade.sql
We have just performed the most cleanest and most stable upgrade path.
Cheers!
In this blog post, I will show you the different methods to stop and start an Oracle RAC instance and an Oracle RAC database.
1. Check the status of the database by using srvctl utility. (execute srvctl on unix prompt)
srvctl status database -d orclrac
2. Stop the instance instrac
srvctl stop instance -d orclrac -i instrac -o immediate
3. Verify that the instance instrac is down
# check out the status of instracin the output of the following command: srvctl status database -d orclrac # check out the processes in the OS level ps -ef | grep pmon # connect to the database and verify to which instance the session is connected sqlplus system/oracle@rac SELECT INSTANCE_NAME FROM V$INSTANCE;
4. To stop two instances (e.g. instrac1, instrac2) in a single srvctl utility command.
srvctl stop instance -d rac -i instrac1,instrac2 -o immediate
5. Startup the database using the srvctl utility
Note: the startoption is optional and it defaults to open. With start command, it accepts the open, mount and nomout options.
srvctl stop instance -d rac -i instrac1,instrac2 -o immediate
6. Checkout the status of the database
srvctl status database -d orclrac