Forum Stats

  • 3,727,356 Users
  • 2,245,375 Discussions
  • 7,852,756 Comments

Discussions

Recommended Settings of mySQL Community Edition in Linux, hosting Reporting Data Warehouse

User_QE6WH
User_QE6WH Member Posts: 2 Green Ribbon

Hi ,

We are hosting a reporting warehouse in mySQL Community which will get input data are in the ranges of 50GB and about 100 users may be using the database get reports. The mySQL will be hosted on a Ubuntu 18.04 Server. Are there any recommended settings specifically in the mySQL Community edition which can optimize the performance please ?

Thanks !

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 345 Employee

    The usual recommendation is to set the InnoDB Buffer Pool size to 75-80% of physical ram. This works for 90% of the installations.

    The big things are a) have plenty of RAM -- the more of your data and indexes that are in memory the better as disk access is S-L-O-W b) have fast disks or disk-type devices, c) minimize other network traffic on that segment -- nothing like an LDAP update or DNS Zone update to kill you database traffic, d) Tune you queries, e) have a third normalized form or better as this is your foundation.

  • dvohra21
    dvohra21 Member Posts: 14,133 Gold Crown

    For data warehouse a dedicated data warehouse would be a better choice, as a data warehouse is generally used for OLAP (online analytical processing), whereas MySQL is optimized for OLTP (online transaction processing).

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 345 Employee

    Heatwave is another option if you are using the MySQL Data Service as the Heatwave engine is designed for high speed data anal;ytics

Sign In or Register to comment.