High MySQL sleep count
Hiya I have a server that gets connections / requests from Germany and South Africa, Johannesburg (my server's based in Cape Town). All servers are in datacentres. Im not 100% sure if its latency related, but I see a load or SLEEP when I do mysqladin proc, which appears to be hogging mysql. My question is. Would adjusting timeout values address this problem. I was thinking of using / setting these values. [mysqld] set-variable= connect_timeout=20 set-variable= wait_timeout=20 set-variable= interactive_timeout=20 set-variable= net_write_timeout=20 Can anyone share some thought or opinion on this. Or whats the correct practice. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
Quoting Sydney Puente sydneypue...@yahoo.com: Hello, I have a mysqldump file created by AutoMySQLBackup. And it hangs when I do a mysql source backup.sql If you are trying to restore from backup.sql to a database called soure then your syntax is wrong. You should be running: mysql source backup.sql Assuming you don´t need a password to connect to mysql... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
On Wed, May 26, 2010 at 10:20 AM, a.sm...@ukgrid.net wrote: If you are trying to restore from backup.sql to a database called soure then your syntax is wrong. You should be running: mysql source backup.sql You are assuming that the file named backup.sql contains a backup of the database named backup. There is no grounds in the mail that support that assumption; I would even say that 'backup.sql' is a rather generic name for a file containing a backup. Such naming is not uncommon for a one-shot backup; or it might be used as a placeholder in an explanation. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: source backup.sql - troubleshoot
Quoting Johan De Meersman vegiv...@tuxera.be: On Wed, May 26, 2010 at 10:20 AM, a.sm...@ukgrid.net wrote: If you are trying to restore from backup.sql to a database called soure then your syntax is wrong. You should be running: mysql source backup.sql You are assuming that the file named backup.sql contains a backup of the database named backup. There is no grounds in the mail that support that assumption; I would even say that 'backup.sql' is a rather generic name for a file containing a backup. Such naming is not uncommon for a one-shot backup; or it might be used as a placeholder in an explanation. Actually Im assuming a DB name of source, which I mentioned in my first post. Thats actually an bad assumption as, as Ive just read, source is an alternative way to read in data from a file that Id never seen before. However the syntax would still seem to be bad, assuming the command is being run from the command prompt as opposed to the mysql command prompt. According to the man page the two options from the command prompt are: shell mysql db_name backup-file.sql OR shell mysql -e source /path-to-backup/backup-file.sql db_name thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
On Wed, May 26, 2010 at 10:54 AM, a.sm...@ukgrid.net wrote: Actually Im assuming a DB name of source, which I mentioned in my first post. Thats actually an bad assumption as, as Ive just read, source is an alternative way to read in data from a file that Id never seen before. However the syntax would still seem to be bad, assuming the command is being run from the command prompt as opposed to the mysql command prompt. According to the man page the two options from the command prompt are: shell mysql db_name backup-file.sql OR shell mysql -e source /path-to-backup/backup-file.sql db_name Ahh :-) It is quite possible for the backup file to contain a use mydatabase statement - I usually do this, makes my restores easier. The clause is added automatically by mysqldump if you use the --databases parameter. It could be argued, however, that this allows accidental restores of a production database, whereas the omittance of the use clause means that the client will barf as soon as you start the restore without target db specification, because you're trying to create objects outside of a database. Both approaches are valid. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: source backup.sql - troubleshoot
Hello, The invocation syntax is OK I think. yes backup.sql was just a generic name. I think the sql file could be called anything. And there is a use event The result of sourcing the sql file is to create only the 1st (of the expected 4 ) tables - and it hangs - I never get the mysql prompt back. -- MySQL dump 10.11 -- -- Database: event -- -- -- Server version 5.0.54a-enterprise /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `event` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `event`; Can I have the commands being issued echo to the screen somehow, so I can identify which command is causing the problem? Or investigate the problem in some other way? TIA -Syd mysql source /home/sydney/event/event_2010-05-25_02h07m.Tuesday.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database changed Query OK, 0 rows affected (0.20 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) From: Johan De Meersman vegiv...@tuxera.be To: a.sm...@ukgrid.net Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wed, 26 May, 2010 10:15:47 Subject: Re: source backup.sql - troubleshoot On Wed, May 26, 2010 at 10:54 AM, a.sm...@ukgrid.net wrote: Actually Im assuming a DB name of source, which I mentioned in my first post. Thats actually an bad assumption as, as Ive just read, source is an alternative way to read in data from a file that Id never seen before. However the syntax would still seem to be bad, assuming the command is being run from the command prompt as opposed to the mysql command prompt. According to the man page the two options from the command prompt are: shell mysql db_name backup-file.sql OR shell mysql -e source /path-to-backup/backup-file.sql db_name Ahh :-) It is quite possible for the backup file to contain a use mydatabase statement - I usually do this, makes my restores easier. The clause is added automatically by mysqldump if you use the --databases parameter. It could be argued, however, that this allows accidental restores of a production database, whereas the omittance of the use clause means that the client will barf as soon as you start the restore without target db specification, because you're trying to create objects outside of a database. Both approaches are valid. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: source backup.sql - troubleshoot
The use event statement is the one that results in the database changed message. You can easily count the result messages after that to find out which statement is hanging. It seems very strange to me that the import would just hang, and not exit with an error. Is there any disk activity ongoing ? Aren't your disks (or your innodb tablespace) full ? Is there anything in the systemlogs that might be relevant ? On Wed, May 26, 2010 at 12:18 PM, Sydney Puente sydneypue...@yahoo.comwrote: Hello, The invocation syntax is OK I think. yes backup.sql was just a generic name. I think the sql file could be called anything. And there is a use event The result of sourcing the sql file is to create only the 1st (of the expected 4 ) tables - and it hangs - I never get the mysql prompt back. -- MySQL dump 10.11 -- -- Database: event -- -- -- Server version 5.0.54a-enterprise /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `event` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `event`; Can I have the commands being issued echo to the screen somehow, so I can identify which command is causing the problem? Or investigate the problem in some other way? TIA -Syd mysql source /home/sydney/event/event_2010-05-25_02h07m.Tuesday.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database changed Query OK, 0 rows affected (0.20 sec) Query OK, 0 rows affected (0.12 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) From: Johan De Meersman vegiv...@tuxera.be To: a.sm...@ukgrid.net Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wed, 26 May, 2010 10:15:47 Subject: Re: source backup.sql - troubleshoot On Wed, May 26, 2010 at 10:54 AM, a.sm...@ukgrid.net wrote: Actually Im assuming a DB name of source, which I mentioned in my first post. Thats actually an bad assumption as, as Ive just read, source is an alternative way to read in data from a file that Id never seen before. However the syntax would still seem to be bad, assuming the command is being run from the command prompt as opposed to the mysql command prompt. According to the man page the two options from the command prompt are: shell mysql db_name backup-file.sql OR shell mysql -e source /path-to-backup/backup-file.sql db_name Ahh :-) It is quite possible for the backup file to contain a use mydatabase statement - I usually do this, makes my restores easier. The clause is added automatically by mysqldump if you use the --databases parameter. It could be argued, however, that this allows accidental restores of a production database, whereas the omittance of the use clause means that the client will barf as soon as you start the restore without target db specification, because you're trying to create objects outside of a database. Both approaches are valid. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: source backup.sql - troubleshoot
On 26/05/2010 12.18, Sydney Puente wrote: [...] CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `event`; Can I have the commands being issued echo to the screen somehow, so I can identify which command is causing the problem? Or investigate the problem in some other way? [...] Not sure if this was already mentioned: mysql --show-warnings -v -u user -ppass db_name dump_file You can use -vv or -vvv for more verbose output. Regards Dimitre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
Thanks Johan,
Ah I see.
So this line for example:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
generates the first
Query OK, 0 rows affected (0.00 sec)
I did not realise, it looks like a comment.
Not sure about disk activity - top says 100% on 1 of the 4 CPUS
Seems to be this causing problems
DROP TABLE IF EXISTS `ping`;
CREATE TABLE `ping` (
`TestName` varchar(50) default '',
`TimeStamp` int(11) default '0',
`Elapsedtime` int(11) default '0',
`Fail` int(11) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `ping`
--
LOCK TABLES `ping` WRITE;
/*!4 ALTER TABLE `ping` DISABLE KEYS */;
I think it is this causing problems
INSERT INTO `ping` VALUES
('Test1',1258368123,1,0),('Test1',1258368134,1,0),('Test1',1258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169,1,0)...
...
TIA
Syd
From: Johan De Meersman vegiv...@tuxera.be
To: Sydney Puente sydneypue...@yahoo.com
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 11:35:22
Subject: Re: source backup.sql - troubleshoot
The use event statement is the one that results in the database changed
message. You can easily count the result messages after that to find out which
statement is hanging.
It seems very strange to me that the import would just hang, and not exit with
an error. Is there any disk activity ongoing ? Aren't your disks (or your
innodb tablespace) full ? Is there anything in the systemlogs that might be
relevant ?
On Wed, May 26, 2010 at 12:18 PM, Sydney Puente sydneypue...@yahoo.com wrote:
Hello,
The invocation syntax is OK I think.
yes backup.sql was just a generic name. I think the sql file could be called
anything.
And there is a use event
The result of sourcing the sql file is to create only the 1st (of the
expected 4 ) tables - and it hangs - I never get the mysql prompt back.
-- MySQL dump 10.11
--
-- Database: event
-- --
-- Server version 5.0.54a-enterprise
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `event`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT CHARACTER
SET latin1 */;
USE `event`;
Can I have the commands being issued echo to the screen somehow, so I can
identify which command is causing the problem?
Or investigate the problem in some other way?
TIA
-Syd
mysql source /home/sydney/event/event_2010-05-25_02h07m.Tuesday.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.20 sec)
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
From: Johan De Meersman vegiv...@tuxera.be
To: a.sm...@ukgrid.net
Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 10:15:47
Subject: Re: source backup.sql - troubleshoot
On Wed, May 26, 2010 at 10:54 AM, a.sm...@ukgrid.net wrote:
Actually Im assuming a DB name of source, which I mentioned in my first
post. Thats actually an bad assumption as, as Ive just read, source is an
alternative way to read in data from a file that Id never seen before.
However the syntax would still seem to be bad, assuming the command is being
run from the command prompt as opposed to the mysql command prompt.
According to the man page the two options from the command prompt are:
shell mysql db_name backup-file.sql
OR
shell mysql -e source /path-to-backup/backup-file.sql db_name
Ahh :-)
It is quite possible for the backup file to contain a use mydatabase
statement - I usually do this, makes my restores easier. The clause is added
automatically by mysqldump if you use the --databases parameter.
It could be argued, however, that this allows accidental restores of a
production database, whereas the omittance of the use clause means that the
client will barf as soon as you start the restore without target db
specification, because you're trying to
Re: source backup.sql - troubleshoot
A-ha ! :-)
That's an InnoDB table, allright. Can you check if your InnoDB file is set
to autoextend ? It smells like it's full and waiting for more space or
something.
Either look in your my.cnf file, or do a show variables like '%inno%';.
Check the filesize of your InnoDB datafiles, too.
On Wed, May 26, 2010 at 1:27 PM, Sydney Puente sydneypue...@yahoo.comwrote:
Thanks Johan,
Ah I see.
So this line for example:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
generates the first
Query OK, 0 rows affected (0.00 sec)
I did not realise, it looks like a comment.
Not sure about disk activity - top says 100% on 1 of the 4 CPUS
Seems to be this causing problems
DROP TABLE IF EXISTS `ping`;
CREATE TABLE `ping` (
`TestName` varchar(50) default '',
`TimeStamp` int(11) default '0',
`Elapsedtime` int(11) default '0',
`Fail` int(11) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `ping`
--
LOCK TABLES `ping` WRITE;
/*!4 ALTER TABLE `ping` DISABLE KEYS */;
I think it is this causing problems
INSERT INTO `ping` VALUES
('Test1',1258368123,1,0),('Test1',1258368134,1,0),('Test1',1258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169,1,0)...
...
TIA
Syd
From: Johan De Meersman vegiv...@tuxera.be
To: Sydney Puente sydneypue...@yahoo.com
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 11:35:22
Subject: Re: source backup.sql - troubleshoot
The use event statement is the one that results in the database changed
message. You can easily count the result messages after that to find out
which statement is hanging.
It seems very strange to me that the import would just hang, and not exit
with an error. Is there any disk activity ongoing ? Aren't your disks (or
your innodb tablespace) full ? Is there anything in the systemlogs that
might be relevant ?
On Wed, May 26, 2010 at 12:18 PM, Sydney Puente sydneypue...@yahoo.com
wrote:
Hello,
The invocation syntax is OK I think.
yes backup.sql was just a generic name. I think the sql file could be
called anything.
And there is a use event
The result of sourcing the sql file is to create only the 1st (of the
expected 4 ) tables - and it hangs - I never get the mysql prompt back.
-- MySQL dump 10.11
--
-- Database: event
-- --
-- Server version 5.0.54a-enterprise
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `event`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `event`;
Can I have the commands being issued echo to the screen somehow, so I can
identify which command is causing the problem?
Or investigate the problem in some other way?
TIA
-Syd
mysql source /home/sydney/event/event_2010-05-25_02h07m.Tuesday.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.20 sec)
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
From: Johan De Meersman vegiv...@tuxera.be
To: a.sm...@ukgrid.net
Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 10:15:47
Subject: Re: source backup.sql - troubleshoot
On Wed, May 26, 2010 at 10:54 AM, a.sm...@ukgrid.net wrote:
Actually Im assuming a DB name of source, which I mentioned in my
first post. Thats actually an bad assumption as, as Ive just read, source is
an alternative way to read in data from a file that Id never seen before.
However the syntax would still seem to be bad, assuming the command is being
run from the command prompt as opposed to the mysql command prompt.
According to the man page the two options from the command prompt are:
shell mysql db_name backup-file.sql
OR
shell mysql -e source
Re: source backup.sql - troubleshoot
Thanks.
Getting better informed by the minute!
plenty of disk space (GBs) - datafiles small MB
mysql show variables like '%inno%';
+-++
| Variable_name | Value |
+-++
| have_innodb | YES|
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 268435456 |
| innodb_checksums| ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500|
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir||
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown| 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method ||
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout| 50 |
| innodb_locks_unsafe_for_binlog | OFF|
| innodb_log_arch_dir ||
| innodb_log_archive | OFF|
| innodb_log_buffer_size | 1048576|
| innodb_log_file_size| 5242880|
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag| 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300|
| innodb_rollback_on_timeout | OFF|
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 1 |
+-++
36 rows in set (0.00 sec)
From: Johan De Meersman vegiv...@tuxera.be
To: Sydney Puente sydneypue...@yahoo.com
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 12:58:07
Subject: Re: source backup.sql - troubleshoot
A-ha ! :-)
That's an InnoDB table, allright. Can you check if your InnoDB file is set to
autoextend ? It smells like it's full and waiting for more space or something.
Either look in your my.cnf file, or do a show variables like '%inno%';. Check
the filesize of your InnoDB datafiles, too.
On Wed, May 26, 2010 at 1:27 PM, Sydney Puente sydneypue...@yahoo.com wrote:
Thanks Johan,
Ah I see.
So this line for example:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
generates the first
Query OK, 0 rows affected (0.00 sec)
I did not realise, it looks like a comment.
Not sure about disk activity - top says 100% on 1 of the 4 CPUS
Seems to be this causing problems
DROP TABLE IF EXISTS `ping`;
CREATE TABLE `ping` (
`TestName` varchar(50) default '',
`TimeStamp` int(11) default '0',
`Elapsedtime` int(11) default '0',
`Fail` int(11) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `ping`
--
LOCK TABLES `ping` WRITE;
/*!4 ALTER TABLE `ping` DISABLE KEYS */;
I think it is this causing problems
INSERT INTO `ping` VALUES
('Test1',1258368123,1,0),('Test1',1258368134,1,0),('Test1',1258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169,1,0)...
...
TIA
Syd
From: Johan De Meersman vegiv...@tuxera.be
To: Sydney Puente sydneypue...@yahoo.com
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 11:35:22
Subject: Re: source backup.sql - troubleshoot
The use event statement is the one that results in the database changed
message. You can easily count the result messages after that to find out
which statement is hanging.
It seems very strange to me that the import would just hang, and not exit
with an error. Is there any disk activity ongoing ? Aren't your disks (or
your innodb tablespace) full ? Is there anything in the systemlogs that might
be relevant ?
On Wed, May 26, 2010 at 12:18 PM, Sydney Puente sydneypue...@yahoo.com
wrote:
Hello,
The invocation syntax is OK I think.
yes backup.sql was just a generic name. I think the sql file could be
called anything.
And there is a use event
The
Re: source backup.sql - troubleshoot
Hmm, probably not that, then. Strange.
You already said there's 100% cpu on one core while that executes. Can you
see if there's disk activity going on ?
After you kill the script, can you check the contents of that table, to see
if any data from the hanging statement is in there ? Can you try to run the
statement by hand, to see if it executes ?
I have to admit I'm a bit lost, here. Whenever I see a MySQL server hanging,
it tends to be because it's waiting for me to free up space so it can
continue writing logs or whatever.
On Wed, May 26, 2010 at 3:11 PM, Sydney Puente sydneypue...@yahoo.comwrote:
Thanks.
Getting better informed by the minute!
plenty of disk space (GBs) - datafiles small MB
mysql show variables like '%inno%';
+-++
| Variable_name | Value |
+-++
| have_innodb | YES|
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 268435456 |
| innodb_checksums| ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500|
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir||
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown| 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method ||
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout| 50 |
| innodb_locks_unsafe_for_binlog | OFF|
| innodb_log_arch_dir ||
| innodb_log_archive | OFF|
| innodb_log_buffer_size | 1048576|
| innodb_log_file_size| 5242880|
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag| 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300|
| innodb_rollback_on_timeout | OFF|
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 1 |
+-++
36 rows in set (0.00 sec)
From: Johan De Meersman vegiv...@tuxera.be
To: Sydney Puente sydneypue...@yahoo.com
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 12:58:07
Subject: Re: source backup.sql - troubleshoot
A-ha ! :-)
That's an InnoDB table, allright. Can you check if your InnoDB file is set
to autoextend ? It smells like it's full and waiting for more space or
something.
Either look in your my.cnf file, or do a show variables like '%inno%';.
Check the filesize of your InnoDB datafiles, too.
On Wed, May 26, 2010 at 1:27 PM, Sydney Puente sydneypue...@yahoo.com
wrote:
Thanks Johan,
Ah I see.
So this line for example:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
generates the first
Query OK, 0 rows affected (0.00 sec)
I did not realise, it looks like a comment.
Not sure about disk activity - top says 100% on 1 of the 4 CPUS
Seems to be this causing problems
DROP TABLE IF EXISTS `ping`;
CREATE TABLE `ping` (
`TestName` varchar(50) default '',
`TimeStamp` int(11) default '0',
`Elapsedtime` int(11) default '0',
`Fail` int(11) default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `ping`
--
LOCK TABLES `ping` WRITE;
/*!4 ALTER TABLE `ping` DISABLE KEYS */;
I think it is this causing problems
INSERT INTO `ping` VALUES
('Test1',1258368123,1,0),('Test1',1258368134,1,0),('Test1',1258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169,1,0)...
...
TIA
Syd
From: Johan De Meersman vegiv...@tuxera.be
To: Sydney Puente sydneypue...@yahoo.com
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 11:35:22
Impossible Out Param Return Value
Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Procedure to select if the row exists. I expect the test of this SP to return null, but it returns a value! The value is always returned if the 'path' matches, regardless of the other values being tested. If the 'path' does not match, it returns NULL as expected. Has anyone seen this before? Thanks, Kevin -- Full explanation below: -- Table 'file_detail' with 1 row: id_file_detail, id_file, id_machine, id_user, path 1 , 1 , 1 , 1 , C:\Program Files\BlueZone -- Stored Procedure to see if row exists: -- DROP PROCEDURE IF EXISTS `find_file_detail`$$ CREATE PROCEDURE `find_file_detail` ( IN id_file int(11), IN id_machine int(11), IN id_user int(11), IN filePath varchar(255), OUT keyOut int(11) ) BEGIN SELECT `id_file_detail` INTO keyOut FROM `file_detail` WHERE (`id_file` = id_file AND `id_machine` = id_machine AND `id_user` = id_user AND `path` = filePath) LIMIT 1; END$$ -- SQL used to test the stored procedure: -- SET @keyOut = NULL; CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut); SELECT @keyOut; -- Notice there is 1 row, the values of 99 do not exist, but the path does exist. This test will return @keyOut = 1. How is this possible? If I change the path string to use 'BlueZone1' (so the paths do not match), then this test will return @keyOut = NULL as expected. It seems as though the only thing getting matched is the path and the other 3 values are being ignored. Does this make sense to anyone? Kevin Baynes Senior Software Developer Rocket Software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Impossible Out Param Return Value
The problem occurs because the parameter names are the same as the column names. I'm not sure why, since the columns are specified in quotes, but it does. Thanks, Kevin -Original Message- From: Kevin Baynes [mailto:kbay...@bluezonesoftware.com] Sent: Wednesday, May 26, 2010 10:07 AM To: mysql@lists.mysql.com Subject: Impossible Out Param Return Value Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Procedure to select if the row exists. I expect the test of this SP to return null, but it returns a value! The value is always returned if the 'path' matches, regardless of the other values being tested. If the 'path' does not match, it returns NULL as expected. Has anyone seen this before? Thanks, Kevin -- Full explanation below: -- Table 'file_detail' with 1 row: id_file_detail, id_file, id_machine, id_user, path 1 , 1 , 1 , 1 , C:\Program Files\BlueZone -- Stored Procedure to see if row exists: -- DROP PROCEDURE IF EXISTS `find_file_detail`$$ CREATE PROCEDURE `find_file_detail` ( IN id_file int(11), IN id_machine int(11), IN id_user int(11), IN filePath varchar(255), OUT keyOut int(11) ) BEGIN SELECT `id_file_detail` INTO keyOut FROM `file_detail` WHERE (`id_file` = id_file AND `id_machine` = id_machine AND `id_user` = id_user AND `path` = filePath) LIMIT 1; END$$ -- SQL used to test the stored procedure: -- SET @keyOut = NULL; CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut); SELECT @keyOut; -- Notice there is 1 row, the values of 99 do not exist, but the path does exist. This test will return @keyOut = 1. How is this possible? If I change the path string to use 'BlueZone1' (so the paths do not match), then this test will return @keyOut = NULL as expected. It seems as though the only thing getting matched is the path and the other 3 values are being ignored. Does this make sense to anyone? Kevin Baynes Senior Software Developer Rocket Software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kbay...@seagullsoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Impossible Out Param Return Value
Kevin WHERE (`id_file` = id_file AND `id_machine` = id_machine AND `id_user` = id_user How is MySQL supposed to distinguish `id_file` from id_file c!? Name params diffferently from columns. PB - On 5/26/2010 9:07 AM, Kevin Baynes wrote: Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Procedure to select if the row exists. I expect the test of this SP to return null, but it returns a value! The value is always returned if the 'path' matches, regardless of the other values being tested. If the 'path' does not match, it returns NULL as expected. Has anyone seen this before? Thanks, Kevin -- Full explanation below: -- Table 'file_detail' with 1 row: id_file_detail, id_file, id_machine, id_user, path 1 , 1 , 1 , 1 , C:\Program Files\BlueZone -- Stored Procedure to see if row exists: -- DROP PROCEDURE IF EXISTS `find_file_detail`$$ CREATE PROCEDURE `find_file_detail` ( IN id_file int(11), IN id_machine int(11), IN id_user int(11), IN filePath varchar(255), OUT keyOut int(11) ) BEGIN SELECT `id_file_detail` INTO keyOut FROM `file_detail` WHERE (`id_file` = id_file AND `id_machine` = id_machine AND `id_user` = id_user AND `path` = filePath) LIMIT 1; END$$ -- SQL used to test the stored procedure: -- SET @keyOut = NULL; CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut); SELECT @keyOut; -- Notice there is 1 row, the values of 99 do not exist, but the path does exist. This test will return @keyOut = 1. How is this possible? If I change the path string to use 'BlueZone1' (so the paths do not match), then this test will return @keyOut = NULL as expected. It seems as though the only thing getting matched is the path and the other 3 values are being ignored. Does this make sense to anyone? Kevin Baynes Senior Software Developer Rocket Software No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database: 271.1.1/2895 - Release Date: 05/25/10 06:26:00
Re: Impossible Out Param Return Value
Kevin, The problem occurs because the parameter names are the same as the column names. I'm not sure why, since the columns are specified in quotes, but it does. Eh? Backticks merely permit use of reserved words as identifiers. PB - On 5/26/2010 9:56 AM, Kevin Baynes wrote: The problem occurs because the parameter names are the same as the column names. I'm not sure why, since the columns are specified in quotes, but it does. Thanks, Kevin -Original Message- From: Kevin Baynes [mailto:kbay...@bluezonesoftware.com] Sent: Wednesday, May 26, 2010 10:07 AM To: mysql@lists.mysql.com Subject: Impossible Out Param Return Value Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Procedure to select if the row exists. I expect the test of this SP to return null, but it returns a value! The value is always returned if the 'path' matches, regardless of the other values being tested. If the 'path' does not match, it returns NULL as expected. Has anyone seen this before? Thanks, Kevin -- Full explanation below: -- Table 'file_detail' with 1 row: id_file_detail, id_file, id_machine, id_user, path 1 , 1 , 1 , 1 , C:\Program Files\BlueZone -- Stored Procedure to see if row exists: -- DROP PROCEDURE IF EXISTS `find_file_detail`$$ CREATE PROCEDURE `find_file_detail` ( IN id_file int(11), IN id_machine int(11), IN id_user int(11), IN filePath varchar(255), OUT keyOut int(11) ) BEGIN SELECT `id_file_detail` INTO keyOut FROM `file_detail` WHERE (`id_file` = id_file AND `id_machine` = id_machine AND `id_user` = id_user AND `path` = filePath) LIMIT 1; END$$ -- SQL used to test the stored procedure: -- SET @keyOut = NULL; CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut); SELECT @keyOut; -- Notice there is 1 row, the values of 99 do not exist, but the path does exist. This test will return @keyOut = 1. How is this possible? If I change the path string to use 'BlueZone1' (so the paths do not match), then this test will return @keyOut = NULL as expected. It seems as though the only thing getting matched is the path and the other 3 values are being ignored. Does this make sense to anyone? Kevin Baynes Senior Software Developer Rocket Software No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database: 271.1.1/2897 - Release Date: 05/26/10 06:25:00
Slow query using string functions
I have a pretty simple query that seems to take a lot longer than it ought to (over 2 minutes). Table `feed_new` has a single VARCHAR(255) column, `new_title`, that is an index. Table `prod` has many fields: `prod_title` and `pub_id` are both indexes (VARCHAR). `feed_new` has 895 records, `prod` has 110432. SELECT feed_new.new_title AS `New Title FROM Feed`, prod.prod_pub_prod_id AS `Lib Code FROM DB`, prod.prod_title AS `Title FROM DB`, prod.prod_num AS `Prod Num`, prod.prod_published AS `Published FROM DB` FROM feed_new JOIN prod ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) = LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5) WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0 ORDER BY feed_new.new_title; *** 1. row *** id: 1 select_type: SIMPLE table: feed_new type: index possible_keys: NULL key: new_title key_len: 768 ref: NULL rows: 1 Extra: Using index *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 9816 Extra: Using where The query is doing a scan of the 9816 records that have pub_id = @PUBID, but even so this seems like a long time. Are the built-in string functions really that slow? I suspect it would be faster if I built separate tables that had just the shortened versions of the titles, but I wouldn't think that would be necessary. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
with query expansion search appends whole rows?
Hello everyone, I am conducting some text retrieval experiments with MySQL 5.5.3. I have a question regarding WITH QUERY EXPANSION search mode. In this presentation [1] I learned that it applies blind relevance feedback. It runs natural language mode query twice and add top 20 results (controlled by ft_query_expansion_limit variable default 20) to original query. Manual says: concatenated with the few most highly relevant documents And my question is: Does it append/concatenate whole rows to the original query? so that second query becomes very long (original query + 20 rows)? Or it selects some important words inside rows? If someone reply this I will be very happy. I am asking this because I tried to verify this as follows: I set ft_query_expansion_limit to 1. Execute natural language query and get first result. Append first row to initial/original query manually. And then re-query this long query using natural language mode. When i compared this results with (initial/original query with query expansion mode), the results were quite different. I also tried to ignore initial query so that i queried first document, results were again different. I am simply trying to reproducing the same results of With Query Expansion using/querying natural language mode query twice. Than you for your consideration. [1] http://forge.mysql.com/w/images/c/c5/Fulltext.pdf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow query using string functions
Jerry, Are you sure this is really your explain plan for this query? That's not at all what I would expect to see. Regards, Gavin Towey -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Wednesday, May 26, 2010 2:14 PM To: mysql@lists.mysql.com Subject: Slow query using string functions I have a pretty simple query that seems to take a lot longer than it ought to (over 2 minutes). Table `feed_new` has a single VARCHAR(255) column, `new_title`, that is an index. Table `prod` has many fields: `prod_title` and `pub_id` are both indexes (VARCHAR). `feed_new` has 895 records, `prod` has 110432. SELECT feed_new.new_title AS `New Title FROM Feed`, prod.prod_pub_prod_id AS `Lib Code FROM DB`, prod.prod_title AS `Title FROM DB`, prod.prod_num AS `Prod Num`, prod.prod_published AS `Published FROM DB` FROM feed_new JOIN prod ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) = LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5) WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0 ORDER BY feed_new.new_title; *** 1. row *** id: 1 select_type: SIMPLE table: feed_new type: index possible_keys: NULL key: new_title key_len: 768 ref: NULL rows: 1 Extra: Using index *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 9816 Extra: Using where The query is doing a scan of the 9816 records that have pub_id = @PUBID, but even so this seems like a long time. Are the built-in string functions really that slow? I suspect it would be faster if I built separate tables that had just the shortened versions of the titles, but I wouldn't think that would be necessary. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
slave-net-timeout
Hi, According to the manual, the slave-net-timeout specifies how long time to wait for data from the master before considering the connection dead. From my experience this actually means that the slave will reconnect if it hasn't received a full event in that period of time. E.g. setting slave_net_timeout to 180 seconds, then an event that takes 240 seconds to transfer will never make it through. The replication will make it 75% through and then start all over. Does anyone know whether that is a bug in the implementation, or whether it is the documentation that could be more clear? Thanks, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
