2 added 187 characters in body
source | link

If you mainly have MyISAM tables, you should increase the bulk insert buffer. Here is what the MySQL Documentation says on setting bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

There are two things you need to do

1) Add it to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

2) Set the global value for it

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

If you do not have the privilege to set bulk_insert_buffer_size globally, then do this

service mysql restart

Of course, this is not for InnoDB.

From another angle, whether the tables are InnoDB or MyISAM, if the indexes are larger that than the table, you may have too many indexes. I usually guestimate that a reload of a MyISAM mysqldump should take 3 times as long as the mysqldump took to make. I also guestimate that a reload of a InnoDB mysqldump should take 4 times as long as the mysqldump took to make.

If you are exceeding the 4:1 ratio for reloading a mysqldump, you definitely have one of two problems:

  • too many indexes
  • indexes just too large due to large columns

You can measure the size of your data by storage engine with this:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

See if the indexes are alsmost as big as the data or even bigger

You may also consider disabling binary logging like this:

echo "SET SQL_LOG_BIN=0;" > footable.sql
mysqldump --databases foo >> footable.sql

before reloading the script

If you mainly have MyISAM tables, you should increase the bulk insert buffer. Here is what the MySQL Documentation says on setting bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

There are two things you need to do

1) Add it to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

2) Set the global value for it

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

If you do not have the privilege to set bulk_insert_buffer_size globally, then do this

service mysql restart

Of course, this is not for InnoDB.

From another angle, whether the tables are InnoDB or MyISAM, if the indexes are larger that than the table, you may have too many indexes. I usually guestimate that a reload of a MyISAM mysqldump should take 3 times as long as the mysqldump took to make. I also guestimate that a reload of a InnoDB mysqldump should take 4 times as long as the mysqldump took to make.

If you are exceeding the 4:1 ratio for reloading a mysqldump, you definitely have one of two problems:

  • too many indexes
  • indexes just too large due to large columns

If you mainly have MyISAM tables, you should increase the bulk insert buffer. Here is what the MySQL Documentation says on setting bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

There are two things you need to do

1) Add it to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

2) Set the global value for it

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

If you do not have the privilege to set bulk_insert_buffer_size globally, then do this

service mysql restart

Of course, this is not for InnoDB.

From another angle, whether the tables are InnoDB or MyISAM, if the indexes are larger that than the table, you may have too many indexes. I usually guestimate that a reload of a MyISAM mysqldump should take 3 times as long as the mysqldump took to make. I also guestimate that a reload of a InnoDB mysqldump should take 4 times as long as the mysqldump took to make.

If you are exceeding the 4:1 ratio for reloading a mysqldump, you definitely have one of two problems:

  • too many indexes
  • indexes just too large due to large columns

You can measure the size of your data by storage engine with this:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

See if the indexes are alsmost as big as the data or even bigger

You may also consider disabling binary logging like this:

echo "SET SQL_LOG_BIN=0;" > footable.sql
mysqldump --databases foo >> footable.sql

before reloading the script

1
source | link

If you mainly have MyISAM tables, you should increase the bulk insert buffer. Here is what the MySQL Documentation says on setting bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

There are two things you need to do

1) Add it to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

2) Set the global value for it

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

If you do not have the privilege to set bulk_insert_buffer_size globally, then do this

service mysql restart

Of course, this is not for InnoDB.

From another angle, whether the tables are InnoDB or MyISAM, if the indexes are larger that than the table, you may have too many indexes. I usually guestimate that a reload of a MyISAM mysqldump should take 3 times as long as the mysqldump took to make. I also guestimate that a reload of a InnoDB mysqldump should take 4 times as long as the mysqldump took to make.

If you are exceeding the 4:1 ratio for reloading a mysqldump, you definitely have one of two problems:

  • too many indexes
  • indexes just too large due to large columns