Data Base Tuning

[eortizn85]

Hello,

We want to do some tuning to our Vicidial Data Base server and read around the forum that there should be some crons set up to clean tables periodically and there is a script somewhere that can help us to optimize our DB. So I was wondering if there is any kind of documentation about where are those scripts and also which crons should I enable to do all the cleaning, or in any case, if is there a general guide to optimize the data base.

My specs:
- Vicidial : 2.12-496a
- Server: 8 core Intel Xeon E5520 @2.27GHz, 10 GB RAM

PS: I'm not familiar with MySQL but I will read up while this post gets any kind of directions

cheers

[williamconley]

1) Always post your Vicidial Version With Build (you left off the build).

2) You should also always post your Installation Method with Version (or link to instructions if it was not from a Vicidial Installer .iso).

3) WHY do you "want to do some tuning"? Note that the Vicidial Installers usually set the my.cnf values properly for Vicidial. Changes are more likely to do harm than make improvements. Are you experiencing some sort of problem or just looking around for something to break?

4) Scripts for servers installed via the Vicibox Installer are in your crontab -e file. Some are commented out. Each script has options in it (if you read the file or execute the script with --help). Have a look in /usr/share/astguiclient and read all those scripts if you want to be proactive and see what toys are out there.

5) Also look in the docs folder (/usr/src/astguiclient/trunk contains lots of cool stuff, including the docs folder, if you installed via Vicibox).

Happy Hunting

PS: I do NOT recommend doing any "tuning" unless you did not install with the Vicibox .iso image. Just sayin'.

[eortizn85]

Hello William, thank you for your quick response.

We were having the "too many connections" error, we increased the RAM and for now it's solved; but reading around the forum found that installing more RAM may not be a permanent solution, so I'm trying to educate myself a bit for a better management of our Vicidial database.

I hear the warning, we are going to play with our test Vici first

Thanks again, cheers

[williamconley]

Too many connections = you need to increase the mysql connections limit. If you had installed with Vicibox's .iso, you'd have that setting already ...?

[eortizn85]

Hello again, I'm particularly interested in these scripts:

AST_DB_optimize.pl
AST_Flush_DBqueue.pl
AST_DB_dead_cb_purge.pl

I would like to know what will they do, I couldn't find a more detailed info about it.

Too many connections = you need to increase the mysql connections limit. If you had installed with Vicibox's .iso, you'd have that setting already ...?

Yes, we increased the mysql connections a couple of times. We had that kind of event a couple of months ago, but we are trying to have a pre-emptive strategy.

Thanks again!

[williamconley]

Hello again, I'm particularly interested in these scripts:

AST_DB_optimize.pl
AST_Flush_DBqueue.pl
AST_DB_dead_cb_purge.pl
...

optimize script will use the mysql system to optimize tables. that's a mysql function. if you run the script with --debug and watch mysql for queries, you'll see that it optimizes table.

flush db queue runs cleanup queries in the DB. run it. watch the queries that run. they are a housekeeping tool to clean up DB entries.

dead_cb_purge will likely delete callbacks that are no longer needed. once again, run it (with --debugX) and you should see what it does.

in all cases perl scripts should be able to run with "--help" to get options. and they can be read as text files since many of them have notes.

Yes, we increased the mysql connections a couple of times. We had that kind of event a couple of months ago, but we are trying to have a pre-emptive strategy.

pre-emptive = you should have installed with Vicibox.com's iso OR at least referred to a virtual installation of vicibox's iso for configuration settings such as my-bigvici.cnf

[eortizn85]

Again, thanks a lot William.

I didn't personally installed Vicidial, thats why I'm trying to understand more of it.

[williamconley]

in that case, install a virtual vicidial right now. you'll gain a deeper understanding. and it's free. if you don't have virtualbox or VMware on your workstation or a vSphere/Proxmox server available, those are all free, too! LOL

And you can install a dozen times until you feel comfortable with it. Even if you don't have the bandwidth to host your own server this is very useful practice.