… Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance limitations on standard EC2 servers with EBS… instance, which is listed as “High-Memory Quadruple Extra Large DB Instance: 68 GB of memory, 26 ECUs (8 virtual cores with… a table+index data size approximately twice as big as memory, so the workload should be somewhat IO-bound. My goal…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… amount of resources system has will place a limit on amount of queries system can ran, for…can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (…this information is to compare systems with different memory amount having same workload. You will often see…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…/dist/5.6.10 | | BACK_LOG | 50 | 80 | | OPEN_FILES_LIMIT | 1024 | 5000 | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS… 4MB instead of 1MB now allowing MySQL to handle larger queries. Makes sense as amount of memory available is much larger these…_files_limit was also raised. Unlikely to cause significant gains unless opening and closing files is expensive operation (such as using MySQL…
Post: Finding an optimal balance of I/O, CPU, and RAM for MySQL
May 16, 2011 By 4 Comments
… to know how MySQL scales as you add more memory to the server. Vadim recently benchmarked the effects of increasing memory and CPU core count. He looked for a balance between utilizing the hardware as much as possible, limiting the… Virident, is that as you add CPUs and increase memory size, MySQL doesn’t scale as well as we would like, and…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… workings of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we… memory). Also, I was testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql… it an edge case — but I would call it a limitation of the current query planner. However I would like to…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
…, why is this? It turns out that this triggered a memory for me from several years ago. I’ve since discovered… it is. We can ignore the upper limit on number of storage engines for MySQL and understanding the relationship between the range… InnoDB itself and in the table_definition_cache). Further reading: MySQL Forge Internals document on the FRM file format Stewart’s…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…of double buffering and I can save the limited amount of file system cache I would …files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which …allocated for innodb_buffer_pool which with other memory needs leaves roughly 1GB for OS Cache. Here…
Post: Speaking at Percona Live London 2012: Choosing Hardware for MySQL
November 19, 2012 By 4 Comments
… talk about ‘Choosing Hardware For MySQL‘. Of course, the most important hardware components will be covered: CPU, Memory, Network, Disk Subsystem and also the Cloud. I will try to help attendees better understand how MySQL uses hardware… random IO operations on disk, what scalability means for MySQL, what it’s limitations are and so on… Want more? Just attend…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
…than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one,…found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 …
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… cases you can use result overload to make MySQL to consume so much memory so it will swap badly until it runs… is to get enough memory allocated to get to address space limit and MySQL will crash when one of internal memory allocations would unexpectedly… easily make server to take up all memory. However there is no per user limit so one user can consume all prepared…
