SEO

Hi,
I have a HP rp3410 with 4Gb RAM running HPUX 11.11. This is running,

Version: '5.0.26-pro-log' socket: '/tmp/mysql.sock' port: 3306 MySQL
Pro (Commercial)

When this machine is put under load (i.e, a lot of database activity),
the clients are receiving "Error 12" errors and the MySQL log has a lot
of errors as follows,

070327 8:24:20 [ERROR] mysql_ha_read: Got error 12 when reading table
'XLDEFN_IN'
070327 8:54:51 [ERROR] mysql_ha_read: Got error 12 when reading table
'XLDEFN_IN'
070327 8:55:19 [ERROR] mysql_ha_read: Got error 12 when reading table
'XLDEFN_IN'

In addition, I see at MySQL startup (in the mysql err log), a number of,

/usr/local/mysql/bin/mysqld: Out of memory (Needed 368389120 bytes)
/usr/local/mysql/bin/mysqld: Out of memory (Needed 276289536 bytes)


My /etc/my.cnf is set as,

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=400M
set-variable = max_allowed_packet=15M
default-table-type=InnoDB
datadir=/data/mysql

The machine is indicating it is under any ram pressure, which makes me
think it may be kernel tuning (of course, I may be wrong). Are there any
recommendations for HPUX kernel tuning with MySQL?

--

Regards,
Ian Collins
Systems Manager
KIWIPLAN Group
Tel: +64 (0)9 2727622
Mob: +64 (0)21 761144

Hello Group,

Looking to use this REPLACE to strip multi line data out of my return
data.

I am having problems getting the \n to work correctly, any ideas?

REPLACE(DESCRIPTION,\n,' ')


SELECT
rpad(CASE WHEN DESCRIPTION IS NULL THEN '' ELSE
REPLACE(DESCRIPTION,'\n',' ') END,80,' ') as var
FROM hardware;



Wishing you the best you know you deserve,

______________________
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401

Ok.. I found the problem.. I needed to add a \r.. but now that opens up
the question can I have a Multi replace search in replace?


Example can I do something like this in MySQL

REPLACE (STRING,'\r' or '\n',' ')


REPLACE(DESCRIPTION,'\r\n',' ')

Wishing you the best you know you deserve,

______________________





Hello Group,

Looking to use this REPLACE to strip multi line data out of my return
data.

I am having problems getting the \n to work correctly, any ideas?

REPLACE(DESCRIPTION,\n,' ')


SELECT
rpad(CASE WHEN DESCRIPTION IS NULL THEN '' ELSE
REPLACE(DESCRIPTION,'\n',' ') END,80,' ') as var
FROM hardware;



Wishing you the best you know you deserve,

______________________
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401

I always go with REPLACE(REPLACE(STRING, '\n', ''), '\r', ''), since
depending on where your data came from there may be one or the other, or
both. Although if there is a shorthand/more efficient way I'd love to
hear it.


Lucas.CTR.Heuman@faa.gov wrote:
> Ok.. I found the problem.. I needed to add a \r.. but now that opens up
> the question can I have a Multi replace search in replace?
>
>
> Example can I do something like this in MySQL
>
> REPLACE (STRING,'\r' or '\n',' ')
>
>
> REPLACE(DESCRIPTION,'\r\n',' ')
>
> Wishing you the best you know you deserve,
>
> ______________________
>
>
>
>
>
> Hello Group,
>
> Looking to use this REPLACE to strip multi line data out of my return
> data.
>
> I am having problems getting the \n to work correctly, any ideas?
>
> REPLACE(DESCRIPTION,\n,' ')
>
>
> SELECT
> rpad(CASE WHEN DESCRIPTION IS NULL THEN '' ELSE
> REPLACE(DESCRIPTION,'\n',' ') END,80,' ') as var
> FROM hardware;
>
>
>
> Wishing you the best you know you deserve,
>
> ______________________
> Lucas Heuman
> CM Web Developer
> SRA International, Inc.
> FAA, WJHTC/Bldg 300, 2nd Fl., H33
> Atlantic City Int'l Airport, NJ 08405
> Phone 609.485.5401
>

Hello all.

Does anyone out there (in mysql world) have a Linux -csh script to
refresh test with production data.

My developers would like their test database to be refreshed nightly
with production data. The production and test mysql servers do not run
in the same box. They run on different boxes. Therefore there is some
ftp or scp required

Thanks

********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

If test and production are supposed to be identical, then use this:

mysqldump -h<IP for Prod> -u... -p... --triggers --routines <db-name on Prod> | mysql -h<IP for Test> -u... -p... -A -D<db-name on Test>

If you havn't noticed, you do not dump the data to a file and ftp or scp anything anywhere !!!
This will actually pipe all mysqldump commands striaght to the test database.

Make sure Linux can withstand the long connect time required.

If you want mysqldump all tables one at a time,
here is a Windows Batch File That I Use Every Week:

1) drop a target database,
2) recreate a target database,
3) Copy all stored procedures from Source to Target
4) Dynamically Create a Windows Batch File to mysqldump every table
(and its triggers, if applicable) starting with the largest table

Using this method, you need not worry about connection time because each table
will be shipped over from Source to Target using a Fresh Connection each time.

@echo off
set TABLESBAT=DynamicBatchFile.bat
echo SysData Full Copy Initiated > DataLoadFull.txt
date /t >> DataLoadFull.txt
time /t >> DataLoadFull.txt
rem
rem
rem Drop SysData_Test from Target
rem Create SysData_Test for Target
rem Load Stored Procedures from Host PROD_IP Database SysData_Prod into Host TEST_IP Database SysData_Test
rem
rem
set SOURCE_HOST=PROD_IP
set SOURCE_USER=...
set SOURCE_PASS=...
set TARGET_HOST=TEST_IP
set TARGET_USER=...
set TARGET_PASS=...
rem
rem
echo DROP DATABASE IF EXISTS SysData_Test; > ResetTarget.sql
echo CREATE DATABASE SysData_Test; >> ResetTarget.sql
echo USE SysData_Test >> ResetTarget.sql
mysqldump -h%SOURCE_HOST% -u%SOURCE_USER% -p%SOURCE_PASS% --no-data --no-create-info --routines NDW >> ResetTarget.sql
rem
rem
rem mysqldump all MyISAM tables from Source to Target in Size Order starting from the Largest Table
rem
rem
mysql -h%SOURCE_HOST% -u%SOURCE_USER% -p%SOURCE_PASS% -A --skip-column-names -DNDW -e"SELECT CONCAT('mysqldump -h%SOURCE_HOST% -u%SOURCE_USER% -p%SOURCE_PASS% --triggers SysData_Prod ',A.table_name,' | mysql -h%TARGET_HOST% -u%TARGET_USER% -p%TARGET_PASS% -A -DSysData_Test') FROM (SELECT table_name,(data_length+index_length) table_length FROM information_schema.tables WHERE table_schema='SysData_Prod' AND engine='MyISAM') A ORDER BY A.table_length DESC,A.table_name;" >> %TABLESBAT%
echo exit >> %TABLESBAT%
mysql -h%TARGET_HOST% -u%TARGET_USER% -p%TARGET_PASS% -A < ResetTarget.sql
start "mysqldumps from PROD to TEST, DO NOT CLOSE !!!" /w %TABLESBAT%
del ResetTarget.sql
del %TABLESBAT%
echo SysData Full Completed >> DataLoadFull.txt
date /t >> DataLoadFull.txt
time /t >> DataLoadFull.txt

PLEASE BE CAREFUL WITH THIS !!!

----- Original Message -----
From: "Charles Brown" <CBrown@BMI.com>
To: mysql@lists.mysql.com
Sent: Tuesday, March 27, 2007 3:17:46 PM (GMT-0500) Auto-Detected
Subject: a Linux -csh script to refresh test with production


Hello all.

Does anyone out there (in mysql world) have a Linux -csh script to
refresh test with production data.

My developers would like their test database to be refreshed nightly
with production data. The production and test mysql servers do not run
in the same box. They run on different boxes. Therefore there is some
ftp or scp required

Thanks

********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com

well, you'd want to come up with your specifics but it's pretty simple using ssh...
put this is your shell...

$ mysqldump db-name | mysql -h remote.box.com db-name

$ mysqldump db-name | ssh user@remote.box.com mysql db-name

$ mysqldump db-name foo | ssh user@remote.box.com mysql bar

hope this helps
bb


-----Original Message-----
From: Brown, Charles [mailto:CBrown@BMI.com]
Sent: Tue 3/27/2007 12:17 PM
To: mysql@lists.mysql.com
Subject: a Linux -csh script to refresh test with production


Hello all.

Does anyone out there (in mysql world) have a Linux -csh script to
refresh test with production data.

My developers would like their test database to be refreshed nightly
with production data. The production and test mysql servers do not run
in the same box. They run on different boxes. Therefore there is some
ftp or scp required

Thanks

********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=b...es@latimes.com

Ian Collins a écrit :
> Hi,
> I have a HP rp3410 with 4Gb RAM running HPUX 11.11. This is running,
>
> Version: '5.0.26-pro-log' socket: '/tmp/mysql.sock' port: 3306 MySQL
> Pro (Commercial)
>
> When this machine is put under load (i.e, a lot of database activity),
> the clients are receiving "Error 12" errors and the MySQL log has a lot
> of errors as follows,
>
> 070327 8:24:20 [ERROR] mysql_ha_read: Got error 12 when reading table
> 'XLDEFN_IN'
> 070327 8:54:51 [ERROR] mysql_ha_read: Got error 12 when reading table
> 'XLDEFN_IN'
> 070327 8:55:19 [ERROR] mysql_ha_read: Got error 12 when reading table
> 'XLDEFN_IN'
>
> In addition, I see at MySQL startup (in the mysql err log), a number of,
>
> /usr/local/mysql/bin/mysqld: Out of memory (Needed 368389120 bytes)
> /usr/local/mysql/bin/mysqld: Out of memory (Needed 276289536 bytes)
>
>
> My /etc/my.cnf is set as,
>
> [client]
> port=3306
> socket=/tmp/mysql.sock
>
> [mysqld]
> port=3306
> socket=/tmp/mysql.sock
> set-variable = key_buffer_size=400M
> set-variable = max_allowed_packet=15M
> default-table-type=InnoDB
> datadir=/data/mysql
>
> The machine is indicating it is under any ram pressure, which makes me
> think it may be kernel tuning (of course, I may be wrong). Are there any
> recommendations for HPUX kernel tuning with MySQL?
>

Hi,
You probably figured that out by now, but for the book.

You're probably using a 32 bits architecture and then are limited to 4GB
total memory allocation. That being said, that's the maximum you can
have, but the kernel reserved a certain value for it's stack and other
part. Depending of the os, it reserved from 800-2G. For example, many
linuxes cannot go over 2.2Gb without seeing those errors.

I'm not very familiar with HP-UX, so you may find parameter to tweak to
increase that value that you can allocate, but I doubt you could get
more than 3Gb so you may want to decrease your mysql settings so it
doesn't allow that much memory...

Example you set key_buffer_size to 400M, but default table to innodb. SO
if you don't have much myisam table you can reduce that to a much lower
value (say 100M?)

Or you can also decrease the per thread buffer, and limit the
max_connection settings...

I'm sure you could find a lot more info around on the net! This
http://www.mysqlperformanceblog.com/...-memory-usage/
also gives a few pertinent information.

If all the memory are needed and you wish to increase those esttings, I
advice to consider a 64 Bits architecture as the next upgrade Should
really help with db is starting to get tight in a 32bits one!

Good luck!

--
Mathieu Bruneau
aka ROunofF

===
GPG keys available @ http://rounoff.darktech.org