… over the range 1..MAX(id)? Yes, there’s a risk if you have irregular distribution of matching rows that you’ll… between 47000 and 50000 with no movies, but your random number generator picks values in that range with equal frequency as… be picked more frequently. Nearly every solution for randomly choosing rows involves some compromise, either of performance, or of accuracy of…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before… around the maximum number of columns is this: /* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min_record… on the following (perl) program (accepts command line parameter of number of columns to create) to produce the CREATE TABLE sql…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…approximately 78GiB of data (32 tables, 10M rows each) and a 4GiB buffer pool. …when looking at transaction latency; the baseline numbers are roughly identical for all four configurations,…log = 50 wait_timeout = 120 max_connections = 5000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… by Dimitri Kravchuk, we have received a large number of questions on why the benchmark results in… reportedly Dimitri was using 8 tables with 1M rows each (about 2.1GB) for his tests, while…=1 –rand-type=uniform –forced-shutdown=1 –max-time=120 –max-requests=0 –percentile=99 –mysql-user=root …
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
…for dynamic rows, however it is not very significant if number of columns is small. For large number of columns dynamic rows become very…query: mysql [localhost] {msandbox} (test) > select max(t1+0) from t99v1; +———–+ | max(t1+0) | +———–+ | 0 | +———–+ 1 row in set (3.26 sec) …
Comment: Researching your MySQL table sizes
June 28, 2012 By 28 Comments
… size i use the formula : (max row size * collation length(bytes)) * number of rows) (i.e)–> ((61*4)*100) { max row size : name = (50+3) , place_id = ( 4) , lastdt_modified = (4) : Total = 61 For 100 rows… to the above data is 61 Kb and for 100 rows it is 24400 Kb. How do i calculate the index…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… a search. Please use this with care: you MUST use row-based binlogging and replication, and remember that the execution is… it is 128, or less. PZ: Looks like another magic number to experiment with. Q17: How InnoBD handles blobs/text fields… in performance tuning in heavily concurrent environments. Sets the max\ imum number of threads allowed inside InnoDB. Value 0 will disable the…
Post: Color code your performance numbers
June 2, 2010 By 4 Comments
… max times – something what is easily computed using built in aggregate functions. We most likely would like to see some percentile numbers….6701 | 0.3006 | 0.0293 | +———–+————+———+ 1 row in set (24.87 sec) These are great numbers to look after. In this case… over 99% cases. I consider this a lot more meaningful number for business than speaking about 99% response time, especially as…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… 0 AVG_TIMER_DELETE: 0 MAX_TIMER_DELETE: 0 *************************** 3. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA:…: 849692 SUM_NUMBER_OF_BYTES_READ: 1936883712 SUM_NUMBER_OF_BYTES_WRITE: 103557693440 *************************** 2. row *************************** FILE_NAME: …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…: Number of rows inserted – 1 INSERT w/FOREIGN KEY constraint: Number of rows inserted – 1 SELECT: 1 ticket per row returned UPDATE: 1 ticket per row examined + 1 ticket per row updated DELETE…_io_threads=8 innodb_io_capacity=500 innodb_max_dirty_pages_pct=90 max_connections=3000 query_cache_size=0 skip-name…
