InnoDB uses its own mutexes and read-write locks instead of POSIX-mutexes pthread_mutex*, the main reason for that is performance, but InnoDB’s implementation isn’t ideal and on modern SMP boxes can cause serious performance problems.
Let’s look on InnoDB mutex (schematic for simplification):
1 2 3 4 5 6 7 | spin_loop: for (i=0; i< innodb_spin_locks;i++) if (mutex_try_lock()) return; // success // we are here if spin loop was not successful reserve_cell in array of cell; wait on condition_variable in reserved cell; |
innodb_spin_locks is configurable via system variable innodb_sync_spin_loops (default value is 20)
There we have:
1. Spin-loop – InnoDB uses spin-loop in hopes thread locked mutex is very fast and will release mutex while current thread runs in spins, so there is saving of expensive context switching. However we can’t run spin very long as it eats CPU resources and after some loops it is reasonable to give CPU resource to concurrent threads.
2. Wait on condition variable using cells from synchronization array. Synchronization array becomes from age of dinosaurs and Windows 95, where count of condition variables was limited, so InnoDB had to implement workaround. The same synchronization array is performance problematic, as it by itself uses mutex inside to protect cells.
With big respect to InnoDB team they still take care of users of Windows 95 I think it is better to remove synchronization array and use condition variables directly.
I’ve made the patch which simply replace array. We have reports from partners that this patch increase scalability on Solaris/Opteron boxes dramatically – so you can test it if you have multi-CPU boxes and high-concurrency load on InnoDB.
Hi,
Sorry for the intrusion. I am a non-technical person who is beginning to sweat a bit over the viability of a system that is being developed for me in mysql.
The system will allow users to build statistical reports on foreign trade data. There are dozens of reports and they basically consist of graphs and tables.
My worry is that the size of the database might make performance an issue. If you could ease my mind I would appreciate it.
The main table will initially have about 10-20 million rows. Each row about 60 fields. Probably about 10-15 GB to start. Each individual row contains information on one import/export transaction (product, quantity, price, date, country of origin, etc.). The system will also pull data from a secondary table that has info on individual companies.
The system will grow by about 10 million rows per year.
Is it realistic to think that a dedicated server (low end celeron 2.4? with 512mb ram) can handle multiple users building online reports against this database???
Yes? No? Maybe?
Thanks.
Charles,
First I should note it is best if you ask questions unrelated to blog post at forum – http://forums.mysqlperformanceblog.com.
Speaking about your question it is hard to tell without seeing reports – how much data they will have need to analyze as well as which kind of reports are you looking for. Should they be done live or are they going to be requiested and become available at later time after they are created.
Processing 10-15GB can take quite a while on low end Celeron box.
As you probably know we do MySQL Performance Consulting and will be happy to help you with sizing and application optimization.