[Greylist-users] Database performance

[Wayne Walker]

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

wwalker at bybent.com                    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:  wwalker at jabber.gnumber.com   AIM:     lwwalkerbybent
IRC:     wwalker on freenode.net
-------------- next part --------------
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.