…. Namely, it requires the list of transactions to be either empty or very short, which basically implies a fully read-only…-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…TPS (more is better) and we start with empty tables. Now, before looking at the graph, …` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id`….myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… IS NULL; If the result set of this query is empty, then there are no orphaned rows. But there are probably… generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5.3…) Records: 2 Duplicates: 0 Warnings: 0 session1 > select * from test where id=1; Empty set (0.00 sec) session1 > rollback; Query OK… COLUMN c; session7 > select * from test_2 order by id; session8 > select * from test_2 order by id; session9 > select * from test_2…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and …Com_drop_viewblogpercona.commanual Com_empty_queryblogpercona.commanual Com_execute_…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…instead of simply responding with empty set MySQL truncates the constant, …2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | type…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S…_DENIED: 0 EMPTY_QUERIES: 13099 In this case I can see this user took 49 CPU seconds per 181243 select queries which is about 270us per select query. We can also get “BUSY TIME…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
…/log/mysql/mysql.log general_log = 1 User correctly authenticated: 121227 8:31:49 38 Connect root@localhost on 38 Query select @@version… = 1 Then we get the information about a particular user: mysql> select * from user_statistics where user=’root’\G *************************** 1. row *************************** USER…: 0 DENIED_CONNECTIONS: 2 LOST_CONNECTIONS: 16 ACCESS_DENIED: 0 EMPTY_QUERIES: 0 TOTAL_SSL_CONNECTIONS: 0 Here we can see…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… in unpredictable, and therefore undesirable performance. If the cache is emptied (perhaps due to a restart, crash, upgrade or power loss… since they were last populated. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL commands can…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… yet. SESSION1: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) mysql> select * from…
