Alright I want to try to keep this topic serious because this affects how the server runs and will affect everyone here.
In my quest to migrate as much of the SQLite database into MYSQL I'm reaching the home stretch.
Right now the following is using MYSQL
ULX Ranks
Gbans
Damage logs
Point Shop 2
utime
The following still uses SQLite and sv.db and I do not know a way to migrate them into MYSQL or if they support MYSQL.
CAC
Experience
Man of the match (MOTM)
autoslays
If anyone can provide MYSQL information about these 4 plugins or possible alternatives to help eliminate them from the sv.db. I do not handle the plugins directly so I don't know if there are settings to enable MYSQL for these plugins. If anyone knows more details please post.
We will always need to have to use the file sv.db because not everything can use MYSQL or has a practical need to use a MYSQL database
Right now the sv.db file is 163 MB
In order to help improve lag on the server I propose the following.
1) Trimming the file of obsolete tables from the database.
There are 35 tables in sv.db
Many of these tables belong to plugins that are either now on MYSQL or are not used anymore
Including old damagelogs, Point Shop 1 data, Experimental Point Shop 2 Data, Spec DM Stats, Achievements, Utime (once migrated to mysql)
These will of course be saved and archived (and probably never looked at again)
2) Do some clean up of databases that are currently in use to ensure durability and stability
Autoslays :
There are 5081 players waiting to be autoslayed right now.
Problem:
Every round start it needs to read this list of 5081 players to ensure that nobody on the server is waiting to be slayed. This causes a little pause at every single round start. This also causes a little bit of lag when a new autoslay is added.
Solution:
1) Delete old records from before a certain point. This database records the time when the autoslay was made so we have an idea of how old these autoslays are. We have autoslays going back 2 years. While it might be funny to see a player come on from 6 months ago get autoslayed for an rdm this is severely effecting server performance. Most of these people are probably never going to return.
2) Delete the inf autoslays and any autoslay amount more than 3. These are players that were most likely banned after a request was made. If they rdmed more then 3 times they were probably banned anyway.
Man Of The Match (MOTM):
There are 3552 players waiting to claim a reward.
EVERY SINGLE one of these rewards are from a player who got first second or third place on a map and NEVER RETURNED to the server.
Problem:
Every round (not just the first round of the map) MOTM has to check 3552 rows of data to see if anyone on the server should get a prize. This contributes to the little pause at every round start and makes the server work a little harder at the end of the map when it needs to update the database of the prizes.
Solution:
Clear out every second place and third place pending reward.
These rewards are pretty lame anyway. This is a huge difference in rows. The pending first and second place winners contribute to about 3000 of the rows of data. Most of these players have left the server and will probably never return anyway. You could also opt to clear all the pending prizes including first place. NOTE: This will not affect wins. Your wins are stored in another table and that database is considerably smaller.
Experience:
Every player on the server, regardless if they have earned experience or not, gets a placeholder in the experience database. There are 118121 players in the experience database. This is the largest database remaining.
Problem:
Every time a player joins the server lags because it needs to read 118141 rows of bullshit players in order to put that little bar in the upper corner of your screen. This is one of the biggest causes of server join lag. The reason for this is its not efficient. The database looks you up by your steam id. This is a big no-no in the database world.
(Explaination) Steamid's are recorded as a string of text which is harder to search for in a database. The proper way that the author should have saved player information is to either use STEAM 64 or GMOD's built in uniqueid system. Steam 64 is a large number and unique ID is a hash computed based off of the players steam ID. Both are integers and would be much faster to search for than strings. Every single other database we use utilizes these methods as the primary method to search for players.
Solution:
A: Remove or replace Experience
B: Trim Experience. Delete all records that are level 1 prestige 0. This equates to 108723 rows of data. That's most of the database. Most of these players have between 0-100 exp. Now that we use PS2 weapons are not unlocked by experience. If a few of these players return they could make it up in a few rounds.
I have a MYSQL bucket list
1) Improve tweaks to caches and buffers
2) Micromanage indexes
3) Nightly Backups
4) Import the legacy permabans into gbans and clear the legacy bans.txt file & Make the bans.txt file read only so that bans don't accidentally populate in it.
5) Investigate if using a virtual ram drive for databases to eliminate disk i/o is possible. Its FREE and it uses any spare ram we have left over.
6) Install everything required to run a website off the sql server and move the gbans website directly to the sql server. It makes sense that the database guy should have access to work on the database site.
7) Install FTP and issue an SSL certificate for it.
8) Continue to harden server from possible break ins, update and upgrade. (Fun fact: About 50 bots attempt to break into the sql a day and fail)
9) Install dropbox so that the backups are saved off site to the cloud in the event of hardware failure.
10) Bend SQL Server Management Studio to my will and force it to work with mysql, install odbc drivers
11) Convert Experience to mysql
12) Convert MOTM to SQL
Am I missing anything? Any Suggestions Concerns or Questions?
In my quest to migrate as much of the SQLite database into MYSQL I'm reaching the home stretch.
Status of the Databases
Right now the following is using MYSQL
ULX Ranks
Gbans
Damage logs
Point Shop 2
utime
The following still uses SQLite and sv.db and I do not know a way to migrate them into MYSQL or if they support MYSQL.
CAC
Experience
Man of the match (MOTM)
autoslays
If anyone can provide MYSQL information about these 4 plugins or possible alternatives to help eliminate them from the sv.db. I do not handle the plugins directly so I don't know if there are settings to enable MYSQL for these plugins. If anyone knows more details please post.
The Next Steps
We will always need to have to use the file sv.db because not everything can use MYSQL or has a practical need to use a MYSQL database
Right now the sv.db file is 163 MB
In order to help improve lag on the server I propose the following.
1) Trimming the file of obsolete tables from the database.
There are 35 tables in sv.db
Many of these tables belong to plugins that are either now on MYSQL or are not used anymore
Including old damagelogs, Point Shop 1 data, Experimental Point Shop 2 Data, Spec DM Stats, Achievements, Utime (once migrated to mysql)
These will of course be saved and archived (and probably never looked at again)
2) Do some clean up of databases that are currently in use to ensure durability and stability
Autoslays :
There are 5081 players waiting to be autoslayed right now.
Problem:
Every round start it needs to read this list of 5081 players to ensure that nobody on the server is waiting to be slayed. This causes a little pause at every single round start. This also causes a little bit of lag when a new autoslay is added.
Solution:
1) Delete old records from before a certain point. This database records the time when the autoslay was made so we have an idea of how old these autoslays are. We have autoslays going back 2 years. While it might be funny to see a player come on from 6 months ago get autoslayed for an rdm this is severely effecting server performance. Most of these people are probably never going to return.
2) Delete the inf autoslays and any autoslay amount more than 3. These are players that were most likely banned after a request was made. If they rdmed more then 3 times they were probably banned anyway.
Man Of The Match (MOTM):
There are 3552 players waiting to claim a reward.
EVERY SINGLE one of these rewards are from a player who got first second or third place on a map and NEVER RETURNED to the server.
Problem:
Every round (not just the first round of the map) MOTM has to check 3552 rows of data to see if anyone on the server should get a prize. This contributes to the little pause at every round start and makes the server work a little harder at the end of the map when it needs to update the database of the prizes.
Solution:
Clear out every second place and third place pending reward.
These rewards are pretty lame anyway. This is a huge difference in rows. The pending first and second place winners contribute to about 3000 of the rows of data. Most of these players have left the server and will probably never return anyway. You could also opt to clear all the pending prizes including first place. NOTE: This will not affect wins. Your wins are stored in another table and that database is considerably smaller.
Experience:
Every player on the server, regardless if they have earned experience or not, gets a placeholder in the experience database. There are 118121 players in the experience database. This is the largest database remaining.
Problem:
Every time a player joins the server lags because it needs to read 118141 rows of bullshit players in order to put that little bar in the upper corner of your screen. This is one of the biggest causes of server join lag. The reason for this is its not efficient. The database looks you up by your steam id. This is a big no-no in the database world.
(Explaination) Steamid's are recorded as a string of text which is harder to search for in a database. The proper way that the author should have saved player information is to either use STEAM 64 or GMOD's built in uniqueid system. Steam 64 is a large number and unique ID is a hash computed based off of the players steam ID. Both are integers and would be much faster to search for than strings. Every single other database we use utilizes these methods as the primary method to search for players.
Solution:
A: Remove or replace Experience
B: Trim Experience. Delete all records that are level 1 prestige 0. This equates to 108723 rows of data. That's most of the database. Most of these players have between 0-100 exp. Now that we use PS2 weapons are not unlocked by experience. If a few of these players return they could make it up in a few rounds.
Down The Road
I have a MYSQL bucket list
1) Improve tweaks to caches and buffers
2) Micromanage indexes
3) Nightly Backups
4) Import the legacy permabans into gbans and clear the legacy bans.txt file & Make the bans.txt file read only so that bans don't accidentally populate in it.
5) Investigate if using a virtual ram drive for databases to eliminate disk i/o is possible. Its FREE and it uses any spare ram we have left over.
6) Install everything required to run a website off the sql server and move the gbans website directly to the sql server. It makes sense that the database guy should have access to work on the database site.
7) Install FTP and issue an SSL certificate for it.
8) Continue to harden server from possible break ins, update and upgrade. (Fun fact: About 50 bots attempt to break into the sql a day and fail)
9) Install dropbox so that the backups are saved off site to the cloud in the event of hardware failure.
10) Bend SQL Server Management Studio to my will and force it to work with mysql, install odbc drivers
11) Convert Experience to mysql
12) Convert MOTM to SQL
Am I missing anything? Any Suggestions Concerns or Questions?
![[Image: horizontal-design-element-3.png]](https://fg-a.com/lines/horizontal-design-element-3.png)
![[Image: MYFtvx7.gif]](https://i.imgur.com/MYFtvx7.gif)
