Re: Why innodb can give the same X gap lock to two transactions?

[Leo Huang]

Heikki,

Thanks for you help!

I also read the comment in file of innodbase/lock/lock0lock.c in which
you said Different transaction can have conflicting locks set on the
gap at the same time.. I think that the innodb gap lock's behavior
just like an IX lock's behavior. When a transaction want to insert a
record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't
it?

I have read some source code in innodbase/lock/lock0lock.c. But I
can't get a clear view of innodb lock modes and lock ways?  Can you
give me more information?

PS: hi, Eric, Our MySQL version is 4.1.18. Thx!



--
Best regards,
Leo Huang

2006/12/18, Heikki Tuuri [EMAIL PROTECTED]:

Leo,

'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the
locked gap. But they do not give the holder of the lock any right to
insert. Several transactions can own X-lock on the same gap. The reason
why we let 'conflicting' locks of different transactions on a gap is
that this way there are less lock waits and less deadlocks.

In Eric Bergen's example, there was a row with id 6, and there the locks
were not gap locks.

Best regards,

Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php

.
From: leo huang Date: December 12 2006 7:46am
Subject: Why innodb can give the same X gap lock to two transactions?

Get Plain Text

Hi, all,

We have an innodb table named test. It has some rows as follow:
mysql show create table test;
+---+-+
| Table | Create Table

 |
+---+-+
| test  | CREATE TABLE `test` (
   `id` int(11) NOT NULL default '0',
   `name` char(20) default NULL,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---+-+
1 row in set (1.75 sec)

mysql select * from test;
++-+
| id | name|
++-+
|  1 | huangjy |
|  2 | huangjy |
|  3 | huangjy |
|  4 | huangjy |
|  5 | huangjy |
|  7 | huangjy |
|  8 | huangjy |
|  9 | huangjy |
++-+
8 rows in set (1.98 sec)

When I start two transactions as follow:

Transaction 1:
mysql begin;
Query OK, 0 rows affected (2.51 sec)

mysql select * from test where id=6 for update;
Empty set (2.17 sec)

Transaction 2:
mysql begin;
Query OK, 0 rows affected (1.56 sec)

mysql select * from test where id=6 for update;
Empty set (2.27 sec)

Now, I use show engine innodb status to see the innodb lock status.
The output as follow:


TRANSACTIONS

Trx id counter 0 5168907
Purge done for trx's n:o  0 5168898 undo n:o  0 0
History list length 2
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208
MySQL thread id 2, query id 46 localhost root
show engine innodb status
---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread
id 2484820912
2 lock struct(s), heap size 320
MySQL thread id 1, query id 45 localhost root
TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168906 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
  0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy ;;

---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread
id 2484419504
2 lock struct(s), heap size 320
MySQL thread id 3, query id 43 localhost root
TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168905 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
  0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy
  ...

As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both
get the X gap locks on the same record. The MySQL Manual said that X
lock is an exclusive lock. Why two transactions can get the same X
lock?

Any comment will be welcomed?

Best regards,
Leo Huang

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

Why innodb can give the same X gap lock to two transactions?

[leo huang]

Hi, all,

We have an innodb table named test. It has some rows as follow:
mysql show create table test;
+---+-+
| Table | Create Table

   |
+---+-+
| test  | CREATE TABLE `test` (
 `id` int(11) NOT NULL default '0',
 `name` char(20) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---+-+
1 row in set (1.75 sec)

mysql select * from test;
++-+
| id | name|
++-+
|  1 | huangjy |
|  2 | huangjy |
|  3 | huangjy |
|  4 | huangjy |
|  5 | huangjy |
|  7 | huangjy |
|  8 | huangjy |
|  9 | huangjy |
++-+
8 rows in set (1.98 sec)

When I start two transactions as follow:

Transaction 1:
mysql begin;
Query OK, 0 rows affected (2.51 sec)

mysql select * from test where id=6 for update;
Empty set (2.17 sec)

Transaction 2:
mysql begin;
Query OK, 0 rows affected (1.56 sec)

mysql select * from test where id=6 for update;
Empty set (2.27 sec)

Now, I use show engine innodb status to see the innodb lock status.
The output as follow:


TRANSACTIONS

Trx id counter 0 5168907
Purge done for trx's n:o  0 5168898 undo n:o  0 0
History list length 2
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208
MySQL thread id 2, query id 46 localhost root
show engine innodb status
---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread
id 2484820912
2 lock struct(s), heap size 320
MySQL thread id 1, query id 45 localhost root
TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168906 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy ;;

---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread
id 2484419504
2 lock struct(s), heap size 320
MySQL thread id 3, query id 43 localhost root
TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168905 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy
...

As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both
get the X gap locks on the same record. The MySQL Manual said that X
lock is an exclusive lock. Why two transactions can get the same X
lock?

Any comment will be welcomed?

Best regards,
Leo Huang

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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Chris



I'm sure it will, what makes you think it won't?

Because some paper say that when the row is deleted or update, Innodb
just make a mark that the row is deleted and it didn't delete the
rows. I can't find more information about the re-use tablespace. Can
you give me more?

Regards,
Leo Huang

2006/7/27, Chris [EMAIL PROTECTED]:

leo huang wrote:
 hi, Chris

 Thank you for your advice!

 I know that Innodb use the logfiles circularly. Can Innodb re-use  the
 deleted rows' disk space in tablespace?

I'm sure it will, what makes you think it won't?

You might need an 'optimize table' or something to see a reduction in
the on disk file size but mysql will reclaim that space as it needs to.



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Chris

So,  the deleted rows' disk space in tablespace can't re-use when I
use Innodb, can it? And the tablespace is growing when we update the
tables, even the amount of rows do not   increase.

Regards,
Leo Huang


2006/7/28, Chris [EMAIL PROTECTED]:

leo huang wrote:
 hi, Chris


 I'm sure it will, what makes you think it won't?
 Because some paper say that when the row is deleted or update, Innodb
 just make a mark that the row is deleted and it didn't delete the
 rows. I can't find more information about the re-use tablespace. Can
 you give me more?

That's the way MVCC works. If you need full acid/transaction support,
that's the only way it can do it (postgresql works exactly the same
way). It can't just delete the row because you might roll back the
transaction and it will have to undo that delete, or other transactions
might be using it for whatever purpose.

http://dev.mysql.com/doc/refman/5.1/en/innodb-multi-versioning.html
http://dev.mysql.com/doc/refman/5.1/en/file-space-management.html



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Chris

Thank you for your advice!

I know that Innodb use the logfiles circularly. Can Innodb re-use  the
deleted rows' disk space in tablespace?

Regards,
Leo Huang

2006/7/26, Chris [EMAIL PROTECTED]:

leo huang wrote:
 hi, Dilipkumar

 Thank you very much!

 I think I know the fact: The Innodb can't reuse the deleted rows' disk
 space. And a solution is: dump the data; shutdown mysql; delete the
 files; restart mysql; import the data.

InnoDB does re-use the space inside the database, it's the logfiles that
are growing. The logs are needed in case you need to replay transactions.


I suggest you read this page:

http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html

and this page:

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

Specify 2-3 entries in the innodb_data_file_path and mysql should (if
I'm reading it properly) rotate between the files and keep size under
control.



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Dilipkumar

Thank you very much!

I think I know the fact: The Innodb can't reuse the deleted rows' disk
space. And a solution is: dump the data; shutdown mysql; delete the
files; restart mysql; import the data.

Regards,
Leo Huang

2006/7/24, [EMAIL PROTECTED] [EMAIL PROTECTED]:

Hi,

Try using the optimize table tablename ,but this will keep the data 
accordingly,but really if it is  a disk space constraint you can go with re-org 
process in which you will have to get a down time for mysql db.Process is 
something like .
Dump all the Innodb tables drop the existing innodb tables  and shutdown mysql, clear the 
Innodb log-space as ibdata1  indata2  iblogfile0  iblogfile1 and also the 
redo logs of the innodb.
Then start the mysql this will create innodb logs 1  innodb2 as what u have 
mentioned in ur cnf file and import the dump .
In this case u can able to reduce the space usage of innodb.
Try this it might help u out.


With Regards
Dilipkumar


 [EMAIL PROTECTED]:

 Hi, all

   I know the Innodb use MVCC to achieve very high concurrency. Can
 Innodb reuse the deleted rows disk space? I have an database which
 have many update operation. If Innodb can\'t reuse the space of deleted
 rows, I worry about that MySQL will exhaust our disk space very
 quickly.

   Any recommend will be welcome!


 Regards,
 Leo Huang

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






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

Can Innodb reuse the deleted rows disk space?

[leo huang]

Hi, all

I know the Innodb use MVCC to achieve very high concurrency. Can
Innodb reuse the deleted rows disk space? I have an database which
have many update operation. If Innodb can't reuse the space of deleted
rows, I worry about that MySQL will exhaust our disk space very
quickly.

Any recommend will be welcome!


Regards,
Leo Huang

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

Is the fsync() fake on FreeBSD6.1?

[leo huang]

Hi,

I benchmarked MySQL 4.1.18 on FreeBSD 6.1 and Debian 3.1 using Super Smack
1.3 some days ago.

The benchmark table  is
CREATE TABLE `Account` (
 `aid` int(11) NOT NULL auto_increment,
 `name` char(20) NOT NULL default '',
 `flag` int(11) NOT NULL default '0',
 `uidcount` int(11) NOT NULL default '0',
 `balance` int(11) NOT NULL default '0',
 `point` int(11) NOT NULL default '0',
 `blocktm` int(11) NOT NULL default '0',
 `ipnum` int(10) unsigned default NULL,
 `newdate` datetime default NULL,
 PRIMARY KEY  (`aid`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And it has 10,000,000 rows.

The SQL statement is
update Account set balance= balance + 1 where aid=?;

The result is followed:
OSClientsResult(queries per second) TPS(got
from iostat)
FreeBSD6.150   516.1
about 2000
Debian3.1   50   49.8
about 200

The result surprise me. The MySQL Performance on FreeBSD6.1 is about 10
times of on Debian3.1，and the output of iostat also shows it.

I know that MySQL uses fsync() to flush both the data and log files at
default when using innodb engine(
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html). Our
evaluating computer only has a 1RPM SCSI hard disk. I think it can do
about 200 sequential fsync() calls per second if the fsync() is real.

Is the fsync() on FreeBSD6.1 fake? I mean than the data is only written to
the drives memory and so can be lost if power goes down. And how I can
confirm this?

If the fsync() is fake, how can I get the real fsync?

Any comment is welcome!

PS:
1. Our evaluating computer is DELL PowerEdge 1650。Its hardware configuration
is followed:
   CPU: 2 * Intel Pentium III 1.33GHz 512KB Level 2 Cache(smp)
   Memory: 1024MB ECC SDRAM
   HD: SEAGATE ST336706LC（36GB Ultra160 SCSI 1RPM）
   NIC: Intel(R) PRO/1000 Network Connection

2. Some important parameters in MySQL configuration file are here:
   log-bin
   sync_binlog=1
   innodb_safe_binlog
   innodb_buffer_pool_size = 384M
   innodb_additional_mem_pool_size = 20M
   innodb_log_file_size = 100M
   innodb_log_buffer_size = 8M
   innodb_flush_log_at_trx_commit = 1
   innodb_lock_wait_timeout = 50


regards,
Leo Huang

Re: SELECT ALL and flag

[leo huang]

hi, Peter

Try this:

select a.name, sum(if (b.table_a.id is NULL,0,1)) as indid from table_a a
left join table_b b on(a.id=b.table_id.id) group by a.id;

best regards,
Leo Huang

2006/6/1, Peter Lauri [EMAIL PROTECTED]:


Hi,

I have a table table_a and table_b:

table_a {
id
name
}

table_b {
table_a_id
b_value
}

Table A is a table with names, and table B is a table with values for a
specific name (optional, therefore a specific table).

I would like to select all records in A, done by:

SELECT name FROM table_a;

Returns:
Peter
Johan
Fredrik

But then I also would like to have a flag that flags if table_b, I want it
to return this if Peters id is the only one matching in table_b:

Peter   1
Johan   0
Fredrik 0

I tried:

SELECT name, if(table_a.id=table_b.table_a_id, 1, 0) AS indid FROM table_a
JOIN table_b;

But that generates multiple rows of the records in table_a. I tried GROUP
BY
in combination with ORDER BY, but I did not manage to get it to work.

How can I do that if() without having to action do a join, I just want to
check if it exist, and then give value 1 or 0.

Anyone with ideas?

/Peter


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

Re: mysql restart error

[leo huang]

hi Dhandapani,

The 3306 port is not listening. But there are some connection whose state is
FIN_WAIT_2 as you can see in my first letter.

After about 10 minutes I shutdowned mysql, I restarted mysql as root using:
/usr/local/mysql/bin/mysqld_safe .  It worked.

Before it, I did this as mysql and I got the error.

Regards,
Leo Huang

2006/3/28, [S] Dhandapani [EMAIL PROTECTED] :

 Hi Leo,

 check for cnf file for which port you have configured the port .If it is
 in 3306 port then do netstat -an|grep LIST ,check for 3306 port is listening
 on your system .If yes you mysql process has not shutdown properly.

 shutdown the mysql process completely and start the mysql process by
 specifying your datadirectory.

  /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
 --datadir=/usr/local/mysql/data/  --user=mysql 

 port= 3306
 socket  = /tmp/mysql.sock

 Regards,
 Dhandapani


 leo huang wrote:

 hi, Lakshmi

  The mysql process had ended. I get it from both mysql err log and ps
 output.

 regards,
 Leo Huang

 2006/3/28, Lakshmi M P
 [EMAIL PROTECTED] [EMAIL PROTECTED]:

  Run   ps -ef | grep mysql and see any mysql process is running and if so
 kill the same and try to start mysql.It may help.
 leo huang wrote:

  hi,

 I met the MySQL restart error today.

 First, I stopped the running mysql server using
 /usr/local/mysql/bin/mysqladmin -uroot shutdown.

 After the server shutdowned, I restarted it using

 /usr/local/mysql/bin/mysqld_safe .

 Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port:
 Address already in use.

 There was no other process that was using the port 3306 which mysql

server

  use. But there were some mysql connect did not release because the

  shutdown.

  The error log is followed:
 060328  8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

 060328  8:20:47  InnoDB: Starting shutdown...
 060328  8:20:49  InnoDB: Shutdown completed; log sequence number 120

 2134241340
 060328  8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown

  complete

  060328 08:20:49  mysqld ended

 060328 08:21:15  mysqld started
 060328  8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address
 already in use
 060328  8:21:15 [ERROR] Do you already have another mysqld server

running on

  port: 3306 ?
 060328  8:21:15 [ERROR] Aborting

 060328  8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown

  complete

  060328 08:21:15  mysqld ended

 The netstat outputs are followed:
 $ netstat -al
 Active Internet connections (including servers)
 Proto Recv-Q Send-Q  Local Address  Foreign

  Address(state)

  tcp4   0  0  bj.3306  s4.9405   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.5168   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.25007  FIN_WAIT_2

 tcp4   0  0  bj.3306  s4.9940   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.3916   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.15229  FIN_WAIT_2
 tcp4   0  0
 bj.3306  s4.6479   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.7873   FIN_WAIT_2

 Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE.

 Any comment will be great thankful!


 Regards,
 Leo Huang


--
 regards,
 Lakshmi.M.P.
 DBA-Support
 Sify Limited.
 Ext:4134

 ** DISCLAIMER **
 Information contained and transmitted by this E-MAIL is proprietary to
 Sify Limited and is intended for use only by the individual or entity to

 which it is addressed, and may contain information that is privileged,
 confidential or exempt from disclosure under applicable law. If this is a
 forwarded message, the content of this E-MAIL may not have been sent with

 the authority of the Company. If you are not the intended recipient, an
 agent of the intended recipient or a  person responsible for delivering
 the
 information to the named recipient,  you are notified that any use,

 distribution, transmission, printing, copying or dissemination of this
 information in any way or in any manner is strictly prohibited. If you
 have
 received this communication in error, please delete this mail  notify us

 immediately at [EMAIL PROTECTED]
 www.sify.com - your homepage on the internet for news, sports, finance,
 astrology, movies, entertainment, food, languages etc

mysql restart error

[leo huang]

hi,

I met the MySQL restart error today.

First, I stopped the running mysql server using
/usr/local/mysql/bin/mysqladmin -uroot shutdown.

After the server shutdowned, I restarted it using
/usr/local/mysql/bin/mysqld_safe .

Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port:
Address already in use.

There was no other process that was using the port 3306 which mysql server
use. But there were some mysql connect did not release because the shutdown.


The error log is followed:
060328  8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

060328  8:20:47  InnoDB: Starting shutdown...
060328  8:20:49  InnoDB: Shutdown completed; log sequence number 120
2134241340
060328  8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

060328 08:20:49  mysqld ended

060328 08:21:15  mysqld started
060328  8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address
already in use
060328  8:21:15 [ERROR] Do you already have another mysqld server running on
port: 3306 ?
060328  8:21:15 [ERROR] Aborting

060328  8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

060328 08:21:15  mysqld ended

The netstat outputs are followed:
$ netstat -al
Active Internet connections (including servers)
Proto Recv-Q Send-Q  Local Address  Foreign Address(state)
tcp4   0  0  bj.3306  s4.9405   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.5168   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.25007  FIN_WAIT_2
tcp4   0  0  bj.3306  s4.9940   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.3916   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.15229  FIN_WAIT_2
tcp4   0  0  bj.3306  s4.6479   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.7873   FIN_WAIT_2

Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE.

Any comment will be great thankful!

Regards,
Leo Huang

Re: mysql restart error

[leo huang]

hi, Lakshmi

 The mysql process had ended. I get it from both mysql err log and ps
output.

regards,
Leo Huang

2006/3/28, Lakshmi M P [EMAIL PROTECTED]:

 Run   ps -ef | grep mysql and see any mysql process is running and if so
 kill the same and try to start mysql.It may help.
 leo huang wrote:
  hi,
 
  I met the MySQL restart error today.
 
  First, I stopped the running mysql server using
  /usr/local/mysql/bin/mysqladmin -uroot shutdown.
 
  After the server shutdowned, I restarted it using
  /usr/local/mysql/bin/mysqld_safe .
 
  Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port:
  Address already in use.
 
  There was no other process that was using the port 3306 which mysql
 server
  use. But there were some mysql connect did not release because the
 shutdown.
 
 
  The error log is followed:
  060328  8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown
 
  060328  8:20:47  InnoDB: Starting shutdown...
  060328  8:20:49  InnoDB: Shutdown completed; log sequence number 120
  2134241340
  060328  8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown
 complete
 
  060328 08:20:49  mysqld ended
 
  060328 08:21:15  mysqld started
  060328  8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address
  already in use
  060328  8:21:15 [ERROR] Do you already have another mysqld server
 running on
  port: 3306 ?
  060328  8:21:15 [ERROR] Aborting
 
  060328  8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown
 complete
 
  060328 08:21:15  mysqld ended
 
  The netstat outputs are followed:
  $ netstat -al
  Active Internet connections (including servers)
  Proto Recv-Q Send-Q  Local Address  Foreign
 Address(state)
  tcp4   0  0  bj.3306  s4.9405   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.5168   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.25007  FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.9940   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.3916   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.15229  FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.6479   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.7873   FIN_WAIT_2
 
  Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE.
 
  Any comment will be great thankful!
 
  Regards,
  Leo Huang
 
 


 --
 regards,
 Lakshmi.M.P.
 DBA-Support
 Sify Limited.
 Ext:4134

 ** DISCLAIMER **
 Information contained and transmitted by this E-MAIL is proprietary to
 Sify Limited and is intended for use only by the individual or entity to
 which it is addressed, and may contain information that is privileged,
 confidential or exempt from disclosure under applicable law. If this is a
 forwarded message, the content of this E-MAIL may not have been sent with
 the authority of the Company. If you are not the intended recipient, an
 agent of the intended recipient or a  person responsible for delivering
 the
 information to the named recipient,  you are notified that any use,
 distribution, transmission, printing, copying or dissemination of this
 information in any way or in any manner is strictly prohibited. If you
 have
 received this communication in error, please delete this mail  notify us
 immediately at [EMAIL PROTECTED]

 www.sify.com - your homepage on the internet for news, sports, finance,
 astrology, movies, entertainment, food, languages etc

Re: mySQL 5 and CPu at 99.99%

[leo huang]

hi, Taiyo
| innodb_buffer_pool_size | 8388608
| key_buffer_size | 8388600

Try to increase the innodb_buffer_pool_size if you use the innodb storage or
key_buffer_size if MyISAM storage is used or both.

You can get more information about innodb_buffer_pool_size and
key_buffer_size from this:
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Best regards,

Leo Huang

2006/2/28, Taiyo [EMAIL PROTECTED]:
 Greetings,

 We are running a server and the CPU is at %99.99 at all times, after about
 2-3 hours of processing queries just hang, sounds like our hardware is
weak
 but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.

 I was hoping someone could look at our settings and would help us analyze
 this issue:

 Please advise.

 Here are the stats:

 Some version information:

 mySQL version: 5.0.16-standard
 Uname: Linux db.example.com
 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
 GNU/Linux
 RAM:4GB
 SWAP  1GB
 HD:   2 SCSI 10k RPM on 2 separate
 controllers.

 Some information about the load:
 Queries per second avg: 16.346 (about)
 Our biggest table is 3.5 million records and we index 3 of the columns for
 fulltext search
 We do a lot of join queries on 2 tables.

 Some mySQL variables:
 [mysqld]
 tmpdir=/db.example.com/tmp
 query_cache_size=1048576
 query_cache_limit = 33554432
 query_cache_size = 33554432
 myisam_sort_buffer_size = 33554432
 sort_buffer_size = 33554432
 max_connections=500
 table_cache = 1000
 max_tmp_tables = 256

 Here is all of my mysql -e 'SHOW VARIABLES':

+-+-
 -+
 | Variable_name   | Value
 |

+-+-
 -+
 | auto_increment_increment| 1
 |
 | auto_increment_offset   | 1
 |
 | automatic_sp_privileges | ON
 |
 | back_log| 50
 |
 | basedir | /
 |
 | binlog_cache_size   | 32768
 |
 | bulk_insert_buffer_size | 8388608
 |
 | character_set_client| latin1
 |
 | character_set_connection| latin1
 |
 | character_set_database  | latin1
 |
 | character_set_results   | latin1
 |
 | character_set_server| latin1
 |
 | character_set_system| utf8
 |
 | character_sets_dir  | /usr/share/mysql/charsets/
 |
 | collation_connection| latin1_swedish_ci
 |
 | collation_database  | latin1_swedish_ci
 |
 | collation_server| latin1_swedish_ci
 |
 | completion_type | 0
 |
 | concurrent_insert   | 1
 |
 | connect_timeout | 5
 |
 | datadir | /var/lib/mysql/
 |
 | date_format | %Y-%m-%d
 |
 | datetime_format | %Y-%m-%d %H:%i:%s
 |
 | default_week_format | 0
 |
 | delay_key_write | ON
 |
 | delayed_insert_limit| 100
 |
 | delayed_insert_timeout  | 300
 |
 | delayed_queue_size  | 1000
 |
 | div_precision_increment | 4
 |
 | engine_condition_pushdown   | OFF
 |
 | expire_logs_days| 0
 |
 | flush   | OFF
 |
 | flush_time  | 0
 |
 | ft_boolean_syntax   | + -()~*:|
 |
 | ft_max_word_len | 84
 |
 | ft_min_word_len | 2
 |
 | ft_query_expansion_limit| 20
 |
 | ft_stopword_file| (built-in)
 |
 | group_concat_max_len| 1024
 |
 | have_archive| YES
 |
 | have_bdb| NO
 |
 | have_blackhole_engine   | NO
 |
 | have_compress   | YES
 |
 | have_crypt  | YES
 |
 | have_csv| NO
 |
 | have_example_engine | NO
 |
 | have_federated_engine   | NO
 |
 | have_geometry   | YES
 |
 | have_innodb | YES
 |
 | have_isam   | NO
 |
 | have_ndbcluster | NO
 |
 | have_openssl| NO
 |
 | have_query_cache| YES
 |
 | have_raid   | NO
 |
 | have_rtree_keys | YES
 |
 | have_symlink| YES
 |
 | init_connect|
 |
 | init_file   |
 |
 | init_slave  |
 |
 | innodb_additional_mem_pool_size | 1048576
 |
 | innodb_autoextend_increment | 8
 |
 | innodb_buffer_pool_awe_mem_mb   | 0
 |
 | innodb_buffer_pool_size | 8388608
 |
 | innodb_checksums| ON
 |
 | innodb_commit_concurrency   | 0

How to keep account independent in replication

[leo huang]

Hi,

How can I keep the account of MySQL independent in replication?

We have two MySQL 4.1.18 nodes: A and B. B replicate A. We want that the
account in A is independent. That is to said, it would not affect the
account in B when we add or delete the account in A.

We add the following option in B's my.cnf and use the INSERT or DELETE
statement in A to deal with the account management now.
  replicate-ignore-db=mysql

As you can see, it is ugly and discommodious.

Is there any better solution?


Best regards,

Leo Huang

Re: Same question, better example

[leo huang]

Ariel,

You can try this:

mysqlselect stri from prueba order by stri+0 desc;

Leo Huang



2006/2/17, Ariel Sánchez Mora [EMAIL PROTECTED]:

 mysql describe prueba;
 +---+-+--+-+-+---+
 | Field | Type| Null | Key | Default | Extra |
 +---+-+--+-+-+---+
 | inte  | int(2)  | YES  | | NULL|   |
 | stri  | char(2) | YES  | | NULL|   |
 +---+-+--+-+-+---+
 2 rows in set (0.00 sec)

 mysql select * from prueba;
 +--+--+
 | inte | stri |
 +--+--+
 |1 | 1|
 |2 | 2|
 |3 | 3|
 |4 | 4|
 |5 | 5|
 |6 | 6|
 |7 | 7|
 |8 | 8|
 |9 | 9|
 |   10 | 10   |
 +--+--+
 10 rows in set (0.00 sec)

 --Is there a way I can make this:

 mysql select stri from prueba order by stri desc;
 +--+
 | stri |
 +--+
 | 9|
 | 8|
 | 7|
 | 6|
 | 5|
 | 4|
 | 3|
 | 2|
 | 10   |
 | 1|
 +--+
 10 rows in set (0.00 sec)

 --come out like this:

 mysql select inte from prueba order by inte desc;
 +--+
 | inte |
 +--+
 |   10 |
 |9 |
 |8 |
 |7 |
 |6 |
 |5 |
 |4 |
 |3 |
 |2 |
 |1 |
 +--+
 10 rows in set (0.00 sec)

 I'm using MySQL 4.1.14 in windows 2000.

 Thanks!

 Ariel

Re: selecting min, max

[leo huang]

hi,

I think the following link would be some help for you!
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html

Leo Huang

2006/2/14, Octavian Rasnita [EMAIL PROTECTED]:

 Hi,

 From: Rhino [EMAIL PROTECTED]
 ...
   I need to extract a list which the following values from this table,
 for
   each hour (in date_time field):
  
   - symbol
   - min(price)
   - max(price)
   - price where date_time is the earliest for that certain hour.
   - price where the date_time is the last from that hour.
   - The sum of volume from that hour.
  
   I have tried to get the list of symbols, then get each hourly period
 and
   calculate those 6 values for each period, but there are many symbols
 and
   very many periods, and it takes very very much time.
  
   Is there a more intelligent way of getting those values in another way
   than
   symbol by symbol and period by period?
  
  It's hard to answer your question since you haven't given us any
 examples
 of
  the SQL you've already tried. You haven't told us which version of MySQL
 you
  are using, either. That makes a big difference since newer versions
 offer
  many more SQL capabilities like views and subqueries that could really
 help
  you.
 
  You certainly shouldn't have to write separate queries for each
 different
  symbol that you are using!
 

 Here is the table definition. The table is simple, but what I want is
 complicated:

 CREATE TABLE `tickers` (
 `symbol` varchar(20) NOT NULL,
 `last_volume` bigint(20) unsigned default NULL,
 `last_price` decimal(20,4) unsigned default NULL,
 `last_update` datetime default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 I want to get a list of values for more periods of time, 5 minutes, 15
 minutes, and hourly.

 I need to get:

 symbol
 date_format(last_update, '%Y-%m-%d') as date
 date_format(last_update, '%H:%i:%s') as time
 min(last_price) as low  (The min value of last_price for that period)
 max(last_price) as high  (the max price from that period)
 last_price as open  (where last_update=min(last_update) from that period)
 last_price as close  (where last_update=max(last_update) from that period)

 The result data should look something like:

 Symbol,data,time,low,high,open,close
 simb1,2006-02-08,10:15:00,1000,1200,1050,1150
 simb1,2006-02-08,10:30:00,1100,1150,1150,1150
 simb1,2006-02-08,10:45:00,1000,1200,1050,1150
 simb1,2006-02-08,11:00:00,1050,1200,1050,1150
 simb1,2006-02-08,11:15:00,1000,1200,1050,1150

 ... then here follow the rest of records for simb1 and for other symbols.

 You may see that the first time is 10:15:00, the next time is 10:30:00,
 the next is 10:45, so the period of time is 15 minutes.

 The first low is the lowest price between 10:15:00 and 10:30:00 and the
 high is the highest price in that period.
 The first open value is the last_price of the first trade from that
 period and the close price is the last_price of the latest trade from
 that
 period.

 I don't know if MySQL can create a query that can get those values fast
 enough.

 Thank you very much.


 Teddy


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

Re: InnoDB Questions

[Leo Huang]

MySQL doesn't work.

I tried to modified the line:
innodb_data_file_path = ibdata1:10M:autoextend

to
innodb_data_file_path = ibdata1:500M
or
innodb_data_file_path = ibdata1:500M:autoextend
or
innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend

they all gave me the same error below.


InnoDB: Error: data file ./ibdata1 is of a different size
InnoDB: than specified in the .cnf file!
InnoDB: Could not open data files
031105 9:42:56 Can't init databases
031105 09:42:56 mysqld ended

The last one really does the matter!! That's if I run out of the space
on the current directory, I won't be able to put another file anywhere
else!?

Leo



Nitin wrote:

You're right, it wont decrease the physical size, but only free up the space
within file to optimize the tablespace, in case, you want to check the size
of this data file, you can remove autoextend from:

innodb_data_file_path = ibdata1:10M:autoextend

and specify the size limit in the place of 10M, but i guess, if you specify
the size to less than 790M (which is the current size of your datafile), to
say 500M and the space is free in that file, it will resize it. That's the
behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it,
and let me know as i dont have my database on innodb yet.

'Tablespace is part of your database. database consists of at least one
tablespace. it's basically used to restrict users from seeing other user's
data. like, you can assign a tablespace to a user and none else (ofcourse
other than root) can see the data.

For more info, have a look at:
http://www.mysql.com/doc/en/InnoDB_File_space.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 8:00 PM
Subject: Re: InnoDB Questions


  

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

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



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions




Hello,

first things first, you cann't resize your datafiles without
  

shutting down


your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data
  

dir or


in /etc dir, for the default options specified there fo the datafile
  

with:


innodb_data_file_path

Yes, you can add data file, just add another entry to above option.
  

option


entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store
  

actual


data. one or more of these files are attached to one tablespace and
  

one file


cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql
  

statements


applied to database. these files are used to restore data in case of
  

any


crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into


InnoDB a


few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44


ib_logfile0


-rw-rw1 mysqlmysql10485760 Nov  4 20:44


ib_logfile1


-rw-rw1 mysqlmysql10485760 Nov  3 00:02


ib_logfile2


But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2


to


get higher performance. Can I do that now, after

Re: Mysql Stoping

[Leo Huang]

Hi Trevor,

I suggest you to compile and reinstall MySQL from the source distribution.

A suggested option is

CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \
   -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \
   --with-mysqld-ldflags=-all-static

For more information, please refer to
http://www.mysql.com/doc/en/Installing_source.html

If you are running a RH Linux try to modify the --prefix=/usr, so you
can use the RH scripts, and don't forget to set up something like --datadir

This is my configure options, hope it helps.

--prefix=/usr --datadir=/var/lib/mysql --with-innodb
--with-extra-charsets=complex --enable-thread-safe-client
--enable-local-infile --enable-assembler --disable-shared
--with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --quiet


Cheers,
Leo




Trevor wrote:

Hi All

Was wondering if someone could shef a bit of light on whats happening, as i keep 
loosing the connection to the mysql server, and i get the following error:
mysqld dead but subsys locked

Thanks in advance

Cheers

Trevor

  




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

Re: Problem with DELETE USING

[Leo Huang]

Bamelis,

The error message doesn't seem to match your SQL...

It only shows up to 'AND tblTest.URL = tblT'
but your SQL is 'AND tblTest.Comment = tblTest2.Comment'
Is that a problem??

An example from  MySQL manual is 'DELETE FROM t1,t2 USING t1,t2,t3 WHERE 
t1.id=t2.id AND t2.id=t3.id'

Leo

Bamelis Steve wrote:

Hi, 

I'm a newbie when it comes to mySQL.

I have the following command.



DELETE FROM tblTest2 

USING tblTest2,tblTest 

WHERE tblTest.Name = tblTest2.Name 

AND tblTest.URL = tblTest2.URL 

AND tblTest.Comment = tblTest2.Comment



I get the following error:

You have an error in your SQL syntax near 'USING tblTest2,tblTest WHERE
tblTest.Name = tblTest2.Name AND tblTest.URL = tblT' at line 1


I want to delete rows from tblTest2 where there are similarities in tblTest.

In fact using a subselect or something.



Could anyone help me please,

Thx





 



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

Re: Viruses from the list

[Leo Huang]

Al Bogner,

Thanks for you info.

Yes, I got quite a few as well. About Microsoft update stuff etc.

But I think emails with viruses are quite common, my mail server 
captures around 2,000 emails with virus everyday. Also this is an old 
virus(relatively speaking), so it should be fine, I think.

Leo

Al Bogner wrote:

I use an email-adress for this list only and since my first posting a few days 
ago I got viruses, while I didn't before.

VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED])
VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED])
VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED])
Wake up people, it was time enough to update virus-definitions. Clean your 
pcs.

I will disable my email-adress soon.

Al

 



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

InnoDB Questions

[Leo Huang]

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?

The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?

I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.

Thanks a lot,
Leo


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

Re: InnoDB Questions

[Leo Huang]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

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



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions


 Hello,

 first things first, you cann't resize your datafiles without
shutting down
 your database. if it's ok with you, have a look at
 http://www.mysql.com/doc/en/Adding_and_removing.html

 you may want to have a look at you my.cnf file, stored in mysql data
dir or
 in /etc dir, for the default options specified there fo the datafile
with:

 innodb_data_file_path

 Yes, you can add data file, just add another entry to above option.
option
 entry is self-explainatory.

 At last, ibdata1, ibdata2 are actual data files used to store
actual
 data. one or more of these files are attached to one tablespace and
one file
 cant span across tablespaces.

 ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
 applied to database. these files are used to restore data in case of
any
 crash or mishap.

 for further info, have a look at:
 http://www.mysql.com/doc/en/InnoDB_start.html

 Enjoy
 Nitin


 - Original Message - 
 From: Leo Huang [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 04, 2003 6:28 PM
 Subject: InnoDB Questions


  Hello,
 
  I have a few questions about InnoDB.
  I am new to InnoDB, and just converted my MyISAM tables into
InnoDB a
  few days ago.
  I notice that it generates these files
 
  -rw-rw1 mysqlmysql2560 Nov  2 13:07
  ib_arch_log_00
  -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
  -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile0
  -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile1
  -rw-rw1 mysqlmysql10485760 Nov  3 00:02
ib_logfile2
 
  But how can those files work together?
 
  In InnoDB documentation, it suggests to add another file ibdata2
to
  get higher performance. Can I do that now, after I have created
the
  ibdata1 and used it for a while?
 
  The most important thing is I deleted a 300M database, but the
ibdata1
  remains the same size. MyPHPAdmin says 330,000KB free. How can I
make
  the data file smaller?
 
  I will be really appreciated if someone can briefly describe
what's
  happening to those files or point me to some articles.
 
  Thanks a lot,
  Leo
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 



-BEGIN PGP SIGNATURE-
Version: PGP 8.0.2

iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN
h21IQZ8ozOUeELhvWSpznyTI
=H/2E
-END PGP SIGNATURE-


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

Re: InnoDB Questions

[Leo Huang]

I don't know.

I will get some time this week, shutdown MySQL, backup my binary files, 
have a go as what Nitin said and see what's going on there.

Leo

Gabriel Ricard wrote:

On Tuesday, November 4, 2003, at 07:58  AM, Leo Huang wrote:

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?


How exactly does this increase performance? Will InnoDB store some 
data in one data file and some in another (somewhat like RAID 1)?

- Gabriel




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

Re: InnoDB Questions

[Leo Huang]

Hello Nitin,

From the timestamp of the log files, it seems that the first two files
works together while the last one seems just sitting there, doesn't do
anything.

Also, will the log files getting bigger and bigger in the future?? If so
how should I deal with them?

For your last suggestion, what will Oracle do if I specify a datafile
size less than the database size, e.g. I specify 20M in the my.cnf while
there are actually 400M of data in my database?

Leo

Nitin wrote:

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:

innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?

The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?

I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.

Thanks a lot,
Leo


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


http://lists.mysql.com/[EMAIL PROTECTED]
  



  




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