Archive for the 'mysql' Category
Flickr.com - PHP/mysql case study
Introduction
Carl Henderson from Flickr.com, a very popular photo blogging service has released a pdf (not sure exactly when this was actually released) detailing the issues they faced with having a high-traffic website.
original PDF can be downloaded here
some interesting points taken from this pdf are below.
Classes, libraries, and systems used
1) smarty for templating
2) PEAR and XML for Email parsing
3) perl for controlling
4) imagemagick for image processing
5) mysql (4.0/innoDb)
6) java, for node service
7) apache 2 and redhat linux
8) 60,000 lines of PHP code
9) 60,000 lines of templates
10) 70 custom smarty functions/modifiers
11) 25,000 DB transactions/second at peak
12) 1000 pages per second at peak
unicode support
1) UTF-8 pages
2) CJKV support
Tips: don’t use HtmlEntities(). Also, Javascript has patchy Unicode Support
Why php was used
1) Everything can be stored in the database, including smarty cache
2) a “shared nothing” approach (as long as php sessions were not used)
Mysql usage
Select’s: 44,220,588
Insert’s: 1,349,234
update’s: 1,755,503
delete’s: 318,439
13 select’s per Insert, Update, and delete
Tips: many of the tables that needed to be full-text searched were de-normalized. This does waste space, but because it allowed for little or no joins, it made searching much faster.
10 database speed tests
By Justin Silverton
I came across the following 10 benchmark tests covering:
SQLite version 3.3.3
SQLite version 3.3.3
SQLite version 2.8.17
SQLite version 2.8.17
PostgreSQL version 8.1.2
MySQL version 5.0.18
FirebirdSQL version 1.5.2
About the hardware/database settings used:
All databases were installed with default settings.
Tests were run on 1.6GHz Sempron with 1GB of ram and 7200rpm SATA disk running Windows 2000 + SP4 with all updates applied.
Test 1: 1000 INSERTs
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t1 VALUES(1,13153,’thirteen thousand one hundred fifty three’);INSERT INTO t1 VALUES(2,75560,’seventy five thousand five hundred sixty’);… 995 lines omittedINSERT INTO t1 VALUES(998,66289,’sixty six thousand two hundred eighty nine’);INSERT INTO t1 VALUES(999,24322,’twenty four thousand three hundred twenty two’);INSERT INTO t1 VALUES(1000,94142,’ninety four thousand one hundred forty two’);
SQLite 3.3.3 (sync):
3.823
SQLite 3.3.3 (nosync):
1.668
SQLite 2.8.17 (sync):
4.245
SQLite 2.8.17 (nosync):
1.743
PostgreSQL 8.1.2:
4.922
MySQL 5.0.18 (sync):
2.647
MySQL 5.0.18 (nosync):
0.329
FirebirdSQL 1.5.2:
0.320
Test 2: 25000 INSERTs in a transaction
BEGIN;CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t2 VALUES(1,298361,’two hundred ninety eight thousand three hundred sixty one’);… 24997 lines omittedINSERT INTO t2 VALUES(24999,447847,’four hundred forty seven thousand eight hundred forty seven’);INSERT INTO t2 VALUES(25000,473330,’four hundred seventy three thousand three hundred thirty’);COMMIT;
SQLite 3.3.3 (sync):
0.764
SQLite 3.3.3 (nosync):
0.748
SQLite 2.8.17 (sync):
0.698
SQLite 2.8.17 (nosync):
0.663
PostgreSQL 8.1.2:
16.454
MySQL 5.0.18 (sync):
7.833
MySQL 5.0.18 (nosync):
7.038
FirebirdSQL 1.5.2:
4.280
Test 3: 25000 INSERTs into an indexed table
BEGIN;CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));CREATE INDEX i3 ON t3(c);… 24998 lines omittedINSERT INTO t3 VALUES(24999,442549,’four hundred forty two thousand five hundred forty nine’);INSERT INTO t3 VALUES(25000,423958,’four hundred twenty three thousand nine hundred fifty eight’);COMMIT;
SQLite 3.3.3 (sync):
1.778
SQLite 3.3.3 (nosync):
1.832
SQLite 2.8.17 (sync):
1.526
SQLite 2.8.17 (nosync):
1.364
PostgreSQL 8.1.2:
19.236
MySQL 5.0.18 (sync):
11.524
MySQL 5.0.18 (nosync):
12.427
FirebirdSQL 1.5.2:
6.351
Test 4: 100 SELECTs without an index
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b=100 AND b=200 AND b=9700 AND b=9800 AND b=9900 AND bTest 5: 100 SELECTs on a string comparison
SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%one%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%two%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%three%’;… 94 lines omittedSELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%ninety eight%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%ninety nine%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%one hundred%’;
SQLite 3.3.3 (sync):
4.853
SQLite 3.3.3 (nosync):
4.868
SQLite 2.8.17 (sync):
4.511
SQLite 2.8.17 (nosync):
4.500
PostgreSQL 8.1.2:
6.565
MySQL 5.0.18 (sync):
3.424
MySQL 5.0.18 (nosync):
2.090
FirebirdSQL 1.5.2:
5.803
Test 6: INNER JOIN without an index
SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
SQLite 3.3.3 (sync):
14.473
SQLite 3.3.3 (nosync):
14.445
SQLite 2.8.17 (sync):
47.776
SQLite 2.8.17 (nosync):
47.750
PostgreSQL 8.1.2:
0.176
MySQL 5.0.18 (sync):
3.421
MySQL 5.0.18 (nosync):
3.443
FirebirdSQL 1.5.2:
0.141
Test 7: Creating an index
CREATE INDEX i2a ON t2(a);CREATE INDEX i2b ON t2(b);
SQLite 3.3.3 (sync):
0.552
SQLite 3.3.3 (nosync):
0.526
SQLite 2.8.17 (sync):
0.650
SQLite 2.8.17 (nosync):
0.605
PostgreSQL 8.1.2:
0.276
MySQL 5.0.18 (sync):
1.159
MySQL 5.0.18 (nosync):
0.275
FirebirdSQL 1.5.2:
0.264
Test 8: 5000 SELECTs with an index
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b=100 AND b=200 AND b=499700 AND b=499800 AND b=499900 AND bTest 9: 1000 UPDATEs without an index
BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a=10 AND a=9980 AND a=9990 AND aTest 10: 25000 UPDATEs with an index
BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE a=2;… 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE t2 SET b=423958 WHERE a=25000;COMMIT;
SQLite 3.3.3 (sync):
1.883
SQLite 3.3.3 (nosync):
1.894
SQLite 2.8.17 (sync):
1.994
SQLite 2.8.17 (nosync):
1.973
PostgreSQL 8.1.2:
23.933
MySQL 5.0.18 (sync):
16.348
MySQL 5.0.18 (nosync):
17.383
FirebirdSQL 1.5.2:
15.542
Test 9: 1000 UPDATEs without an index
BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a=10 AND a=9980 AND a=9990 AND aTest 10: 25000 UPDATEs with an index
BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE a=2;… 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE t2 SET b=423958 WHERE a=25000;COMMIT;
SQLite 3.3.3 (sync):
3.153
SQLite 3.3.3 (nosync):
3.088
SQLite 2.8.17 (sync):
3.993
SQLite 2.8.17 (nosync):
3.983
PostgreSQL 8.1.2:
5.740
MySQL 5.0.18 (sync):
2.718
MySQL 5.0.18 (nosync):
1.641
FirebirdSQL 1.5.2:
2.976
If you want to see some more information on the above and 10 more tests, you can go Here
optimizing mysql tables
By Justin Silverton
Many times, slow access to a mysql database can be the result of Badly defined or non-existent indexes and fixing these can often lead to better performance. Here is an
example table:
CREATE TABLE address_book (
contact_number char(10) NOT NULL,
firstname varchar(40),
surname varchar(40),
address text,
telephone varchar(25)
);
example query: SELECT firstname FROM address_book WHERE contact_number = ‘12312′;
This will retrieve the firstname of a person added to the address_book table, based on the contact number.
Without any kind of indexes added to this table, mysql will have to search through each row to find the item that you would like to find, which is very inefficient.
Optimizing your table
There is a built-in command called explain, that can show you what, if any, indexes that are being used to retrieve results.
example:
EXPLAIN SELECT firstname FROM address_book WHERE contact_number = ‘12312′;
This will return a set of results that will tell you how myql is processing the results
table: The table the output is about (will show multiple if you have joins)
type: The type of join is being used.best to worst the types are: system, const, eq_ref, ref, range, index, all
possible_keys: Shows which possible indexes apply to this table
key: And which one is actually used
key_len: The length of the key used. The shorter that better.
ref: The column, or a constant, is used
rows: The number of rows mysql believes it must examine to get the data
extra: You don’t want to see “using temporary” or “using filesort”
and index can be added to the above example table using the following command:
ALTER TABLE address_book ADD INDEX(contact_number);
you can also add an index on only part of a varchar. In the following, I will add an index on only 8 of the 10 characters.
ALTER TABLE address_book ADD INDEX(contact_number(8));
Why would you want to do this?
Indexes do increase performance in the right situations, but they are also a tradeoff between speed and space. The bigger an index is, the more space it will consume on your harddrive.
Using the query optimizer/analyzer
the following command can analyze your table key distribution to find out the best indexes to use:
analyze table *tablename*
also, another thing to keep in mind is the fact that over time, update and delete operations leave gaps in the table, which will cause un-needed overhead when reading data from your tables.
from time to time, it is a good idea to run the following (which will fix the above issue):
optimize table *tablename*
5 mysql tips
By Justin Silverton
These are some tips that may help you out when dealing with mysql tables (known in 4.1 and below).
1) char and varchar are case sensitive
example:
if you have a table that contains the following:
table newtable (
name varchar(32)
)
name contains the name “John Smith”.
the following statement: “SELECT * from newtable where name=’john smith’ will return our record.
to stop this from happening, use the following when you create your table:
CREATE TABLE newtable (
name VARCHAR(32) BINARY
)
2) Varchar type is limited to 255 characters
3) Varchar trailing spaces are stripped
example: insert into newtable values(’Test with no spaces ‘);
select concat(name, ‘no spaces’) FROM newtable;
output will be: Test with no spacesnospaces
Varchar works this way, because it saves space by stripping the spaces.
if you need to keep the trailing spaces in the data you are adding to a varchar type,
you need to use the text or blob types.
4) operator
The (or) operater is a logical operator
example: select ’string1′ ’string2′ will not return ’string1string2′
5) function parameters
This issue has caused me many headaches in the past, and I am not sure why this issue was never fixed. If there is a space
between the paramater list and an internal function that you want to execute, it will return an error.
example: select min (my_field) from mytable wil return an error, while select min(my_field) from my_table will not
another article on oracle/mysql
“In November, I looked at Oracle’s purchase of InnoDB, as well as their release of Oracle Express, and the effect on MySQL. In short, I concluded that the positives for Oracle were clear, but that MySQL must be feeling outmaneuvered. Since InnoDB, with its full transactional capability, has become an integral part of their product offering, I suggested that both BerkleyDB (their first transactional storage engine, but which has never got beyond so-called gamma status) and MaxDB (what was SAPDB, which is a fully-featured database but does not yet integrate well with MySQL’s other products) had both become critically important.
Shortly after Oracle purchased InnoDB, MySQL vice-president Richard Mason acknowledged that MySQL were “evaluating options to replace that functionality in some way,” but that they were “not at the point yet where we can go public with what that plan is but we will be shortly.”
However, since then Oracle has upped the ante even more. On February 14th, Oracle purchased Sleepycat Software, who provides MySQL with the Berkeley DB transactional storage engine. Furthermore, in the last few days, rumors have been flying that Oracle also intends to purchase both Zend (’the PHP Company’), as well as JBoss. I believe this is another smart series of moves by Oracle that can only benefit them……”
The rest can be found Here