[Ilugc] Database Application
- From: lug@xxxxxxxxxxxxxxxxxx (Chandrashekar Babu)
- Date: Mon Jul 26 22:26:58 2010
Hi,
I have developed an php appliaction with mysql as background. This
applications main part is data entry, it handles huge no of data, same way
the application is accessed by 3000 people at same time, my db traffic
becomes high and application becomes slow, can you suggest me some of
methods so that i can optimize the database. say like creating stored
procedures.
If your PHP application could be refactored, try using HEAP/MEMORY
tables for MySQL database. Logic could be something like below:
- Check for existence of MEMORY table on request and if exists,
perform all SELECT from the table. If MEMORY table does not
exist, perform the following:
CREATE TABLE mem_table ENGINE=MEMORY AS
SELECT fields FROM disktables WHERE conditions_as_apply;
and then, SELECT fields FROM mem_table;
- For all INSERT, UPDATE, DELETE operations, perform the same
on the generated MEMORY table too. Based on the database
design, there might be situations where you might re-generate
the MEMORY table.
Better yet, create STORED procedures and TRIGGERS for updating
data to replicate/regenerate your MEMORY tables along with disk
tables. For fetching data, create a SQL FUNCTION to abstract the logic
of accessing contents from the MEMORY table.
Your PHP application can call the SQL FUNCTION to access data, while
updates can be done by calling STORED PROCEDURES.
Of course, the above methods call for a thorough refactoring of your
database
design and also your application - best done if your application is not too
big/or
database scalability is a big concern.
Other options are to check if your SELECT queries are expensive (full table
scans, inefficient indexes and so on). Try enabling general logging on your
database, analyse your queries and try running EXPLAIN SELECT on these
queries to get a clearer picture.
Deployment level optimizations might include fine-tuning your MySQL
configuration which helps quite a lot of times.
Using memcached/APC cache are other options (which I would not
recommend without fine tuning your Database and queries in the
first place).
Good luck!
Cheers,
Chandrashekar.
--
Chandrashekar Babu.,
Independent FOSS Technologist and Corporate Trainer.,
http://www.chandrashekar.info/
+91-9884386075, +91-9543019934,
+91-44-32216664
Other related posts: