WampServer

I've been running MAMP on a mac for years - & still do. Recently I have bought a new i7 laptop & have installed Wampserver to do dev work on the move.

I have configured Wampserver & have it running nicely, i've changed it to run on port 8888 so projects are compatible with MAMP (helps me transfer projects between systems).

I'm currently working with a large amount of data that needs importing into a database. It seems that Wampserver will take days to process one single file - whereas I managed to get the old dual core Mac to run the same import in about 1.5hrs.
It is also just as slow trying to import an SQL file in phpmyadmin that has been exported from the mac.

Something seems to be slowing down the mysql queries drastically on my wampserver install.

I'm running windows 8, have Avast antivirus installed & i'm using the microsoft firewall.
I have added exceptions for filesystem to the wamp folder and localhost:8888 in the webshield in Avast
The firewall is a little over my head to be honest - I can see the wamp port 8888 is allowed.
I have tried turning off both the antivirus & the firewall & it doesn't seem to make any difference.

I have some small websites running locally as well - these just use php with no mysql & they seem to run perfectly with no lag...it seems that when mysql is involved, things slow down.

I have seen mentions of using fastCGI - do I need to try this?
Anything else I can try to improve the speed?

So how are you importing the data?

That isn't the issue.

The data is provided as large text files - i'm processing one file at a time running through it line by line & inserting the results into a table using PHP. The largest file has around 4 million entries.

Processing this single large file in Wampserver as it stands is going to take a LOT of time - I think maybe 2-3 days.
My old mac ran the same script in 1.5hrs.

If I export the successfully imported table/database from the mac using phpmyadmin & then try to import this using phpmyadmin on Wampserver, it is still going to take days to import the data.


There is an issue with the way mysql is connecting/performing.

MYISAM or INNODB? Either way check the MySQL config against what you have on your MAC.

By just fiddling with the buffering loading 4milloin+ records I managed to reduce a runtime of > 1 day down to 45 minutes using a innodb database.

You may need to get hold of some monitoring tools to see where the bottleneck is.

The table in question is INNODB

Are you able to provide a little more info about tweaking the buffering?

Any suggestions for monitoring tools as well....sorry this is a whole new area for me.

I cannot for the life of me remember what I used to monitor mysql persormance.

I did find this page a useful start point for what parameters to play with [www.ibm.com]

I just spent an hour looking for the browser based tool I used and cannot find it anywhere, sorry.

windows 8 is the issue...

you must run mysql on 127.0.0.1 on windows 8

any reference to localhost for mysql on windows 8 is a disaster in terms of speed.

for example if you have

mysql_connect("localhost","user",pW";

compared to

mysql_connect("127.0.0.1","user",pW";

you could be looking at 1.5sec in speed between theses just for the connection.


The issue is to do with sockets and ipv4/ipv6


if your only using phpmyadmin , you can edit to config.inc.php and change the second reference of localhost to 127.0.0.1 for instant speed boost!!

Steven Martin
stevenmartin99@gmail.com
stevenmartin99@hotmail.com
PampServer.com - [pampserver.com]



Edited 2 time(s). Last edit at 03/15/2013 06:12PM by stevenmartin99.

Steven - you are an absolute legend....as you say, instant speed boost - fantastic.

I haven't tried the large file yet - but I can tell that its all running loads faster already!

Ok - i'm updating this in case it helps anyone else in the future.

Changing mysql to connect to 127.0.0.1 instead of localhost makes a big difference - very noticeable on the front end of websites, response time seems immediate - very good.

This morning I tried some test imports on a file with around 300 data entries.
Initial time time to complete the import was 48+secs.
From there I have played with the different .ini files that come with mysql - some of them improved the time, some made it worse.
The small file took the time down to ~25secs...a huge improvement.
I then played a little with the settings - I took max_allowed_packet down to 256K & this has dropped the time again down to ~20secs.

I still think it could be a bit quicker - i'll have a look at the Mac mysql config later to see if there's any major differences.

Thanks again for the input above - the difference is HUGE!

down vote
favorite
This query doesn't complete in a reasonable amount of time:

mysql> select * from prices where symbol='GOOG' and date in
(select max(date) from prices where symbol='GOOG' and yearweek(date) > 201001
group by yearweek(date));
'prices' is keyed off id, and has a secondary unique index of (symbol, date):

mysql> show index from prices;
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| prices | 0 | PRIMARY | 1 | id | A | 468915 | NULL | NULL | | BTREE | |
| prices | 0 | SECONDARY_INDEX | 1 | date | A | 10905 | NULL | NULL | YES | BTREE | |
| prices | 0 | SECONDARY_INDEX | 2 | symbol | A | 468915 | NULL | NULL | YES | BTREE | |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
From looking at the EXPLAIN output, it seems as though MySql is not using the index on (date, symbol). Does the combination of WHERE symbol='GOOG' and date IN (...)) require a full table scan and not use the secondary unique index?

mysql> EXPLAIN select * from prices where symbol='GOOG' and date in (select max(date) from prices where symbol='GOOG' and yearweek(date) > 201001 group by yearweek(date));
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
| 1 | PRIMARY | prices | ALL | NULL | NULL | NULL | NULL | 468915 | Using where |
| 2 | DEPENDENT SUBQUERY | prices | index | NULL | SECONDARY_INDEX | 17 | NULL | 468915 | Using where; Using index; Using temporary; Using filesort |
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)
www.cusabio.com/ELISA_Kit-84599/ il-6 elisa kit



Edited 4 time(s). Last edit at 03/17/2013 11:53AM by zdy.

Final update:

After playing with more MySQL settings, I managed to improve the speed a little more - nothing quite so significant.
Front end use & general SELECT operations were working fine but multiple INSERTs were still running slow.

I then stumbled across a blog article written by someone trying to increase the speed of UPDATE queries.
He mentioned that when an UPDATE/INSERT query is written, the table has to update its catalogue after the update has been performed - this is an intensive process that can slow things down dramatically.

Simply splitting the updates into transactions of groups of 100 has completely sorted the issue. The query time on the test file of ~300 dropped from 17secs to 0.35secs. A large file with 3.9 million entries now takes just 23mins....problem sorted!

I still don't understand why this wasn't an issue on the Mac...but its sorted now & thats all I c are about.



Edited 1 time(s). Last edit at 03/18/2013 08:49AM by craftygeek.