Many inserts in a row - why is the occasional one really slow?

I am inserting many records into a mysql table

PLease note I am using
INSERT INTO table SET colname='data', colname2='data2'....
and inserting one at a time

as opposed to
INSERT INTO table (colname1,colname2,colname3) Values (data1,data2,data3)
and inserting several records at a time

This is because I need a confirmation of successful entry for each record.

My script inserts anywhere up to 250 records at a time, and is uploading records from my WINXP/apache/php/mysql setup to a linux/apache/php/mysql webserver, using my 1500K ADSL connection.

Of the 250 records, 98% of them insert in under 0.2 seconds. But a few records take anywhere up to 2 mins to insert... all records are the same length, so I do not know why this is happening.

My PHP upload script takes up to 6 mins to execute instead of about 20 seconds... and this happens roughly 1 record in 50. This was not happening until today - no dramas for over 12 months.

Any ideas?

I tried a check table - it's OK.

There are a few indexes on the table but since most records go fast I doubt the indexes are the cause.
LVL 1
Dr_SnapidAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
skeep1Connect With a Mentor Commented:
Hi,

Is this the only script you are running on this database? You could be tying up processor resources by some other means. Figure out how many seconds there between the delayed records and see if any other processes are running at that interval.

Hope this helps.
0
 
Raynard7Commented:
as most of the records go in fast and then there are some that are slow I think that the indexes are the problem;

I say this because if the indexes are working properly they are created with blank room in the binary tree for records to be inserted without having to completely re-write the index; if there is insufficent room the whole index needs to be rebuilt,

If you try

alter table `tablex` DISABLE KEYS

as your first statement and then

alter table `tablex` ENABLE KEYS

as your last statement this could help.

Also - is there a priority setting on the db for selects over inserts? as this could effect the efficiency of the insert.

Also - are there other users using the system at the same time? they could be using the resources.

Also - have you optimized the table recently; the indexes and order in the file could be out and a simple optimize on the table could fix it.
0
 
Dr_SnapidAuthor Commented:
There are no priority settings enabled.

Yes there are other processes reading from the table as well. There are never any updates though - only the inserts made by this script and some reads made by some reporting scripts.

No I have not optimized recently. I was thinking this would make little difference since there is never any 'changes' made to the table - only inserts. The table has 3 million records, and each record is quite long. Table is about 300Mb, the indexes are about 150Mb. I would not want the table to be locked for too long if I can avoid it and suspect an optimize would take quite some time. Do you still think an optimize could help?

If I try what you suggest with disabling keys while I insert, would the reads be affected while the keys are not enabled?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ppfoongCommented:

It might caused by the mysql server trying to find the hostname of your client, and your client only has IP address but no hostname. The server will wait until resolve timeout before continue.

Try add this line into [mysqld] section of the my.cnf file, and restart the mysql daemon.


skip-name-resolve

0
 
Dr_SnapidAuthor Commented:
ppfoong: Would you expect this to have an effect now when it hasnt in the past? This is definately a new problem and I have not changed my.cnf.

I may still give it a try but I am not sure hhow it could have not been an issue before now if this is the cause.
0
 
Dr_SnapidAuthor Commented:
skip-name-resolve did not help, but thanks anyway
0
 
Dr_SnapidAuthor Commented:
Looks like skip-name-resolve actually did help a separate issue, so thanks ppfoong! Now when I connect with mysql administrator, it connects much faster.

I have also tried optimize. Took about 5 mins. Seemed to speed things up as a rule, however I still get the same every 50th odd record taking a long time. Now the delay is only about 25 seconds though - I suspect the name resolve was indeed happening during this delay period and now it isnt, shortening the delay.

The optimise just helped speed up the database.

Neither is the true cause of the problem, but I like the progress...

0
 
ppfoongCommented:

Hmm... check the amount of memory used. Are your server harddisk swapping?

0
 
todd_farmerCommented:
What type of table is this (InnoDB, MyISAM, etc.)?  Have you tried to lock the tables while updating?
0
 
Dr_SnapidAuthor Commented:
Tables are MyISAM. Cannot lock the table because it needs to be read from at any time.
Not sure about the memory being used - it is a linux server and swap file is managed automatically by the OS as far as I know.
0
 
ppfoongCommented:

If the server is using swap, occasional lag can happen during the swapping/paging process.

You can check for the memory status using the "free" command or the "procinfo" command.

0
 
Dr_SnapidAuthor Commented:
When I run 'free' it tells me this about the swap:

MEMORY
Total 1002452, Used 988008, Free 14444

SWAP
Total 4980004, Used 108, Free 497896

That seems good to me... but it is clearly using swap even if only a small amount.

If this is potentially a problem, should I try and reduce memory usage somehow?
0
 
ppfoongCommented:

Seems like not a memory problem either.

If you have phpmyadmin, go to the "Show MySQL runtime information" page, it is also known as "Status" on the top menu. Look for any red figures and advice from phpmyadmin. Might need to do some more tuning to the config.

0
 
Dr_SnapidAuthor Commented:
We dont use PHPmyadmin... but i can still see the processlist using mysql control centre.

Many processes have high values. Some processes have up to 1200 seconds process time... that seemed bad to me at first till i realised the command column says sleep. They go up and up till that thread executes another query.

So no major dramas there that i can see.

There are about 35 threads connected although only 1 or 2 running at a time. No threads cached.

Damn. Still no closer.
0
 
ppfoongCommented:

I would suggest you to download phpmyadmin and run the above for diagnose.

Alternatively, you can download Mysql Administrator from Mysql website, and run it from your Windows PC. Inside the Health section of Mysql Administrator, you can see almost the same thing as in phpmyadmin's "Show MySQL runtime information".

0
 
Dr_SnapidAuthor Commented:
I have Mysql Administrator.

In the health tab it shows:
Connection usage: about 14% avg
traffic: 60,000 avg, peaking at 1,671,080 kb
number of queries: 8 avg, 142 max (i think this is per second)

Memory health is saying 100% hitrate alot, average is 75%. This looks like a potential culprit... but i am not sure how I would reduce this.
0
 
Dr_SnapidAuthor Commented:
Sorry, th memory health value stated above is under 'key efficiency' heading. I am not sure what that means...
0
 
Dr_SnapidAuthor Commented:
Upping the points, you guys have been great
0
 
ppfoongCommented:

Is there any delay problem, under Status Variables > Performance > Delayed.

Also, if your table is innodb, make sure you have a large enough innodb_log_file_size in the my.cnf.

If your server has more than one processor, or using hyperthreading, try adding "skip-concurrent-insert" in the my.cnf too.

0
 
Dr_SnapidAuthor Commented:
Do delays at all.
Not innoDB, it is MyISAM.
Single processor no hyperthreading...
0
 
ppfoongConnect With a Mentor Commented:

For your 1Gb memory and MyISAM, try these tuning on top of existing setting:

[mysqld]
skip-locking
skip-bdb
key_buffer = 256M
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
table_cache = 256
thread_cache = 8
query_cache_size= 16M
max_connections = 256

0
 
Dr_SnapidAuthor Commented:
Still no luck. This is frustrating the hell out of me. I am going to try inserting into a new table, to see if the problem lies with the table.
0
 
Dr_SnapidAuthor Commented:
Wait a minute - the server says there are no slow queries... could this mean the actual query packet is being delayed before mysql even receives it?
0
 
Dr_SnapidAuthor Commented:
You legends! Thanks skeep1! Turns out another process ran at the same time the delays happened. When i stopped the process, all went fine. The process was tying up mysql.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.