… it is a lot larger problems with MySQL 5.5 with addition of user variables, stored procedures, prepared statements etc which can be a memory hog. My intent with this post is dual. I would encourage MySQL Team… might look at how it uses MySQL to identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the… other tools used by mysql client, so you might get a feel you can’t connect to the database at all (use mysql -A… just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using…
Post: MySQL Stored Procedures problems and use practices
June 12, 2007 By 21 Comments
… while back MySQL 5.0 was about 10% slower than 4.1 without stored procedures but was 20% faster if Stored Procedures are used. And this is of course not the limit. You may also have other reasons to use Stored Procedures besides performance…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
…all your stored procedures in your slaves servers. Example: mysql> create procedure this_pyear() BEGIN INSERT INTO t VALUES(YEAR(CURDATE())); END;// mysql> CALL this…actual used auto incremental value is logged as an extra event just before the statement. MySQL uses the INSERT_ID variable to store …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… views which use the complete refresh method, incrementally refreshable views are not built directly from SQL. Instead, Flexviews includes a MySQL stored procedure API, called the SQL_API which is used to define the view. These stored procedures maintain the Flexviews data…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… in MySQL 5.1 Disk Space For MyISAM tables hosting providers used to use disk quotas for MyISAM tables. You can also use similar… to run their queries. Stored Procedures – How much memory can stored procedure allocate ? say can you create 1000 variables in stored procedure and set 1M result…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
… whole transaction is inside a stored procedure? Whether this works or not depends, again, on how the stored procedure call is logged to the…) What you see depends on your version of MySQL, because the logging of stored procedures has changed over time. If just the CALL statement had been logged, I think we might have been safe using a stored procedure…
Post: Filtered MySQL Replication
November 7, 2007 By 7 Comments
… shared here mainly applies to logical level replication. MySQL 5.1 can use row level replication which alleviates some of these issues… this query is using. This works in much larger amount of cases, but not all of them. For example stored procedures are handled (because binary logging is done on statements as executed inside stored procedures), however Stored Functions are not – so…
Post: Profiling MySQL stored routines
January 19, 2009 By 3 Comments
… an application based entirely on stored routines on MySQL side. Even though I haven’t worked much with stored procedures, I though it’s… statistics available for this query SELECT whatstheweatherlike(5); Not overly useful for query optimization, is it? Well, I can still aggregate…
Post: MySQL Slow query log in the table
May 31, 2007 By Leave a Comment
As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had… over and over again you can create stored procedure, something like LAST_SLOW_QUERIES and use it instead, just remember unless you add… queries which is used if log table is locked. One thing which would be rather handy is UDF or stored procedure to “normalize…
