June 19, 2013

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

… dumps of the dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints_parser generates mysql>LOAD DATA INFILE ‘/path/to/SYS…, `first_name` VARCHAR(45) CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’ NOT NULL, `last_name` VARCHAR(45) CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci…

Filed Under: Insight for DBAs, MySQL Tagged With: , , , , , ,

Post: Understanding the maximum number of columns in a MySQL table

April 8, 2013 By 5 Comments

…base. However… for those using MySQL, Percona Server, MariaDB or any other of the MySQL branches/forks, you get to …FORM as, well, you used to only get three characters after a dot in a file name. Back … of columns you can create. If you try setting the storage engine to InnoDB however, you will …

Filed Under: MySQL Tagged With: , , , , , , , ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But ….commanual server_idblogpercona.commanual shared_memoryblogpercona.commanual shared_memory_base_nameblogpercona.commanual show_slave_auth_infoblogpercona.commanual skip_character_set_client_…

Filed Under: Insight for DBAs, MySQL

Post: Converting Character Sets

March 17, 2009 By 22 Comments

… the entire database and re-importing it with the appropriate server & client character sets. This is a three-step process, where one must… –database=database [options] Options: –askpass Prompt for a MySQL password –charset The target character set to convert to –collate The target collation to…

Filed Under: Insight for Developers Tagged With: , ,

Post: MySQL Upgrade Webinar Questions Followup

June 28, 2012 By 7 Comments

…setting character set to utf8 – this is not needed as MySQL 5.5 continues to support various character sets same as previous MySQL versions. Moreover utf8 character set continues…it to upgrade from MySQL 5.5 to Percona Server 5.5 Compatibility is our highest priority with Percona Server, so it should…

Filed Under: Insight for DBAs, MySQL

Post: Common MySQL traps webinar questions followup

January 17, 2013 By 1 Comment

…takes most of the total response time of the server: that’s typical of a hot spot and this…(again as long as you use a 1-byte character set for CHAR). Here CHARs will be more flexible and… Q: Are these recommendations true of all versions of MySQL? If not which versions? The recommendations I made are…

Filed Under: MySQL

Post: MySQL 5.5 and MySQL 5.6 default variable values differences

February 21, 2013 By 8 Comments

…_10/data/mysq | | CHARACTER_SETS_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux… set of installations innodb_buffer_pool_instances is now 8 instead of 1 optimizing for higher concurrency workloads. Makes sense as servers…_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from var56 where variable…

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…: mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_myisam_stopword’; ERROR 1231 (42000): Variable ‘innodb_ft_server_stopword_table’ can’t be set… least for the moment, this table must use the latin1 character set. mysql: CREATE TABLE innodb_ft_list2 ( value VARCHAR(18) NOT NULL…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Comment: Why MySQL could be slow with large tables ?

December 17, 2012 By 141 Comments

…/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql init_connect=’SET collation_connection = utf8_general_ci; SET NAMES utf8;’ default-character-set=utf8 character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set…

Filed Under: Insight for DBAs Tagged With: ,

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… is that nested-loop joins on large data sets are very expensive.  If MySQL supported sort-merge or hash joins, you….  Use the smallest data types you can, the simplest character sets you can, and watch out for NULLable columns.  Use… point: Use InnoDB Assuming that you will use the stock MySQL server, InnoDB is usually your best bet. (Actually, XtraDB might be…

Filed Under: Insight for Developers Tagged With: , , , ,
« Older Posts