INNODB

  • 22
Discussions
Forum's Comments
  • Using with MySQL So what I did so if anyone reads this in the future. I exported my SQL (Export Tab) from my database in PHPMyADMIN and then open it with my Notepad++ or just regular notepad works, but takes longer to load. I did a search for MyISAM and replaced all with INNODB. I then imported  (Import Tab) and overwrote my tables in PHPMYADMIN. Then I did as Alex mentioned above adding the last section to my (my.cnf in my case) Mysql file and this is my end result. Alot of my changes I borrowed from others Configs here that worked great for them: 

    performance-schema=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    innodb_stats_on_metadata = off
    innodb_buffer_pool_instances = 4
    innodb_buffer_pool_size=134217728
    transaction_isolation = READ-COMMITTED
    lock_wait_timeout = 4
    innodb_lock_wait_timeout = 4
    innodb_rollback_on_timeout = 1
    binlog_format = MIXED
    innodb_log_file_size = 400M
    max_allowed_packet=268435456
    open_files_limit=10000
    default-storage-engine=INNODB
    innodb_file_per_table=1
    key_buffer_size = 64M
    = 16M
    thread_stack = 192K
    thread_cache_size = 192
    max_connections = 250
    table_open_cache = 100000
    max_heap_table_size = 128M
    tmp_table_size = 1024M
    query_cache_limit = 2M
    query_cache_size = 32M
    query_cache_type = on

    (This section I added especially for Froala in the past. So before you install Froala I would recommend this. Mine was already installed so I had to run a script to convert it. Took me a lot of Google searching)
    collation-server = utf8mb4_unicode_ci
    init-connect='SET NAMES utf8mb4'
    character-set-server=utf8mb4

    If you have any other things I can trey with this MySQL CNF for better performance please share :) 

    • It's possible to use InnoDB with UNA, it's functioning better in hight concurrency, but some server side tuning is needed to avoid deadlocks. 

      First set (or add) the following line into inc/header.inc.php 

      define('BX_DATABASE_ENGINE', 'INNODB'); ///< db engine

      after 

      define('BX_DATABASE_NAME', 'xxxxxx'); ///< db name

      Then the following server side settings for MySQL is needed in [mysqld] section:

      innodb_stats_on_metadata = off
      innodb_buffer_pool_instances = 4 # number of CPUs
      innodb_buffer_pool_size = 4096M # ~80% of available RAM
      
      transaction_isolation = READ-COMMITTED
      lock_wait_timeout = 4
      innodb_lock_wait_timeout = 4
      innodb_rollback_on_timeout = 1
      binlog_format = MIXED
      innodb_log_file_size = 400M



      Kyle added a discussion to , INNODB

      It was fixed with the cache clear multiple times and a MySQL Reboot.

      Since doing a database change I keep getting the following database error: 

      Query:
      REPLACE INTO `bx_timeline_cache` SET `type` = 'owner',`context_id` = ,`profile_id` = '',`event_id` = '1328',`date` = '1576078059'

      Mysql error:
      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`profile_id` = '',`event_id` = '1328',`date` = '1576078059'' at line 1

      Location:
      The error was found in query function in the file /home/vybz/public_html/modules/boonex/timeline/classes/BxTimelineDb.php at line 409.

      collation_connection:
      utf8mb4_unicode_ci


      Has anyone ran into this after converting. With Froala when I was on MyISAM I had to change to utf8mb4_unicode_ci for collation. Could this be the problem, possibly needing to change type with ?  Ive also upgraded to Litespeed but that should be unrelated.  

      1
      ... or jump to: 2019