Hi Mark, we identified a few single-thread performance issues during the Barcelona meeting. Monty should have them fixed in his private tree. Fixes are quite generic and should improve performance almost in all cases. An exception is bottlenecks specific to certain use cases. Could you send me any relevant information my way if you have it around? Thanks, Sergey On Mon, Jan 20, 2014 at 08:00:45AM -0800, MARK CALLAGHAN wrote:

Are there other goals for single-thread performance regressions? There are many that have nothing to do with replication code although almost every single-threaded replication hurts replication.

On Mon, Jan 20, 2014 at 2:54 AM, Axel Schwenke wrote:

...

Hi,

for those of you who didn't attend the Barcelona meeting last week, here are the plans for next steps in benchmarking:

1. top priority is automatic running of DBT3 for each new release of MariaDB-10.0 and MySQL-5.6. Probably also for MariaDB-5.5. I'll send out another mail to pinpoint DBT3 conditions (i.e. which table statistics to use etc.)

2. multithreaded replication slave is now in a condition that it can and should be benchmarked

3. the problem pointed out by Yoshinori in

http://yoshinorimatsunobu.blogspot.de/2013/12/single-thread-performance-regr... must be checked for MariaDB. Monty announced that he will commit a patch this week that should help with that problem.

4. for the NUMA scalability problem, Kristian will try to find the definitive reason with profiling tools. Besides that ad-hoc testing of new patches from Svoj continues as before.

5. Galera benchmarking will be done by the Galera team for now.

XL

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

-- Mark Callaghan mdcallag@gmail.com

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

Hi Mark, thanks for this interesting reading. I will also want to have a look at how much MariaDB is affected by reported regressions and if there are easy ways to workaround them. Recently, when I ran single thread benchmarks I observed similar results, that is parser and optimizer were top CPU time consumers (as well as bus traffic producers). Thanks, Sergey On Mon, Jan 20, 2014 at 09:32:41AM -0800, MARK CALLAGHAN wrote:

My analysis was based on benchmarks and described in a few blog posts. This is interesting for a few reasons. 1) replication is single-threaded at least for a few more years. Eventually we will all have the Kristian-inspired idea of getting parallel replication apply even when there is only one database shard and that helps some. But there are always some workloads that really are single threaded on the master and during replication apply. 2) fast storage means that IO-bound workloads might be able to get 100,000+ IOPs on a commodity server. So even when IO-bound we need much higher throughput in the past 3) At least with public-facing information Oracle doesn't write about this topic. Their performance focus is high concurrency. 4) RAM per host is much larger so more workloads are not IO bound and expect high throughput

I don't think a complex workload is needed to show the problem. sysbench and dbt might be sufficient. linkbench should also be useful to MariaDB.

http://mysqlha.blogspot.com/2013/09/mysql-41-forever.html http://mysqlha.blogspot.com/2013/09/single-threaded-connect-performance.html http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.ht... http://mysqlha.blogspot.com/2013/05/mysql-56-single-threaded-performance.htm... http://mysqlha.blogspot.com/2013/05/mysql-56-versus-40-for-read-only.html http://mysqlha.blogspot.com/2013/04/mysql-56-single-thread-update-only.html http://mysqlha.blogspot.com/2013/03/mysql-56-single-threaded-read-only.html

On Mon, Jan 20, 2014 at 8:18 AM, Sergey Vojtovich wrote:

...

Hi Mark,

we identified a few single-thread performance issues during the Barcelona meeting. Monty should have them fixed in his private tree. Fixes are quite generic and should improve performance almost in all cases. An exception is bottlenecks specific to certain use cases.

Could you send me any relevant information my way if you have it around?

Thanks, Sergey

...

Are there other goals for single-thread performance regressions? There are many that have nothing to do with replication code although almost every single-threaded replication hurts replication.

On Mon, Jan 20, 2014 at 2:54 AM, Axel Schwenke wrote:

...

Hi,

for those of you who didn't attend the Barcelona meeting last week, here are the plans for next steps in benchmarking:

1. top priority is automatic running of DBT3 for each new release of MariaDB-10.0 and MySQL-5.6. Probably also for MariaDB-5.5. I'll send out another mail to pinpoint DBT3 conditions (i.e. which table statistics to use etc.)

2. multithreaded replication slave is now in a condition that it can and should be benchmarked

3. the problem pointed out by Yoshinori in

http://yoshinorimatsunobu.blogspot.de/2013/12/single-thread-performance-regr...

...

must be checked for MariaDB. Monty announced that he will commit a

On Mon, Jan 20, 2014 at 08:00:45AM -0800, MARK CALLAGHAN wrote: patch

...

...

this week that should help with that problem.

4. for the NUMA scalability problem, Kristian will try to find the definitive reason with profiling tools. Besides that ad-hoc testing of new patches from Svoj continues as before.

5. Galera benchmarking will be done by the Galera team for now.

XL

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

-- Mark Callaghan mdcallag@gmail.com

...

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

-- Mark Callaghan mdcallag@gmail.com

This is good news. Don't forget to have someone write a blog post about it. On Tue, Jan 21, 2014 at 7:04 AM, Michael Widenius wrote:

Hi!

...

...

...

...

...

"Sergey" == Sergey Vojtovich writes:

Sergey> Hi Mark, Sergey> we identified a few single-thread performance issues during the Barcelona Sergey> meeting. Monty should have them fixed in his private tree. Fixes are quite Sergey> generic and should improve performance almost in all cases. An exception is Sergey> bottlenecks specific to certain use cases.

A short followup of the above:

While testing trivial queries, we noticed some code paths at top that should not be there: - Mallocs (simple queries should be run without any mallocs). - I managed to remove some of them. - Some atomic increments/sum could be avoided. (For simple SELECT * FROM empty_table, an atomic increment could take almost 1% of execution time) - This was fixed by marking more memory as THREAD_SPECIFIC and not do atomic operations on these until SHOW STATUS - Lots of calls to current_thd - A large portion of these calls are now removed. - Some byte rotate operations where slower than expected. These was often used in Aria tables and MyISAM tables. - We will replace these with one assembler instructions for X64 ships which will make them MUCH faster.

I have done the above fixed in my 10.0 tree. I just need to finalize and benchmark this before I push...

Regards, Monty

-- Mark Callaghan mdcallag@gmail.com

I noticed the latest blog post from Kristian after posting my question. Haven't read all of it yet, but your work looks very good. Your blog posts are also a great way to get more visibility for MariaDB (on Facebook and elsewhere). On Mon, Jan 20, 2014 at 8:58 AM, Kristian Nielsen wrote:

MARK CALLAGHAN writes:

...

Are there other goals for single-thread performance regressions? There are many that have nothing to do with replication code although almost every single-threaded replication hurts replication.

I am planning to do a general in-depth analysis of single-threaded performance to look for things that can be improved. I plan to look at different relevant use-cases, not restricted to replication. I just blogged about a large improvement in in-memory read-only sysbench.

(This i the reason I asked you a couple months ago about interesting loads to analyse for single-threaded performance regressions).

My experience is that whatever I take the time to look into, I always find significant opportunities for improvement that are relative easy to achieve.

- Kristian.

-- Mark Callaghan mdcallag@gmail.com