[Author Prev][Author Next][Thread Prev][Thread Next][Author Index][Thread Index]

Re: [school-discuss] Web-based School Administration...

I've had pretty good experience with MySQL in a heavy, write intensive environment.  Our system monitoring software consolidates system logs over the network from several machines (maybe 6) to a single larger computer running syslogd interfaced into MySQL database.  Over a two year period we've logged more than 19 million entries.  During busy times of the day (i.e. maillog is busy from the servers, apache access log, popper messages, and a massive packet filter log) I've seen transactions on the order of 80 to 100 per second.

The table structure is pretty simple, but then again if you are doing massive volumes of data, it should be simple.  I'm able to accomplish pretty good read times for selected queries.  However, some queries take upwards of 15 minutes.  Most likely that is becuase I'm not fully indexed.  (Actually doing that right now;  I'll let the list know when I've finished what the times are)  Check out some of the following:

mysql> desc syslog;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| ID            | int(11)       |      | PRI | NULL    | auto_increment |
| Machine       | varchar(50)   |      | MUL |         |                |
| FacilityLevel | varchar(25)   |      | MUL |         |                |
| Date          | timestamp(14) | YES  | MUL | NULL    |                |
| message       | varchar(255)  | YES  | MUL | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select count(*) from syslog;
+----------+
| count(*) |
+----------+
| 19253975 |
+----------+
1 row in set (0.00 sec)

mysql> select ID, Machine, FacilityLevel, Date, message from syslog order by Date desc limit 10;
+----------+------------+---------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID       | Machine    | FacilityLevel | Date           | message                                                                                                                                               |
+----------+------------+---------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| 19253975 | 10.0.0.1   | mail.info     | 20030826111023 | sendmail[4599]: h7QG60e04576: to=root, ctladdr=root (0/0), delay=00:00:00, xdelay=00:00:00, mailer=local, pri=30081, dsn=2.0.0, stat=Sent             |
| 19253974 | 10.0.0.1   | mail.info     | 20030826111022 | sendmail[4599]: h7QG60e04576: to=root, ctladdr=root (0/0), delay=00:00:00, xdelay=00:00:00, mailer=local, pri=30081, dsn=2.0.0, stat=Sent             |
| 19253973 | 10.0.0.1   | mail.info     | 20030826111022 | sendmail[4576]: h7QG60e04576: from=root, size=81, class=0, nrcpts=1, msgid=<XXXXXXXXXXXXXXXXXXXXXXXXXXx>, relay=root@localhost |
| 19253972 | 10.0.0.1   | mail.info     | 20030826111022 | sendmail[4576]: h7QG60e04576: from=root, size=81, class=0, nrcpts=1, msgid=<XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>, relay=root@localhost |
| 19253971 | 10.0.10.50 | kern.info     | 20030826111022 | kernel: cipcb3: cipe_recvmsg                                                                                                                          |
| 19253970 | 10.0.10.50 | kern.info     | 20030826111022 | kernel: cipcb3: cipe_sendmsg                                                                                                                          |
| 19253969 | 10.0.10.50 | daemon.notice | 20030826111022 | ciped-cb[1536]: keepalive timeout                                                                                                                     |
| 19253968 | 10.0.10.50 | kern.info     | 20030826111022 | kernel: Packet log: input ACCEPT eth1 PROTO=17 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX L=300 S=0x00 I=0 F=0x4000 T=43 (#28)                              |
| 19253967 | 10.0.10.50 | kern.info     | 20030826111020 | kernel: cipcb0: cipe_recvmsg                                                                                                                          |
| 19253966 | 10.0.10.50 | kern.info     | 20030826111020 | kernel: cipcb0: cipe_sendmsg                                                                                                                          |
+----------+------------+---------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql>




David Frankson wrote:
I'm sure that the open source dbs could handle the read-load of our product,
but the write-load kills them.  We support teacher period attendance and
teacher gradebook scaled up to the mega district (+100k students).  If
attendance can't be taken at nearly instantaneous times, teachers will have
the product thrown out.  At that level you need to perfectly optimize the
table schema, and index design for the quickest writes possible, and then
analyze the locking patterns and query execution paths during a full
transaction load.  SQL Server's development and optimization tools are
unequaled, and their query engine is impressive.  I love to rip on Microsoft
as much as every other open source user, but SQL Server has earned my
respect.

Dave


----- Original Message ----- 
From: "Myles O'Meara" <milo@fullsite.com>
To: <schoolforge-discuss@schoolforge.net>
Sent: Monday, August 25, 2003 9:26 PM
Subject: Re: [school-discuss] Web-based School Administration...


On Tue, 2003-08-26 at 11:38, Leon Brooks wrote:
  
On Fri, 22 Aug 2003 01:23, David Frankson wrote:
    
As far as databases go, we use SQL Server, and I am very happy with
it.  Its impressive to see 100k+ student districts and states running
hundreds of transactions per second.  At times we have managed to
push SQL Server to its limits, and we have not been able to get that
kind of output out of any of the open source databases.
      
I'm surprised that you haven't been able to get that out of PostgreSQL
or ibFirebird. MySQL, I could understand; it's fine for lots of simple
reads but anything complex or involving many writes and it all goes
wahoonie-shaped. Were you running through ODBC or direct? That makes
about a 50% performance difference in some circumstances.

Cheers; Leon
    

I'm also surprised that you have not been able to get the open source
databases to perform to a level equal or better than SQL server
(Sybase). From what I have seen the main two - MySQL and Postgres have
very good performance specs. I have found both to be very reliable
though somewhat different in performance.
I just looked on the mysql.com site to see what their latest news was.
This is just one article that highlights a point made above

"The Associated Press (AP), the world's oldest and largest news agency
with a global daily reach of 1 billion people, is leveraging the MySQL®
database to power its AP Hosted News service. The AP's mission critical
MySQL-based application serves up a massive amount of AP hosted content
and supports hundreds of thousands of transactions every day for 11,000
concurrent users."

That's not bad for an open source database.
Myles
  

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
   Quinn Team Incorporated
  Micah T. Quinn - President
    (Voice) 281.465.4311
    (Fax)   281.465.4434
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=