…” and “lower” characters ! Yes I often use this too, in MySQL and in Postgres, it is just one of the tricks… query in the temp table – I sort and limit the temp table and fetch it – I perform all my joins from the temp table (no freaky… trick if you need ! It works very very well with MySQL too.
Post: How To Test Your Upgrades - pt-upgrade
December 29, 2011 By 7 Comments
…database and table with –temp-database and –temp-table is recommended, this ensures separation of the test table from your production databases and tables. Use a… Lastly, although if you are upgrading your hardware alongside your MySQL version, you should also consider running these tests on …
Post: Profiling MySQL stored routines
January 19, 2009 By 4 Comments
… sec) host # tail /var/log/mysql/mysql-slow.log # Time: 090115 2:25… Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort:… | SELECT feeling INTO feels FROM weather WHERE temp = in_temp | +———-+————+————————————————————-+ 2 rows in set …
Comment: MySQL VIEW as performance troublemaker
November 22, 2010 By 57 Comments
… table at the start of the process “create temporary table my_temp as (select * from my_view)”, and do the selects on the temp table. Yet, mysql has a long walk…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…’, ’5683551′ ‘Select_range_check’, ’0′ ‘Select_scan’, ’1104831′ ‘Slave_open_temp_tables‘, ’0′ ‘Slave_retried_transactions’, ’0′ ‘Slave_running’, ‘OFF’ ‘Slow_launch…/mysql.sock pid-file=/var/run/mysql/mysql.pid datadir=/db/disk2/mysql/data log-bin=/var/log/mysql/log log-slow-queries=/var/log/mysql…
Comment: How much overhead is caused by on disk temporary tables
April 21, 2008 By 26 Comments
…, keep an eye out for the Debian upgrades of the MySQL package (it might be possible that an upgrade will take you to a MySQL server version that has this bug). Btw. using tmpfs for… when MySQL started to write out several n*100MB temp tables to disk (a tmp table was mostly less than 200MB, but several tmp tables…
Comment: Using MyISAM in production
June 20, 2006 By 36 Comments
… tables are only 90MM rows with 10-10GB in disk space We have around 30 tables which are transactions related but some TEMP tables which are transient in nature for 3 weeks Here are symptoms: We found that MySQL… doing nothing resulting in large number of db connections Once MySQL unfreezes, all of threads complete and everything is fine again…
Comment: Estimating Undo Space needed for LVM Snapshot
June 10, 2008 By 5 Comments
… versus when its time to look for more table storage) but it definitely allows mysql snapshots to be a lot smaller. I… tables w/ read lock doesn’t interfere with any queries or anything) flush tables with read lock; (it loops until slave_open_temp_tables=0) flush logs; show master status; > status.log show slave status; >> status.log make lvm snapshot of data partition unlock tables…
Comment: Innodb Performance Optimization Basics
June 16, 2012 By 75 Comments
…, 12Gb ram i would reinstall and use 64bit OS and MySQL. MySQL what i know have a limit of 2GB on 32bit… than you think. Sam, temp tables you can solve my increasing memory used for heap, so the temporary tables are written to memory… /dev/shm instead but ended up by increasing max_heap_table_size. I know there is one more parameter you have…
Comment: MariaDB 5.3.4 benchmarks
February 22, 2012 By 24 Comments
… and insert a new record in one of the tables on each iteration. Mysql is slow there as it does not support hash join. But it compensates by caching the results and the joined temp table. However if you insert a record you will force mysql to expire its cache related…
