I just made MyQuery 3.5.0 available for download from sourceforge. If you do not know about this program, this is my Windows based MySQL Query editor and database monitor program. And yes, it is Windows only.
It was a long time since I updated MyQuery, but I have been been at it for a long time, but slowly as I haven't had the bandwidth, and I have collected a few things I wanted in this release, but of all those things, many has to wait for now, as decided to add a major feature which did require a fair bit of coding and testing: autocomplete. When editing SQL statements, autocomplete for names of tables, columns etc is really useful, but it is not as easy as it sounds. What makes this a bit complicated are things like the default database (which I need to figure out for anything that is typed), table aliases and separate name spaces (which is a good thing, but makes like in this instance difficult).
Table aliases are particularly nasty as MyQuery really doesn't parse the SQL but just uses the current names of all objects. So when I type table1. you might expect a lit of all columns in the table table1 to be listed, but it isn't as easy as that as table1 might be an alias for another table. The way I manage this right now is to list all columns in all tables when I see something which looks like a table name (table1 in this case), but there is no such table (and no such database for that matter), I list all columns in all tables in the current database. This might not be what you want, but it's the best I can do right now.
To be honest, I really think autocomplete is a terribly useful feature, in particular when dealing with databases with many tables or tables with long names (such as when working with the information_schema database).
Another thing I have added in this release is a more flexible way of dealing with database status variables, which can now be organized into groups, which are easy to manage and which are persisted.
What I want to add is stuff like MySQL 5.6 information_schema additions, MariaDB specific functions and engines, JSON load and unload and some other MySQL 5.6 stuff. In addition, I'd like to do some integration with my sqlstats plugin. But now I have at least finished MyQuery 3.5.0, and I'm quite happy with it. If you have used earlier versions of MyQuery, this version also fixes a bunch of minor memory leaks and at least one crashing bug (which happened in some instances in the connect dialog).
Happy SQLing!
/Karlsson
Tuesday, October 23, 2012
Tuesday, October 9, 2012
Too many or too big open MyISAM tables??
MySQL tries to be smart and to cram as much performance out of available hardware, and one thing that most MySQLers knows is that opening a table (ie. opening the frm file, and depending on the storage engine, any additional files related to the table) is expensive from a performance point of view. When we see the opened_tables status variable increasing, we tend to increase the table_open_cache setting and keep as many tables as possible open and avoid reopening them.
When it comes to MyISAM though, this has a nasty side-effect. When the server crashes and there are MyISAM tables open, these might well need to be REPAIRed. And if you have big MyISAM tables, this is an issue. Let's say that your application use several MyISAM tables, with the same content, and that you create new tables after a certain time, to keep the size of each individual table down? There are other reasons why you have this effect also, say a large MyISAM table that this rarely updated or read from, but suddenly it is, and then it is kept alone again? When a crash occurs, you might be stuck with several MyISAM tables that are open, but have not been accessed in a long time so might not need to be open, but still there is a risk that these will require a long and boring REPAIR.
There is a Feature request for this; 67142, but I was thinking that instead of having low level server code do this, this would be a good thing to implement using EVENTs. So let's give it a shot.
To being with, I need to figure out when a table was last touch. MySQL doesn't record when a table was last read from, but if we are OK with flushing tables that haven't been written to in a specific time, then the update_time column in the information_schema table does the trick. To figure out what tables to flush, I need to select table and database names from this table for all tables that haven't been modified within a certain time. Also, I must filter so I only get MyISAM tables, and make sure that I don't hit the MyISAM tables in the mysql database. An appropriate SELECT may look like this:
SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema != 'mysql'
AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(update_time) > <seconds>
That's not enough of course, all this gets me are the table and database names, we need to do something more than this. But let's start from the beginning. An EVENT will run an SQL statement, and in my world, there is mostly one statement it should run, which is a CALL to a stored procedure. And in this case, this procedure should use the SQL above to figure out what tables to flush, and then do the actual flushing. As the FLUSH command, when using inside a MySQL Routine, will not take any parameters, we have to run this as a PREPARED statement. All in all, when we end up with is something like this:
DELIMITER //
DROP PROCEDURE IF EXISTS myisamflush;
CREATE PROCEDURE myisamflush(p_sec INTEGER)
BEGIN
DECLARE v_no_data BOOL DEFAULT FALSE;
DECLARE v_database VARCHAR(64);
DECLARE v_table VARCHAR(64);
DECLARE v_dummy INTEGER;
DECLARE cur1 CURSOR FOR SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema != 'mysql'
AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(update_time) >
p_sec;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_data = TRUE;
OPEN cur1;
-- Loop for all MyISAM-tables that haven't been updated
-- for p_sec seconds.
FETCH cur1 INTO v_database, v_table;
WHILE NOT v_no_data DO
-- Create the FLUSH statement.
SET @tmpstmt = CONCAT('FLUSH TABLES ', v_database, '.', v_table);
-- Prepare and execute the statement.
PREPARE stmt1 FROM @tmpstmt;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-- Get the next table / database.
FETCH cur1 INTO v_database, v_table;
END WHILE;
CLOSE cur1;
-- This is to overcome a bug that causes a warning,
-- even though the warning was caught.
SELECT COUNT(*) INTO v_dummy FROM mysql.user;
END;
//
DELIMITER ;
Note that noop SELECT at the end? In some versions of MySQL, when a handler is run, like in this case the CONTINUE HANDLER FOR NOT FOUND warnings will still be around here, which means the execution of the procedure will finish with a warning. To get rid of that, I issue an SQL statement that always runs.I'm not sure why this is happeniing, and for it might be that I have a mistake somewhere, but I do not think so.
So far so good, now all we need is an event, in this case I will run every 15 minutes and flush tables that hasn't been used in the last 15 minutes, but you can set this to anything:
DROP EVENT IF EXISTS myisamflush;
CREATE EVENT myisamflush
ON SCHEDULE EVERY 15 MINUTE
DO CALL myisamflush(900);
Was this enough? Maybe, but to get this working check if the event scheduler is running:
mysql> SHOW VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
In this case, it wasn't running so we should start it (and we should also modify the config file so that the event scheduler is running when the server is restarted, but that is a different story):
mysql> set global event_scheduler=1;
Before we finish up, note that everything above assumes that you are running with root privileges or similar.
Cheers
/Karlsson
When it comes to MyISAM though, this has a nasty side-effect. When the server crashes and there are MyISAM tables open, these might well need to be REPAIRed. And if you have big MyISAM tables, this is an issue. Let's say that your application use several MyISAM tables, with the same content, and that you create new tables after a certain time, to keep the size of each individual table down? There are other reasons why you have this effect also, say a large MyISAM table that this rarely updated or read from, but suddenly it is, and then it is kept alone again? When a crash occurs, you might be stuck with several MyISAM tables that are open, but have not been accessed in a long time so might not need to be open, but still there is a risk that these will require a long and boring REPAIR.
There is a Feature request for this; 67142, but I was thinking that instead of having low level server code do this, this would be a good thing to implement using EVENTs. So let's give it a shot.
To being with, I need to figure out when a table was last touch. MySQL doesn't record when a table was last read from, but if we are OK with flushing tables that haven't been written to in a specific time, then the update_time column in the information_schema table does the trick. To figure out what tables to flush, I need to select table and database names from this table for all tables that haven't been modified within a certain time. Also, I must filter so I only get MyISAM tables, and make sure that I don't hit the MyISAM tables in the mysql database. An appropriate SELECT may look like this:
SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema != 'mysql'
AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(update_time) > <seconds>
That's not enough of course, all this gets me are the table and database names, we need to do something more than this. But let's start from the beginning. An EVENT will run an SQL statement, and in my world, there is mostly one statement it should run, which is a CALL to a stored procedure. And in this case, this procedure should use the SQL above to figure out what tables to flush, and then do the actual flushing. As the FLUSH command, when using inside a MySQL Routine, will not take any parameters, we have to run this as a PREPARED statement. All in all, when we end up with is something like this:
DELIMITER //
DROP PROCEDURE IF EXISTS myisamflush;
CREATE PROCEDURE myisamflush(p_sec INTEGER)
BEGIN
DECLARE v_no_data BOOL DEFAULT FALSE;
DECLARE v_database VARCHAR(64);
DECLARE v_table VARCHAR(64);
DECLARE v_dummy INTEGER;
DECLARE cur1 CURSOR FOR SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema != 'mysql'
AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(update_time) >
p_sec;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_data = TRUE;
OPEN cur1;
-- Loop for all MyISAM-tables that haven't been updated
-- for p_sec seconds.
FETCH cur1 INTO v_database, v_table;
WHILE NOT v_no_data DO
-- Create the FLUSH statement.
SET @tmpstmt = CONCAT('FLUSH TABLES ', v_database, '.', v_table);
-- Prepare and execute the statement.
PREPARE stmt1 FROM @tmpstmt;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-- Get the next table / database.
FETCH cur1 INTO v_database, v_table;
END WHILE;
CLOSE cur1;
-- This is to overcome a bug that causes a warning,
-- even though the warning was caught.
SELECT COUNT(*) INTO v_dummy FROM mysql.user;
END;
//
DELIMITER ;
Note that noop SELECT at the end? In some versions of MySQL, when a handler is run, like in this case the CONTINUE HANDLER FOR NOT FOUND warnings will still be around here, which means the execution of the procedure will finish with a warning. To get rid of that, I issue an SQL statement that always runs.I'm not sure why this is happeniing, and for it might be that I have a mistake somewhere, but I do not think so.
So far so good, now all we need is an event, in this case I will run every 15 minutes and flush tables that hasn't been used in the last 15 minutes, but you can set this to anything:
DROP EVENT IF EXISTS myisamflush;
CREATE EVENT myisamflush
ON SCHEDULE EVERY 15 MINUTE
DO CALL myisamflush(900);
Was this enough? Maybe, but to get this working check if the event scheduler is running:
mysql> SHOW VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
In this case, it wasn't running so we should start it (and we should also modify the config file so that the event scheduler is running when the server is restarted, but that is a different story):
mysql> set global event_scheduler=1;
Before we finish up, note that everything above assumes that you are running with root privileges or similar.
Cheers
/Karlsson
Monday, October 1, 2012
Moving on and flying high...
As of today, Oct 1, yours truly is again working closer to the MySQL community. I have had 2 great years at Recorded Future, no doubt, and I have had a chance to work with some cool technologies and learn quite a few things I didn't know before. But not it's tim to high the sky, and I am from today working as Sales Engineer at SkySQL.
This is going to be fun, I will keep up my blogging, I hope to keep up my MongoDB investigations and above all I'll be involved with the SkySQL Cloud offerings!
That's all for now, but if you know me, that's not the end of it, far from it!
Cheers
/Karlsson
This is going to be fun, I will keep up my blogging, I hope to keep up my MongoDB investigations and above all I'll be involved with the SkySQL Cloud offerings!
That's all for now, but if you know me, that's not the end of it, far from it!
Cheers
/Karlsson
Subscribe to:
Posts (Atom)