… MySQL in wild. I also wanted to share some troubleshooting techniques I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to understand how this memory allocation…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
… they estimate memory consumption by MySQL Server in given configuration. What is the formula they could use. The reasons to worry about memory usage… to do with real memory consumptions. In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… this data to estimate capacity of MySQL system ? We can look at CPU and IO consumption per Query and compare it to… to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc… to use this information is to compare systems with different memory amount having same workload. You will often see increasing amount…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…KEY (`i`), KEY `j` (`j`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1. row *************************** Name: sample… than 4 bits per locked row. In practice this means memory consumption by row level locks should not be the problem even…
Post: Why Swapping is bad for MySQL Performance ?
January 18, 2010 By 11 Comments
… you get MySQL or other applications using too much memory on the box or OS behaving funny and using more memory for cache… space to swap in the page (we’re speaking about memory pressure right?) which means swapping out some page. This would… have some unexpected memory consumption spike you may prefer to see slowdown instead of MySQL being killed because of out of memory but do…
Post: Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
… – o_profile (often together with strace) are great tools for MySQL Performance analyses. Too many people end tuning on plans and…. Indeed allocating with mmap is much slower than allocating from memory memory pool which process already “owns” and in real life it… pools rather than mmap. This though will at least increase memory consumptions as deallocating of these large buffers will not instantly return…
Post: MySQL Prepared Statements
August 2, 2006 By 19 Comments
… only consumed time but also required extra memory consumption both on server and client. So in MySQL 4.1 Prepared statement came. Do… Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of…. Do not forget to close prepared statements – Many memory leaks reported in MySQL Server turned out to be prepare statements or cursors…
Post: Withdrawal of Memory allocation in Stored Function
September 7, 2007 By 1 Comment
Returning to my post about memory consumption with Stored Function at this moment I want to annul … probably there was something wrong with the whole box, not MySQL. I apologize if it was confusing. Although I am almost…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… to get enough memory allocated to get to address space limit and MySQL will crash when one of internal memory allocations would unexpectedly… so they would be still be created in memory. Memory Tables If you can create memory tables you can create any number of… MySQL Server barely functional. The limits which exist are not really helpful as they do not define query complexity and resource consumption…
Comment: MySQL Server Memory Usage
September 1, 2006 By 18 Comments
…. b. The question really is how do you determine the memory consumption per connection in average? This leads to the next question…/mysql.sock pid-file=/var/run/mysql/mysql.pid datadir=/db/disk2/mysql/data log-bin=/var/log/mysql/log log-slow-queries=/var/log/mysql…=/var/log/mysql innodb_log_arch_dir=/var/log/mysql innodb_table_locks=0 innodb_buffer_pool_size=1800M # USE ALL MEMORY AVAILABLE…
