Setting up mysql server on a seperate host

am 04.01.2004 13:44:04 von Amol Sapkal

Hi All,

I am setting up a MRTG server which polls close to 100 nodes. A lot of perl scripts process the
data collected by this server. This processing is heavy and involves database (mysql) and flat mrtg
log files. There are other perl scripts which are used as part of a mailing engine (uses sendmail).
MRTG is called by cron every 5 minutes and the 100 nodes are divided such that there are 4 simple
shell scripts which contain the MRTG execution lines. These 4 scripts are called in cron every 5
minutes. There are other scheduled jobs which run only few (1-4) times a day.

Few days back, when I increased my nodes polled by 20 (from 80 to 100) and introduced a perl
script, I could see a lot of processing happening on the box. 'top' showed that most of it was
being crunched up my mysql (20-40%), mrtg, snmpwalks.

Now, I want to shift my entire mysql database and processing to another server (in the LAN) without
affecting much the scripts which will be accessing them. So, my scripts will be on one server and
database on other. The question is:
How do I do it?
Cheers,

Amol Sapkal
--------------------------
"Logic is a systematic method of coming to the wrong conclusion with confidence"

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Setting up mysql server on a seperate host

am 04.01.2004 17:02:14 von Rudy Lippan

On Sun, 4 Jan 2004, Amol Sapkal wrote:

> Now, I want to shift my entire mysql database and processing to another
> server (in the LAN) without affecting much the scripts which will be
> accessing them. So, my scripts will be on one server and database on
> other. The question is: How do I do it? Cheers,

If your script does not explicitly specify the connect DSN you can use the
DBI_DSN envionment variable, otheriwse you are going to have to modify
your script so that the host to connect to is configurable (environment
varible/config file / &c..

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Setting up mysql server on a seperate host

am 04.01.2004 17:02:14 von Rudy Lippan

Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 17:27:47 von Amol Sapkal

>
> If your script does not explicitly specify the connect DSN you can
use the
> DBI_DSN envionment variable, otheriwse you are going to have to
modify
> your script so that the host to connect to is configurable
(environment
> varible/config file / &c..

Pls give me an example code. the configs could be:

MySQL server: 10.1.1.2 (hostname: mars)
database name: metric
port:3306 (default)
Script are on:10.1.1.1 (hostname:earth)

Below will be part of my scripts (no harm in changing these):
$dbh = DBI->connect($dsn, $user, $password)

Could you help me with defining my $dsn with above configs? I have not
set any username/password for my database (have installed it using
simple rpms)

TIA,
Amol Sapkal

>
>
> Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 17:27:47 von Amol Sapkal

Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 17:32:53 von Amol Sapkal

Will setting up nfs help?

'/var/lib/mysql' being shared on the mysql server.

Regds,
Amol

> On Sun, 4 Jan 2004, Amol Sapkal wrote:
>
> > Now, I want to shift my entire mysql database and processing to
another
> > server (in the LAN) without affecting much the scripts which will be
> > accessing them. So, my scripts will be on one server and database on
> > other. The question is: How do I do it? Cheers,
>
> If your script does not explicitly specify the connect DSN you can
use the
> DBI_DSN envionment variable, otheriwse you are going to have to
modify
> your script so that the host to connect to is configurable
(environment
> varible/config file / &c..
>
>
> Rudy
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=sugiarto.Then@infotronik.c om

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 17:32:53 von Amol Sapkal

Re: Setting up mysql server on a seperate host

am 04.01.2004 18:45:42 von Jochen Wiedmann

Amol Sapkal wrote:

> Will setting up nfs help?
>
> '/var/lib/mysql' being shared on the mysql server.

No, you cannot use Unix sockets. You have to use a DSN like

DBI:mysql::

Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Setting up mysql server on a seperate host

am 04.01.2004 18:45:42 von Jochen Wiedmann

Re: Setting up mysql server on a seperate host

am 04.01.2004 18:46:52 von Jochen Wiedmann

Amol Sapkal wrote:

> MySQL server: 10.1.1.2 (hostname: mars)
> database name: metric
> port:3306 (default)
> Script are on:10.1.1.1 (hostname:earth)

Your DSN is

DBI:mysql:metric:10.1.1.2

or

DBI:mysql:metric:mars

Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Setting up mysql server on a seperate host

am 04.01.2004 18:46:52 von Jochen Wiedmann

Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 20:04:54 von Amol Sapkal

>
> > MySQL server: 10.1.1.2 (hostname: mars)
> > database name: metric
> > port:3306 (default)
> > Script are on:10.1.1.1 (hostname:earth)
>
> Your DSN is
>
> DBI:mysql:metric:10.1.1.2
>
> or
>
> DBI:mysql:metric:mars

hi,
I got my DSN right. but now i get a new problem.

My script transcript:
--
use DBI;

my $DSN="dbi:mysql:database=metric;host=mars;port=3306";
my $user="root";
my $password="";
my $mysql = DBI->connect($DSN,$user,$password);
---

i have added entry for "mars" to my /etc/hosts on earth(10.1.1.1) and also an
entry "mysql:127.0.0.1" to /etc/hosts.allow

On the mysql host mars (10.1.1.2), "/etc/my.cnf" reads:

---
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
innodb_data_file_path = ibdata1:10M:autoextend (I added this line after some google)

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

------

when i run my script, i get the following error:

-----
DBI->connect(database=metric;host=mars;port=3306) failed: Lost connection to M
ySQL server during query at ./rtr_status.pl line 12
-----

also, on running the script on earth, restarts the mysqld on mars.
-----
#tail -f /var/log/mysqld.log
Number of processes running now: 1
mysqld process hanging, pid 22937 - killed
040105 01:13:36 mysqld restarted
040105 1:13:36 InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
040105 1:13:36 InnoDB: Started
/usr/libexec/mysqld: ready for connections
-----

Pls help.

Cheers,

Amol Sapkal
--------------------------
"Logic is a systematic method of coming to the wrong conclusion with confidence"

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 20:04:54 von Amol Sapkal

Re: Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 20:40:49 von Amol Sapkal

>
> On the mysql host mars (10.1.1.2), "/etc/my.cnf" reads:
>
> ---
> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> innodb_data_file_path = ibdata1:10M:autoextend (I added this line after some
> google)
>
>
> [mysql.server]
> user=mysql
> basedir=/var/lib
>
> [safe_mysqld]
> err-log=/var/log/mysqld.log
> pid-file=/var/run/mysqld/mysqld.pid
>
> ------
>
> when i run my script, i get the following error:
>
> -----
> DBI->connect(database=metric;host=mars;port=3306) failed: Lost connection to M
> ySQL server during query at ./rtr_status.pl line 12
> -----
>
> also, on running the script on earth, restarts the mysqld on mars.
> -----
> #tail -f /var/log/mysqld.log
> Number of processes running now: 1
> mysqld process hanging, pid 22937 - killed
> 040105 01:13:36 mysqld restarted
> 040105 1:13:36 InnoDB: Out of memory in additional memory pool.
> InnoDB: InnoDB will start allocating memory from the OS.
> InnoDB: You may get better performance if you configure a bigger
> InnoDB: value in the MySQL my.cnf file for
> InnoDB: innodb_additional_mem_pool_size.
> 040105 1:13:36 InnoDB: Started
> /usr/libexec/mysqld: ready for connections
> -----
>

Now i have taken out the line "innodb_data_file_path = ibdata1:10M:autoextend " and added "skip-
innodb ". Only difference being, the error has now changed to:

---

Number of processes running now: 1
mysqld process hanging, pid 23512 - killed
040105 01:37:41 mysqld restarted
/usr/libexec/mysqld: ready for connections

---

regds,
Amol Sapkal

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 20:40:49 von Amol Sapkal

Re: Re: Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 21:00:46 von Amol Sapkal

>
> Now i have taken out the line "innodb_data_file_path = ibdata1:10M:autoextend
> " and added "skip-
> innodb ". Only difference being, the error has now changed to:
>
> ---
>
> Number of processes running now: 1
> mysqld process hanging, pid 23512 - killed
> 040105 01:37:41 mysqld restarted
> /usr/libexec/mysqld: ready for connections
>

I have finally managed to do it :)

Here are the configs:
On mysql server (/etc/my.cnf):
---
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
set-variable = thread_stack=1M

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
----

Added both server IPs to each other's /etc/hosts file.
Granted permissions on the mysql database (metric) to user "root" on host "earth".

Above is what was done, and it worked.

Thanks to all for the help.

Cheers,

Amol Sapkal
--------------------------
"Logic is a systematic method of coming to the wrong conclusion with confidence"

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Re: Re: Re: Setting up mysql server on a seperate host

am 04.01.2004 21:00:46 von Amol Sapkal