PHP Mysql Hang?

PHP
1

Hey Sitepoint,

I just discovered these forums. So far I’m very impressed with what I see.

I’ve been curious about this little issue I’ve been having for a long time and I’d like to get some clarification if possible.

I have a Ubuntu server on my local network. I use it for development of various CMS functionality. I write a lot of PHP scripts that get executed via the cron daemon. These scripts often clean up duplicate entries in the table, analyze the database for customer suggestions and things like that.

Well some of these scripts eat up a few hundred MB and can sometimes take 20 minutes to complete (yea I don’t write the best SQL queries ;).

Before these scripts are run via cron I obviously run them myself a few times to ensure they execute as predicted. Now, the issue I’m having is that when I run these scripts manually my server seems to lock up and I am unable to request anymore (from Apache I’m guessing) webpages until the script run has completed.

I have noticed two strange occurrences to this problem. If I run PHPMYADMIN (even in the same browser during a long script execute) it still allows me to query the database, and also if I open a second browser (such as IE) it will also allow me to access the local webserver (during a long script execute).

Can anyone explain to me what the hell is going on? I presume Apache has some kind of limit that prevents 1 singular connection (per session?) from hogging too much of the systems resources? Would that explain why PHPMYADMIN or another browser can run at the same time during a long script execution?

Any tips in the right direction greatly appreciated.

2

Is your PHPMyAdmin install on the same server as the db?

Are the cron’s running as root?

3

PHP is on the same server, same web root, just located at /pma actually.

The cron’s run as root yes.

4

Does your website INSERT / UPDATE data in the tables the cron jobs work on, and are the tables MyISAM?

5

Yes && Yes :rofl:

6

Well, there’s your problem right there :slight_smile:
See, MyISAM uses table locking. This means that when one process is reading (SELECT) from a table, INSERT and UPDATE queries are not allowed to run (other SELECT queries are though, since they don’t modify the data, which is why you can use PHPMyAdmin just fine), and are queued until the reading of the table is done and the lock is released (this is to prevent one process reading a row while another process is updating that same row, which can result in all sorts of mayhem; like only 2 out or 4 fields are updated at the moment you read, etc).
Luckily MySQL also supports another storage engine called InnoDB, which doesn’t have this problem because it uses row level locking instead of table level. So when a process wants to update a row, it locks just that row, not the whole table, and other processes are free to do whatever they wish to all the other rows.
So the solution to your problem is quite simple, just convert the table(s) to InnoDB.
The drawback is though is that MyISAM doesn’t support fulltext index, so if you need that, you’d have to split your table in two tables: 1 MyISAM with the fields that need to be in the fulltext, and one InnoDB table with all the other fields, and connect them via PK/FK.
(needless to say, don’t forget to backup first)

7

You sir, are a wizard :wink:

I don’t even know what Fulltext fields are. Care to elaborate? You aren’t referring to the field_type mediumtext or > are you?

8

If you don’t know what they are, you’re most likely not using them :slight_smile: (no, it’s got nothing to do with mediumtext).

Full indices are described here, and what you can do with them is described [URL=“http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html”]here.

And in case you think “Wow, that sounds good, Imma us that!”, don’t; get Solr instead :slight_smile:

9

I’m not even going to touch anything else for the time being.

I’m having some issues turning the database into innodb. Do I want to change the database to innodb? or just all of the tables in the database? I see some guides on the web saying PMA can adjust them but I don’t see that option in my specific version of PMA and I see others randomly reporting this issue as well.

10

You need to change the individual tables to MySQL; you can’t convert the whole database at once AFAIK.

11

That’s not a problem. I thought the database itself somehow had to be made into innodb.

12

This may not tie in with how it is in the version your using but in the version I’m using, when your viewing the table that you wish to change the storage engine for you click on “Operations”. In the “Table options” part you’ll see a storage engine field with a drop-down menu, select the storage engine that you want to change the table to use then click go

13

Yes, my version has that too! That’s how I adjusted the tables I needed to have as InnoDB.

I was looking for a drop down that would adjust the whole database. But now that I know the database itself isn’t innoDb or MyISAM it’s not a problem.

Next question though, still highly related as well.

Does it matter if I mix MyISAM and InnoDB together? Do I only need my high record tables to be InnoDB?

14

No that doesn’t matter; you can mix and match as you please :slight_smile:
Do take into account their differences though (MyISAM supports full text search, InnoDB doesn’t. InnoDB supports true foreign keys, MyISAM doesn’t. Etc, etc, etc).

15

Aye :smiley: I have and will continue to learn about this amazing piece of open software. Thank you for your help.

16

Have a read of this from the MySQL manual, near the bottom is a table comparing the storage engines and their features (MySQL version 5.1).

17

Yea, unfortunately my knowledge of MySQL itself is extremely limited. Whilst I know how to read/write a database from the PHP perspective my knowledge of the server software itself is very low. I’ve read over at least 10 pages like that explaining the differences between the storage engines but I have no clue what half of the terminology means in it lol.

I may have bit off more than I can chew on this particular project, but hey, that’s how you learn right? It’s just a personal project, so there is no time constraints or client pressure but still, I’d like to get it working for my own integrity’s sake.