FullText Search sped up, with DB speeding up
So, I was googling around and found MyISAM as MySQL storage engine to be a speed killer. And what do you know? Our Forum software was using MyISAM for most tables.
So I converted some of the database tables from MyISAM to the newer InnoDB. The performance gains were small at first. But InnoDB allowed me in turn to set a big "innodb_buffer_pool_size" of about a Gigabyte of RAM with our MariaDB Server. Now after doing a first Search, successive searches are much much much faster, as the underlying database table gets cached by our database server now.
So....Despite our over 1 Million Posts, the Search now will return within 3 to 4 seconds in most cases.
Unfortunately, the Database Server eats too much RAM. Had to reduce it to a few hundred MB, so the machine doesn't grind to a halt again. The Search has become faster anyhow.
Additionally, thanks to using InnoDB now, the main page shouldn't get hung up any more, if someone else is doing a Search. How so? - Well the former Database engine MyISAM, was doing transactional locking on the whole table, freezing the Forum while a search was running. The InnoDB engine, in contrast has 'row locking'; so multiple read-accesses can be running on the same table at the same time. Thus fixing the hang-ups we have had.