Re: Utilize maximum system resources for better performance

[Ronald Brown]

Rick James Wrote:
-------------------------------------------------------

> > query_cache_type = 1
>
> In servers with 'continual' writes, the query
> cache is usually a waste. Each write to a table
> will purge all entries in the QC for that table.
>

disabled query_cache_type = 1

now my queries takes 1.xx seconds while previously they were takeing in 0.0062649250030518 seconds
and its on no load, just one user running single query, i tried runiing various types, but still query takes, 1.xx time

> > need to update all rows (6mil+) once a day
>
> Is that `content`? Are you updating just a few
> column(s)? Perhaps votes and dates?
>
> I would recommend (if practical) that you
> 'vertically partition' that table. Move the
> things you need to update into a second table.
> The two tables would share the PRIMARY
> KEY(record_num) (except that it would be
> AUTO_INCREMENT in only one table). The new table
> would be something like 1/20th the size, hence the
> nightly effort to update it would take about
> 1/20th the time. Also move any indexes that are
> needed.
>
> JOINing on record_num would let you "put them back
> together" for whatever queries need both sets of
> columns.
>
> Caveat: It will take a long time to do the split.
> pt-online-schema-change may provide a way to do
> it with virtually zero downtime.
>

i will surely implement it in near future as it needs to change queries in my whole app.
but there are already queries which do multiple/ 2-3 table joins on vairous tables,
wont this effect performance if query has 1 more join ?

> > "hash" is hex string with 40 length
>
> Yes it is 40 _characters_, but no it is still not
> 40 _bytes_, but rather 120 (because of utf8).
> Assuming it is only ascii characters:
>
> ALTER TABLE content MODIFY COLUMN hash CHAR(40)
> CHARACTER SET ascii COLLATE ascii_general_ci NOT
> NULL
>
> That will shave about 1GB off `content`, which now
> takes about 10GB.

this looks promising, implementing it. , so basically hex chars falls unders ascii so we can set ascii character set. and if the field contains non ascii characters then we need to use utf which uses more bytes per char.

learning new things everyday. Thanks for your input, i really appreciate it.