Can this work along with MySQL Async, meaning would I be able to ensure both resources in the server config and they wouldn’t have conflicts? I really wanna test this resource on my inventory mod and see if it has some performance gains but well, don’t wanna do that if I’m forced to also port the other queries of the other 80+ resources to oxmysql.
I ask this because of the compatibility layer added in with the aliases you have
EDIT: nvm, read the posts a little bit and I’m doing as instructed
Alright, I ported everything from mysql-async to oxmysql, it was quite easy, really appreciate the compatibility layer.
Unfortunately it didn’t seem to fix the performance issues with my inventory as some queries are still taking longer than 150ms.
And I don’t really get it, rn the server has 9 people, and it’s struggling with a query as trivial as this:
[ script:oxmysql] [WARNING] atro-newinventory took 166.33ms to execute a query!
[ script:oxmysql] DELETE FROM atro_inventory WHERE owner = ? AND slot = ? LIMIT ? [“Char1:11000011032adfc”,“15”,2].
This also happened with async, any idea of what’s going on?
Out of curiosity, how many rows are in the atro_inventory table? Also, is the MySQL server localhost or remotely connected?
As of right now, there’s 58472 rows, in database terms is such a low number
And to my second question? Is the mysql server localhost or remote?
Should be localhost, all on the same live server
You can always tell based on what your mysql connect string looks like in the server.cfg.
Can I ask why you’ve got 58 thousand rows in your inventory table? It’s doubtful you have that many active players and your environment is struggling to sort through that many to find 2.
I would write a function that removes table entries from you DB for user IDs that haven’t been seen in x amount of months. You can also set the gameserver on a server with more resources available(processor, memory).
Each item represents a row, because each item has it’s own durability even if it’s stacked on top of another item, and some of them have their own set of information, so indexing an item by x parameter is way easier, databases were designed this way. I couldn’t imagine doing a query to see if the player has x amount of items by selecthing a whole LONGTEXT (a JSON structure) then doing the for loop in LUA to count the items then proceeding to do X thing if the count matched or surpassed the requirement, that would’ve taken way longer.
Still I would’ve expected this level of performance on a table with millions of rows, not just 50k, that’s dev environment amounts of data on a regular webapp project
I understand your point on searching an array inside a row but as for it being a trivial amount of data, It seems that your server disagrees with you.
I’ve not witnessed any performance issues using oxm and I run it on some pretty wimpy VMs but that being said, my dev environments don’t often deal with a large number of rows, so I’ll leave that discussion for someone with real world experience.
My suggestion to prune dead users remains, however. I’ve done that in the past to improve live server performance and it worked well to restore and maintain performance.
Seems like there’s no saved state on the server, rather accessing the inventory is always a database query. Table structure is something like
owner slot data Char1:11000011032adfc 15 {name: “bread”, count: 3}
or
owner slot name count … Char1:11000011032adfc 15 bread 3 …
58472 is a ridiculously high row count for a server that doesn’t seem to be very active (9 people) even if considering multiple characters and non-player inventories. Since your resource seems to have high traffic with the database you may need to optimise mysql server settings such as cache size. It’s also a good idea to properly index your columns when they are used for table lookups (owner, slot).
There may be some other optimisations to look at like using prepared statements and building bulk queries.
Another thing, server hitches could falsely report a query as being slow.
Database engines on their own are incredibly optimized, because they’ve been designed precisely to deal with insanely large amounts of data, using super complex logic to find and index information as soon as possible. That’s why I kinda cringe when I see people using LUA IO library to store data in a file instead of using a table on a database.
It’s normally 72 players, but it’s an UK server so at the time of writting it was sleep time for most of the players.
Attempting your suggestion would basically remove the uniqueness of an individual item. Say, I have a piece of paper item, is stackable to 10, I can write stuff on each individual item, that info is stored on a separate column called data as JSON. The way you’re suggesting it would not allow me to stack the item or the info would be lost or replaced by the last item added to the stack. Let alone individual item durability for those that spoil or break after some time.
I’ll consider indexing those two columns, and use oxmysql prepare on most of these constantly reused queries, see if it makes any noticeable difference
Wasn’t sure if you had metadata.
I’ll consider indexing those two columns
You may need to do multi-indexes but depends on your queries.
use oxmysql prepare on most of these constantly reused queries, see if it makes any noticeable difference
I’ve generally noticed prepare becomes useful once you have two or more parameters, with each parameter widening the performance difference; though with the latest release it may be a bit slower due to a change to use transactions.
I have some more performance tweaks coming for the next update.
Hello j’ai un problème dans ma console :
Ma ligne de configuration est comme ceci :
set mysql_connection_string “mysql://u4181_h6iwRnXK9z:u4181_h6iwRnXK9z@185.117.0.**/s4181_SevenSky?charset=utf8mb4”
Je ne peux pas modifier le nom de ma base de donné ni mes identifiants car j’ai un hébergeur de jeux fivem donc je ne peux pas choisir cela
English, dude. English.
Use the other connection string format since Pterodactyl hates the former.
sorry
when you say use the other connection string, is that the one?
set mysql_connection_string “mysql://u4181_h6iwRnXK9z:.DOibXNy.u=LhbopV@@gz6ce@185.117.0.**/s4181_SevenSky?dateStrings=true”
because I have already tried this solution but I always have the same error
Live server is working under the prepare statements now, no luck unfortunately 
Look into some innodb optimisations here.
Or you could try installing MyRocks and see how it compares.
What’s the query speed like when executing the query directly, rather than through oxmysql?
This one for example, I can try to compare it but unfortunately oxmysql log isn’t showing the correct data that’s passed to the query (instead of 373295 it should be a string that starts with ‘CharX:’
script:oxmysql] [WARNING] atro-newinventory took 161.9505ms to execute a query!
[ script:oxmysql] SELECT id, itemName, COUNT(itemName) AS quantity, information, slot, orientation, IF(expirationDate IS NOT NULL, TIMESTAMPDIFF(SECOND, creationDate, expirationDate), -1) AS fullQuality, IF(expirationDate IS NOT NULL, TIMESTAMPDIFF(SECOND, NOW(), expirationDate), -1) AS quality FROM atro_inventory WHERE owner = ? GROUP BY itemName, slot [373295]
ok, I could Identify who it was on this one, using dbeaver this one happened in 309ms
[ script:oxmysql] [WARNING] atro-newinventory took 233.965ms to execute a query!
[ script:oxmysql] SELECT id, itemName, COUNT(itemName) AS quantity, information, slot, orientation, IF(expirationDate IS NOT NULL, TIMESTAMPDIFF(SECOND, creationDate, expirationDate), -1) AS fullQuality, IF(expirationDate IS NOT NULL, TIMESTAMPDIFF(SECOND, NOW(), expirationDate), -1) AS quality FROM atro_inventory WHERE owner = ? GROUP BY itemName, slot [37372]
I don’t think doing it this way is a good benchmark
