Friday, April 25, 2014

MySQL Partitions timestamp - datetime

So I recently realized that I have not yet talked much about MySQL partitions.
Many good blog posts on MySQL partitions already exists and I have listed a few below.
I happen to come across the following situation and I hope it proves helpful to others.

While the timestamp data type is often used datetime works better with partitions.
( timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

So the following it an example how how to fix this.

To start with we have this simple table.

CREATE TABLE `t1` (
  `t1_id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(25) DEFAULT NULL,
  `field2` int(10) DEFAULT '0',
  `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`t1_id`),
  KEY `tr` (`time_recorded`)
) ENGINE=InnoDB AUTO_INCREMENT=856964


We want to partition on the time_recorded field. To make it direct we will break it out per month.


ALTER TABLE t1
 PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
     PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
     PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
     PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
     PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
     PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
     PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
     PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
     PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
     PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
     PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
     PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
     PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
     PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
 );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed


So now what....

Well I know we need the Primary key updated if we want the partition.

 ALTER TABLE t1  DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE;
Query OK, 0 rows affected (38.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

Notice that I have LOCK=NONE this is because I am using MySQL 5.6
 The primary key change will not allow me to add the partitions though, I still need to adjust the datatype.  I am using the LOCKED=SHARED this time. Please review the hyperlinks above for more information. If you happen to pick a type that does not work, it usually suggests a workable solution.

 ALTER TABLE t1 CHANGE  time_recorded  time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED;
Query OK, 854312 rows affected (41.89 sec)
Records: 854312  Duplicates: 0  Warnings: 0


So now we can add our partition.

 ALTER TABLE t1
    ->  PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
    ->      PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
    ->      PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
    ->      PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    ->      PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
    ->      PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
    ->      PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    ->      PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
    ->      PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
    ->      PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    ->      PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
    ->      PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
    ->      PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    ->      PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
    ->  );
Query OK, 854312 rows affected (50.74 sec)
Records: 854312  Duplicates: 0  Warnings: 0


We can SELECT,DELETE,UPDATE,INSERT and etc  per partition. More on that here: https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html.

SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|       661752 |
+--------------+
1 row in set (0.55 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|        64952 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|        71336 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|        56272 |
+--------------+
1 row in set (0.05 sec)


While this works and we do now have a partition. We also now have to consider the partition maintenance.  Glynn's blog gives a very nice example of an automated Dropping and adding of partitions. Granted if you do not want to drop the partition you will need to adjust the example.
I have adjusted it for my table example.  Please review Glynn's blog for more details.


DROP PROCEDURE IF EXISTS Rotate_t1_Partition;
DELIMITER ;;
CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME)
BEGIN
  -- Setup
  DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
  DECLARE partitionToDrop VARCHAR(64);

  -- Find and drop the first partition in the table.
  SELECT partition_name
    INTO partitionToDrop
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_schema='forums_mysql'
    AND table_name='t1'
    AND partition_ordinal_position=1;
  SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ',  partitionToDrop);
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;

  -- Add a new partition using the input date for a value limit.
  SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN (TO_DAYS(\'',  DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))');
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;

  -- Cleanup
  SET @stmt = keepStmt;
END;;
DELIMITER ;


So I can update this table now with the following easily. 

CALL Rotate_t1_Partition('2015-03-01');
Query OK, 0 rows affected (1.11 sec)


You can also, as Glynn's blog points out, you can use this with a NOW() +interval 1 MONTH or which ever time frame you decide for your partitions. Grant you have to keep in mind that it will fail if you pass a date that is already a partition.

To continue with the automation you can add this to an EVENT.

 CREATE EVENT Rotate_t1_Partition
    -> ON SCHEDULE EVERY 1 MONTH
    -> DISABLE ON SLAVE
    -> COMMENT 'Remove oldest partition and add a new one '
    -> DO
    -> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH);
Query OK, 0 rows affected (0.04 sec)


Keep in mind a valid time for the above example. I just used this as an example. 


Saturday, April 19, 2014

Heartbleed secure & MySQL

Well a lot of concern, and rightfully so, has been made about the heartbleed bug recently.

I do not think I should try to add much more than those that I consider experts have already mentioned. If you have not reviewed the following posts you should.
It comes down to a couple of different aspects.
  • Update and secure your OS first. 
    • IF you use SSL with MySQL then 
      • Stop MySQL
      • Create new certs for your connections. 
        • That should include new expire dates and etc.
      •   Start MySQL  
Patching your OpenSSL is pretty straight forward.  You need to update to openssl-1.0.1e-16+
It is very likely that your code repositories will have this update, at least that is what I have seen so far.


# yum list openssl   resulted in 1.0.1e-16.el6_5.7


 Via Fedora 20 for example

# rpm -q openssl
openssl-1.0.1e-30.fc20.x86_64

#yum update openssl

Installed  1:1.0.1e-37.fc20.1

So the steps are easy to follow, it is up to you to ensure your system is secure.



Monday, April 7, 2014

WebScaleSQL Installation solved... part 2

This is a follow up to the :  WebScaleSQL Installation attempted... part 1

So being a typical geek, developer, idiot or whatever.. I failed to RTFM or in this case the FAQ.

So once I paid attention and didn't just rush into it,  I installed WebScaleSQL correctly.
The biggest problem was that I was using the Linux OS (Oracle Linux VM) which is just too old and  the Oracle repositories did little to help get it upgraded. While I wanted to start with something that everyone could use easily, that just was not it.

I downloaded Fedora 20. You can see that they already use GCC 4.8.2. 
So the point is simple enough,  if you want to use new technology, update your OS as well.

If you really want to upgrade your GCC and stay on your current OS these hyperlinks might be useful:
Below are the steps I took to get it installed. Once the dependencies are in place it is a basic source install.

#cd /usr/local/
#yum -y install gcc git readline-devel  gcc-c++ bison-devel bison cmake ncurses-devel
# gcc -v
gcc version 4.8.2 20131212 (Red Hat 4.8.2-7) (GCC)

#git clone https://github.com/webscalesql/webscalesql-5.6.git
#ln -s webscalesql-5.6 mysql
#groupadd mysql
#useradd -r -g mysql mysql
#cd mysql/
#cmake . -DENABLE_DOWNLOADS=1
-- Successfully downloaded http://googlemock.googlecode.com/files/gmock-1.6.0.zip
-- Configuring done
-- Generating done
-- Build files have been written to: /usr/local/src/webscalesql-5.6

#make
Scanning dependencies of target INFO_BIN
[  0%] Built target INFO_BIN
Scanning dependencies of target INFO_SRC
[  0%] Built target INFO_SRC
Scanning dependencies of target abi_check
[  0%] Built target abi_check
Scanning dependencies of target zlib
[  1%] Building C object zlib/CMakeFiles/zlib.dir/adler32.c.o
 .....
[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process
[100%] Built target my_safe_process
#make install
#chmod +x scripts/mysql_install_db
#yum -y install perl-Data-Dumper-Names
#./scripts/mysql_install_db --user=mysql
#chown -R mysql data
#./bin/mysqld_safe &
# ./bin/mysql

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.17    |
+-----------+

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | OFF   |
| super_read_only  | OFF   |
| tx_read_only     | OFF   |
+------------------+-------+

cd mysql-test ; perl mysql-test-run.pl 


OK now that we got it up and running... we can explore it...

Thursday, April 3, 2014

MySQL Explain & SQL_NO_CACHE

So today I was helping someone out with their database performance and ran across some poorly written queries. Now, certainly everyone makes mistakes, the goal is to do everything you can to avoid them.

So just a couple of helpful hints to do before you let a query loose into your environment.

Always execute the query via explain first. Something as simple as doing explain first will confirm no typos (since it will not run), and allow you optimize the query.
Several links already exist about how to use explain:
The goal is simple. You want valid Keys in the possible_keys and keys (not null) and when it comes to key_len you do not want every table to be 100s of rows. If you can get the first key_len to be 200 (just a number I picked as an example) then the following to be 5,4,3,2,1 and not another 200 then your query should run well. That is a very simplistic and high level statement and I suggest you review the hyperlinks listed to understand Explain more. The query I saw today had 5+ joins and a sub-select (via a join was better in the where statement) and 200+ rows for every key_len. A few adjustments can allow your query to drop from 200 seconds to 1 second or less. Always, always, always do explain.

The next hint, try to test your queries with SQL_NO_CACHE. This allows you to test the real query and optimize it the best way you can. Once it becomes cached ( if it will be ) then it will just run that much faster for you. 
One last note... Take a look at the SQL Performance tips for MySQL that once existed on the forge site but now are at - https://wikis.oracle.com/pages/viewpage.action?pageId=27263381


Tuesday, April 1, 2014

MySQLUserclone or just Copy MySQL Users

I recently ran across a MySQL Forum post that was looking for how to migrate users onto a new system.

While this could be done a number of ways, I figured it gave me a chance to play and demo the mysqluserclone tool.

So I have two databases and only one wiki user:


root@localhost [(none)]> select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.10-log |
+------------+
root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost                                                                                     |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480'                            |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+

 select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.30-log |
+------------+
show grants for wikiuser@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'wikiuser' on host 'localhost'



So now use the mysqluserclone tool


Usage: mysqluserclone --source=user:pass@host:port:socket --destination=user:pass@host:port:socket joe@localhost sam:secret1@localhost

# mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning wikiuser@localhost to user wikiuser@localhost
# ...done.

root@localhost [(none)]>  select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.30-log |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost                                                                                     |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost'                                                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Notice that it is missing a password because I did not set a password for the user. Opps that is not good enough.

Granted it works great if you want to set up new user accounts with new passwords. I could of passed the new password as part of the clone command

mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser:<PASSWORD>@localhost


But what if I do not know the passwords? I want to ensure all the clients have all their same passwords.

I could also do the process below.
  • Gather grants from DB 1 
  • Edit the file for grants I want to move
  • Edit the user file to add comments to make safe sql
  • Load the file into DB 2
select CONCAT('SHOW GRANTS FOR `',USER,'`@',HOST,';') as showgrants FROM mysql.user INTO OUTFILE '/tmp/showgrants.sql';

vi /tmp/showgrants.sql

mysql < /tmp/showgrants.sql  > /tmp/user_grants.sql

vi /tmp/user_grants.sql
:%s/Grants for/#Grants for/g
:%s/\n/;\r/g
mysql --port=3307  -u root -p  < /tmp/user_grants.sql 
> show grants for wikiuser@localhost;
+----------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost                                                                            |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480'                   |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+----------------------------------------------------------------------------------------------------------+



Now I have the same User , Host and password.