[strat]

I installed everything correctly and my server seems to be running great, except when I create Zones and save them they will still be there if I were to change the map and change it back, but when the server restarts the zones are not there and it says Zonegroup not found. In order to load the zones I need to go into the admin menu and manually load the zones. Any way to automatically load the zones?

[Cooky]

Originally Posted by strat I installed everything correctly and my server seems to be running great, except when I create Zones and save them they will still be there if I were to change the map and change it back, but when the server restarts the zones are not there and it says Zonegroup not found. In order to load the zones I need to go into the admin menu and manually load the zones. Any way to automatically load the zones?

LOL.. nice reading... litteraly 5 posts above yours: https://forums.alliedmods.net/showpo...postcount=2544

[MrJerry]

Hello,

I use CkSurf for a minigames server, but when all player in one team are dead the round don't restart..
i try to put some line in my server.cfg but still the same please someone can explain me how to resolve that ?
ty

[sneaK]

So I've come to find out that there is a very inefficient query that causes serious SQL usage, and in my experience up to almost 30 second query times with a populated database.

char sql_selectRecordCheckpoints[] = "SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = '%s' AND mapname='%s' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35 FROM ck_bonus a LEFT JOIN ck_checkpoints b ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup WHERE a.mapname = '%s' GROUP BY a.zonegroup";

(line 72 in sql.sp)

Cleaned up, readable query:

PHP Code:

SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35
FROM ck_checkpoints
WHERE steamid = '%s' AND mapname='%s' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35
FROM ck_bonus a LEFT JOIN
     ck_checkpoints b
     ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup
WHERE a.mapname = '%s'
GROUP BY a.zonegroup"; 


I ran a profile on a sample query, this is what I came up with:

Spoiler

mysql> SET PROFILING=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = 'STEAM_1:0:4040087' AND mapname='surf_mom' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35 FROM ck_bonus a LEFT JOIN ck_checkpoints b ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup WHERE a.mapname = 'surf_mom' GROUP BY a.zonegroup;
+-----------+---------+---------+---------+--------+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| zonegroup | cp1     | cp2     | cp3     | cp4    | cp5     | cp6  | cp7  | cp8  | cp9  | cp10 | cp11 | cp12 | cp13 | cp14 | cp15 | cp16 | cp17 | cp18 | cp19 | cp20 | cp21 | cp22 | cp23 | cp24 | cp25 | cp26 | cp27 | cp28 | cp29 | cp30 | cp31 | cp32 | cp33 | cp34 | cp35 |
+-----------+---------+---------+---------+--------+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
|         0 | 5.92969 | 12.2344 | 20.1328 | 27.832 | 34.7344 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
|         1 |       0 |       0 |       0 |      0 |       0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
+-----------+---------+---------+---------+--------+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
2 rows in set (27.47 sec)

mysql> SHOW PROFILE FOR QUERY 0;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000243 |
| checking permissions |  0.000017 |
| checking permissions |  0.000011 |
| checking permissions |  0.000017 |
| Opening tables       |  0.000188 |
| System lock          |  0.000026 |
| optimizing           |  0.000058 |
| statistics           |  0.000102 |
| preparing            |  0.000031 |
| optimizing           |  0.000019 |
| statistics           |  0.000076 |
| preparing            |  0.002882 |
| Creating tmp table   |  0.000118 |
| Sorting result       |  0.000020 |
| executing            |  0.000012 |
| Sending data         |  0.000195 |
| executing            |  0.000014 |
| Sending data         | 27.463481 |
| Creating sort index  |  0.000126 |
| optimizing           |  0.000015 |
| statistics           |  0.000023 |
| preparing            |  0.000024 |
| executing            |  0.000013 |
| Sending data         |  0.000043 |
| removing tmp table   |  0.000017 |
| Sending data         |  0.000014 |
| removing tmp table   |  0.000013 |
| Sending data         |  0.000012 |
| query end            |  0.000016 |
| closing tables       |  0.000026 |
| freeing items        |  0.000323 |
| logging slow query   |  0.000065 |
| cleaning up          |  0.000030 |
+----------------------+-----------+
33 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = 'STEAM_1:0:4040087' AND mapname='surf_mom' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35 FROM ck_bonus a LEFT JOIN ck_checkpoints b ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup WHERE a.mapname = 'surf_mom' GROUP BY a.zonegroup;
+----+--------------+----------------+------+-------------------+-----------+---------+-------------+--------+--------------------------------------------------------+
| id | select_type  | table          | type | possible_keys     | key       | key_len | ref         | rows   | Extra                                                  |
+----+--------------+----------------+------+-------------------+-----------+---------+-------------+--------+--------------------------------------------------------+
|  1 | PRIMARY      | ck_checkpoints | ref  | PRIMARY           | PRIMARY   | 68      | const,const |      2 | Using where                                            |
|  2 | UNION        | a              | ref  | PRIMARY,bonusrank | bonusrank | 98      | const       |   1062 | Using index condition; Using temporary; Using filesort |
|  2 | UNION        | b              | ALL  | NULL              | NULL      | NULL    | NULL        | 159232 | Using where; Using join buffer (Block Nested Loop)     |
| NULL | UNION RESULT | <union1,2>     | ALL  | NULL              | NULL      | NULL    | NULL        |   NULL | Using temporary                                        |
+----+--------------+----------------+------+-------------------+-----------+---------+-------------+--------+--------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SHOW INDEXES FROM ck_checkpoints;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ck_checkpoints |          0 | PRIMARY  |            1 | steamid     | A         |       53077 |     NULL | NULL   |      | BTREE      |         |               |
| ck_checkpoints |          0 | PRIMARY  |            2 | mapname     | A         |      159233 |     NULL | NULL   |      | BTREE      |         |               |
| ck_checkpoints |          0 | PRIMARY  |            3 | zonegroup   | A         |      159233 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> SHOW INDEXES FROM ck_bonus;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ck_bonus |          0 | PRIMARY   |            1 | steamid     | A         |        6236 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          0 | PRIMARY   |            2 | mapname     | A         |       24945 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          0 | PRIMARY   |            3 | zonegroup   | A         |       24945 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            1 | mapname     | A         |         218 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            2 | runtime     | A         |       24945 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            3 | zonegroup   | A         |       24945 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

As you can see, it takes an insane amount of time, and the join isn't using indexes when a map has a bonus. Maps with large numbers of completions & players loading can cause servers to see severe delays in initial player loading times.

Any mysql wizards out there that might be able to help with this?

Edit: SOLVED, see this post: https://forums.alliedmods.net/showpo...postcount=2556

[fluffys]

Originally Posted by sneaK So I've come to find out that there is a very inefficient query that causes serious SQL usage, and in my experience up to almost 30 second query times with a populated database. char sql_selectRecordCheckpoints[] = "SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = '%s' AND mapname='%s' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35 FROM ck_bonus a LEFT JOIN ck_checkpoints b ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup WHERE a.mapname = '%s' GROUP BY a.zonegroup";

The query only takes around 0.2 - 0.3 seconds for me, however you could possibly just modify the query and remove the bonus checkpoints, I don't think there's any surf map that has a staged bonus, so the checkpoints for bonuses is rather useless

SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = '%s' AND mapname='%s' AND zonegroup = 0;

[sneaK]

Originally Posted by fluffys The query only takes around 0.2 - 0.3 seconds for me, however you could possibly just modify the query and remove the bonus checkpoints, I don't think there's any surf map that has a staged bonus, so the checkpoints for bonuses is rather useless SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = '%s' AND mapname='%s' AND zonegroup = 0;

That's what I was thinking, I don't have any staged bonuses but I don't want to remove the functionality of bonus checkpoints. I'm using this query as a temporary workaround, but it's not a proper fix.

Could you list your ck_bonus and ck_checkpoints indexes for me? If it's not asking too much, could you also do an EXPLAIN like I have above so I can see the result of yours? I'd be interested if it was different for some reason.

Also, the time it takes varies with the amount of completions total, and on the map since as you can see in the EXPLAIN, it is essentially doing a full db scan.

[fluffys]

Originally Posted by sneaK That's what I was thinking, I don't have any staged bonuses but I don't want to remove the functionality of bonus checkpoints. Could you list your ck_bonus and ck_checkpoints indexes for me? If it's not asking too much, could you also do an EXPLAIN like I have above so I can see the result of yours? I'd be interested if it was different for some reason. Also, the time it takes varies with the amount of completions total, and on the map since as you can see in the EXPLAIN, it is essentially doing a full db scan.

My ck_bonus will be different because I've added styles

Spoiler

--------------
SHOW INDEXES FROM ck_checkpoints
--------------

+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ck_checkpoints |          0 | PRIMARY  |            1 | steamid     | A         |      114450 |     NULL | NULL   |      | BTREE      |         |               |
| ck_checkpoints |          0 | PRIMARY  |            2 | mapname     | A         |      343352 |     NULL | NULL   |      | BTREE      |         |               |
| ck_checkpoints |          0 | PRIMARY  |            3 | zonegroup   | A         |      343352 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

--------------
SHOW INDEXES FROM ck_bonus
--------------

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ck_bonus |          0 | PRIMARY   |            1 | steamid     | A         |       21493 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          0 | PRIMARY   |            2 | mapname     | A         |       42987 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          0 | PRIMARY   |            3 | zonegroup   | A         |       42987 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          0 | PRIMARY   |            4 | style       | A         |       42987 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            1 | mapname     | A         |         605 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            2 | runtime     | A         |       42987 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            3 | zonegroup   | A         |       42987 |     NULL | NULL   |      | BTREE      |         |               |
| ck_bonus |          1 | bonusrank |            4 | style       | A         |       42987 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

--------------
EXPLAIN SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = 'STEAM_1:0:20019070' AND mapname='surf
_mom' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp3
5 FROM ck_bonus a LEFT JOIN ck_checkpoints b ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup WHERE a.mapname = 'surf_mom' GROUP BY a.zonegroup
--------------

+----+--------------+----------------+------+---------------+-----------+---------+---------------------+------+-----------------------------------------------------------+
| id | select_type  | table          | type | possible_keys | key       | key_len | ref                 | rows | Extra                                                     |
+----+--------------+----------------+------+---------------+-----------+---------+---------------------+------+-----------------------------------------------------------+
|  1 | PRIMARY      | ck_checkpoints | ref  | PRIMARY       | PRIMARY   | 196     | const,const         |    2 | Using where                                               |
|  2 | UNION        | a              | ref  | bonusrank     | bonusrank | 98      | const               | 9074 | Using where; Using index; Using temporary; Using filesort |
|  2 | UNION        | b              | ref  | PRIMARY       | PRIMARY   | 98      | surftimer.a.steamid |    4 |                                                           |
| NULL | UNION RESULT | <union1,2>     | ALL  | NULL          | NULL      | NULL    | NULL                | NULL |                                                           |
+----+--------------+----------------+------+---------------+-----------+---------+---------------------+------+-----------------------------------------------------------+

[sneaK]

Holy crap. SOLVED, after like a week.

Apparently you can't use indexes with a join statement between 2 tables that have different collations. I think the initial installation of cksurf created ck_bonus using collation 'utf8_general_ci', and ck_checkpoints using 'latin1_swedish_ci'. Changing ck_bonus to use latin1_swedish_ci did the trick, sped up queries by over 100x.

Thanks for the help fluffys!

[zwetch]

How do I add Bonus tiers from in-game with a command? Or can Bonus tiers only be added directly to the Database.

[sneaK]

Originally Posted by zwetch How do I add Bonus tiers from in-game with a command? Or can Bonus tiers only be added directly to the Database.

sm_addmaptier - Usage: sm_addmaptier <ZoneGroup> <Tier>

Zonegroup 0 = Main map
Zonegroup 1 = Bonus 1
Zonegroup 2 = Bonus 2

etc