ID:1872728
 
BYOND Version:508
Operating System:Windows 8 64-bit
Web Browser:Chrome 43.0.2357.124
Applies to:Dream Seeker
Status: Open

Issue hasn't been assigned a status value.
Descriptive Problem Summary: SQL queries are causing CPU spikes, and in our particular use of it we're transferring players from server to server. When the code is waiting for a response from the remote MySQL server, it seems to wait that amount in real-time.

Also, if the MySQL server goes offline or stops responding, the server essentially crashes.

We spoke to this issue with Lummox before and he acknowledged that something unusual was happening.
The MySQL Implementation seems very slow in general on BYOND for some reason.
This isn't CPU usage, it's because the SQL query is blocking.
Whoops. This isn't a beta bug - could it be moved to 'bug reports'?

Thanks.
How are we measuring the CPU here?
You can easily see that there is a "pause" when using

http://www.byond.com/forum/?post=1881983

Just compile run and watch the profiler, while using the verb Do i get anything, which runs the proc Check_Payment_System()

Check_Payment_System() does a total of two MySQL queries (small queries) but it seems DreamSeeker freezes for a good 1 - 2 seconds.

Of course this could be down to latency I'm around 253ms from mysql.byondpanel.com

Payment Check() also freezes DS..

A way around this is to have MySQL on each box that holds each server allowing for quick localhost calls, you can set up Master to Master replication with MySQL to force all the records across the "cluster", ideally you shouldn't need to but it'll work.
In response to Stephen001
Here's a quick snapshot of a CPU profile:
http://puu.sh/iIA29/e2db5e166b.png

A single MySQL query (regardless of size or complexity, it seems) takes roughly 0.068 cpu and will send world.cpu spiking up to +50% higher than normal values for as long as it the query takes to fully execute.

We have a background process running that updates the amount of players in a several on an external database, for example, every 5 seconds. The query looks something like:

UPDATE `servers` SET `Players`=[players] WHERE `Address`='[world.address]:[world.port]'


Whereas our much larger query used for updating the `player_savefiles` table, which takes up noticeably no more cpu than the previous query, looks like:

var/header = "(`Name`, `Ckey`, `Slot`, `Coords`, `Dir`, `Preview`, \
`Gender`, `HP`, `MP`, `EXP`, `EXP_Next`, `Level`, `Max_HP`, `Max_MP`, \
`Melee`, `Agility`, `Spell`, `Techniques`, `Accessories`, `Spell_Points`, \
`Stat_Points`, `Class`, `PVP_Score`, `Craft_Mem`, `Checkpoint`, `Misc_Data`)"


var/values = "('[P.name]','[ckey(P.key)]',[slot],'[coords]',[P.dir],\
'
[encoded_preview]',[gender],[P.hp],[P.mp],[P.exp],[P.exp_next],[P.lvl],[P.max_hp],\
[P.max_mp],[P.melee],[P.agility],[P.spell],'[encoded_techs]','[P.write_accessories()]',\
[P.spell_points],[P.stat_points],'[P.class]',[P.pvp_score],'[list2text(P.crafting_memory, ";")]','[P.checkpoint]','[assoc2text(P.misc_data)]')"

query = "INSERT INTO `player_savefiles` [header] VALUES [values]"
world.cpu doesn't measure how much your CPU is actually being used. What it measures is how much of the allotted tick time the server spends running procs. While it's waiting for a response from MySQL it's doing nothing, so the actual CPU usage is low, but world.cpu will be higher because it's measuring the ability to complete the tick on time.
In response to Lummox JR
I actually did not know that. Pretty informative, thank you!
How big roughly is this servers table (in terms of number of rows), and what's the latency like between the BYOND server and the MySQL server?

Similarly, are you able to operate a mysql native client on the same server as the BYOND server, if it's remote to the MySQL server?
The `servers` table looks like this:

CREATE TABLE `servers` (
`Network` varchar(100) NOT NULL COMMENT 'The name of the cluster.',
`Address` varchar(100) NOT NULL,
`Type` varchar(50) NOT NULL COMMENT 'The type of server this represents',
`Id` tinyint(3) unsigned NOT NULL COMMENT 'The ID of this server in the cluster.',
`Players` mediumint(8) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


The table doesn't have an index, could that cause any prbolems even if the table doesn't hold any more than 1-10 rows?
Here I am pinging from my machine (which I am testing on) to the MySQL server:




And no, I'm not able to operate a mysql client on the same server as the byond server.
That's a shame. Can you run a MySQL client local to the server? I'd be interested in you running the following

EXPLAIN UPDATE `servers` SET `Players`=[players] WHERE `Address`='[world.address]:[world.port]'

But substituting all the BYOND variables for appropriate values you currently find in the database. I kind of wonder if MySQL is basically constantly hard-parsing the query every time as you don't have bind variables for your parameters, and that MySQL's server is loaded on CPU.

Can you get CPU load, memory use and disk I/O statistics from the machine the MySQL server is running on at all? If it's a standardish linux SSH you have to the MySQL server, I can provide commands to support that.
Unfortunately I'm using MySQL 5.5 and not 5.6, is the version that supports EXPLAIN UPDATE.

Not sure if this will help, but running EXPLAIN SELECT gives me the following:

EXPLAIN SELECT * FROM `servers` WHERE `Address`='192.168.1.233:5000'

------------
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,servers,ALL,,,,,1,Using where
------------


It's standard linux, also. I'm not too savvy with it, so I'd appreciate the commands.
In response to Doohl
Doohl wrote:
Unfortunately I'm using MySQL 5.5 and not 5.6, is the version that supports EXPLAIN UPDATE.

Not sure if this will help, but running EXPLAIN SELECT gives me the following:

> EXPLAIN SELECT * FROM `servers` WHERE `Address`='192.168.1.233:5000'
>
> ------------
> id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
> 1,SIMPLE,servers,ALL,,,,,1,Using where
> ------------
>


It's standard linux, also. I'm not too savvy with it, so I'd appreciate the commands.

free -m Free RAM

top List of processes + CPU

dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync disk I/O
Alternatively for ongoing disk I/O you've got:

iostat -d

or

iostat -n (if it's mounted on NFS, if in doubt, run anyway)

or

vmstat -d
The explain plan was a bit vague, sadly. Try the following:

SET profiling = 1;
UPDATE `servers` SET `Players`=[players] WHERE `Address`='[world.address]:[world.port]';
SHOW PROFILES;
// Select the query ID for your update for use below:
SHOW PROFILE ALL FOR QUERY [query id];
Here's all that run in a visual SQL program:

http://puu.sh/iIJSQ/6eca155135.png


As for the CPU/IO/Memory dump, do you want me to get it while receiving / executing queries?
In response to Stephen001
Also ioping

Doohl RAM should be OK to do now, its mainly disk I/O that'll be the issue IMO.

A lot of people will use SSD drives (especially on a VPS) for a MySQL server, VPS mainly share drives (and I/O) with the other containers, which could cause your hdd speed to slow down..
Unfortunately this is where a native MySQL client on the BYOND server would have been handy, to see what kind of effect the latency is playing on the profile, as that's the last of the environmental things worth exploring.

Which proc in your profile is running that update query (directly or indirectly through another call)? Might be worth sorting by total CPU, to help pick it out, if it's a hog.

Also yep, would agree with ATHK. You should be able to do a quick check of all the stats now really, and we're probably most interested in disk I/O (unless you're over-committed on RAM I guess, but it seems like any paging isn't affecting the query tons).
Page: 1 2