…MySQL manual, especially …show_procedure_codeblogpercona.commanual Com_show_procedure_…row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows_insertedblogpercona.commanual Innodb_rows_readblogpercona.commanual Innodb_rows…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… MySQL stored procedure API, called the SQL_API which is used to define the view. These stored procedures maintain … than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select …
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…mysql> DELIMITER // mysql> CREATE PROCEDURE test_read(pl INT) -> BEGIN -> DECLARE t INT; -> SET @x = 0; -> REPEAT SET @x = @x + 1; -> SELECT COUNT…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…lot larger problems with MySQL 5.5 with addition of user variables, stored procedures, prepared statements etc …you might want to look at Prepared_stmt_count to see how many prepared statements are … ENGINE: MEMORY NAME: #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257 DATA_LENGTH: 126984 INDEX_LENGTH…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
… in statement mode. The row-based format does offer advantages particularly if triggers or stored procedures are used, or if …play with: select count(*) from t1; +———-+ | count(*) | +———-+ | 65536 | +———-+ 1 row in set (0.02 sec) My test setup includes a MySQL slave. After …
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…rows –start-position 336797275 mysql-bin.000022 | mysql mysql> select count(*) from salaries where emp_no = 10001; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql… this method can be good quick rollback procedure in case something fails during the operation. *…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count…procedures…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… see how fast the SQL procedure code is at … for each row: mysql> explain select ….\G *************************** 1. row *************************** id: 1 …mysql> delimiter // mysql> create function speaks_english(c char(3)) returns integer deterministic > begin > declare res int; > select count…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…Cache” which is quite helpful for MySQL Performance optimization tasks but there … which required processing millions of rows now can be instantly summoned … or stored procedure calls are not, even if stored procedure would simply …do not need to be cached. Counting query cache efficiency There are …
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…unless you have complex queries. Sorting 10 rows will be as fast with 1MB … Here is why: List of rarely considered MySQL Server Memory Requirements Thread buffers can …step, especially specially crafted ones. Stored Procedures. Compex stored procedures may require a lot of … as you might have counted…
