List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 26 2010 1:35pm
Subject:Re: source backup.sql - troubleshoot
View as plain text  
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 <sydneypuente@stripped>wrote:

> 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       | 10000                  |
> +---------------------------------+------------------------+
> 36 rows in set (0.00 sec)
>
>
>
> ________________________________
> From: Johan De Meersman <vegivamp@stripped>
> To: Sydney Puente <sydneypuente@stripped>
> Cc: a.smith@stripped; mysql@stripped
> 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 <sydneypuente@stripped>
> 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;
> >>/*!40000 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 <vegivamp@stripped>
> >To: Sydney Puente <sydneypuente@stripped>
> >>Cc: a.smith@stripped; mysql@stripped
> >>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
> <sydneypuente@stripped>
> 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 <vegivamp@stripped>
> >>>>To: a.smith@stripped
> >>>>Cc: Sydney Puente <sydneypuente@stripped>;
> mysql@stripped
> >>>>Sent: Wed, 26 May, 2010 10:15:47
> >>>>Subject: Re: source backup.sql - troubleshoot
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>On Wed, May 26, 2010 at 10:54 AM, <a.smith@stripped> 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
> >
> >
> >
> >>
>
>
> --
> 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

Thread
source backup.sql - troubleshootSydney Puente25 May
  • RE: source backup.sql - troubleshootDaevid Vincent26 May
  • Re: source backup.sql - troubleshootRob Wultsch26 May
  • Re: source backup.sql - troubleshoota.smith26 May
    • Re: source backup.sql - troubleshootJohan De Meersman26 May
      • Re: source backup.sql - troubleshoota.smith26 May
        • Re: source backup.sql - troubleshootJohan De Meersman26 May
          • Re: source backup.sql - troubleshootSydney Puente26 May
            • Re: source backup.sql - troubleshootJohan De Meersman26 May
              • Re: source backup.sql - troubleshootSydney Puente26 May
                • Re: source backup.sql - troubleshootJohan De Meersman26 May
                  • Re: source backup.sql - troubleshootSydney Puente26 May
                    • Re: source backup.sql - troubleshootJohan De Meersman26 May
            • Re: source backup.sql - troubleshootDimitre Radoulov26 May