…: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances… of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload. OK, so that was all about the…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…, but it behaves as if you use READ COMMITTED for all write queries, in spite of your chosen transaction isolation level (considering…, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE… prevent problems with the replication you should change the binary log format to “row”. Depending on the statement, the behavior of…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…: while true do date >> ps.log ps aux | grep mysqld >> ps.log sleep 60 done Check for Table…grow up to max_packet_size per connection) are all connection buffers and so if you close connection…There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…I am facing major issues with slow query output. The tables are in InnoDB with MySQL 5 …mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1…approx 3 million records is taking too long and all my transactions are locked. select count(1),field1 …
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… version of MariaDB 5.3. We were in our annual all-staff meeting last week, so we are a little slow… Group commit for the binary log makes the server much faster with the binary log enabled Faster queries through speedier joins, faster subqueries…, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5.5 might be here pretty soon — I caught…
Post: Percona welcomes Drizzle 7.1
April 10, 2012 By Leave a Comment
… data type ZeroMQ plugin Log output to syslog is enabled by default Updated DEB and RPM packages Query log plugin and auth_schema…,000 lines of code. The remaining 68 plugins are not all trivial either, they include: authentication via PAM, authentication via HTTP… plugin, authentication via tables (similar to MySQL), RabbitMQ, regex policy, logging to syslog, authentication via LDAP. This shows that major pieces…
Post: Slow Query Log analyzes tools
September 6, 2006 By 83 Comments
…’); Filtering Slow Query Log – Especially after the changes to log all queries slow query log may be growing too rapidly to follow, so we implemented slow query log filter (based on parse_mysql_slow_log by…
Post: Impact of logging on MySQL’s performance
February 10, 2009 By 7 Comments
… GiB # of connections No logging, NOTPM Logging queries >1 SEC, NOTPM ratio 1 sec / no_logging Logging all queries, NOTPM Ratio all_logging / no_logging 1 9607 9632 1.00… logging to /dev/null. CPU-bound case – 10 warehouses, Database size: 1.31 GiB # of connections No logging, NOTPM Logging all queries, NOTPM Ratio all_logging /no_logging…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… long_query_time=0, because if you set it to some other value say 0.1 seconds, it will miss all queries shorter… set the variable log_slow_verbosity to ‘full’, this variable is available in Percona Server and allows us to log all the extra… that logging all queries in this fashion as opposed to the general query log, enables us to have the statistics available after the query is actually…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…. CONNECTION IDENTIFIER Each slow log entry now contains a connection identifier, so you can trace all the queries coming from a single connection… which was to treat long_query_time value as seconds. In effect one could only choose to log all queries to see those which take less than 1s. Now long_query_time is in microseconds and…
