resource management of PvPGN

[DingGGu]

hello happywar! nice to meet you,
i operate a war3 pvpgn server in korea, (realtime online users max 9000) average 7000

i know pvpgn's resource management is bad (MySQL memory usage, PvPGN CPU usage)

how about improvement pvpgn memory?

in my opinion
now pvpgn's system saved users & clans data some unregular times (or shutdown server)
however, send real-time query to mysql?

or something other ideas for account memory usage.

[RElesgoe]

@HarpyWar I think PvPGN currently saves account and clan data periodically, not in real-time. Should we change that?

[HarpyWar]

No, we should not change that. Instead it breaks initial idea which is not a bad, I think.
Currently mechanism of the storage is on the graph:

1. When user logs into a server, all the user settings loads from a storage into a server memory.
2. Any account attributes changes (clans, rating, email, etc.) lead to the fact that account pointer will be added into a "dirtylist" and remain up there until usersync interval expires
3. Then "dirtylist" with accounts iterates userstep times and only "dirty" attributes update into a storage (after that they are marked as "clean"). If "dirtylist" has more than userstep count then server will wait next usersync seconds to update remaining accounts.
4. Account data remain up in memory until userflush interval expires since last access to one of account attributes. It also flushes userstep accounts at once. Flush mean that account still in memory, but it's attributes are not set.

usersync, userstep, userflush are defined in bnetd.conf

@DingGGu for now I can't tell anything about CPU usage by PvPGN. May be above values doesn't correspond for the accounts number on your server, for example userstep is too small and there are continuous saving and flushing. I don't know.
But I just found several unoptimized SQL queries, because of no Indexes for several columns. It may occurs a lot of memory and CPU usage by MySQL, because it needs to load all the table into a memory for search.
I can try fix that if you can send me your database dump (just for testing, harpywar@gmail.com).

[DingGGu]

@HarpyWar I can't send the database for you.
i hard coded link between PvPGN and CMS based on PHP (http://xpressengine.com)

My DB is over gigabytes.

• Our Server use some other process.
  when the account not login over 30days, we delete all of account data.

can we talk about pvpgn on IRC?

I'm not good at C Language. so i want to help your skills. :)

[anonbyte]

@DingGGu what name ur pvpgn server?

[HarpyWar]

Actually, accounts count do not affect a performance on PvPGN side (except slightly more memory usage). There are hashtables used for accounts where search is "immediately", independent of size.

So, the problem should be in MySQL. In addition to miss indexes there is also varchar(128) used for all fields instead of proper types for each data type. MSSQL is much smarter in this case and because work faster.
I will try to workaround this problem, but if you provide your large database I can do it faster and better.

P.S. I do not use instant messaging and I also do not know C language, just learn it from PvPGN code.

[DingGGu]

@anonbyte http://m16.ggu.la/
maybe you need korean.

Currently 7517 Users, 1892 Games Running, 1144 Channels available.

[HarpyWar]

@DingGGu you can set to NULL passhash1, email, acct_lastlogin_ip in a database dump if you care about the privacy of your players. File with dump can be uploaded here https://mega.co.nz

[DingGGu]

@HarpyWar I send the link for your email.
the table is very annoying.
PvPGN tables are pvpgn_member, pvpgn_Record, pvpgn_auth, pvpgn_friends, pvpgn_clan, pvpgn_clanmember, pvpgn_command_log, pvpgn_member_bnetd_loginlog

thank you.

[HarpyWar]

I was slight wrong in several items (according to a post above #85 (comment)):

1. All accounts loaded into a memory not always, but only:
   a) with plain storage type
   b) when rebuilding ladder files (when server starting in ladder.cpp > Ladders::rebuild; accountlist_load_all(ST_FORCE) with flag ST_FORCE is used in that function only).
   Count of accounts In a database does not affect on performance as I said, it only consume more memory when loading all accounts.
2. The same.
3. The same, but each attrubute was spawned a new query. Now it changed to merge modified attributes into a single query and then perform it.
4. Flush function does free memory from all user attributes (except id and username).
   Flush/sync period is only affect on how much accounts will be processed at once. For instance, it will iterate 100 accounts at once, then a server will do some work to handle client packets, and again return to flush/sync to process next 100 accounts, etc.

Also there is a global variable SQL_ON_DEMAND that allows change read mode. It was defined and each attribute was read in a single query. I have removed that definition to read all attributes in a single query from each table, and I have edited it to read only needed tables on demand, but not all at once.

@DingGGu you can examine commits above and apply the changes on your server.

I recommend you set a new config option userflush_connected = false. And userflush to a larger value (for instance, 3 hours), usersync and userstep are normal by default for any server. But you can change usersync if you need to save changes into a database frequently.
If your website is customized to modify account attributes directly in a database then you should update attributes through a PHP bot that is connected to your pvpgn server with performing a command /set user key value.

It's better to place database server on the same machine where pvpgn run, to except delay between large set of queries.

---

I have removed sql2 mode. It was designed to improve performance, but there is an initial architecture error due to a lot of sql queries, single query for each attribute. So, it Is anyway slower than sql mode that I have optimized.
And there are no web projects for "sql2", I guess this deletion almost will not affect anybody.

[DingGGu]

My Server uses daily-reboot. (restart bnetd)
when i disable this function(I defined on /etc/crontab kill my bnetd)
Memory use over 2G and SQL is also use Full load.

maybe this can solve memory problem.
but this is not solve basis problem.

[HarpyWar]

If I understand you correct you applied all the above patches, set config options and your server (pvpgn process) still uses a lot of memory and high cpu usage. Right?
If you have high mysql cpu utilization then you should explore sql queries in your web site and add proper indexes into tables. Because one high frequency sql query that selects data by field without index may be a bottle neck in your system.