krispykreme Posted January 17, 2008 Share Posted January 17, 2008 Not sure if these are related but I have a messaging system for my users which is done to the following... mysql_query("INSERT INTO message (src,dest,msg,time,inbox,crew,title,date) VALUES ('$id','$msgtoid','$msg','$time','inbox','$crw[0]','$title','$date');"); $event="You have recieved a message from <a href=profile.php?pid=$user>$pmp[0]</a>. The subject of the message is <a href=messages.php>$title</a>."; mysql_query("INSERT INTO newsfeed (userid,event,time) VALUES ('$id','$event','$time');"); sometimes the insert into message doesnt work it seems but the message title and such will show up in the news feed also my tables constantly have a *overhead* that appears like every 12 hours and stays like that till i *optimize* the table, right now a smaller table has a over head and is seen below... Space usage Type Usage Data 107,433 B Index 30,720 B Overhead 4,023 B Effective 134,130 B Total 138,153 B Optimize table is overhead bad? I cant find much documentation about it and would having a overhead make it have spotty inserts into tables? also if my database is very query intensive and information is constantly changing in some tables should i not use MyISAM? Thanks for your time as I'm very confused, the database is live and gets 300k+ page views and each page view has at least 2 query statements involved Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/ Share on other sites More sharing options...
fenway Posted January 17, 2008 Share Posted January 17, 2008 Overhead in the result of "holes" from deleting records. As for your inserts, they can't just fail and not report and error... unless you're not checking. Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-441752 Share on other sites More sharing options...
krispykreme Posted January 17, 2008 Author Share Posted January 17, 2008 thanks for the reply i personally dont know if there was an error since it has never happend to me but i guess i can turn on the error logs and that would keep track? Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-441910 Share on other sites More sharing options...
fenway Posted January 17, 2008 Share Posted January 17, 2008 You keep turn on logging, of course... but your script can also handle this. Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-441914 Share on other sites More sharing options...
krispykreme Posted January 17, 2008 Author Share Posted January 17, 2008 i cant seem to find a log or how to turn it on i can show errors in the page, but that doesnt matter since it NEVER happens when i send a message so id never see the error Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-442167 Share on other sites More sharing options...
krispykreme Posted January 17, 2008 Author Share Posted January 17, 2008 also some more information not sure if this will help... This MySQL server has been running for 9 days, 2 hours, 4 minutes and 31 seconds. It started up on Jan 08, 2008 at 03:10 PM. Query statistics: Since its startup, 298,689,482 queries have been sent to the server. Total ø per hour ø per minute ø per second 299 M 1.37 M 22.83 k 380.46 The following are all RED numbers that i found in phpmyadmin in *status* Slow_queries 4 Handler_read_rnd 15 M Handler_read_rnd_next 276 G Slow_launch_threads 2 Created_tmp_disk_tables 6 Sort_merge_passes 18 k Opened_tables 30 k Table_locks_waited 11 M those are the ones in red, i think red is bad but who knows lol Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-442210 Share on other sites More sharing options...
fenway Posted January 18, 2008 Share Posted January 18, 2008 i cant seem to find a log or how to turn it on i can show errors in the page, but that doesnt matter since it NEVER happens when i send a message so id never see the error So when does it happen? Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-442790 Share on other sites More sharing options...
krispykreme Posted January 18, 2008 Author Share Posted January 18, 2008 it happens to my users sometimes i guess for instance the message will show up in the news feed but not in the messages i search the messages table and it doesnt exist, but the newsfeed inserted it fine Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-442912 Share on other sites More sharing options...
fenway Posted January 18, 2008 Share Posted January 18, 2008 How are messages / newfeeds related? Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-442995 Share on other sites More sharing options...
krispykreme Posted January 18, 2008 Author Share Posted January 18, 2008 mysql_query("INSERT INTO message (src,dest,msg,time,inbox,crew,title,date) VALUES ('$id','$msgtoid','$msg','$time','inbox','$crw[0]','$title','$date');"); $event="You have recieved a message"; mysql_query("INSERT INTO newsfeed (userid,event,time) VALUES ('$id','$event','$time');"); they are both triggered one after another seeing how newsfeed insert is after the message insert so if a newsfeed insert was instructed, a message insert should of happend right before that, like i said before all the messages i send work and insert, but some people in the database seem to have issues Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-443005 Share on other sites More sharing options...
fenway Posted January 18, 2008 Share Posted January 18, 2008 I would add a custom error handler, log all of the info, watch it for a while... this shouldn't happen without a proper DB warning. Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-443020 Share on other sites More sharing options...
krispykreme Posted January 19, 2008 Author Share Posted January 19, 2008 i think i found it while messing around whenever someone put a ' in the message it would stop it because i wasnt doing $title = mysql_real_escape_string($title); $msg = mysql_real_escape_string($msg); however i put that in there and i can now put the ' things and it goes through with that statement always something simple ; P Quote Link to comment https://forums.phpfreaks.com/topic/86398-solved-overhead-insert-not-always-inserting/#findComment-443710 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.