Is Outsystems Platform optimised for MySQL Server?

Is Outsystems Platform optimised for MySQL Server?

  

Performance: Outsystems Platform running on SQL Server vs MySQL

I tested on the same server specification, and found that Outsystems Platform running on SQL Server is always much faster than Outsystems Platform running on MySQL? 


Outsystems Platform is not optimised for MySQL Server or is something else wrong?

Slow performance when publishing an application or when loading a webscreen/page.




Hello Robert,

OutSystems platform always generates code that is optimised for the flavour of database that it is using, in order to take the best advantage of the capabilities provided by the database engine. In doing so, the platform ensures that the experience for the end user is as seamless and pleasant as it can be.

There are a few scientific, peer-reviewed, articles that compare performance benchmarks among database engines. The trend conclusion among these articles is that MySQL is generally slower than SQL Server, for typical (default) configurations, but of course this depends on a lot of stuff, including the amount of data that each database needs to work on. In short, assuming the amount of data is equivalent, it should be normal that you experience some differences in the performance.

However, if you feel like it, I would like to pursue this topic with your help, so I kindly ask you to provide a few more details regarding your specific case:

  • Can you send us your MySQL configuration (my.cnf)?
  • Can you send us some details about the amount of data that you have stored in SQL Server and MySQL for a given page that you notice is slower?
  • Can you clarify on the definition of slower? Is it 1.5 times slower? 2 times slower? Marginally slower?
  • Can you provide an example application where you notice the difference?

Thank you and best regards,

Ricardo Marques


Ricardo 

You are right, I see what you are saying.

With the default configurations for both SQL Server and MySQL, it was found (from external benchmark/test) that ...

SQL Server is often faster with SELECT, DELETE, and UPDATE (2-3 times faster)

MySQL is often faster with INSERT. (1.5-2 times faster)

From own finding, publishing an eSpace that would take 30 seconds via SQL Server, would often take 90+ seconds in MySQL. (sometimes even longer, it's a noticeable difference).


Note: Correctly configuring MySQL might help with performance

Solution

Hello, Robert,

MySQL has to configure everything at hand, it will depend on the operating system whether it is Windows or Linux. I'm adding some parameters that will improve performance.

MySQL official link, explaining each parameter:

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

I tested in a virtual environment, the Outsystems platform in version 10.0.404.0 using the Windows 2012 R2 operating systems, the MySQL 5.7 database. It has greatly improved performance in my environment.

### Outsystems checklist setup
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
log_bin_trust_function_creators=1
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci

### InnoDB Files
innodb_checksum_algorithm = innodb
innodb_doublewrite = OFF
innodb_autoextend_increment = 64
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_compression_level = 6
innodb_compression_failure_threshold_pct = 0
innodb_compression_pad_pct_max = 50

### InnoDB Threads
#innodb_numa_interleave = OFF
innodb_read_io_threads = 4 # (Pending reads)
innodb_write_io_threads = 4 # (Pending writes)
innodb_thread_concurrency = 0 # (cs)
innodb_concurrency_tickets = 5000
innodb_thread_sleep_delay = 10000
innodb_adaptive_max_sleep_delay = 150000
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = OFF

Best Regards,

Guilherme Castro

Solution

Hi Guilherme,

Thank you for sharing your tweaks, these should probably work with other versions of MySQL as well.

Please bear in mind that, at this time, MySQL 5.7 is not supported.

Regards,
Ricardo

Guilherme Castro, Thanks for sharing your tips! 

Hi Ricardo,


he have found that there are a few issues in the queries generated by the platform for MySQL, at least in version 9.1.603 (please see support ticket #1624573). It would be awesome if the platform queries could be reviewed to make it more efficient when we have bigger tables in MySQL.


Regards,

Carlos

Hi guys,


Do you experience timeouts in MySQL that are only detected if you investigate deeply the causes? The symptom is something that takes <1s to complete, takes 20/30s.


We're having that issue since the beginning and it's terrible because when it happens (and it does often), all users halt in their requests...