On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it’s hard to make a general rule… using replication, a temporary table could vanish if the slave restarts and then subsequent queries against the temp table fail. Q: Why doesn…
Comment: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
April 4, 2013 By 17 Comments
… i converted databases from MyISAM to InnoDB, mysql tunning script gives below results for temp table , server has 4G ram, which almost uses…_table_size) , what values you experts are recomended ? TEMP TABLES Current max_heap_table_size = 128 M Current tmp_table_size = 128 M Of 21559 temp tables…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
… MySQL 5.6. This option can be used to produce 5.6-style metadata files, that can be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs Fixed… copying failure. Bug fixed #1170806. Percona XtraBackup would leave xbtemp temp files behind due to a typo. Bug fixed #1172016. innobackupex…
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
…, XtraBackup drives down backup costs while providing unique features for MySQL backup. The new 2.1.1 GA version offers improved…. innobackupex now uses Perl’s DBD::MySQL package for server communication instead of spawning the MySQL command line client. Support for InnoDB… would leave xbtemp temp files behind due to a typo. Bug fixed #1172016. Percona XtraBackup would assume the table has been dropped…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, …_tmpdirblogpercona.commanual slave_net_timeoutblogpercona.commanual Slave_open_temp_tablesblogpercona.commanual Slave_received_heartbeatsblogpercona.commanual Slave_retried_…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…_Log_File: mysql-bin.000007 …. omitted …….. Exec_Master_Log_Pos: 207 slave1 > show status like ‘%temp%’; +————————+——-+ | Variable_name | Value | +————————+——-+ | Slave_open_temp_tables | 1…, I still don’t see any way to use temporary tables with MySQL statement-based replication without some risk of breaking slaves…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
…_commands: 85 Commit_transactions: 0 Rollback_transactions: 0 mysql> SHOW TABLE_STATISTICS LIKE ‘hosting2%’; +——————————–+———–+————–+————————-+ | Table | Rows_read | Rows_changed | Rows_changed_x_#indexes… under Other_commands a few MySQL performance counters broken down by users (full joins, table scans, disk temp tables, etc.) limiting command availability to…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… and grouping and DISTINCT and UNION may/will also create temp tables; the same applies to views and probably some other things…_allowed_packet bigger and ran the following on my laptop: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1…
Post: Introducing our Percona Live speakers
May 10, 2011 By 4 Comments
… CTO and President at Schooner, who makes a high-performance MySQL software appliance. (It used to be hardware-only, if you….) His session will be about benchmarks of replication alternatives for MySQL. Hasn’t this been done before, and is it really…. That’s a nightmare of temp tables and filesorts and multiple queries and all sorts of things in MySQL — and that’s just…
Comment: Can MySQL temporary tables be made safe for statement-based replication?
May 27, 2008 By 8 Comments
… that I have my code surrounded by START TRANSACTION; COMMIT; mysql> SHOW binlog events IN ‘bin-logs.000440′\G *************************** 1. row…. If I create two temp tables, the BEGIN occurs between the two, however if I do create temp table, drop temp table, create temp table, the BEGIN occurs…
