Cerberus ® - business approach to monitoring
MySQL Remote Database Administration, Monitoring and Support
- SATS Remote DBAis dedicated to optimizing database uptime and performance, and to reducing the cost of IT ownership. Our Like Clockwork RDBA service saves you time, recourses, continuity, and helps you to ally IT tasks with the business goals of your organization. Many other companies chose SATS for our dependability, cost-effectiveness, and expert technological support. SATS RDBA advantages help our customers achieve costs savings of up to 75% while increasing the quality of DBA coverage.
- SATS Remote DBA could be a one stop service provider for all this platforms:
- Oracle, PostgreSQL, Hadoop, MS SQL, MySQL, Mongo DB
For specific inquiries please contact SATS RDBA Services or call 650 889-5533.
Speeding up MySQL by using Materialized Views
PROBLEM CASE
Sometimes we would like to allow frequent access to big and active tables, we have already discuss mat-views in PostgreSQL ( Speeding up PostgreSQL by using Matviews )
and by popular demand - enjoy the MySQL port.
Similar to PostgreSQL, this materialized view logic is missing natively in MySQL,
however this could be developed easily with SQL logic.
Though MySQL's query cache is pretty helpful in many situations, the fact is every entry gets invalidated very fast
in an active database, rendering it almost useless, mat-views in MySQL are a great way of taking full
advantage of the query cache as well.
So without further ado..
-
Step 1 - Creation of matviews functions and data dictionary
Create the mat view table (data dictionary) this will include the original view for each mat view and the last refresh date and duration as well.
CREATE TABLE matviews (
mv varchar(64) NOT NULL PRIMARY KEY ,
view varchar(64) NOT NULL ,
last_refresh TIMESTAMP ,
refresh_time INTEGER );
Once this is done let's create the create_matview, drop_matview and refresh_matview as per below:
DROP procedure IF EXISTS create_matview;
CREATE procedure create_matview( matview varchar(64), view_name varchar(64))
NOT DETERMINISTIC
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'ERROR! a matview or table with that name exists. will not proceed' as ERR;
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
SELECT 'ERROR! a matview with that already name exists. will not proceed' as ERR;
IF (select count(*)
from information_schema.tables
where table_schema in (select database())
and table_name=matview
) THEN
call raise_error;
END IF;
SET @time_start = CURRENT_TIMESTAMP();
SET @crtbl= CONCAT('CREATE TABLE ',matview,' as select * from ', view_name);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @time_stop = CURRENT_TIMESTAMP();
SELECT @time_start as Refresh_start, @time_stop as Refresh_end;
INSERT INTO matviews (mv, view, last_refresh,refresh_time) VALUES (matview, view_name, @time_start, UNIX_TIMESTAMP(@time_stop) -UNIX_TIMESTAMP(@time_start));
END//
delimiter ;
delimiter //
DROP procedure IF EXISTS refresh_matview;
CREATE procedure refresh_matview( matview varchar(64))
NOT DETERMINISTIC
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'ERROR! a matview with that name does not exist, will not proceed' as ERR;
IF (select count(*)!=1
from matviews
where mv= matview
) THEN
call raise_error;
END IF;
START TRANSACTION;
SET @time_start = CURRENT_TIMESTAMP();
SET @v_name = (SELECT view from matviews where mv=matview limit 1);
SET @crtbl= CONCAT('DELETE FROM ',matview);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @crtbl= CONCAT('INSERT INTO ',matview,' SELECT * FROM ',@v_name);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @time_stop = CURRENT_TIMESTAMP();
SELECT @time_start as Refresh_start, @time_stop as Refresh_end;
update matviews set last_refresh = @time_start, refresh_time=UNIX_TIMESTAMP(@time_stop) -UNIX_TIMESTAMP(@time_start) where mv=matview;
COMMIT;
END//
delimiter ;
delimiter //
DROP procedure IF EXISTS drop_matview;
CREATE procedure drop_matview( matview varchar(64))
NOT DETERMINISTIC
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'ERROR! a matview with that name does not exist, will not proceed' as ERR;
SET AUTOCOMMIT=0;
IF (select count(*)!=1
from matviews
where mv= matview
) THEN
call raise_error;
END IF;
START TRANSACTION;
SET @crtbl= CONCAT('drop table ',matview);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
delete from matviews where mv=matview;
COMMIT;
END//
delimiter ;
There is a (simple) security mechanism while creating the mat view in order to check for existing tables with that name (caution - no such mechanism exists on refresh).
Please only use the stored procedures (create_matview, refresh_matview, drop_matview) and don't edit matview table in any way (or do at your own risk).
-
Step 2 - Create a source view ( in this case V_TOP_A_B)
mysql> CREATE VIEW V_TOP_A_B AS
-> select V.OBJECT_ID, V.OBJECT_NAME from
-> A join B using (OBJECT_ID)
-> where A.DATE IN (CURRENT_DATE, CURRENT_DATE - INTERVAL 7 DAY)
-> GROUP BY A.OBJECT_ID
-> ORDER BY SUM(A.foo) desc
-> LIMIT 0,250000;
Query OK, 0 rows affected (0.00 sec)
-
Step 3 - Create mat-view
For this we will be using create_matview procedure (first parameter is destination MV, second is source view\table)
mysql> call create_matview('MV_TOP_A_B','V_TOP_A_B');
+--------------------------+--------------------------+
| Refresh_start | Refresh_end |
+--------------------------+--------------------------+
| 2013-11-01 11:40:11 | 2013-11-01 11:40:17 |
+--------------------------+--------------------------+
1 row in set (6.56 sec)
Query OK, 1 row affected (6.56 sec)
Now we can compare the runtime of the source view vs the runtime of the mat-view
mysql> select SQL_NO_CACHE * from V_TOP_A_B limit 5; select SQL_NO_CACHE * from MV_TOP_A_B limit 5;
+----------------+-------------------+
| OBJECT_ID | OBJECT_NAME |
+----------------+-------------------+
| 20 | foo.com |
| 1011111112 | foo2.com |
| 443 | foo3.com |
| 1331859 | foo4.com |
| 12230 | foo5.com |
|
+----------------+------------------+
5 rows in set (4.77 sec)
+----------------+------------------+
| OBJECT_ID | OBJECT_NAME |
+----------------+------------------+
| 20 | foo.com |
| 1011111112 | foo2.com |
| 443 | foo3.com |
| 1331859 | foo4.com |
| 12230 | foo5.com |
+----------------+------------------+
5 rows in set (0.03 sec)
In this test case, improvement is by 150 times (!!)
-
Step 4 - Additional Usage Notes
Refresh should be done using the refresh_matview stored procedure.
mysql> call refresh_matview('MV_TOP_A_B');
+--------------------------+--------------------------+
| Refresh_start | Refresh_end |
+--------------------------+--------------------------+
| 2013-11-01 11:42:10 | 2013-11-01 11:42:24 |
+--------------------------+--------------------------+
1 row in set (14.15 sec)
Query OK, 1 row affected (14.16 sec)
You can add a small cron to do that for you (at whatever intervals you would like), on Linux - you can schedule this bash script for example:
#!/bin/bash
mysql -ufoo foobar -e "call refresh_matview('MV_TOP_A_B')"
This is the dictionary (includes last refresh time and duration), feel free to use in order to keep tabs on the mat-views and refresh status.
mysql> select * from mat-views;
+---------------------+--------------------+---------------------+--------------+
| mv | view | last_refresh | refresh_time |
+---------------------+--------------------+---------------------+--------------+
| MV_TOP_A_B | V_TOP_A_B | 2013-11-01 11:42:10 | 14 |
+---------------------+--------------------+---------------------+--------------+
1 row in set (0.00 sec)
You can create any indexes you would like on the MV itself to give an additional boost if needed.
Questions/Comments? - feel free to contact Cerberus (which is me) on
RDBA.net forums under Oracle DBA corner.