Prism

Database Connection Troubleshooting

    Prism handles the database connection as best as it can - but we can't always know what your setup is going to need, and it's up to you to adjust things.

    If you're seeing database connection issues, there are several things you can do to try to tailor Prism to your needs. These usually aren't problems with Prism - though we're always looking at ways of improving things. If the steps on this list don't work, please chat with us in IRC or file a bug.

    This document assumes you've had Prism connected to a MySQL database successfully, but you're seeing various connection errors.

    Lock Wait Timeout / Lock Exhaustion errors

    If you're seeing errors similar to Lock wait timeout exceeded; try restarting transaction or The total number of locks exceeds the lock table size it typically means that your mysql server is running out of memory for table locks.

    MySQL InnoDB tables lock records that an sql query includes so that other queries won't also modify the query. It uses some of your memory to store information on the locks and the transactions. By default, MySQL only allocates 8MB for this.

    This error mostly shows up during Prism purges, especially when a lot of new data is being written as the purges run. We're looking into ways to help avoid this issue, but we can only do so much.

    Check over the following list for a few options (in order of necessity):

    • Later Prism 2 Betas added an index on the prism_data.epoch column which is often used in delete queries. Make sure that index is added: ALTER TABLE prism_data ADD INDEX (epoch)
    • If you run your own mysql server, allocate more memory for locking. Adjust the innodb_buffer_pool_size mysql configuration and reboot mysql.
    • Prism only runs purges on server boot. There are some technical reasons we haven't yet made it more automated on a schedule. Make sure you're running purges often. The fewer the records in the table, the less worry locking should be.
    • Some users have written their own purge scripts in other languages that they run regularly through cron or other scheduling tools.
    • Try reducing the number of purges and purge record limits.

    Pool Exhaustion Errors

    These issues usually happen for these reasons, usually two are involved:

    • Prism can't communicate with MySQL fast enough - your mysql is across the internet, the machine is very slow, etc.
    • There's extreme amounts of data being logged, and that's eating your connections.
    • There's a problem with the database server - possibly disk errors, connection errors, corrupt tables, memory problems, etc.

    Why?

    Before you start tweaking the database connection settings, we need to find out why it's having issues to begin with. Check the following things:

    Database is not corrupt, queries function quickly in mysql directly.

    Try a query to a mysql database directly, without java in the way. Run something like SHOW COLUMNS FROM prism_actions;

    How fast does that return? If any of your test queries take a long time, you need to look into what's wrong with the database/server.

    Are you logging data you don't care about?

    First off, make sure water-flow and lava-flow are disabled. They're horribly spammy and hardly ever useful. Items that water/lava break are still recorded. The flow events will seriously burden your database and the connections.

    If you have them off, try looking at the data being logged.

    Use this query to see which actions are being logged most:

    SELECT COUNT(id), action_type FROM prism_actions GROUP BY action_type ORDER BY COUNT(id) DESC;

    Might give you insight into what's taking up space that you may not need.

    What are all those connections doing?

    Run the following query in your mysql server: SHOW FULL PROCESSLIST;

    What are all of those doing? You might want to make a http://pste.me of the results and bring them to prism IRC. We'll help.

    Remote?

    Most people who see these errors are on remote mysql servers. That means your database server is not on the machine that your Minecraft server is on and for every single database query that traffic has to travel across the internet.

    Local/networked mysql servers are much faster and always recommended, but not always an option. If moving mysql to a local server isn't possible, try the following:

    Recommendation:

    • Increase pool connection limit, but check with your mysql service provider on what your limit is
    • Try increasing or decreasing the max wait.
    • Increase the actions per batch if you're not extremely tight on server memory. Check with your service provider on how much data a single query is limited to. Rarely, services have it set extremely low.

    Adjust Settings

    If the database is good, and the data being logged looks normal, then it's simply a problem with speeds.

    prism.database.max-pool-connections - Increase this to allow the pool to open more connections to your server. The more connections, the more data can be sent to mysql and the less long traffic times will impact you.

    prism.database.max-wait defines how long the pool will wait for a connection before throwing errors. You can try to increase this, so that it'll wait longer for connections to finish.

    actions-per-insert-batch refers to how many actions are sent per batch insert. The only things that limit this are memory (ram), and your mysql server's setting for max_allowed_packets. It's very possible to increase this number to 5000 or higher.

    Every time the recorder runs, it will empty the queue with batch insert statements, and by changing it to 5000 instead of 1000, increases the speed that the queue is emptied dramatically. A 110k block world edit saves in 19 seconds with the default settings, but saves in 5 seconds with the increased batch size.

    Check Your Tables

    Corrupt tables or problems with mysql will cause errors or slowness. Make sure it's all working well.

    CHECK TABLE prism_actions;

    And if needed:

    REPAIR TABLE prism_actions

    Wiki markup to link to this page: [[database-connection-troubleshooting]] or [[database-connection-troubleshooting|Database Connection Troubleshooting]]