from:"Yu Watanabe"

How to tweek the max connections

2013-04-07 Thread Yu Watanabe

Hi all!

I would like know if there are any calculatios for 
specifying the maximum number of the 'max_connections' value.

I understand that max_connections should be decided by the remaining RAM size
of the system. But exactly how should it be actually calculated?

I am currently using RHEL 5.3 32 bit and following are samples of the memory 
usage of my system.

[root@GWM bin]# ps -eo pcpu,vsz,rss,args | grep mysql
 0.0   5420  1172 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql 
--pid-file=/var/lib/mysql/GWM.pid
 0.0 332592 40040 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql 
--plugin-dir=/usr/lib/mysql/plugin --user=mysql 
--log-error=/var/lib/mysql/GWM.err --pid-file=/var/lib/mysql/GWM.pid --
socket=/var/lib/mysql/mysql.sock --port=3306
 0.0   4980   760 grep mysql

[root@GWM bin]# free
 total   used   free sharedbuffers cached
Mem:  16623956   127270123896944  0 103668   11634904
-/+ buffers/cache: 988440   15635516
Swap: 10482404   1368   10481036

Version of mysql is ,

[root@GWM bin]# mysqladmin -u root --password=groundwork variables | grep 
version
| version   | 5.5.25-log

 |
| version_comment   | MySQL Community Server 
(GPL)   
|
| version_compile_machine   | i686  

 |
| version_compile_os| Linux

Thanks,
Yu


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Need help for performance tuning with Mysql

2012-05-24 Thread Yu Watanabe

Alex

Thank you for the advice.

Probably, we will put index (key) on both columns.

Thanks,
Yu

Alex Schaft さんは書きました:
On 2012/05/24 07:37, Alex Schaft wrote:
 You are selecting a record based on the value of data_id and
 thold_enabled, but don't have an index on either? Add an index for both.
 If data_id is unique, then you would only need an index on that.

 Alex


On second thought, an index on thold_enabled won't mean much I think, so
either leave it off or create an index on data_id plus thold_enabled.
Someone more knowledgeable may correct me.

Alex


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Need help for performance tuning with Mysql

2012-05-24 Thread Yu Watanabe

Johnny

Thank you for the reply.

Second, make sure your Innodb buffer pool is allocating as much ram as
possible. I'd even go as far as adding another 8gb of ram to the
server. The buffer pool setting is going to give you the best
performance increase.

  The problem is mainly on MyISAM engine.

Also, what kind of hard disks do you have the data files on? Raid? No raid?

  The server has no RAID.

Thanks,
Yu

Johnny Withers さんは書きました:
I don't see any attachments.

First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process
is painless.

Second, make sure your Innodb buffer pool is allocating as much ram as
possible. I'd even go as far as adding another 8gb of ram to the
server. The buffer pool setting is going to give you the best
performance increase.

Also, what kind of hard disks do you have the data files on? Raid? No raid?

Sent from my iPad

On May 22, 2012, at 9:08 PM, Yu Watanabe yu.watan...@jp.fujitsu.com wrote:

 Hello all.

 I would like to ask for advice with performance tuning with MySQL.

 Following are some data for my server.

 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1-enterprise

 Attached file
 # my.cnf.txt  : my.cnf information
 # mysqlext_20120522131034.log : variable and status information from 
 mysqladmin

 I have 2 database working with high load.

 I wanted to speed up my select and update queries not by
 optimizing the query itself but tuning the my.cnf.

 I have referred to following site,
 http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

 and read Hiperformance Mysql vol.2 ,
 and increased the following values,

 table_cache
 thread_cache_size
 tmp_table_size
 max_heap_table_size

 but made not much difference.

 According to the ps and sar result

 *1 PS result
 Date   Time  CPU%  RSS VSZ
 2012/5/22  21:00:39  109   294752  540028

 *2 SAR
 Average CPU user 25%
sys  5%
io   3%

 I assume that MySQL can work more but currently not.

 I am considersing to off load 1 high load database to
 seperate process and make MySQL work in multiple process.

 It would be a great help if people in this forum can give
 us an adivice for the tuning.

 Best Regards,
 Yu Watanabe


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Need help for performance tuning with Mysql

2012-05-23 Thread Yu Watanabe

Rick

Thank you for the reply.

1. There are only a few things worth tuning -- see 
http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried)


  The page is really cool. Its very simple and easy to understand.

2. Instead INDEXes and schema design must be studied.  Please provide:
SHOW CREATE TABLE

  | thold_data | CREATE TABLE `thold_data` (
`id` int(11) NOT NULL auto_increment,
`rra_id` int(11) NOT NULL default '0',
`data_id` int(11) NOT NULL default '0',
`thold_hi` varchar(100) default NULL,
`thold_low` varchar(100) default NULL,
`thold_fail_trigger` int(10) unsigned default NULL,
`thold_fail_count` int(11) NOT NULL default '0',
`thold_alert` int(1) NOT NULL default '0',
`thold_enabled` enum('on','off') NOT NULL default 'on',
`bl_enabled` enum('on','off') NOT NULL default 'off',
`bl_ref_time` int(50) unsigned default NULL,
`bl_ref_time_range` int(10) unsigned default NULL,
`bl_pct_down` int(10) unsigned default NULL,
`bl_pct_up` int(10) unsigned default NULL,
`bl_fail_trigger` int(10) unsigned default NULL,
`bl_fail_count` int(11) unsigned default NULL,
`bl_alert` int(2) NOT NULL default '0',
`lastread` varchar(100) default NULL,
`oldvalue` varchar(100) NOT NULL default '',
`repeat_alert` int(10) unsigned default NULL,
`notify_default` enum('on','off') default NULL,
`notify_extra` varchar(255) default NULL,
`host_id` int(10) default NULL,
`syslog_priority` int(2) default '3',
`cdef` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `rra_id` (`rra_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |

SHOW TABLE SIZE

  You must be mentioning about the show table status

mysql show table status where name = thold_data;
+++-++--++-+-+--+---++-+-
+-+---+--++-+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | 
Data_length | Max_data_length | Index_length | Data_free | Auto_increment | 
Create_time | Update_time | Check_time  
| Collation | Checksum | Create_options | Comment |
+++-++--++-+-+--+---++-+-
+-+---+--++-+
| thold_data | MyISAM |  10 | Dynamic| 6161 | 90 |  
555128 | 281474976710655 |   140288 | 0 |  70258 | 
2012-05-24 10:41:47 | 2012-05-24 10:47:19 | 2012-05-24 
10:41:47 | latin1_swedish_ci | NULL || | 
+++-++--++-+-+--+---++-+-
+-+---+--++-+
1 row in set (0.00 sec)

EXPLAIN SELECT

  I have seen the following select query in the slow query log.
  I also saw update queries as well.

mysql explain select * from thold_data where thold_enabled='on' AND data_id = 
91633;
++-++--+---+--+-+--+--+-+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  
| rows | Extra   |
++-++--+---+--+-+--+--+-+
|  1 | SIMPLE  | thold_data | ALL  | NULL  | NULL | NULL| NULL 
| 6161 | Using where | 
++-++--+---+--+-+--+--+-+
1 row in set (0.06 sec)

If cache size tuning is not an option ,
do you think that following action would be an choice to faten the queries 
little bit more?

1. depriving the database and setup as an another process. (multiple mysql 
processes)
2. Move the MYD, MYI, frm to ram disk (/dev/shm)

Thanks,
Yu

Rick James さんは書きました:
100% CPU -- A slow query.  Tuning will not help.  Period.

1. There are only a few things worth tuning -- see 
http://mysql.rjweb.org/doc.php/memory (they don't include the ones you tried)

2. Instead INDEXes and schema design must be studied.  Please provide:
SHOW CREATE TABLE
SHOW TABLE SIZE
EXPLAIN SELECT ...
 

 -Original Message-
 From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
 Sent: Tuesday, May 22, 2012 7:07 PM
 To: mysql@lists.mysql.com
 Subject: Need help for performance tuning with Mysql
 
 Hello all.
 
 I would like to ask for advice with performance tuning with MySQL.
 
 Following are some data for my server.
 
 CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
 Memory : 8GB
 OS : RHEL 4.4 x86_64
 MySQL  : MySQL 5.0.50sp1

Need help for performance tuning with Mysql

2012-05-22 Thread Yu Watanabe

Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf. 

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values, 

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ   
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to 
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Need help for performance tuning with Mysql

2012-05-22 Thread Yu Watanabe

Also following is the free command result.

 total   used   free sharedbuffers cached
Mem:   81623807843676 318704  0  956325970892
-/+ buffers/cache:17771526385228
Swap:  8032492  235608008932

Thanks,
Yu


Yu Watanabe さんは書きました:
Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL  : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt  : my.cnf information
# mysqlext_20120522131034.log : variable and status information from mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf. 

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values, 

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date   Time  CPU%  RSS VSZ   
2012/5/22  21:00:39  109   294752  540028

*2 SAR
Average CPU user 25%
sys  5%
io   3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to 
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

__


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Need help for performance tuning with Mysql

2012-05-22 Thread Yu Watanabe

Hello Tsubasa.

Thank you for the reply. (返信ありがとうございます。)

Our high loaded DB are both INNODB and MyISAM.
Espicially , on MyISAM.

I will consider the tuning of innodb_buffer_pool_size as well.

Do you know the tips for how to tune the disk access for MyISAM?

Thanks,
Yu

Tsubasa Tanaka さんは書きました:
Hello,

I seem your mysqld doesn't use enough memory.

Date Time CPU% RSS VSZ
2012/5/22 21:00:39 109 294752 540028

if your mysqld uses InnoDB oftenly,
edit innodb_buffer_pool_size in you my.cnf.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

It is solution for only sql's large result,i think.
if you doesn't recognize that problem causes large result,
you should approach other way,too.

regards,

ts. tanaka//

2012/5/23 Yu Watanabe yu.watan...@jp.fujitsu.com:
Also following is the free command result.

total used free sharedbuffers cached
Mem: 81623807843676 318704 0 956325970892
-/+ buffers/cache:17771526385228
Swap: 8032492 235608008932

Thanks,
Yu

Yu Watanabe さんは書きました:
Hello all.

I would like to ask for advice with performance tuning with MySQL.

Following are some data for my server.

CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL : MySQL 5.0.50sp1-enterprise

Attached file
# my.cnf.txt : my.cnf information
# mysqlext_20120522131034.log : variable and status information from
mysqladmin

I have 2 database working with high load.

I wanted to speed up my select and update queries not by
optimizing the query itself but tuning the my.cnf.

I have referred to following site,
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

and read Hiperformance Mysql vol.2 ,
and increased the following values,

table_cache
thread_cache_size
tmp_table_size
max_heap_table_size

but made not much difference.

According to the ps and sar result

*1 PS result
Date Time CPU% RSS VSZ
2012/5/22 21:00:39 109 294752 540028

*2 SAR
Average CPU user 25%
sys 5%
io 3%

I assume that MySQL can work more but currently not.

I am considersing to off load 1 high load database to
seperate process and make MySQL work in multiple process.

It would be a great help if people in this forum can give
us an adivice for the tuning.

Best Regards,
Yu Watanabe

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Setting up MySQL on D drive on Windows

2012-01-12 Thread Yu Watanabe

Hi!

I would like to ask question about the instruction fo setting up MySQL 
environment
on D drive.

I have installed MySQL on D drive using MSI installer on following platform,

Windows 2008 R2 
Mysql community edition 5.5.19

In this situation I would like to ask following question.

1. There are two data directories installed from the installer,

  A. D:\mysql\data
and 
  B. C:\ProgramData\MySQL\MySQL Server 5.5\data

  In order to use the data directory in D drive, 
  do you need to overwrite above directory A with directory B?

2. Do you just only need to change the datadir value in the [mysqld] section
   in my.ini? Or do you need other changes?

It would be helpful if someone can introduce a good instruction for installing
Mysql environent on in D drive.

Thanks,
Yu 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Data file for MyISAM engine

2011-11-24 Thread Yu Watanabe

Hello Johan.

Thank you for the reply.
I see. So it will depend on the key buffer size.

Thanks,
Yu

Johan De Meersman さんは書きました:
- Original Message -
 From: Yu Watanabe yu.watan...@jp.fujitsu.com
 
 It seems that MYD is the data file but this file size seems to be not
 increasing after the insert sql.

That's right, it's an L-space based engine; all the data that has, is and will 
ever be created is already in there, so storage never 
increases :-p

Seriously though; the MYD file is the datafile and the MYI file is the index 
file. Both of those will increase with use, although since 
storage allocation happens based off pages, not records, increases will only 
happed when the existing pages are filling up.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=yu.watan...@jp.fujitsu.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Data file for MyISAM engine

2011-11-24 Thread Yu Watanabe

Hi Reindl.

Thanks for the reply.

So, which memory corresponds to 'pages' for the MyISAM then?
It would be helpful if you can help me with this.

Thanks,
Yu 

Reindl Harald さんは書きました:
key buffer is memory and has nothing to do with file sizes
filesize increeases by data and keys
key buffer is as the name says a memory-buffer for kyes

Am 24.11.2011 10:25, schrieb Yu Watanabe:
 Hello Johan.
 
 Thank you for the reply.
 I see. So it will depend on the key buffer size.
 
 Thanks,
 Yu
 
 Johan De Meersman さんは書きました:
 - Original Message -
 From: Yu Watanabe yu.watan...@jp.fujitsu.com

 It seems that MYD is the data file but this file size seems to be not
 increasing after the insert sql.

 That's right, it's an L-space based engine; all the data that has, is and 
 will ever be created is already in there, so storage never 
 increases :-p

 Seriously though; the MYD file is the datafile and the MYI file is the 
 index file. Both of those will increase with use, although 
since 
 storage allocation happens based off pages, not records, increases will only 
 happed when the existing pages are filling up.


 -- 
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel

 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=yu.watan...@jp.fujitsu.com


 
 

-- 

Mit besten Grusen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmuhlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Data file for MyISAM engine

2011-11-22 Thread Yu Watanabe

Hi !

I would like to ask question regarding to the MyISAM engine.

Is there any physical file that you have to be aware of its size 
for disk sizing, like the ibdata1 in innodb storage engine? 

It seems that MYD is the data file but this file size seems to be not
increasing after the insert sql.

Thanks,
Yu


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

How to tweek the max connections

Re: Need help for performance tuning with Mysql

Re: Need help for performance tuning with Mysql

Re: Need help for performance tuning with Mysql

Need help for performance tuning with Mysql

Re: Need help for performance tuning with Mysql

Re: Need help for performance tuning with Mysql

Setting up MySQL on D drive on Windows

Re: Data file for MyISAM engine

Re: Data file for MyISAM engine

Data file for MyISAM engine

11 matches

Site Navigation

Mail list logo

Footer information