DB selects on master from web servers

[gnm]

I'm seeing MySQL selects on the master DB server. The slave configuration is in place in the config.php file. The DB slaves are getting most of the reads. But it seems like a few reads are trickling to the master DB. Any ideas?

[gnm]

Is this possibly a known bug perhaps?

[Steve Machol]

I have no idea and given the lack of response my guess is that you have not provided anywhere near enough information for someone to help you.

[feldon23]

Well the question is, and maybe a MySQL guru like eva2000 can chip in here, when you have a Master and Slave MySQL database setup with vBulletin, are all SELECT functions supposed to happen on the Slave server, or are some going to still be seen on the Master?

The idea is that vBulletin can write to the database by doing INSERT and UPDATE on the Master server while simultaneously doing a read or SELECT on the Slave server, doubling the number of clients you can handle simultaneously.

And the original poster is asking if it's normal to still see SELECTs hitting the Master db.

[gnm]

Well the question is, and maybe a MySQL guru like eva2000 can chip in here, when you have a Master and Slave MySQL database setup with vBulletin, are all SELECT functions supposed to happen on the Slave server, or are some going to still be seen on the Master?

The idea is that vBulletin can write to the database by doing INSERT and UPDATE on the Master server while simultaneously doing a read or SELECT on the Slave server, doubling the number of clients you can handle simultaneously.

And the original poster is asking if it's normal to still see SELECTs hitting the Master db.

Yes exactly Feldon. I would think if the master/slave configuration in the config.php file is set, then there shouldn't be any SELECT statements on the master database. But I am seeing them. I was wondering if there is something I missed, or is there a bug, or is this normal behavior? Thanks.

[Wayne Luke]

What queries are they? I mean there are hundreds of files and queries in the system. It would be a little easier to track down with more information.

[gnm]

Ok, I've isolated the last lines from the slow log on the master ...

And then run mysqldumpslow, to be safe I've replaced our domain with example.

See attachments.
http://img352.imageshack.us/img352/9...syearbi5.pngOn the graph you will find the queries processed by the mysql master :
- 1st tier : no select so qcache disabled
- 2nd tier : we upgraded to vbulletin 3.6.5
- 3rd tier : ouch... Ok let's put the query cache back..

As you can imagine max_connections is a blocking factor... We have 5 slaves each with mysql slave listening locally, since there is still a lot of select on the master we've reached our max_conn...

[gnm]

up...

[Scott MacVicar]

I'll look more at this tomorrow but all queries to the thread table are run against the master because of a potential lag to the slave.

User makes post, gets redirected and the slave is say 20a or a minute behind the master, they get a thread not found error which can cause mass confusion. The only things that are configured to go the slave are queries where stale data is of no or very little importance. There is also the issue of running one query against the master then the next against the slave, as in within a few milliseconds of each other.

It could be possible to add some extra code on each page load to see what the Seconds Behind Master value is on the slave and act appropriately though it would add a query to every single page.

[gnm]

Thank you for your answer.

I thought vbulletin had some kind of mechanism, tricking the poster via a cookie with its own post (which get deleted when the slaves catch up).


In my experience the queries on the master are not complex * (as long as they don't involve SELECTs) and so very fast : insert a new post/thread, update a post/thread --> it is based on an post/thread_id which is indexed.

I've checked the mysqlbinlog on the server the max exec_time is 5 seconds, that means the query runs for 5 seconds on the master, so it will take 5 seconds on the slave before to be up to date (the time spent to travel from master to slave is negligible)... I guess the "message posted, thank you" redirect page can catch if the slave got the update (or not yet) and then show the page.

With the current situation we can't scale, we have a limit based on mysql connections of how many visitors we can serve at a particular moment, so better not to 'waste' precious mysql threads with queries which can run on the slaves.

Note : we can't increase indefinitely number of allowed connections either.


* Example :
UPDATE thread SET
replycount = replycount + 1,
lastpost = 1184603988,
lastposter = 'gnm',
lastpostid = 16439936
WHERE threadid = 605788;

[Scott MacVicar]

All queries that run against the master are clearly marked.

query_first / query_write / query_read - happen on the master
query_first_slave / query_read_slave - happen on the slave.

query_write obviously needs to go to the master since its a write query.

The query_first and query_read are ones that I determined needed to go against the master, I'll do a double check over the files to see if there are any others that I can use.

Is your problem in regards to the number of connections the master can handle rather than the time queries take to run? As vBulletin creates a connection to both the master and slave at script start time regardless if its needed.

Perhaps MySQL Cluster support is more what you need?

[Scott MacVicar]

On another note, I've just updated another 20 queries that were running against the master that should really have been running against the slave.

[gnm]

Definitely the less queries the master has to run, the more connections it would handle...

In the previous version of vbulletin there was no select at all on master (mysql_queries-year.png graph), we smoothly reached 8000 visitors without too much stress on the sql master. Now it's impossible, the pool of connection is reached very quick with all those SELECTs. Especially because of read/write statements and locks (see sql-stress. htop screenshot on the master) the load on the server goes crazy : 338 of load is not good, and it's not a crapy server...

[gnm]

up.

[gnm]

re up...

Do you know how hard is it to switch back to the previous 'good' version ?
I don't know how much the table structure changed.

We can't currently have more than ~ 7000 visitors at once, due to all those selects on one mysql server.