… indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer… user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb… metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…. This is where Metadata files come in to play Creating a Metadata File The format of the metadata file is XML-based. I have included the metadata file I used to… listing of all columns in the schema, really handy to sort by name to see if you have any implicit constraints…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… cases your virtual disk is basically nothing more than a file on the host filesystem rather than an actual physical volume… you’re thinking about employing a filtering solution of any sort and it’s something you’re not that familiar with…] /usr/libexec/mysqld: Incorrect information in file: ‘./mysql/servers.frm’ ERROR: 1033 Incorrect information in file: ‘./mysql/servers.frm’ Running mysql_install…
Comment: Percona XtraBackup 2.1.3 for MySQL available for download
May 30, 2013 By 3 Comments
… could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4432877 K bytes of memory Hope… automatically 130528 09:53:56 mysqld_safe mysqld from pid file /var/lib/mysql/srv-d2-mysql02.pid ended in an…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… = none innodb_adaptive_flushing_method = keep_average innodb_file_per_table = true innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_method… = 1 innodb_buffer_pool_size = 36G innodb_log_file_size = 4G innodb_log_files_in_group = 2 innodb_log_block_size=4096…_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G myisam_repair_threads…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method = O_DIRECT…_flush_log_at_trx_commit = 1 innodb_log_file_size = 1G innodb_log_files_in_group = 2 innodb_purge_threads = 1 innodb…_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam…
Post: High-load problems? Investigate them with 'pt-query-digest'
April 23, 2013 By 2 Comments
…, but at least you get a nice ranking of queries sorted by the number of times they appear in processlist. In… first snapshot from each *-processlist capture (under normal circumstances, this file will have –run-time snapshots separated by timestamps) and ran…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…/sec. Obviously we can’t get sorted result set faster than that. I placed temporary sort files on tmpfs (/dev/shm) to avoid… partially sorted but only 10 rows fetched from the data file and sent and only they are counted. In practice this means Sort_rows may well understate sort activity happening…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
… MyISAM_table1 and so on for the 16 files. Since MyISAM are flat files, that should be faster, especially with the keys…# myisamchk -rq test_concat – check record delete-chain – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 3 – Fixing index 1…# myisamchk -rq test_concat – check record delete-chain – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 3 – Fixing index 1…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… allocated every time it is needed. The problem with the sort buffer comes from the way Linux allocates memory. Monty Taylor… process, the computer never swapped and I pre-warmed the file cache before the benchmark by doing “alter table sorttest engine… order by data limit 78000,1;show session status like ‘Sort_merge_passes’;select * from sorttest order by data limit 78000…
