Re: [PHP] [JOB OFFER] contractor wanted for PHP project! From: Mark Maggelet (maggelet <email protected>)
Date: 09/28/00

On Tue, 26 Sep 2000 08:23:46 -0700, Adam Powell (adam <email protected>)
wrote:
>Hi there, thanks for the reply.
>
>We originally did start with mySQL last November, and it worked
>great. In
>fact I did not want to move to Oracle at all, however there were
>some issues
>with mySQL that meant we could not use it any more... and if these
>were
>sorted out we would love to change back :)
>
>When I say 300 connections, its 300 connections per web server and
>we have
>around 24 of them... our first problem was that we had problems
>getting
>mySQL to manage more than 1000 connections due to the number of open
>files
>in a 32 bit system. We changed this max from 1024 to 2048, but then
>ran
>into some other linux kernel based problems that gave us really bad
>performance.
>
>The biggest problem however was table locking. We currently do
>around 6500
>sql executions per second at peak, on tables with over 3 million
>rows in,
>and because mySQL uses table locking the entire database would just
>hit a
>deadlock, and that was in February, so that was at around 1,000
>executions
>per second. We had a table called market_stock that would hold all
>the
>items a person had in their shop, then we had to split this into 8
>sub
>tables market_stock_1 to 8. This wasnt the perfect scenario because
>of the
>increased maintenace, and the lack of scalability. We were told
>that Oracle
>handled tables that did UPDATEs and SELECTs at the same times better
>due to
>the fact it used row locking.

Mysql only locks tables when it's inserting or updating, and you can
usually get around it by using 'delayed insert' and 'update low
priority', which wait until no client is reading from the table (but
if you are really getting that kind of traffic, it might not happen
very often). I have Mysql tables with over 3 million rows that never
give me any trouble - tho the traffic is not nearly so heavy as
yours. It's a great database, and you could probably get it to work
for you if you spent the effort of finding the right architecture.
for example, you could do all your inserts and updates to a main db
machine, and do selects on one of several other secondary machines
that sync with the main one at intervals.

It seems to me that the easiest way to solve your problem would be
spending more money on hardware and a really good DBA. Make sure you
have a bunch of the fastest scsi drives in the world and stripe them.

Good luck, this is the highest traffic php site I've heard of, and
it's good to hear that the bottleneck is with Oracle :)

- Mark

>We are currently offloading a lot of the work onto mySQL also, all
>the
>stats, chat boards, and neomessages on the site are handled by two
>dual 700s
>running mysql 3.23. Each page connects to like 4 database machines
>now, its
>getting a bit ridiculous :)
>
>Anyway, so the aim is to build a connection pooling into the PHP
>OCI8 module
>to reduce the overhead generated by all these connections into
>Oracle, so if
>anybody has any idea let us know :)
>
>Thanks,
>Adam Powell,
>Neopets.Com
>
>----- Original Message -----
>From: "Martin Cameron" <cameron <email protected>>
>To: "Adam Powell" <adam <email protected>>; "mysql" <mysql <email protected>>
>Sent: Tuesday, September 26, 2000 1:37 AM
>Subject: Re: [PHP] [JOB OFFER] contractor wanted for PHP project!
>
>
>> Hi Adam
>>
>> You need to give a bit more detail.
>>
>> Is the database application that you are running, dependent on
>"transactions".
>> If not, are you hell-bent on sticking with Oracle.
>>
>> One of the claims to fame of MySQL is its speed. You talk about
>>persistent
>> connections of 300. MySQL comes stock standard set for 100.
>>
>> Before embarking on getting an Oracle programmer on board, I would
>consider
>> whether or not you have the right tools for the job. If the pages
>>you are
>> serving are a regurgitation of data from the database, then you
are
>probably
>> not using the right engine. My query, if I were you, would not be
>>to find
>an
>> Oracle programmer, but to find someone who can deal to your real
>>problem -
>> delivery of data from a database to web pages at the rate
>>demanded. I
>suspect
>> that at the end of the day you will opt for someone who can
>>convert your
>> current database over to MySQL - only because it appears that
>>speed is
>your
>> problem abd MySQL delivers results much faster than any other
>>database
>engine -
>> so I am led to believe.
>>
>> If you want some real direction from this mailing list as to the
>>solution
>to
>> your problem, I suggest that you post a few more details about the
>>data
>being
>> served up, the hardware that you have delivering it, and the
>>supporting
>> programs around it. You will find this list more than
>>accommodating in
>steering
>> you in the right direction.
>>
>> Regards
>> Martin Cameron
>>
>> Adam Powell wrote:
>>
>> > Hi, we are looking for a PHP contractor (possibly not just one)
>>to help
>us
>> > with a current project.
>> >
>> > We have a large oracle database, and we are using OCI8 to
>>connect from
>> > PHPv4.0.0, and our database is dying due to the sheer number of
>connections.
>> > The current OCI module does not have connection pooling, so each
>>of our
>> > apache servers is making 300 persistant connections to Oracle.
>>The site
>in
>> > question does over 50 million pageviews a day, and connections
>>are
>proving
>> > to be a MAJOR bottleneck in our growth.
>> >
>> > Basically, we need people who have experience in this area to
>>help us
>create
>> > a OCI8/apache connection pooling module. If anybody is
>>interested
>please
>> > get in touch. We are located in Glendale, LA, and will pay for
>> > travel/accomodation expenses plus generous compensation.
>> >
>> > Thanks a lot,
>> > Adam Powell,
>> > Neopets.Com
>> >
>> > --
>> > PHP General Mailing List (http://www.php.net/)
>> > To unsubscribe, e-mail: php-general-unsubscribe <email protected>
>> > For additional commands, e-mail: php-general-help <email protected>
>> > To contact the list administrators, e-mail: php-list-
>>admin <email protected>
>>
>>
>> --
>>
-------------------------------------------------------------------
>>--
>> Please check "http://www.mysql.com/documentation/manual.php"
before
>> posting. To request this thread, e-mail mysql-
>>thread51653 <email protected>
>>
>> To unsubscribe, send a message to:
>> <mysql-unsubscribe-noirotr=forges-
>>courcelles.fr <email protected>>
>>
>> If you have a broken mail client that cannot send a message to
>> the above address (Microsoft Outlook), you can use:
>> http://lists.mysql.com/php/unsubscribe.php
>>
>>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: php-general-unsubscribe <email protected>
>For additional commands, e-mail: php-general-help <email protected>
>To contact the list administrators, e-mail: php-list-
>admin <email protected> 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-general-unsubscribe <email protected>
For additional commands, e-mail: php-general-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>