Database performance

[biggerbananas]

Hi again,

I'm still tinkering and trying to get this set up properly.

I'm trying to get the db running on a separate server - which I've done, but performance is awful 15 - 20 seconds for every action on the site.

I've created a separate database server and sat this box on a private network - pretty much as per the suggestions in the docs. I currently have the frontend and backend on one box at the moment. This box has 2 x network cards. One of these is providing a connection to the private network the database is sat on.

I don't think the problem is the network as there are only the two boxes on this private network and ping times are fine.

I've modified db.php and cache.php to point to the new server and configured mysql to allow connections from the frontend box. it works, but wow is it slow. I assume the problem is that I've not got the db properly optimised for network use somewhere. I've done some research and tried tinkering with setting in 50-server.conf but can't seem to get anything to work.

Have you got any pointers in terms of what I might have missed, or what the optimal config settings are?

Thanks

Rich

[proditis]

Hi there,

Good to see you're back at it.

This is very odd behavior indeed. In general we dont mess with the database much other than the needed changes for the platform to work (init file, plugins etc). I suggest you revert back your config to its original state and do any optimisations after we've figured out what this delay is.

A few things you can try on the top of my head

1. check that this happens from backend also or if its a frontend only problem?
2. make sure you're connecting to the database server by IP. (lets eliminate any DNS issues)
3. Make sure you can perform queries to the database from the CLI and that it doesnt take 20 seconds to reply. From the frontend server run mysql -hyourdbhost -uusername -ppassword -e "select * from player" and ensure you get results fast)
4. Visit the frontend with developer tools open and check the network tab for requests that do not complete/resolve
5. Ensure frontend server can connect to the database server memcached port (nc -tv dbhostip 11211)
6. If you're trying with browser give it a try with curl also, since no external assets are loaded if there is a problem with the PHP contacting the db it will show here as delay also.
7. Check app.log for any errors

Let me know how these go and we can take it from there.

Best regards,

Pantelis

[biggerbananas]

Thanks - will run some tests later.

I suspect though it is something I've missed in the set up process as I'm not 100% sure what bits need doing on which boxes.

Note I'm now building on real hardware as troubleshooting networking on cloud servers was a pain.

I started by building everything on one box roughly as follows:

1. Installed a fresh Debian build and added php 7.4
2. Followed the guide - installing all applications manually on a single linux host: https://echoctfred.readthedocs.io/INSTALL-LINUX/
3. I followed all the steps apart from using composer to install frontend and backend running migrations and configuring apache
4. Cloned the disk onto another one and installed this in the 2nd box.
5. Completed the all in one setup on the box by using composer to install frontend and backend, running the migrations and configuring apache

Having proved it is all working nicely I then booted the 2nd box with the cloned disk and networked them ( database is on 172.24.0.253 and frontend / backend 172.24.0.10), then followed this process to re-configure box 1 to use the db on box 2.

ON box 2 (new database server)

1. Change hostname from frontend to database
2. Edit /etc/mysql/mariadb-conf.d/50-server.conf to bind to ip address on box 2 (172.24.0.253)
3. Edit /etc/memcached.conf and and change bind address to 172.24.0.253
4. Log into mysql and run:

• CREATE USER 'root'@'ip_address' IDENTIFIED BY '';
• GRANT ALL PRIVILEGES ON . TO 'root'@'172.24.0.10';

5. Run theses command on db machine:

nc -zv 172.24.0.253 3306
(returns successful connection)
nc -zv 172.24.0.253 11211
(returns successful connection)

• echo "init_file=/etc/mysql/mysql-init.sql" >>/etc/mysql/mariadb.conf.d/50-mysqld.cnf
• mysql < /etc/mysql/mysql-init.sql

6. Edit the config files on frontend / backend box to point to db:

• edit frontend/config/db.php and change localhost to 172.24.0.253
• edit frontend/config/cache.php, - change host from 127.0.0.1 to 172.24.0.253.

7. Repeat with the backend/config files

8. Run migrate commands.

• ./backend/yii migrate --interactive=0
• ./backend/yii init_data --interactive=0
• ./backend/yii migrate-sales --interactive=0
• ./backend/yii template/emails

Interestingly at this point is the first indication that something is not right. The commands work, but are very slow to start i.e appears to be a big initial connection delay. Then they complete quickly.

9. reboot both boxes, configure initial backend user and params then test.

It now works to an extent, as noted 15 - 20 seconds delay per click and I've also just noticed some pages on the frontend don't load e.g help / changelog links result in an error.

Reverting the db.php and cache.php files to point to localhost and all is good again, including the help and changelog pages.

Sorry - lots there, but I imagine I've missed something obvious that you might instantly spot...

[biggerbananas]

Additionally I can confirm the problem seems to be the initial connection over the network - logging into mysql using

• mysql -u user -h 172.24.0.253 -p password

This works - but takes 25 seconds to connect. Once connected I can run sql commands without any notable delay (the database is pretty much empty mind).

[biggerbananas]

Hang on I might have solved it.

Just needed a good nights sleep.

Looks like you're thoughts on dns were correct. Although I've used IPs not hostnames addinng skip-name-resolve to the 50_server.cnf file appears to have sorted it.

Off to test it properly now.

[biggerbananas]

Seems to have fixed the connection delay, but now the frontend won't work - 404 error for any link including the login?

[proditis]

Nice, i was suspecting something related to DNS. Good that you fixed it.

For the 404 just import the mysql-init.sql on the database server and it'll go away. Also since i suspect you restarted the mysql and this didnt get populated, check that there is init_file in your database cnf that points to mysql-init.sql and that memcached starts before mysql. Alternatively you'll have to re-import the mysql-init.sql file after every restart of the server, mysqld or memcached.

[biggerbananas]

Thanks, getting close to having it working.

Don't think that is the problem though.

I've got the line:

• init_file=/etc/mysql/mysqld-init.sql

in the 50-mysqld.cnf file on the db server and I've manually run this in the db server:

• mysql < /etc/mysql/mysql-init.sql

Still just seeing 404 errors?

[proditis]

The 404s usually mean that the frontend is unable to retrieve the url routes from the database/memcached.

Also although the init_file should theoretically be imported at every run, i've seen instances where this was not the case, but havent been able to reproduce in order to track down what is going on.

Try to stop/start memcached followed by a stop/start of mysql. From the database server confirm that the mysql can talk to memcached by executing something like mysql -NBe 'select memc_get("sysconfig:event_name")'

On the frontend visit the frontpage and make sure the links point to the appropriate urls. When the routes are not accessible the links will be in the form of site/login instead of /login etc.

I'll try to think what else could be the cause for that and get back to you if none of these works.

[biggerbananas]

Thanks - appreciate it. It is Sunday though so don't feel you need to be replying to my questions!

It must be related to something on the db server - as if I switch back to running it locally all the same box it is all good.

I've stopped and started memcached and mysql - that made no difference.

When I run that command it returns "null" - I assume it should come back with the name of the event as configured in the db. So probably does point to memcached. Assume memcached is running but has not data in it?

I'm going to go back over all the build steps to see if I've missed something....

[proditis]

Need the distraction tbh, i'm working on some platform features and i'm kinda stuck myself :)

I suspect that your memcache is not listening to 127.0.0.1, make sure your memcache listens on both internal and 127.0.0.1 on the database server.

[biggerbananas]

Hope some inspiration comes soon!

I think we might be getting close now - I may have completely misunderstood the memcache config.

Still no 100% sure mind. I'd assumed this needed to listen on the network when using a separate db server so had it configured on 172.24.0.253. I've just change the etc/memchaced conf on the db server and set it to listen on 127.0.0.1 and 172.24.0.253. I can now run that command locally and from the frontend and get the event name back.

Still getting 404 errors though - what do you mean by listen on both internal and 127.0.0.1?

[proditis]

I need to check the documentation to make sure this is clear, which doc were you following for these setups?

mysql connects on the memcached listening on 127.0.0.1. The frontend, backend and vpn connect to this memcache listening on the internal interface. In your case you need to listen to both IPs: 127.0.0.1 as well as 172.24.0.253.

Check the frontend logs and the frontend/config/cache.php and confirm the IP of the memcache server. Also make sure you restart the webserver (if apache) or php-fpm to make sure there are no stale connections on the old memcached that was running.

[biggerbananas]

Yep makes sense, I had it only listening to 127.0.0.1, but now have it listening on both.

frontend/config/cache.php is configured to 172.24.0.253

Have restarted apache and rebooted the box.

I don't think there is a guide for this - I followed the manual install in linux host https://echoctfred.readthedocs.io/INSTALL-LINUX/ bur have been trying to work the rest out - not all that successfully.

in terms of fronted logs - where are these stored? I think previously you mentioned - and I was looking at frontend/runtime/logs/app.log. This is not longer present - the frontend runtime folder is empty?

I think I have most of the pieces now - so might have a go at rebuilding the database server from scratch.

[proditis]

Can you confirm that you can connect to the memcached from the frontend memccat --servers=172.24.0.253:11211 sysconfig:event_name

I cant think of something that would require the db box to be re-installed tbh. These errors are usually indicative of a memcache issue.
Can you provide the output of ps -auxwww|grep memcache

What is the URL you're trying to load that returns 404? Make sure you're not on a broken URL already (eg site/index).

The runtime/logs/app.log is populated when there is an error so for it being missing is a good thing :D

[biggerbananas]

Ok - so I've discovered that when adding 127.0.0.1 back into the memcached conf it is no longer available over the network. nc -zv 172.24.0.253 returns connection refused

Taking out 127.0.0.1 netcat will connect.

Must be something wrong with the syntax to specificy multiple ips - I've tried a couple of the mechanisms suggested online, but will continue to tinker.

[proditis]

You need to add a -l per ip such as:-l 127.0.0.1 -l 172.24.0.253

[biggerbananas]

Thanks - yep just sussed that. Most of the docs I found suggested you could use comma or semi colon seperators.

Done that as per you suggestion and now working!

Thankyou sir, you are a genius!

Couple of other things I've noticed you may want to be aware of...

There does seem to be a bug somewhere around creating the first user. I've seen a few times now that when trying to login as the first user created in the backend portal the login prompt will throw a server error (this is what we saw the other day - and I've just had it again). Creating a user from the command line does not appear in the database, but having done this deleting the original user and creating users in the portal then works. Might just be something odd I'm doing - but have managed to replicate that on a few occasions.

I'm not sure hints are working properly - on all the builds I've got up and running, I've not managed to get hints to send - I can create them, but despite sending them the users never receive. I'm hoping with the new separate db this might work - will let you know.

Thanks again.

[proditis]

Glad this worked out.

In general the first player or better the player with ID 1 gets treated as admin on the frontend. This eliminates some access rules and permissions checks so it allows to check things that are not normally visible to the normal players.
The error you getting when logging in for the first time might have to do with permissions. Make sure frontend/web/images/avatars is writable by the web server user. If you can provide the exact steps you do when creating and activating the user i might be able to reproduce and see what is going on.

You can check that the hints and notifications are added to the players by visiting the backend Activity->Notifications and Activity->Player Hints.

[biggerbananas]

Great - thanks. On to the VPN server now, but will hopefully be able to have a proper play with it soon.