MySQL 5.0 is out

[TrafficProducer]

MySQL 5.0 is out:

A DBA's Perspective

MySQL 5.0 represents a huge leap forward for the world's most popular open source database management system. While MySQL has been the database of choice for managing high-volume web sites and embedded database applications for years, version 5.0 provides exceptional new functionality that paves the way for larger adoption at the enterprise level. Advancements in the areas of application development, transactional processing, data integrity, and manageability put the MySQL database server on par with proprietary database vendors whose costs are many times more.

This paper first provides a technical overview of MySQL from the DBAs perspective, and then focuses on new features in 5.0 that demonstrate how the latest version of MySQL builds upon an already award-winning database engine to provide more industrial-strength capabilities that help to manage the needs of demanding transactional processing systems, large data warehouse applications, and high-traffic web sites.

MySQL 5.0 is out:

[brian.mark]

5.0 has been out for a while now. I've been testing it on my development box at home and haven't noticed too much that I'm getting excited over. The triggers are sort of cool, but not so much for websites. Same with stored procedures. It'll make some of my accounting statistics easier from multiple programs, but that's about it.

The only reason I'm really looking at it is for this statement: "Reduced hardware costs by incrementally adding several low-cost commodity systems vs. upgrading high-cost mainframe-class systems". I downloaded it sortly after my post on November 5th and haven't had any problems, but also haven't done any real heavy load testing.

Brian.

[TrafficProducer]

but also haven't done any real heavy load testing.

It would be interesting to know if SQL 5 is any faster for searching, (Queries).

I use FULLTEXT method to check about 1Gb data and think it's too slow at 4 to 10 seconds to look through the data and return a webpage. (PERL DBI)

I'm pritty new at SQL so their may be something else I could do to increase speeds? May be a look at C ???

[brian.mark]

Originally Posted by TrafficProducer

It would be interesting to know if SQL 5 is any faster for searching, (Queries).

I use FULLTEXT method to check about 1Gb data and think it's too slow at 4 to 10 seconds to look through the data and return a webpage. (PERL DBI)

I'm pritty new at SQL so their may be something else I could do to increase speeds? May be a look at C ???

5 is a bit quicker for fulltext (depending on how your indexes are actually set up). In some cases, dramatic. Your use of Perl DBI or C really doesn't have any bearing, however. It won't matter what language you speak if the searching itself takes too long. Try the query directly in the database instead of via a program and watch the time. I bet that's most of the time, not the script being accessed.

Brian.

[TrafficProducer]

Try the query directly in the database instead of via a program and watch the time. I bet that's most of the time, not the script being accessed.

What a great idea... Why didn't I think of doing this!

I have do this a couple of times just to check the tables out abit, but not really for timing.

Sometimes I miss the obvious.

Thanks for the idea :)

[TrafficProducer]

Originally Posted by TrafficProducer

What a great idea... Why didn't I think of doing this!

I have do this a couple of times just to check the tables out abit, but not really for timing. (using phpMyAdmin)

Sometimes I miss the obvious.

Thanks for the idea :)

[kgun]

Brian.mark wrote:
5.0 has been out for a while now. I've been testing it on my development box at home and haven't noticed too much that I'm getting excited over. The triggers are sort of cool, but not so much for websites. Same with stored procedures. It'll make some of my accounting statistics easier from multiple programs, but that's about it.

Are you aware of the following:
For example, MySQL 5.0 adds a mode that enables strict data-type checking. Thus, the database will issue errors for invalid dates, numbers and strings without requiring additional programming.

The new MySQL also include an ANSI SQL-compliant Data Dictionary to access metadata and a new library for arithmetic that will give high accuracy on financial and mathematical operations.

MySQL 5.0 also packs new storage engines, tools and extensions. For example, the Archive Storage Engine is designed for storing large amounts of data without indexes in a very small footprint. That's intended to handle historical data to keep enterprises in compliance with audits, such as with Sarbanes-Oxley regulations. (my underline)

The new MySQL 5.0 also includes an Instance Manager tool for starting and stopping the MySQL Server both locally or remotely.
http://www.eweek.com/article2/0,1895,1876702,00.asp

and

Oracle May Want to Spur MySQL's Growth, not Hurt It
http://www.eweek.com/article2/0,1895,1871112,00.asp

a new library for arithmetic that will give high accuracy on financial and mathematical operations.

That may drastically reduce computation time for large complex databasedriven sites. Aritmetich operations with inline functions (operators) directly on the datastructure are (ordinarily) most efficient.

Related link:
http://www.sitepoint.com/forums/showthread.php?t=313377

[kgun]

I'm pritty new at SQL so their may be something else I could do to increase speeds? May be a look at C ???

I do not know Perl, but C is as far as I know the closest you come to ASM. C++ has too much overhead. I once saw a fractal programmed in C. Then it was programmed in ASM and run 100! times faster.

In (earlier verions of) Borland C++ you could use

ASM {Some code;}

It should be possible to run a case if test for compiler version. Use ASM if intel processer else up to you.

This is not so important on small datastructures and small systems, but if you have databases of Tb's (some may even have databases of Pb's) then there can be a significant difference in execution speed.

There is a good book Numerical recipes in C and another Mumerical analysis in Assembler. Both come with disks of good source code. I have both.

Sorting algorithms is very important. As I say in the last thread in the post above, some run in linear time. There is also a strong similarity between sorting and searching.

I know your librariies, and they are large and growing. In case of exponential growth, you have to be forward looking.

Related link about XP and minimalism:
http://www.sitepoint.com/forums/show...3&page=3&pp=25

[brian.mark]

kgun, those aren't features that most web developers will ever care about. For an accounting system, sure. For some pieces of an enterprise system (ERP, CRM, etc.) it's nice. But why will a normal web developer care? They won't.

Most MySQL installs are already overkill. People are just looking for a way to store a price in one spot and call it on many pages of a website. There's no need for a relational database for that.

C may be the closest thing to assembly, but if the database is taking all of the time it won't matter one bit. I've played around with compiling some of my slower running scripts in the past and found that the database was the hold-up, so I gained nothing by increasing the complexity of the program itself. Perl is very close to C, and for a normal web page it is much more functional. No need to recompile just to see what happens, etc.

Brian.

[kgun]

kgun, those aren't features that most web developers will ever care about. For an accounting system, sure. For some pieces of an enterprise system (ERP, CRM, etc.) it's nice. But why will a normal web developer care? They won't.

I very, very much disagree. Are you old enough to know what, I think it was Bill Gates (but I am not sure) said in the mid 1980's about the IBM At and the 20 Mb harddisk? I shall not say more since I can not remember the exact words and who said them, but they have been very much sited.

1. To be more precise about the ASM book I mentioned.
Don Morgan: Numerical Methods: Real Time and Embedded Systems Programming

FROM THE CRITICS
Booknews
The mathematical techniques professional assembly-language programmers and embedded systems engineers need to write numeric applications for real-time embedded systems are accompanied by a disk (in MS/PC-DOS format) which contains the routines presented in the book plus a simple C shell that can be used to exercise them. Annotation c. Book News, Inc., Portland, OR (booknews.com)


What is on the Disk The files on the disk follow those in the book.

FXMATH.ASM contains most of the fixed point routines
used throughout,except for those written especially for the floating point routines. These exist in the files where they are used.

FPMATH.ASM contains the basic arithmetic functions for floating point. There are also some ancillary functions here,such as those that compare, find the ceiling and floor and absolute value of a fl

I0.ASM is where all the conversion routines are kept.

TRANS.ASM contains the routines for the elementary
functions as well as the pseudo-random number generator.

TABLE.ASM is a data file containing the various table used by the other modules, though principally by TRANS.ASM.

MATH.C is a C shell that may be used instructive in
how to call these routines from a higher level language. It was used initially to test the routines. It offers very little in the way of debugging

SPECTRAL.C contains the Chi-square test and graphics
test for the random number generator.

SAMPLES has several basic arithmetic routines in
assembler for other processor than the 8086. It is on the disk for those who might be interested.

Numerical Recipes in C http://www.library.cornell.edu/nr/bookcpdf.html
is the same as this new published in C++ http://www.cambridge.org/uk/computerscience/recipes/

Related links:
http://www.nr.com/
http://www.numerical-recipes.com/forum/

[brian.mark]

What does any of that have to do with the database features being useful for a web developer? The way I read it, you're talking about assembly and not databases at all.

Here's a benchmark for you if that's what you were really talking about:

Perl with 97 selects to generate a sales report: 17.2046 seconds.

C code with the same 97 selects to generate a sales report: 17.2032 seconds.

Sum of the time for the statements to execute: 16.9950 seconds.

Sure, .2096 vs .2082 is a difference, but the database is most of the time in generating most web pages, not the language used. If someone is going to hang around for 17.2032 seconds to see that report, then surely they'll stick around an extra .014 seconds to see it.

As to your comment about size of the disk (I think that's the only part where you addressed my comment that you bolded at the beginning of your post), on a web server that's not the issue. For compliance, you need to keep transactional data. That's not allowed to be stored on a web server in the first place, so unless you're violating merchant agreement TOS and doing that anyway, the only part that a website needs to track is what products are on my site right now and where they go on the site.

Was there some other reason you think a web developer needs to look at 5.0?

Brian.

[kgun]

I'm pritty new at SQL so their may be something else I could do to increase speeds? May be a look at C ???

I have seen slow SyBase SQL systems in practice. Multiple Oilfields and Oil plattforms with a central SQL database where every little tag on the plattform had a tag in the database.

In one of the above mentioned posts I wrote:
You have to know the following:

1. Everything digital is a bitstream (dynamic or static).
2. In large projects you have to figure out bottlenecks.
- Memory.
- Connections
- Processor speed.
- Lenguage speed ((Assembler), C is is faster than C++). But some say
that processor speed is much cheaper than programming time. There
is a tradeoff.
- Disk capacity.
3. Generally, minimalise connections.
4. Use distributed processing (on multiple servers) only in really large
projects.
5. I agree, operate on the the data(base) structure where possible.
6. Do you use 1 --> n relations or k --> n relations.
7 Use (memory) references (pointers) in stead of copies where possible.
9. Last but not least, the algorithm and datastructures you use are
important. E.g. some sorting algorithmes run in n*log(n) time, while
others run in linear time, e.g. counting sort and radix sort in some cases.
A lot of databaseoperations are sorting or sorting related, so be sure that
you use the most effective algorithm.
http://www.sitepoint.com/forums/showthread.php?t=313377


Here:
http://www.acomputerportal.com/
http://www.agreatportal.com/index.html

some examples of the portals TrafficProducer builds. The web is increasing exponentially. I do not know how he indexes / archives sites and / or pages.

Depending on storage and use, the burden on the system may increase very fast. It is also difficult to foresee future usage in the digital world that is (after all) in its childhood.

Effective digital logistics http://multifinanceit.com/it/logistics/logistics.htm is becoming increasingly important.

[TrafficProducer]

ASM and run 100! times faster.

Ahh! Memories.. :)

I first used Assembly on the Sinclair ZX80.

Then did some Assembly on a 286, (MS DOS 4, VGA machine), mixed with C, and the speeds did increase 100x compared with BASIC. Speed increased by writing/reading files in Blocks of data instead of Character by Character as well.

But aren’t we suppose to talk in Higher Language = More close to Natural Language, (English). At lease where possible.

e.g.

a=a+25; as apposed to LDA, 25

Joke, :-
But hey! let's do it in Pure Binary for true speed:-

11000110, 11010010, 10000101... etc...

[TrafficProducer]

Traffic Producer.

I do not know how he indexes / archives sites and / or pages.

Most my sites use PERL grep to search, pull results and consruct pages from a Text file.

This is OK for smaller data files, maybe about 1Mb but for larger files 1Gb SQL appear better, (faster) and easier to sort the results.

[kgun]

I'm pritty new at SQL

I am pritty new to MySQL. Important to get an overview of possibilities and efficient use. I can reccomend the SitePoint forums http://www.sitepoint.com/forums/forumdisplay.php?f=182. There you usually get help in less than 24 hours. I had a problem, posted it there and got help in some hours.

I think their MySQL forum is good and SitePoint sell some good books on PHP and MySQL like this:
http://www.sitepoint.com/books/phpmysql1/ You can domload the first chapters free.


Combine that with the updated documentation for MySQL 5.0 and you should have the right tools.

Religion is difficult to discuss. Personally I think that C (C#) is not a bad choice, but Perl (that I do not know) and PHP may be good enough. PHP is in my view, a quasi (no private, protected and public datatype, no multiple inheritance etc.) OOP language. In the end it depends on functionality and cost effectiveness.

Joke, :-
But hey! let's do it in Pure Binary for true speed:-

11000110, 11010010, 10000101... etc...

Divide and multiply by (2) rotating /shifting on the register and inline assembler code where possible and useful. Anything faster unless yous shall get drowned in 0's and 1's? :-) :-)

Digression to joke: We all look for the new fast Pc or workstation that doubles processor speed. By shifting :-) language we can increase speed by a factor of 100.

Can you C? It is portable.

<Ad begin>
And Simula http://www.simula.no/ still (after 40 years) lives in Norway. We use it for educational purposes. Nearly selfdocumented that language. Show me a bette (easier to learn) OOP language in 2005. Should be used in every introductory course on every IT institute on the globe where OOP is teached. http://xp.meetup.com/13/events/4795977/
<Ad end>

Talk small.

#define { = Begin
#define } = End

:-)

[kgun]

http://home.simula.no/BESTweb/

http://home.simula.no/~simula/se/bestweb/

http://www.simula.no/projects

that may be of interest.