Improve Performance

Yii 1.1 General Discussion
1

Hello

I have a script that must run as fast as possible , This script is about 50 lines

This script have 4 parts

1- get inputs and validates , take about [color="#2E8B57"]1 Microsecond[/color]

2- authenticates , take about [color="#FF0000"]1 second[/color]

3- sending download file , take time depend on file size but not important it is about [color="#2E8B57"]5 milliseconds[/color] usually

4- log usages , take about [color="#FFA500"]0.1 second[/color] (it is just new model , initialization and model->save())

"Part 2" or "authenticates" is about 8 lines , 7 lines take about [color="#2E8B57"]10 Microseconds[/color] but 1 line take about [color="#FF0000"]1 seconds[/color]

that means 49 lines take about [color="#FFA500"]0.1 second[/color] and 1 line take [color="#FF0000"]1 second[/color]

1 second is very large for this script because this script must run by many user (about 10,000 user) and each user run this script 100-1000 times/day

The line that take [color="#FF0000"]1 second[/color] is :




$service = Service::model()->find($criteria);

and $criteria is




$criteria = new CDbCriteria;

$criteria->compare('LOWER(t.username)', strtolower($username));

$criteria->compare('LOWER(t.password)', strtolower($password));

$criteria->compare('t.enable', 1);

1- [size="5"]How can I improve this script to run faster ??[/size]

2- Usually how long Yii ActiveRecord 's [color="#FF0000"]find()[/color] method takes ?

2- Usually how long Yii ActiveRecord 's [color="#FFA500"]save()[/color] method takes ?

2

ActiveRecord find() needs to access a database, execute the query, fetch and return data. So, to improve the script, you need to find exactly where is the problem. You can enable Yii debuger and see how is this Query performing. This can help with the first question.

If the problem is not only the query, but the auth process at all, maybe you need to consider options like caching data or to store a session for users (to avoid this excess of auth requests).

For questions 2 and 3, this will vary depending on your database configuration, if you have index on these tables, if database is in a different server, you will have to consider network between and etc.

3

I enable Yii debugger




defined('YII_DEBUG') or define('YII_DEBUG',true);

now how can I see how my code running and how take time ?

4

This:




$criteria = new CDbCriteria;

$criteria->compare('LOWER(t.username)', strtolower($username));

$criteria->compare('LOWER(t.password)', strtolower($password));

$criteria->compare('t.enable', 1);

Will produce this sql (or similar)




SELECT * FROM user WHERE LOWER(t.username) = 'aaaa' AND LOWER(t.password) = 'bbb' AND t.enable = 1;

First, why do you need to select all the columns from the table? Doesn’t a “SELECT username FROM …” suffice?

Secondly, the bottleneck, your ‘user’ table must have an index on the columns: username, password, enable. in the exact order. But here’s the tricky part, because you use a function in your where clause, mysql cannot use the index even if it has it, so it ends up scanning the entire table, which as you say, if it has 10k rows, takes that second.

In order to fix it, just change the character set of your tables into utf8_general_ci so that you don’t have to run comparisons like WHERE LOWER(a) = ‘a’.

Next, create a covering index as i said, on the columns: username, password, enable

Then, run an EXPLAIN on your query to make sure it uses the proper index:


explain select username, email from user where username = 'a' and password = 'b' and enable = 1;

If the output of explain looks okay and you see the index being used, then simply change your query to:




$criteria = new CDbCriteria;

$criteria->select = 't.username';

$criteria->compare('t.username', $username);

$criteria->compare('t.password', $password);

$criteria->compare('t.enable', 1);

And be done with it ;)

5

Two tips:

  1. Use http://www.yiiframework.com/extension/yiidebugtb/ .

  2. Use XDebug - see http://www.yiiframework.com/wiki/487/profiling-using-xdebug .

Usually you will get enough information with the first step - locate the SQL request.

When YII_DEBUG is off, logging should take a limited amount of time.

You can also enable caching, etc.

For your information, I have built a Yii application where the generation of the page (with a lot of features) requires a lot of queries. I just tried one for an account and these are the statistics (with debugging on, yiidebugtb active): 4198 queries, 2.834 seconds to generate the page. So averaging the total time over the queries that is about 0.7 ms per query on an intel® Xeon® CPU E3-1245 V2 @ 3.40GHz .

To achieve performance, I have analyzed several queries, adapted indexes and the way I made the queries (still using the AR functionnality), enable caching (query caching, APC, …).

Field order in the indexes is important. In your case, I would recommend an index which is enable, username, password and make sure that the username and password are stored as lowercase in the database to avoid the call to LOWER() - if you need to keep the original mixed case, you can add a duplicate column.

6

This isn’t really related but … I really hope you’re not storing your user’s passwords unencrypted :huh:

7

I have 2 authentication system , in first I have encrypted password and in second unencrypted

There is no security problem

8

twisted1919 and le_top thanks for your help

I will try to improve my script and will ask here if have a question

9

As long as you can un-encrypt the password(maybe using a key), you do have a problem.

10

I install yiidebugtb

and yiidebugtb told me "Opening DB connection" take about 1 seconds and other DB Queries take about 0.1 seconds

Why "Opening DB connection" need 1 seconds?

my database is MySQL , and engine is InnoDB , now I have less that 10 records in my table

11

for authentication I have 2 table , one is for user and one is for service

in user table I encrypt passwords

in service I save password un-encrypted , I need save passwords unencrypted or atleast two-way encryption

12

The connection is made only once per application cycle so i wouldn’t worry about that too much, though one second is pretty much.

You can update your php to version 5.4 because it uses mysqlnd (http://dev.mysql.com/downloads/connector/php-mysqlnd/) which should be faster.

If your database server is remote, then that 1 second makes sense.

Also if you are on a windows system it makes sense since things will behave differently than on a linux machine.

13

So you’re storing an API key? That’s as good as a password and should see the same level of protection.

14

Not in all cases. Btw: MySQL-nd became the default in 5.4. But it’s been available a lot earlier ;)

While it’s true that Windows behaves a lot different, an entire second for establishing a db connection is just way too much as this is actually a cheap operation. See this answer on StackOverflow. if that doesn’t help, I’d advise establishing a thread cache.

15

I solve DB problems and now my script run better , now just 0.1 seconds need for get inputs and validates , authenticates ,sending very small files (less than 50kB),log usages

but for sending file about 1MB , it take 1.3 seconds and it is large time

for sending files , Now I use this class

http://www.phpclasses.org/browse/file/9051.html

I think this script is not optimized ,

what is Fastest Way to Serve a File Using PHP?Speed is critical and may be I need Resumable downloads

16

Great that you solved your DB issues. Maybe you could share what you fixed so that others seraching on this forum might get some ideas from it.

17

The most sophisticated class I know of is this one. For optimal speed, though, you may consider to take PHP out of the equation alltogether.

18

It it not shareable because , my problem is not once , I change several files to optimize my script

19

GDownload class it better but it have 2 problem

1- The resume not works even author told works

2- This need long time for downloading , for 2 MB file 1.5 Sec needed

When I use Directly apache download , 2MB just need 0.06sec

I need better way for downloading that send 2MB about 0.1 sec

20

What’s keeping you from using Apache directly?