MySQLperformanceremotesocket
I'm running a couple of projects which consume a lot of MySQL power (10'000 qps in peaktimes)…now I'm wondering what would be the best setup in order to keep it fast and smooth even if the user numbers grow.
Currently I'm using 1 server with nginx, php-fpm and mysql (dual xeon, 24 GB ram, SSD's) and connecting to MySQL through a local socket.
Would I experience a "performance drop" if i had a remote MySQL server of the same specification in order to split up webserver / db load?
What sort of tuning are we talking about here?
Perhaps the best idea would be for both of you to read a book such as "High Performance MySQL" and TOGETHER plan the best strategy.
Doing it remotely is definitely not the most efficient way of doing things but its not totally impossible if you have decent cooperation on the other end.
I think the real key here is going to be for both of you to be on the "same team".
A "slow to respond" sysadmin is also not the most fun to work with. Perhaps you guys can schedule some phone time and go over a general battle plan... then fine tune it from there?
Sorry to sound vague and general but that's the best I can do with the details of your question.
Hope this helps.
Altough in the time, when the question was asked, it was really impossible, but it is possible nowadays.
You can to both: UNIX=>TCP and UNIX=>UNIX forwarding.
For example:
ssh \
-R/var/run/mysql.sock:/var/run/mysql.sock \
-R127.0.0.1:3306:/var/run/mysql.sock \
somehost
It is possible since OpenSSH 6.7.
Best Answer
There will be a performance hit, but rather in the latency part. So if you run a lot of parallel connections, you probably won't notice. If you're doing 10kqps serially in one (or just a few) connection, you'll definately see a difference.
The query processing itself will not take longer, just the data roundtrip time from and to the client will increase (even 10Gbit ethernet would play a role here) by around a milisecond or so (for 1Gbit line). So if the query itself took another milisecond, you're limited to about 500qps per connection.
With local socket, the roundtrip time is pretty much in tens-of-microseconds range ...