When we’re looking at benchmarks we typically run some stable workload and we run it in isolation – nothing else is happening on the system. This is not however how things happen in real world when we have significant variance in the load and many things can be happening concurrently. It is very typical to hear complains about MySQL interactive performance – serving simple standard web traffic is drastically impacted when some heavy queries are ran in background or backup is done with mysqldump – a lot more than you would expect from simple resource competition. I finally found some time to look further in this problem and see what can be done to remedy it. We designed the benchmark the following way – there is a small table (200MB) which completely fits in the Innodb Buffer Pool (512MB).
Side load may massively impact your MySQL Performance
1 Reference Manual :: 22.214.171.124 SHOW ENGINE INNODB STATUS and the InnoDB Monitors
5 Ways to Boost MySQL Scalability | SQL Zone
There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see. 1. Tune those queries
Faudra que je jete un oeil à innodb_flush_log_at_trx_commit, je le connaissais pas celui la. by Aug 2
Re: Lock issues with Table Partitioning
The problem is the locking it self, usually one only uses a couple of tables, i.e. lets say 10 table in a very big join. That results in 10 locks that first needs to be stored in the tables, and then they need to be sorted (MySQL's way to avoid deadlocks, by always locking in the same order). And then the locks need to be locked. But if we use two partitioned tables with 200 partitions each, that results in 400 locks which take much more time to sort and to lock (even if only inserting one row, or a simple 'select * where primary_key=1' query).
November 3, 2007 By Peter Zaitsev39 Comments My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description. Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool. This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough.
innodb_flush_method n'est pas spécifié dans notre conf. Je ferai bien un restart de DB2 en spécifiant sa valeur à O_DIRECT (pas possible de le setter dynamiquement). by Oct 30
mysql-log-filter - Google Code
The script filters the MySQL Slow Query Log to show queries which impacted performance most and is intended to be used by DB admins and application developers. The log file is analyzed and processed as a stream, line after line, so there is no need to load the whole log file into memory. --no-duplicates is a very useful option to see only necessary statistics.