Configuration for a Medium Database with low number of Connections.
Hi, hope everyone is well.
I am currently using mysql for a local (all users on LAN) database system used for keeping track of media files and information about them, equating to about 100-200 unique fields for each file. The querying and database updating is handled by third party software and can not be tweaked much. We are using the stock configuration file for "large systems with 1-2GB of ram" that came with mssql (so most variables are default), however, I am most certain this is far from optimized for our needs. Currently our database is a little over 2GBs and growing at about a GB a year. At any given time, there are no more than 20 connections. There is around 5 queries a minute. Queries are usually only on 2 or fields at a time (say file name and type.) The database is frequently being updated by clients (multiple times a minute,) on multiple (lets say 15-20) clip fields at a time. In addition to the unique queries taking place, there are many (20-30) queries that run over and over (say the same query every 3 minutes), to check for updates in the database. Additionally, many of our queries we frequently use (like one to test for correctly named files, for instance,) rely on long, ambiguous regexp expressions. These queries can take very long amounts of time (30+ seconds), and can get even longer when combined with another term. Temporary tables are constantly being created, this seems to be the time limiting factor when it comes to speed. Updating (inserting into) the database also takes some time (20-45 seconds). Queries, particularly those with regexp, and inserts lock up the database for their duration, which can drastically slow our workflow. Our server is running on a Mac Server (OSX) with an Octcore i7 (I believe,) with hyper threading (16 logical cores) and 32GB or Ram. At any given moment, our memory utilization by msql is under 350MBs. My questions are, how can I get MySQL to use more of the RAM at its disposal, and/or use more than one core per "task" (seeing 100% CPU utilization of ONE of the 16 logical processors at a time during long query or set?) Are their any other setting I should look at changing (assuming a near stock config currently) that would allow greater database efficiency? Re-writing queries is not really an option, as I have said, so my tweaking would be limited to the configuration of the database itself. Thank you so much and have a great day.