… helpful or not depends on index selectivity – how large proportion of rows matches to particular … large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL …multiple servers to host portions of data set. Store portion of data you’re going to work with …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… it is a lot larger problems with MySQL 5.5 with addition of user variables, stored procedures, prepared statements etc which can be a memory hog. My intent with this post is dual. I would encourage MySQL… at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
…+———–+ | 0 | +———–+ 1 row in set (3.26 sec) mysql [localhost] {msandbox} (test) > explain select max(t1…. This is because Innodb does not store data in native MyISAM format and conversion … expected as table data is stored in BTREE index very similarly to how indexes are stored. Summary:…
Post: How does MySQL Replication really work?
January 9, 2013 By 9 Comments
… think we have one that covers the very basics of how MySQL replication really works on the high level. Or it’s… aspects of MySQL replication, but my main focus will be the logistics – how replication events are written on the master, how they are… buffered and because they are sequential. The binary log file stores data that replication slave will be reading later. Whenever a replication…
Comment: Full text search for all MySQL Storage Engines
June 28, 2006 By 37 Comments
… own wordid and then you have a lookup table which stores the relation between wordid and docid. The problem with this… make it easier to count I leave out the mysql codes needed to store these bytes). If you have an average of…+ windowsbox: Vectorbased search: 0.01 seconds I dont know how sphinx stores data internally but hopefully something of what I have said above…
Post: How Percona diagnoses MySQL server stalls
March 3, 2011 By 6 Comments
… gathers many different types of data, including different angles on the same data. No single tool, no matter how powerful, is an answer… relevant. After you’ve gathered some data, you need to look through it. It is stored in /root/collected/ by default. Use… post them on Percona.TV, our MySQL video/screencast blog site. Another interesting topic is how to examine a system that appears…
Post: Percona Live MySQL Conference and Expo 2013: The talks I want to see
February 12, 2013 By 4 Comments
… how to get beyond a single-mastered data store and why it’s a hard problem to solve. State of the Art for MySQL… instruction about what some migration strategies might look like. Using MySQL Performance Schema to debug performance issues - (Zburivsky Danil, Pythian) – I… native language to me, this seems compelling. Managing data and data archiving using MySQL 5.6 new features of Portable Tablespace and EXCHANGE…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
… all the table meta-data is stored. Fortunately, they were running with innodb_file_per_table so the data itself was available. What…_TABLENAME.ibd /var/lib/mysql/recover/ chown mysql.mysql /var/lib/mysql/recover/$CURRENT_TABLENAME.ibd sleep 1 echo “importing tablespace” mysql -u root -e…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… is example how it can look: mysql> show processlist; +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | Id | User …, stored procedures etc (which are stored in…when backing up data from the slave …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… need other resources such as network IO or memory to store temporary table, but let us ignore them for a moment… About Performance by Cary Millsap. So how we can use this data to estimate capacity of MySQL system ? We can look at CPU… estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log_fsyncs for your…
