… problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to… other areas in Innodb than buffer-pool which had it locking relaxed in latest MySQL 5.0 Hopefully the patches …. These are for MySQL 4.0 at this point but we hope to port some of them to MySQL 5.0+ …
Post: MySQL 5.6.7-RC in tpcc-mysql benchmark
October 8, 2012 By 22 Comments
… two bugs: MySQL 5.6.7 locks itself on CREATE INDEX MySQL 5.6.7-rc crashed under tpcc-mysql workload But at… = 4 thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…. The other reason for difference is join_buffer which reduces number of passes MySQL had to perform and so number of row… MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key… thing – since MySQL 4.1 sorting can be done with storing row data in the sort file, not only key values and row…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…, 62743962, 62744142, 62744144, 62744146) With secondary key defined on (group_id,deleted…configured with innodb_buffer_pool_instances=16 and 400GB buffer pool. Here…mysql_execute_command,mysql_parse,dispatch_command,do_command,do_handle_one_conn ection,handle_one_connection,start_thread,clone 4…
Comment: Should you move from MyISAM to Innodb ?
January 21, 2009 By 59 Comments
… innodb plugin from innodb.com , using ver. 1.01 and mysql 5.1.24: Our 15 tables are partitioned (with 1024… innodb_file_per_table is on, and data is compressed (key-buffer-size=4). Total db content is only 15G. Our Inserts drop… the innodb_buffer_pool_size to 1G (having 4G ram) which seems to be the highest buffer allowed. if exeeded, mysql does not…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
… tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still….00G | 0.96 | | art87.article87 | 12.67M | 15.83G | 4.79G | 20.62G | 0.30 | | art116.article116 | 10.49M …for MyISAM can also help you to size your key buffer compared to operating system cache. I also use …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…_blocks_time=1000 on MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5.6.10 has…: SIMPLE table: dim_date type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_OrderDateKey rows: 1 Extra: Using… the contents of the buffer pool in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR…_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL 5….00 Using where 1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 100.00 1 SIMPLE lineitem…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails… dirty buffers. Normal reads originated from query executions are executed by threads running queries. On Unix/Linux you will always see 4… was not read to the buffer pool. Finally you can see buffer pool hit ratio which measures buffer pool efficiency. 1000/1000 corresponds…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
… (about 100% of 400% available, MPM and dual core reports 4 processors). We have 6Gb of physical memory available but since…/mysql/slow-queries.log max_connections=650 open_files_limit=6144 max_connect_errors=10000 interactive_timeout=3600 wait_timeout=3600 key_buffer_size=32M # Only for MyISAM which isnt used. sort_buffer_size=2M query_cache_size…
