… are being length can be converted to fixed size (basically everything but BLOB/TEXT) MySQL can use read_rnd_buffer to optimize data… accessed in pretty much random row pointer (typically physical) order. MySQL takes bunch of pointers from sort_buffer (just enough so…_size is important (optimization works in following conditions): Row pointers are stored in the sort_buffer, not the whole data selected Blob…
Comment: Blob Storage in Innodb
February 10, 2010 By 13 Comments
… sized blob, innodb will need to allocate 25 GB of memory to satisfy this query. See: http://mysqlinsights.blogspot.com/2009/01/mysql-blobs-and-memory-allocation.html The people at pbxt have created a solution for their storage engine using a streaming blob protocol, but this requires a change to mysql client as far as i know…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might… underlying tables involved and the query execution plan used by MySQL. The end result might be that you end up limiting…_tbl_size variable is set to 32M on the server, so on-disk tables are probably being created because of blob columns…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
… testing. InnoDB’s inbuilt row compression MySQL‘s inbuilt compress() function leveraging zlib … `id` int(11) NOT NULL AUTO_INCREMENT, `dat` blob, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET…DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE TABLE `non_compressed_data` ( `id`…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
… in the MEMORY storage engine (also known as HEAP) in MySQL has been one of the long-standing limitations caused by…-VARCHAR support for MEMORY tables for MySQL 5.0. Although this patch did not add the BLOB and TEXT support, it refactored… request it explicitly and how to set the data block size at CREATE TABLE time, are in the documentation. What of…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… keep max_prepared_stmt_count very low. Prepared Statements and Blob Data If you’re want to get memory consumed by… parse tree inside MySQL which of course depends on query size which is controlled by max_allowed_packet. However some MySQL optimizations such as equity propagation and range expansion can cause Parse Tree to blow up in size. For most…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 17 Comments
… to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on…. In fact setting tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value… tmp_table_size and max_heap_table_size ChangeSet@1.2358, 2007-01-03 14:45:26+03:00, ted@ted.mysql.internal…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
…during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is …use values 64M-512M depending on server size. innodb_log_buffer_size Default for this one is kind of …you have update activity spikes however or work with blobs a lot you might want to increase it. …
Post: MySQL Storage Engines - PBXT
May 14, 2007 By 6 Comments
… the blobs which are never fragmented and handled efficiently. PBXT team even leads the project to add scalable blob streaming to MySQL which would be fun for many applications if implemented well. It is too bad MySQL Users Conference… which may require a lot of IO for large data sizes. But you of course should take into account this is…
Comment: When should you store serialized objects in the database?
January 22, 2010 By 47 Comments
… value before returning it as an object. As far as “blob size“, I think some common sense should be used. I haven… performance hit, but I imagine it’s related to MySQL‘s block size, which I’m sure I’m under. Most JSON…
