Character Position Sql , Make it more fast.

Hello this is Hesa,

Well i have a little problem here.
I send my position every 0.5 seconds to the server ( 0.5 cuz .4 make the game lag ).
So Photon receive my position and rotation and save it in sql.
But in sql the data happend a couple of second after.
Do you know why ?
If you need my code just ask it.

Comments

  • We can't really help with SQL here. This is a question for some database enthusiast forum.
    What we can say is that storing each player's position in a DB two times a second won't work. Keep it in memory and save it as rarely as possible.
  • 1. Ensure that the database server does not run on the same machine as photon
    2. Ensure that the DB server has an appriate amount of RAM cause with such a amount of requests firing through you will need a massive cache (depending on your CCU we talk about 16-128GB here), as a DB that is writting to filesystem will not hold up for more than a few thousand to a few ten thousand requests per second depending on the SELECTs and JOINs you send
    3. Ensure to configure your sql server correctly for such a usage (depends a bit on which sql server you use in detail)
  • Ok Thank you, i am currently on localhost so sql and photon are on same.

    Wich method you recommend me to save position and share it ?
    Can you help me more in detail with skype , teamviewer or anything i dont want you to do all the things, just to help me a bit , and i dont really know how to configure mysql for that usage.

    Thank you :D
  • Can someone tell me some theory about creating a photon/sql position systems ?
    Something Like WoW/Rift/Aion/Etc...
    What is the best time to save position , Interval ?
    do i make something like last position and current position and if the position is not equal then i show them move to new position ?

    If someone can take the time to explain this in details its gonna help me and alot of people.

    Plus, in game im gonna make a request to get all character online and with all basic info.

    From Photon how i send multiple string sql to unity ?

    Do i make something ... for each string send the info to unity.
    And unity is gonna receive it and generate object if the user is in range , else its online show them online and in wich map guild etc...

    I think its a good way , but if its not stop me please lol.
  • The storing to DB should never be of importance for what you show to clients.
    Storing to the DB is a 'replication' of the world state in case the player simply drops out.
    As such you don't need such high frequency writes, cause the avg movement speed is low enough that even losing 10s of movement data has no impact.

    If you have WoW alike flying mounts you would not store any positions during flying potentially but the last position at ground, unless you have WoW alike permanent flying. REason is that if it is temporal, the player might drop to dead in case of a relogin.


    Getting all the players online: Thats trivial, thats a single request and thus no heavy thing per se. The Cache of the db server just needs to be large enough to keep all online characters and the data you request in detail within the cache, so it can do it at little to no lookup costs.
    In such a case I would then actually encode the data into XML, compress it and send it through as a single packet, otherwise you might flood the connection too much.
  • dreamora wrote:
    In such a case I would then actually encode the data into XML, compress it and send it through as a single packet, otherwise you might flood the connection too much.

    I am actually struggling to get exactly this working. Do you know of a compression library that works with both Photon and Unity? I tried to use the compression libraries that come with visual studio, but there were problems having them work in Unity. I have also been trying to get DotNetZip to work and having limited success. From what I have thus far, it seems the XML is compressing and sending to the client fine, but when I try to decompress the compressed XML it comes out blank when using the ZipInputStream class.

    If anyone has had success with this, or a different library, I'd appreciate knowing which library and/or an example of how you get it to work.

    Thanks.
  • Did you ensure that you compress it and then send the bytes and that you are not by error doing it as string? otherwise you might be hit by text encoding problems unless you explicitely specify the string encoding (and even then, bytes are cleaner and granted)
  • Hmm, I have been using this method on the server:
            public string Compress(string text)
            {
                byte[] buffer = Encoding.UTF8.GetBytes(text);
                MemoryStream ms = new MemoryStream();
    
                using (ZipOutputStream zip = new ZipOutputStream(ms))
                {
                    zip.CompressionLevel = Ionic.Zlib.CompressionLevel.BestCompression;
                    zip.Write(buffer, 0, buffer.Length);
                }
    
                ms.Position = 0;
                MemoryStream outStream = new MemoryStream();
    
                byte[] compressed = new byte[ms.Length];
                ms.Read(compressed, 0, compressed.Length);
    
                byte[] gzBuffer = new byte[compressed.Length + 4];
                System.Buffer.BlockCopy(compressed, 0, gzBuffer, 4, compressed.Length);
                System.Buffer.BlockCopy(BitConverter.GetBytes(buffer.Length), 0, gzBuffer, 0, 4);
    
                return Convert.ToBase64String(gzBuffer);
            }
    

    and that is placed in a string in the response.

    Then on the client:
        public string Decompress(string compressedText)
    
        {
            byte[] compressedBuffer = Convert.FromBase64String(compressedText);
    
            using (MemoryStream ms = new MemoryStream())
            {
                int msgLength = BitConverter.ToInt32(compressedBuffer, 0);
                ms.Write(compressedBuffer, 4, compressedBuffer.Length - 4);
    
                byte[] buffer = new byte[msgLength];
    
                ms.Position = 0;
                using (ZipInputStream zip = new ZipInputStream(ms))
                {
                    zip.Read(buffer, 0, buffer.Length);
                }
    
                return Encoding.UTF8.GetString(buffer);
            }
        }
    

    Maybe I'm overlooking a silly mistake.