… to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a… CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new server. Quite easy, isn… EACH ROW SET NEW.create_date = NOW() */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; Ok…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… with parser or it is something nasty going on on Innodb level, so I tried running the test with MyISAM tables…_for_mysql() “PHASE 3:”, InnoDB judge whether the SQL is “SELECT” or not. 5.0 does scanning the SQL for each time. if… = FALSE; } } The “REPLACE 1000 rows” SQL doesn’t have “SELECT” so, InnoDB scan all of the SQL each times… (* “INSERT” may not use…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it…; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…0 Tuning in /etc/my.cnf (included in FDA) [mysqld] sql-mode = IGNORE_SPACE transaction-isolation = READ-COMMITTED max_allowed_packet = 1M…autoextend innodb_file_per_table innodb_log_group_home_dir = /log/mysql/var/ innodb_checksums = 0 innodb_doublewrite = 0 innodb_buffer_pool_size = 5000m innodb_…
Comment: MySQL File System Fragmentation Benchmarks
July 9, 2008 By 28 Comments
…/Data/” default-character-set=latin1 default-storage-engine=INNODB #default-storage-engine=MyISAM sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO…=256K #*** INNODB Specific options *** innodb_additional_mem_pool_size=20M innodb_flush_log_at_trx_commit=0 innodb_log_buffer_size=4M innodb_buffer_pool…
Comment: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… of making other queries slower by increasing the global value. innodb_purge_batch_size is a performance enhancement, Should also be… setting because we expect most tables to be InnoDB and use innodb_file_per_table. sql_mode also uses STRICT_TRANS_TABLES by default… features because of caution or backwards compatibility. Say the new innodb checksums in hardware, that 5.5 doesn’t understand, or…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
… SCSI RAID: [mysqld] long_query_time=5 skip-bdb skip-innodb skip-locking query_cache_limit=1M query_cache_size=32M…_connections=1500 log_slow_queries=/var/log/mysql-slow.log sql-mode=TRADITIONAL concurrent_insert=2 low_priority_updates=1 The problem… not available, for instance) and eking performance out of an InnoDB table for raw SELECT speed will take a committee of…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he ran this query: SELECT MIN(update_time) FROM…. Oooops. What, then, can we learn from this? Unfortunately, setting sql_mode does not help you here. Although 0024-06-21 is… NULL, PRIMARY KEY (`i`), KEY `i` (`i`,`update_date`) ) ENGINE=InnoDB and then inserted a few bogus rows with the 0024…
Comment: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
…://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout There are cases where it rolls… warnings that we wanted to escalate to errors without changing SQL_MODE (so we could enable strictness in dev and run our…
Post: Read/Write Splitting with PHP Webinar Questions Followup
January 23, 2013 By 4 Comments
…a single class that can switch between different modes on successive queries. Q: Is RBR faster …redesign my prototype to use hints embedded in SQL comments, instead of instantiating different wrapper classes. Q…with using a write-through cache? A: The InnoDB buffer pool is effectively a type of write…
