Solved

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

Posted on 2006-07-04
24
338 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
ID: 17040061
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
ID: 17040121
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
ID: 17040410

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
ID: 17040510
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
ID: 17040536
skip-name-resolve did not help, but thanks anyway
0
 
LVL 1

Author Comment

by:Dr_Snapid
ID: 17040572
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
ID: 17040601

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

0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 17043109
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
ID: 17047095
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
ID: 17047514

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
ID: 17047577
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
ID: 17047613

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:Dr_Snapid
ID: 17047673
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
ID: 17047699

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
ID: 17047719
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
ID: 17047725
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
ID: 17047728
Upping the points, you guys have been great
0
 
LVL 14

Expert Comment

by:ppfoong
ID: 17047798

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
ID: 17047819
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
ID: 17047851

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
ID: 17055348
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
ID: 17055565
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
ID: 17055647
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
ID: 17055822
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Best way to find a random number in a list of a million numbers 11 45
simple mysql statement 3 32
RDBMS and No sql database 4 46
updating the date data 12 25
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

896 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

13 Experts available now in Live!

Get 1:1 Help Now