June 20, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… will need a Java driver for your database — I’m using the MySQL Connector/J Install the graphiz package Creating a mysql.properties file…, and comments — a great way for a high level overview of the table sizes and an easy way to use the search feature of your browser to zero in on a particular table Constraints…

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

Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer

March 29, 2013 By 10 Comments

… use a caching layer to store expensive result sets or objects.  Some typical use cases include: Complicated query result set (search results… As a quick benchmark, I ran some batches of fetch and store against both a standard memcached instance and a minimally tuned MySQL 5… per/cmd 0.0951 ms range per/cmd InnoDB MySQL Select (same table): Fetching [1,000,000] items: 441573 ms total 0…

Filed Under: MySQL Tagged With: , , , ,

Comment: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer

March 30, 2013 By 10 Comments

…://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html there are some caching settings to play with: “Table cache… case, if memcached cannot find a key in memory, it searches for the value in an InnoDB table.” I’d like to see…

Filed Under: MySQL Tagged With: , , , ,

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

March 4, 2013 By 7 Comments

…: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_…a “+” in front of our search term, then that term *must* appear in the search results. But does it? With 5.5: mysql…

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

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… InnoDB full-text search in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in the sense that the SQL required to run a MATCH… the table does not contain a suitable column,” but there are only a handful of references to FTS_DOC_ID found when searching the MySQL…

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

Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5

May 20, 2011 By 11 Comments

…even for read only workloads. Workloads which got a lot of attention such as Sysbench and … The query looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, …search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql…

Filed Under: MySQL

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… server does everything itself. Especially when using a CMS where DB structure is prepdefined A: MySQL Server will not automatically define any indexes… table, not other index, also you only can have equality comparison as a search condition WHERE A=5 ORDER BY B will use index (A… a lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table…

Filed Under: Insight for DBAs, MySQL

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…. And besides, most applications have a lot of the same components. Tagging, friends, queues, click tracking, search, paginated displays — we’ve seen… itself OK, so here’s a very basic audit, whose scope I’ll keep within just a MySQL server. The first step is… this.) There are a bunch of other things I’ll look at, including checking for default users in the mysql.* tables, running mk…

Filed Under: Insight for Developers Tagged With:

Post: Sphinx search performance optimization: multi-threaded search

January 16, 2013 By 10 Comments

Queries in MySQL, Sphinx and many other database or search engines are typically single-threaded. That is when you issue a single query… src1 { type = mysql sql_query = SELECT id, text FROM table } source src1p0 : src1 { sql_query = SELECT id, text FROM table WHERE id % 3…, you may want these to be ranges by using a helper table in MySQL to define those ranges, mixing it with sql_query…

Filed Under: MySQL Tagged With: , , ,

Post: Joining many tables in MySQL - optimizer_search_depth

April 20, 2012 By 8 Comments

… joins with few tables, – it is not clear how far from optimal plans do we get by using a greedy search. From the same discussion we can learn how automatic selection works – it picks value of min(number of tables… somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as…

Filed Under: Insight for DBAs, MySQL
« Older Posts