Solved

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

Posted on 2006-07-04
24
331 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:Dr_Snapid
24 Comments
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 14

Expert Comment

by:ppfoong
Comment Utility

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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
skip-name-resolve did not help, but thanks anyway
0
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 14

Expert Comment

by:ppfoong
Comment Utility

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

0
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
What type of table is this (InnoDB, MyISAM, etc.)?  Have you tried to lock the tables while updating?
0
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 14

Expert Comment

by:ppfoong
Comment Utility

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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 14

Expert Comment

by:ppfoong
Comment Utility

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 14

Expert Comment

by:ppfoong
Comment Utility

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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
Sorry, th memory health value stated above is under 'key efficiency' heading. I am not sure what that means...
0
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
Upping the points, you guys have been great
0
 
LVL 14

Expert Comment

by:ppfoong
Comment Utility

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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
Do delays at all.
Not innoDB, it is MyISAM.
Single processor no hyperthreading...
0
 
LVL 14

Assisted Solution

by:ppfoong
ppfoong earned 200 total points
Comment Utility

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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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
 

Accepted Solution

by:
skeep1 earned 200 total points
Comment Utility
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
 
LVL 1

Author Comment

by:Dr_Snapid
Comment Utility
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now