Hello. We changed our Server system from a single Server to a Cluster
system using MySQL 5 as DB. We have two Mysql Server 1xmaster 1xslave.
Since we changed the Server system our Queries running slower and
sometimes the hole DB Server crush. What never happend before on the
Single Server system we never had a issue with that.

Question is what is the real different between having the MySQL and
Apache server on the same system as havening 3 web server 2 DB Server
and 2 File server connected with 1 Gbit internal Network.
Could that lead to slower Queries?

Thank you for your .

Falko

FalkoL wrote:
> Hello. We changed our Server system from a single Server to a Cluster
> system using MySQL 5 as DB. We have two Mysql Server 1xmaster 1xslave.
> Since we changed the Server system our Queries running slower and
> sometimes the hole DB Server crush. What never happend before on the
> Single Server system we never had a issue with that.
>
> Question is what is the real different between having the MySQL and
> Apache server on the same system as havening 3 web server 2 DB Server
> and 2 File server connected with 1 Gbit internal Network.
> Could that lead to slower Queries?

Of course it's always faster to have a localhost server, no matter if
it's mysql or any other kind of server, as it's faster to fetch
something without getting out on a network.

There shouldn't be any noticeable difference between localhost and a
server which you connect over a short cable which uses gigbit network.

Troubles that can cause these kind of slow downs can be:

- The mysql configuration is badly done on the servers
- Firewall badly setup which makes that the first server requested
fails, has to wait out for time out before trying next server
- Faulty NIC which causes a lot of lost packages
- Badly written SQL queries which has to send a lot of unnecessary data
for each query
- File system on servers are extremely slow due wrong driver used or a
bad file system selected like vfat/ntfs or hard drive breaking down.

There may be even more reasons for the slow down... but I think you
should take a look on the servers that they are properly configured.

--

//Aho

Hi Aho,

we will check all the thinks above.

Our DB Server is a HP Dual Intel Xeon Core 2 Duo with 4 GB RAM that is
our my.cnf

[mysqld]
key_buffer_size=400M
key_buffer=400M
table_cache=512M

join_buffer_size=5M
sort_buffer_size=50M
read_buffer_size=5M
myisam_sort_buffer_size = 32M

query_cache_size=450M
query_cache_type=1
query_cache_limit=64M

max_connections=5000

thread_cache=400M
thread_cache_size=400M
thread_concurrency=2
thread_stack=1M

#concurrent_insert=2
#max_connections = 512
#max_user_connections = 256
interactive_timeout = 250
wait_timeout = 10
connect_timeout = 15

skip-external-locking
skip-bdb
skip-innodb

long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log

tmp_table_size = 256M

Would you count this as Badly?


Thank you for your .

Falko

On Nov 7, 12:42 pm, "J.O. Aho" <u...@example.net> wrote:
> FalkoL wrote:
> > Hello. We changed our Server system from a single Server to a Cluster
> > system using MySQL 5 as DB. We have two Mysql Server 1xmaster 1xslave.
> > Since we changed the Server system our Queries running slower and
> > sometimes the hole DB Server crush. What never happend before on the
> > Single Server system we never had a issue with that.
>
> > Question is what is the real different between having the MySQL and
> > Apache server on the same system as havening 3 web server 2 DB Server
> > and 2 File server connected with 1 Gbit internal Network.
> > Could that lead to slower Queries?
>
> Of course it's always faster to have a localhost server, no matter if
> it's mysql or any other kind of server, as it's faster to fetch
> something without getting out on a network.
>
> There shouldn't be any noticeable difference between localhost and a
> server which you connect over a short cable which uses gigbit network.
>
> Troubles that can cause these kind of slow downs can be:
>
> - The mysql configuration is badly done on the servers
> - Firewall badly setup which makes that the first server requested
> fails, has to wait out for time out before trying next server
> - Faulty NIC which causes a lot of lost packages
> - Badly written SQL queries which has to send a lot of unnecessary data
> for each query
> - File system on servers are extremely slow due wrong driver used or a
> bad file system selected like vfat/ntfs or hard drive breaking down.
>
> There may be even more reasons for the slow down... but I think you
> should take a look on the servers that they are properly configured.
>
> --
>
> //Aho

FalkoL wrote:
> On Nov 7, 12:42 pm, "J.O. Aho" <u...@example.net> wrote:
>> FalkoL wrote:
>>> Hello. We changed our Server system from a single Server to a
>>> Cluster system using MySQL 5 as DB. We have two Mysql Server
>>> 1xmaster 1xslave. Since we changed the Server system our Queries
>>> running slower and sometimes the hole DB Server crush. What never
>>> happend before on the Single Server system we never had a issue
>>> with that.
>>
>>> Question is what is the real different between having the MySQL and
>>> Apache server on the same system as havening 3 web server 2 DB
>>> Server and 2 File server connected with 1 Gbit internal Network.
>>> Could that lead to slower Queries?
>>
>> Of course it's always faster to have a localhost server, no matter if
>> it's mysql or any other kind of server, as it's faster to fetch
>> something without getting out on a network.
>>
>> There shouldn't be any noticeable difference between localhost and a
>> server which you connect over a short cable which uses gigbit
>> network.
>>
>> Troubles that can cause these kind of slow downs can be:
>>
>> - The mysql configuration is badly done on the servers
>> - Firewall badly setup which makes that the first server requested
>> fails, has to wait out for time out before trying next server
>> - Faulty NIC which causes a lot of lost packages
>> - Badly written SQL queries which has to send a lot of unnecessary
>> data for each query
>> - File system on servers are extremely slow due wrong driver used or
>> a bad file system selected like vfat/ntfs or hard drive breaking
>> down.
>>
>> There may be even more reasons for the slow down... but I think you
>> should take a look on the servers that they are properly configured.
>>
>> --
>>
>> //Aho
> Hi Aho,
>
> we will check all the thinks above.
>
> Our DB Server is a HP Dual Intel Xeon Core 2 Duo with 4 GB RAM that is
> our my.cnf
>
> [mysqld]
> key_buffer_size=400M
> key_buffer=400M
> table_cache=512M
>
> join_buffer_size=5M
> sort_buffer_size=50M
> read_buffer_size=5M
> myisam_sort_buffer_size = 32M
>
> query_cache_size=450M
> query_cache_type=1
> query_cache_limit=64M
>
> max_connections=5000
>
> thread_cache=400M
> thread_cache_size=400M
> thread_concurrency=2
> thread_stack=1M
>
> #concurrent_insert=2
> #max_connections = 512
> #max_user_connections = 256
> interactive_timeout = 250
> wait_timeout = 10
> connect_timeout = 15
>
> skip-external-locking
> skip-bdb
> skip-innodb
>
> long_query_time = 1
> log-slow-queries = /var/log/mysql/mysql-slow.log
>
> tmp_table_size = 256M
>
> Would you count this as Badly?
>
>
> Thank you for your .
>
> Falko
>
Please do not top post (top posting fixed)

FalkoL wrote:
> Hello. We changed our Server system from a single Server to a Cluster
> system using MySQL 5 as DB. We have two Mysql Server 1xmaster 1xslave.
> Since we changed the Server system our Queries running slower and
> sometimes the hole DB Server crush. What never happend before on the
> Single Server system we never had a issue with that.
>
> Question is what is the real different between having the MySQL and
> Apache server on the same system as havening 3 web server 2 DB Server
> and 2 File server connected with 1 Gbit internal Network.
> Could that lead to slower Queries?
>
> Thank you for your .
>
> Falko
>
>

That depends.

If you're CPU bound on your old server, moving the database to a
different server could very well speed things up. And the same could be
true if you have I/O contention between the MySQL server and the web
server, although it's less likely.

But moving MySQL to a separate server also typically gives you more
resources - memory, etc.

Now the time it takes to transfer data between MySQL and your
application will, of course, be longer, even on a 1GB network. How much
slower depends, of course, on the amount of data being transferred. But
even things like connecting will take longer.

So the bottom line is - it can be slower, but I wouldn't expect it the
be that much slower.

However, you've also added one other thing - the master/slave
relationship. It means both servers will have more work to do than when
you had a single server. How much depends on how often your data
changes. But that is going to slow it down.

My recommendation would be to not try to change so much so quickly. Get
it running with a single MySQL server to start. Compare MySQL config
parameters between the old and new systems, and at tuning the single
system. Once you get that working as well as possible, add the slave.
You might have to tune a little more, but it shouldn't be that much.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Wed, 07 Nov 2007 20:17:51 -0000, FalkoL wrote:
> Hello. We changed our Server system from a single Server to a Cluster
> system using MySQL 5 as DB. We have two Mysql Server 1xmaster 1xslave.
> Since we changed the Server system our Queries running slower and
> sometimes the hole DB Server crush. What never happend before on the
> Single Server system we never had a issue with that.
>
> Question is what is the real different between having the MySQL and
> Apache server on the same system as havening 3 web server 2 DB Server
> and 2 File server connected with 1 Gbit internal Network.
> Could that lead to slower Queries?
>
It's possible, but not terribly likely.... unless those file servers are
ALSO holding your database stores...

--
Windows gives you a nice view of clouds so you can't see any potentially
useful boot time messages.
-- Bill Hay in the Monastery

FalkoL wrote:

> Hi Aho,
>
> we will check all the thinks above.
>
> Our DB Server is a HP Dual Intel Xeon Core 2 Duo with 4 GB RAM that is
> our my.cnf
>
> [mysqld]
> key_buffer_size=400M
> key_buffer=400M
> table_cache=512M


The M should not be there, you cannot cache 512 million tables.


> join_buffer_size=5M
> sort_buffer_size=50M
> read_buffer_size=5M
> myisam_sort_buffer_size = 32M
>
> query_cache_size=450M
> query_cache_type=1
> query_cache_limit=64M
>
> max_connections=5000


I bet your server will crash long before you have 5000 connections.


> thread_cache=400M


I don't think I've ever seen a thread_cache over 100 or so. 400 million in
ludicrous.


> thread_cache_size=400M
> thread_concurrency=2


thread_concurrency is traditionally 2 * CPUs (or cores these days), try 8.


Also you may like to restart mysql with --skip-name-resolve otherwise you
will pay for a DNS and rDNS lookup for every connection. If you have slow
DNS this will cause lots of problems.


--
Brian Wakem