Re: Database performance
Wayne Walker <[email protected]>
| Newsgroups | gmane.mail.spam.greylist.user |
|---|---|
| Message-ID | <[email protected]> |
Attached this time. On Thu, Jan 05, 2006 at 01:16:13PM -0600, Wayne Walker wrote: > Brian, > > I did some experimenting. If instead of : > > REPLACE INTO relayreport SELECT * FROM relaytofrom; > # which for me on 500,000 rows going into 2.5 M rows, takes 3.5 minutes > > We do a little more work: > > ONCE - create a table (temp_archive) idenitcal to relayreport, BUT with NO indexes > > Then in db_maintenance.pl we : > > INSERT INTO temp_archive SELECT * FROM relaytofrom; > # which for me on 500,000 rows take 3.5 SECONDS > > REPLACE INTO relayreport SELECT * FROM temp_archive; > DELETE FROM temp_archive; > > Then the whole process takes about 2% longer, But the relaytofrom table > is only locked for 2% of said runtime instead of 100%. > > I've attached the experimental benchmarking I did. > > Wayne -- Wayne Walker www.unwiredbuyer.com - when you just can't be by the computer [email protected] Do you use Linux?! http://www.bybent.com Get Counted! http://counter.li.org/ Perl - http://www.perl.org/ Perl User Groups - http://www.pm.org/ Jabber: [email protected] AIM: lwwalkerbybent IRC: wwalker on freenode.net
relaydelay_experiment.txt
(text/plain, 4.2 KB)
Table type: MyISAM, Latin1
mysql> select count(*) from relaytofrom;
+----------+
| count(*) |
+----------+
| 515304 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from relayreport
-> ;
+----------+
| count(*) |
+----------+
| 2687720 |
+----------+
1 row in set (0.00 sec)
Ran replace twice to verify consistency (not quite valid since all the
rows are gauranteed dups, but normally most of them are.
mysql> replace into relayreport select * from relaytofrom;
Query OK, 782489 rows affected (2 min 40.34 sec)
Records: 515304 Duplicates: 267185 Warnings: 0
mysql> replace into relayreport select * from relaytofrom;
Query OK, 1030608 rows affected (3 min 42.45 sec)
Records: 515304 Duplicates: 515304 Warnings: 0
mysql> replace into relayreport select * from relaytofrom;
Query OK, 1030608 rows affected (3 min 37.48 sec)
Records: 515304 Duplicates: 515304 Warnings: 0
Definitely takes longer to overwrite than to insert, as expected
Now created a temp_archive table (from the table structure of
relaytofrom).
Will insert into it twice and replace into it twice (deleteing all
recordsin between.
mysql> insert into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (28.82 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql> delete * from temp_archive;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from temp_archive' at line 1
mysql> delete from temp_archive;
Query OK, 515304 rows affected (0.06 sec)
mysql> insert into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (28.12 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql>
mysql> delete from temp_archive;
Query OK, 515304 rows affected (0.06 sec)
mysql> replace into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (28.07 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql> delete from temp_archive;
Query OK, 515304 rows affected (0.07 sec)
mysql> replace into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (28.14 sec)
Records: 515304 Duplicates: 0 Warnings: 0
So, pushing from the temp table into the relayreport table is of course the same speed as above:
mysql> replace into relayreport select * from temp_archive;
Query OK, 1030608 rows affected (3 min 26.00 sec)
Records: 515304 Duplicates: 515304 Warnings: 0
I recreated the temp_archive table with no indexes (save the PRI key
for id) to see if it affected insert time. It certainly did. Down to 7
seconds! That's 30 times faster than the replace into a populated table.
So for the guy who is getting locked up for about 90 minutes, we should
drop that down to two minutes. By generating the temp_archive table and using insert into it (with no indexes).
Wow! cut insert time in half again removing the auto_increment and primary key from the temp_archive table!!!
mysql> CREATE TABLE `temp_archive` ... with only id auto_increment/primary
Query OK, 0 rows affected (0.01 sec)
mysql> insert into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (7.03 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql> delete from temp_archive;
Query OK, 515304 rows affected (0.05 sec)
mysql> insert into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (6.85 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql> delete from temp_archive;
Query OK, 515304 rows affected (0.05 sec)
mysql> replace into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (6.84 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql> drop table temp_archive;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `temp_archive` ... with no indexes
Query OK, 0 rows affected (0.01 sec)
mysql> insert into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (3.56 sec)
Records: 515304 Duplicates: 0 Warnings: 0
mysql> delete from temp_archive;
Query OK, 515304 rows affected (0.04 sec)
mysql> insert into temp_archive select * from relaytofrom;
Query OK, 515304 rows affected (3.52 sec)
Records: 515304 Duplicates: 0 Warnings: 0
This won't change the total run time, but it will make a ~60X reduction in the time that the relaytofrom table is locked.